mysqld config / performance

globestern
Posts: 69
Joined: 2005-04-20 11:34

mysqld config / performance

Post by globestern »

hi

vorerst eine etwas doofe frage.. ich starte mysql über mysqld_safe --user=mysql &

jetzt möchte ich die konfiguration etwas optimieren, da die cpu sehr stark ausgelastet ist und ich noch jede menge ram frei habe..

nun habe ich die /etc/my.cnf editiert -> ist es richtig, dass ich die optimierungen unter [mysqld] eintrage oder muss ich [mysqld_safe] verwenden?

nun zur konfig.. da wie gesagt viel ram frei ist, habe ich mich für eine etwas memory lastige konfiguration entschieden:

Code: Select all

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
skip-external-locking
user = mysql

key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
query_cache_size = 128M
query_cache_type = 1
query_cache_limit = 2M
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 8M
read_rnd_buffer_size = 16M

thread_cache = 64
thread_concurrency = 4
myisam_sort_buffer_size = 16M


wenn ich ./mysqladmin -u root -p variables ausgebe, werden mir die oben beschriebenen werte angegeben.. daher schliesse ich draus, dass [mysqld] richtig war *g*
allerdings bringt die optimierung ziemlich wenig:

load average: 6.96, 5.45, 13.26
Mem: 2060520k total, 666520k used, 1394000k free, 107736k buffers
der server swapt nicht..

momentan sieht es aber eher danach aus, als sei der httpd schuld:

8273 apache 17 0 254m 24m 3132 S 27 1.2 0:35.34 httpd
7700 apache 17 0 258m 27m 3260 S 14 1.4 2:03.15 httpd
8413 apache 17 0 258m 28m 3244 S 14 1.4 0:19.99 httpd
8475 apache 15 0 247m 18m 2648 S 9 0.9 0:03.26 httpd
7664 mysql 15 0 533m 67m 4264 S 6 3.3 1:36.25 mysqld

daher hier noch kurz die httpd.conf:

Code: Select all

Timeout 30
KeepAlive On
MaxKeepAliveRequests 150
KeepAliveTimeout 13
<IfModule worker.c>
StartServers         5
MaxClients         375
MinSpareThreads     15
MaxSpareThreads     20
ThreadsPerChild     25
</IfModule>


div. nicht gebrauchte module wurden deaktiviert.

noch kurz zum projekt:
ca. 20000 besucher täglich - woltlab board aktiv, google crawlt relativ viel, nebenbei ein ziemlich db lastiges script

hardware:
AMD Athlon(tm) 64 X2 Dual Core Processor 3600+
2GB RAM
500GB HDD im S-ATA RAID1

vielleicht kann ja jemand helfen :)
schönes weekend!
mfg
Top

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Re: mysqld config / performance

Post by isotopp »

Globestern wrote:nun habe ich die /etc/my.cnf editiert -> ist es richtig, dass ich die optimierungen unter [mysqld] eintrage oder muss ich [mysqld_safe] verwenden?


mysqld.

Code: Select all

key_buffer = 384M
query_cache_size = 128M
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 8M
read_rnd_buffer_size = 16M

thread_cache = 64
thread_concurrency = 4
myisam_sort_buffer_size = 16M


Was sagt

Code: Select all

select
  table_schema,
  engine,
  sum(data_length)/1024/1024 as dl_mb,
  sum(index_length)/1024/1024 as il_mb,
  sum(table_rows)/1000/1000 as mrows
from
  information_schema.tables
where
  table_schema not in ("mysql", "information_schema")
group by
  table_schema, engine;


Ein Query Cache von 128M ist sehr, sehr groß. Verkleinere auf 32M.
net_buffer_length sollte nie verstellt werden.
thread_concurrency hat auf Deinem System keine Wirkung.
Deine read_buffer und read_rnd_buffer sind sehr groß. Beachte, daß dieser Speicher per Connection verbraucht werden kann.
myisam_sort_buffer_size wird nur bei repair table, alter table add index und create index verwendet. Er wird per Thread alloziert. Er sollte, wenn möglich, groß genug sein, um den größten Index im Speicher zu halten. 16M sind da eher klein. Meintest Du sort_buffer_size stattdessen?

Code: Select all

2GB RAM


vm.swappiness = 0?
Slow Query Log enabled? mysqldumpslow ausgewertet?
SHOW GLOBAL STATUS analysiert?
Top

