mysqld config / performance

[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

[isotopp]

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?

[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

[isotopp]

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.

[globestern]

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