MySQL Optimierung

Anonymous

MySQL Optimierung

Post by Anonymous »

Hallo,

das ganze ist ein bisschen groß geworden, aber dafür ausführlich. Vielen Dank für die Mithilfe.

AMD Opteron 148
3 GB DDR-RAM
Apache2 + PHP 5.2.0-8+etch7
5.0.32-Debian_7etch1-log

Der Server ist kaum ausgelastet. Durchschnittlicher Load ist 0.3
Arbeitsspeicher wird auch nur zu 10-20% genutzt.

Mir gehts in erster Linie um die Geschwindigkeit, da der Server genug Ressourcen hat.

Die Größe aller Datenbanken:

37 MB mit Indexes.
29 MB ohne Indexes.

Jeden Tag kommen etwa 1500 Datensätze hinzu.

Code: Select all

Dieser MySQL-Server läuft bereits 4 Tage, 23 Stunden, 1 Minuten und 53 Sekunden. Er wurde am 08. August 2007 um 22:17 gestartet. 

Traffic                 Ã¸ pro Stunde
Empfangen 80 MiB         684 KiB
Gesendet 604 MiB         5.198 KiB
Insgesamt 684 MiB        5.882 KiB

Verbindungen                ø pro Stunde         %
max. gleich. Verbind. 26      ---                --- 
Fehlgeschlagen 1.357          11,40              0,37%
Abgebrochen 16                0,13               0,00%
Insgesamt 365 k               3.065,38           100,00%

Abfragestatistik: Seit seinem Start wurden 1.417.718 Abfragen an diesen MySQL-Server gesandt.

Insgesamt  ø pro Stunde   Ã¸ pro Minute     Ã¸ pro Sekunde
1.418 k    11,91 k        198,51           3,31 



my.cnf:
----------------------------------------------
[client]
port      = 3306
socket      = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket      = /var/run/mysqld/mysqld.sock
nice      = 0

[mysqld]
user      = mysql
pid-file   = /var/run/mysqld/mysqld.pid
socket      = /var/run/mysqld/mysqld.sock
port      = 3306
basedir      = /usr
datadir      = /var/lib/mysql
tmpdir      = /tmp
language   = /usr/share/mysql/english

skip-external-locking

max_connections = 100
default_table_type    = MYISAM
transaction_isolation = REPEATABLE-READ
max_allowed_packet    = 64M
max_heap_table_size   = 128M
tmp_table_size        = 128M
key_buffer_size       = 128M
sort_buffer_size      = 8M
join_buffer_size      = 8M
read_buffer_size      = 8M
read_rnd_buffer_size  = 16M
bulk_insert_buffer_size = 32M
myisam_sort_buffer_size = 64M
myisam_max_sort_file_size = 1G
myisam_max_extra_sort_file_size = 1G
myisam_repair_threads = 1
thread_cache_size     = 32
table_cache           = 1024
query_cache_type      = 1
query_cache_size      = 128M
query_cache_limit     = 4M
thread_concurrency    = 8
local-infile          = 0
long_query_time       = 4
log_slow_queries
log_queries_not_using_indexes
log_long_format
myisam_recover
skip-innodb
innodb_thread_concurrency = 8
innodb_buffer_pool_size = 384M
innodb_additional_mem_pool_size = 16M
innodb_data_home_dir = /var/lib/mysql
innodb_log_arch_dir = /var/lib/mysql
innodb_log_group_home_dir = /var/lib/mysql
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_file_size = 100M
innodb_log_buffer_size = 8M
innodb_log_files_in_group = 2
innodb_flush_log_at_trx_commit = 1
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

max_connect_errors    = 10000

[mysqlhotcopy]
interactive-timeout
skip-bdb

[mysqldump]
quick
quote-names
max_allowed_packet   = 16M

[mysql]

[isamchk]
key_buffer_size       = 256M
sort_buffer_size      = 256M
read_buffer_size      = 4M
write_buffer_size     = 4M

[myisamchk]
key_buffer_size       = 256M
sort_buffer_size      = 256M
read_buffer_size      = 4M
write_buffer_size     = 4M
----------------------------------------------

tuning-primer.sh:
----------------------------------------------
 -- MYSQL PERFORMANCE TUNING PRIMER --
             - By: Matthew Montgomery -

MySQL Version 5.0.32-Debian_7etch1-log i486

Uptime = 4 days 23 hrs 29 min 20 sec
Avg. qps = 3
Total Questions = 1425921
Threads Connected = 2

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

SLOW QUERIES
Current long_query_time = 4 sec.
You have 8845 out of 1425940 that take longer than 4 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine

WORKER THREADS
Current thread_cache_size = 32
Current threads_cached = 24
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 2
Historic max_used_connections = 26
The number of used connections is 26% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 1 G
Configured Max Per-thread Buffers : 3 G
Configured Max Global Buffers : 664 M
Configured Max Memory Limit : 4 G
Total System Memory : 3.91 G

Max memory limit exceeds 85% of total system memory

KEY BUFFER
Current MyISAM index space = 9 M
Current key_buffer_size = 128 M
Key cache miss rate is 1 : 40
Key buffer fill ratio = 5.00 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 1 M
Current query_cach_limit = 4 M
Current Query cache fill ratio = 1.00 %
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 8 M
Current record/read_rnd_buffer_size = 15 M
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 8.00 M
You have had 0 queries where a join could not use an index properly
Your joins seem to be using indexes properly
join_buffer_size >= 4 M
This is not advised

OPEN FILES LIMIT
Current open_files_limit = 2158 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 1024 tables
You have a total of 254 tables
You have 509 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 128 M
Current tmp_table_size = 128 M
Of 7463 temp tables, 35% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 7 M
Current table scan ratio = 10 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 1 : 2412
You may benefit from selective use of InnoDB.
If you have long running SELECT's against MyISAM tables and perform
frequent updates consider setting 'low_priority_updates=1'
If you have a high concurrentcy of inserts on Dynamic row-length tables
consider setting 'concurrent_insert=2'.
----------------------------------------------