globestern
Posts: 69
Joined: 2005-04-20 11:34

Re: mysqld config / performance

Post by globestern »

herzlichen dank für eure ausführlichen antworten!

mytop werde ich mir noch ausführlich anschauen

table_schema engine dl_mb il_mb mrows
admin_blog MyISAM 0.12239170 0.05078125 0.00025400
admin_bla MyISAM 4.23159027 0.22753906 0.05726700
admin_bla2 MyISAM 1.39603233 0.74804688 0.04563800
admin_wbb3 MyISAM 60.96754169 46.73828125 1.05281300


Code: Select all

Variable_name    Value 
Aborted_clients   3
Aborted_connects   6185
Binlog_cache_disk_use   0
Binlog_cache_use   0
Bytes_received   2334638706
Bytes_sent   4263267360
Com_admin_commands   0
Com_assign_to_keycache   0
Com_alter_db   0
Com_alter_db_upgrade   0
Com_alter_event   0
Com_alter_function   0
Com_alter_procedure   0
Com_alter_server   0
Com_alter_table   0
Com_alter_tablespace   0
Com_analyze   0
Com_backup_table   0
Com_begin   0
Com_binlog   0
Com_call_procedure   0
Com_change_db   946463
Com_change_master   0
Com_check   0
Com_checksum   0
Com_commit   0
Com_create_db   0
Com_create_event   0
Com_create_function   0
Com_create_index   0
Com_create_procedure   0
Com_create_server   0
Com_create_table   1212
Com_create_trigger   0
Com_create_udf   0
Com_create_user   0
Com_create_view   0
Com_dealloc_sql   0
Com_delete   1543930
Com_delete_multi   47
Com_do   0
Com_drop_db   0
Com_drop_event   0
Com_drop_function   0
Com_drop_index   0
Com_drop_procedure   0
Com_drop_server   0
Com_drop_table   0
Com_drop_trigger   0
Com_drop_user   0
Com_drop_view   0
Com_empty_query   0
Com_execute_sql   0
Com_flush   0
Com_grant   0
Com_ha_close   0
Com_ha_open   0
Com_ha_read   0
Com_help   0
Com_insert   866184
Com_insert_select   5928
Com_install_plugin   0
Com_kill   0
Com_load   0
Com_load_master_data   0
Com_load_master_table   0
Com_lock_tables   0
Com_optimize   3
Com_preload_keys   0
Com_prepare_sql   0
Com_purge   0
Com_purge_before_date   0
Com_release_savepoint   0
Com_rename_table   0
Com_rename_user   0
Com_repair   44
Com_replace   622
Com_replace_select   0
Com_reset   0
Com_restore_table   0
Com_revoke   0
Com_revoke_all   0
Com_rollback   0
Com_rollback_to_savepoint   0
Com_savepoint   0
Com_select   3824180
Com_set_option   1516479
Com_show_authors   0
Com_show_binlog_events   0
Com_show_binlogs   3
Com_show_charsets   18
Com_show_collations   18
Com_show_column_types   0
Com_show_contributors   0
Com_show_create_db   0
Com_show_create_event   0
Com_show_create_func   0
Com_show_create_proc   0
Com_show_create_table   0
Com_show_create_trigger   0
Variable_name    Value
Com_show_databases   18
Com_show_engine_logs   0
Com_show_engine_mutex   0
Com_show_engine_status   0
Com_show_events   0
Com_show_errors   0
Com_show_fields   1
Com_show_function_status   0
Com_show_grants   5
Com_show_keys   1
Com_show_master_status   0
Com_show_new_master   0
Com_show_open_tables   0
Com_show_plugins   0
Com_show_privileges   0
Com_show_procedure_status   0
Com_show_processlist   1
Com_show_profile   0
Com_show_profiles   0
Com_show_slave_hosts   0
Com_show_slave_status   0
Com_show_status   2
Com_show_storage_engines   0
Com_show_table_status   118
Com_show_tables   51
Com_show_triggers   0
Com_show_variables   59
Com_show_warnings   0
Com_slave_start   0
Com_slave_stop   0
Com_stmt_close   0
Com_stmt_execute   0
Com_stmt_fetch   0
Com_stmt_prepare   0
Com_stmt_reprepare   0
Com_stmt_reset   0
Com_stmt_send_long_data   0
Com_truncate   0
Com_uninstall_plugin   0
Com_unlock_tables   0
Com_update   1473781
Com_update_multi   0
Com_xa_commit   0
Com_xa_end   0
Com_xa_prepare   0
Com_xa_recover   0
Com_xa_rollback   0
Com_xa_start   0
Compression   OFF
Connections   952665
Created_tmp_disk_tables   30350
Created_tmp_files   109
Created_tmp_tables   779719
Delayed_errors   0
Delayed_insert_threads   0
Delayed_writes   0
Flush_commands   1
Handler_commit   0
Handler_delete   789592
Handler_discover   0
Handler_prepare   0
Handler_read_first   264866
Handler_read_key   480350518
Handler_read_next   499826189
Handler_read_prev   532277
Handler_read_rnd   131829188
Handler_read_rnd_next   2400547665
Handler_rollback   0
Handler_savepoint   0
Handler_savepoint_rollback   0
Handler_update   23715035
Handler_write   404630320
Key_blocks_not_flushed   0
Key_blocks_unused   335175
Key_blocks_used   11807
Key_read_requests   1429246565
Key_reads   34197
Key_write_requests   440969
Key_writes   186210
Last_query_cost   0.000000
Max_used_connections   152
Not_flushed_delayed_rows   0
Open_files   738
Open_streams   0
Open_table_definitions   226
Open_tables   512
Opened_files   146626
Opened_table_definitions   2717
Opened_tables   12793
Prepared_stmt_count   0
Qcache_free_blocks   13274
Qcache_free_memory   64272944
Qcache_hits   8817448
Qcache_inserts   3603018
Qcache_lowmem_prunes   140251
Qcache_not_cached   215760
Qcache_queries_in_cache   33831
Qcache_total_blocks   81272
Questions   19943245
Rpl_status   NULL
Variable_name    Value
Select_full_join   17264
Select_full_range_join   0
Select_range   79314
Select_range_check   0
Select_scan   3022992
Slave_open_temp_tables   0
Slave_retried_transactions   0
Slave_running   OFF
Slow_launch_threads   1
Slow_queries   1433
Sort_merge_passes   67
Sort_range   60089
Sort_rows   253894907
Sort_scan   959403
Table_locks_immediate   8773092
Table_locks_waited   119385
Tc_log_max_pages_used   0
Tc_log_page_size   0
Tc_log_page_waits   0
Threads_cached   61
Threads_connected   3
Threads_created   323
Threads_running   2
Uptime   232607