"Of 7463 temp tables, 35% were created on disk": Ich denk das sollte nicht sein, wieso es passiert kann ich mir nicht genau erklären. TEXT Datentypen verwende ich fast nicht.

Was noch so rot in phpMyAdmin ist:

Code: Select all

Slow_queries 8.801  Anzahl der Anfragen, die länger als long_query_time benötigten. 

Handler_read_rnd 611  Anzahl der Anfragen, eine Zeile basierend auf einer festen Position zu lesen. Dieser Wert wird hoch sein, wenn Sie viele Anfragen ausführen, die erfordern, dass das Ergebnis sortiert wird. Wenn Handler_read_rnd hoch ist, haben Sie wahrscheinlich viele Anfragen, die MySQL zwingen, ganze Tabellen zu scannen, oder Sie haben Joins, die Schlüssel nicht richtig benutzen. 

Handler_read_rnd_next 3.300 k Anzahl der Anfragen, die nächste Zeile in der Daten-Datei zu lesen. Dieser Wert wird hoch sein, wenn Sie viele Tabellen-Scans durchführen. Im Allgemeinen weist das darauf hin, dass Ihre Tabellen nicht korrekt indiziert sind, oder dass Ihre Anfragen nicht so geschrieben sind, dass Sie Vorteile aus den Indexen ziehen, die Sie haben. 

Created_tmp_disk_tables 2.607  Anzahl der (implizit) auf der Platte erzeugten temporären Tabellen bei der Ausführung von Statements. Wenn Created_tmp_disk_tables hoch ist, sollten Sie eventuell die Variable tmp_table_size herauf setzen, damit temporäre Tabellen im Speicher erzeugt werden statt auf der Festplatte. 

Key_reads 79 k Die Anzahl physikalischer Lesezugriffe eines Schlüssel-Blocks von der Platte. Wenn key_reads hoch ist, ist Ihr key_cache wahrscheinlich zu klein. Die Cache-Zugriffsrate kann mit key_reads / key_read_requests berechnet werden. 

Key_writes 20 k Die Anzahl physikalischer Schreibvorgänge eines Schlüssel-Blocks auf Platte. 

Opened_tables 2.920 Anzahl der Tabellen, die geöffnet wurden. Wenn Opened_tables hoch ist, ist Ihre table_cache-Variable wahrscheinlich zu niedrig. 

Table_locks_waited 237  Wie oft eine Tabellensperre nicht sofort erlangt werden konnte und gewartet werden musste. Wenn dieser Wert hoch ist und Sie Performance-Probleme haben, sollten Sie zunächst Ihre Anfragen optimieren und dann entweder Ihre Tabelle(n) zerteilen oder Replikation benutzen.
Top

User avatar
Joe User
Project Manager
Project Manager
Posts: 11519
Joined: 2003-02-27 01:00
Location: Hamburg

Re: MySQL Optimierung

Post by Joe User »

Verwende bitte die upgedatete my.cnf im Datenbankbank-Sticky, Deine Version ist/war ausserst unoptimal :oops:
Bezüglich der tmp-tables: Verwendest Du TEXT/BLOB in mehreren Tabellen?
Bezüglich der handler-read*: Deine Indexe sollten überdacht/optimiert werden.
Top

Anonymous

Re: MySQL Optimierung

Post by Anonymous »

Habe soeben Deine upgedatete Version der my.cnf in meine kopiert.

Nein, ich verwende fast in keiner Tabelle TEXT/BLOB Typ. Wo die meisten Abfragen(ca. 90%) kommen, ist überhaupt keine TEXT/BLOB vorhanden.

Meine Indexe sind aber so aufgebaut, dass Sie Vorteile ziehen. Könnte aber ja sein, dass diese wohl trotztdem nicht optimal sind:

Code: Select all

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(10)      | NO   | PRI | NULL    | auto_increment |
| server    | varchar(4)   | NO   |     |         |                |
| date      | varchar(20)  | NO   |     |         |                |
| name      | varchar(200) | NO   | UNI |         |                |
| origname  | varchar(200) | NO   |     |         |                |
| hits      | int(10)      | NO   |     | 0       |                |
| ip        | varchar(50)  | NO   |     |         |                |
| size      | float        | NO   |     |         |                |
| ok        | tinyint(1)   | NO   |     | 0       |                |
| mail      | varchar(200) | NO   |     |         |                |
| code      | varchar(6)   | NO   |     |         |                |
| kommentar | varchar(255) | NO   |     |         |                |
| lastview  | int(10)      | NO   |     | 0       |                |
+-----------+--------------+------+-----+---------+----------------+

+--------+-----------+---------+-------------+------------+----------+------------+---------+-------+-----+-----------+
| Table  | Non_unique| Key_name| Seq_in_index| Column_name| Collation| Cardinality| Sub_part| Packed| Null| Index_type|
+--------+-----------+---------+-------------+------------+----------+------------+---------+-------+-----+-----------+
| tabelle|         0 | PRIMARY |            1| id         | A        |      119447|     NULL| NULL  |     | BTREE     |
| tabelle|         0 | name    |            1| name       | A        |      119447|     NULL| NULL  |     | BTREE     |
+--------+-----------+---------+-------------+------------+----------+------------+---------+-------+-----+-----------+



Anfragen sind dann unter PHP:

Code: Select all

SELECT * FROM `tabelle` WHERE id='$_GET[id]' LIMIT 1
UPDATE `tabelle` set hits='$new1' where name='$_GET[name]'
Top