Created_tmp_tables 779719 -> tmp_table_size erhöhen?
Slow_queries 1433 -> das script wird momentan überarbeitet :-)

ich habe nun die my.cnf wie folgt angepasst:

key_buffer = 384M
max_allowed_packet = 16M
table_cache = 512
query_cache_size = 32M
query_cache_type = 1
query_cache_limit = 2M
sort_buffer_size = 2M
read_buffer_size = 8M
read_rnd_buffer_size = 16M
thread_cache = 64
myisam_sort_buffer_size = 256M


vm.swappiness = 0? --> done
Top

User avatar
isotopp
RSAC
Posts: 482
Joined: 2003-08-21 10:21
Location: Berlin

Re: mysqld config / performance

Post by isotopp »

Globestern wrote:
table_schema engine dl_mb il_mb mrows
admin_blog MyISAM 0.12239170 0.05078125 0.00025400
admin_bla MyISAM 4.23159027 0.22753906 0.05726700
admin_bla2 MyISAM 1.39603233 0.74804688 0.04563800
admin_wbb3 MyISAM 60.96754169 46.73828125 1.05281300


Wie man sehen kann, verwendest Du ausschließlich MyISAM. Du kannst "skip-innodb" konfigurieren. Das spart unter Umständen RAM. Du kannst außerdem in $datadir alle ib_logfile?, ibdata? und alle *.ibd-Files entfernen. Das spart dann noch ein wenig Plattenplatz.

Du hast ein Datenvolumen von etwa 64MB. Das bedeutet, daß Dein File System Buffer Cache etwa 64 MB MYD-Dateien cachen muss. Du hast außerdem ein Indexvolumen von etwa 48 MB. Eine key_buffer_size von mehr als 48MB ist also nicht sinnvoll bei Dir. Um Raum für Wachstum zu haben solltest Du ggf. einen bestimmten Faktor da oben drauf schlagen.

Created_tmp_disk_tables 30350
Created_tmp_tables 779719


3.8% aller "Using temporary" Tabellen gehen auf die Platte (created_tmp_tables * 100 / created_tmp_disk_tables). Das ist kleiner als 10% und einigermaßen okay.

Handler_read_first 264866
Handler_read_key 480350518
Handler_read_next 499826189
Handler_read_prev 532277
Handler_read_rnd 131829188
Handler_read_rnd_next 2400547665


handler_read_rnd und handler_read_rnd_next zählen Zeilenzugriffe ohne Index. Beide Werte sind recht hoch. Schauen wir weiter unten mal auf die select_* counter. Es sieht so aus als hätte Deine Datenbank schlechte Indices oder schlechtes SQL.

Key_blocks_unused 335175
Key_blocks_used 11807


335175 1KB Blöcke key_buffer_size nicht verwendet. Siehe meine Anmerkung oben.
Von 48MB Summe aller MYI-Dateien sind 11MB geladen. Wahrscheinlich existieren also tote Tabellen oder Indices, die nicht gebraucht werden.

Key_read_requests 1429246565
Key_reads 34197


Von 1429246565 Zugriffen in den Key_Buffer konnten nur 34197 nicht aus dem Cache befriedigt werden und haben Disk-Zugriffe getriggert. Das ist eine Ratio von 41794 (300 ist okay, 1000 ist sehr sehr gut). Dein Key Buffer ist größer als notwendig.

Qcache_free_blocks 13274
Qcache_free_memory 64272944
Qcache_hits 8817448
Qcache_inserts 3603018
Qcache_lowmem_prunes 140251
Qcache_not_cached 215760
Qcache_queries_in_cache 33831
Qcache_total_blocks 81272


Eine Query kann entweder aus dem Query cache beantwortet werden (Qcache_hits) oder muß ausgeführt werden. Im zweiten Fall ist sie entweder cacheable (Qcache_inserts) oder uncacheable (Qcache_not_cached). Von allen Queries überhaupt (Qcache_hits + Qcache_inserts + QCache_not_cacheable) sind also nur ein Anteil (Qcache_hits) Treffer.

8817448*100/(8817448+3603018+215760) = 69.77%

Das ist eine exzellente Hit-Rate für den Query Cache. Der Query-Cache ist für Deine Anwendung nützlich.

Select_full_join 17264
Select_full_range_join 0
Select_range 79314
Select_range_check 0
Select_scan 3022992


Ein Full Join ist ein Join, der ohne Index ausgeführt wird. Das ist sehr, sehr schlecht und sollte nie auftreten. Full Joins treten auf, wenn Indices fehlen ("log_queries_not_using_indexes") oder wenn das SQL schlecht formuliert ist.

Sort_merge_passes 67


Wenn ein Sort (in ORDER BY oder GROUP BY) zu groß ist, dann muß er in mehreren Passes auf der Platte durchgeführt werden. Wenn dieser Wert sehr hoch ist (ist er hier nicht!), dann kann man überlegen sort_buffer_size vorsichtig hoch zu drehen (dauerhaft oder für die Dauer eines Sorts). Da das ein per-thread Buffer ist, sollte man das mit sehr viel Zurückhaltung machen. Du solltest es bei Dir gar nicht machen.

Table_locks_immediate 8773092
Table_locks_waited 119385


Du hast table_locks_waited * 100 / table_locks_immediate = 1.3% aller Zugriffe auf MyISAM-Tabellen, bei denen der Zugriff auf das Table Lock hat warten müssen. Eine Ratio von 1% führt in der Regel zu meßbaren Performanceproblem, eines Ratio von 3% führt in der Regel zu unakzeptablem Zeitverhalten ("SHOW PROCESSLIST" zeigt eine Menge Queries in "Locked" State und max_connections "ist zu klein").

Das Problem ist in der Regel nicht mit MyISAM zu lösen. Die Tabellen, auf denen die meisten Locks gesehen werden sollten auf InnoDB umgestellt werden.

Threads_cached 61
Threads_connected 3
Threads_created 323


Threads_created ist sehr klein, dein Thread_cache ist gross genug.

Created_tmp_tables 779719 -> tmp_table_size erhöhen?


Tmp_Tables sind nix schlimmes. Tmp_disk_tables sind schlimm. Bei Dir ist alles okay.
Top

globestern
Posts: 69
Joined: 2005-04-20 11:34

Re: mysqld config / performance

Post by globestern »

riesen dank für deine ausführliche erklärung und auswertung!
werde mir das ganze genauer anschauen und danach berichten
Top