MySQL Hohe Load - Wie troubleshooting?

MySQL, PostgreSQL, SQLite
kgbschnik
Posts: 10
Joined: 2006-06-28 15:02

MySQL Hohe Load - Wie troubleshooting?

Post by kgbschnik » 2007-10-01 17:23

Hallo,

ich habe seit ca. 4 Tagen eine hohe Load, die anscheinend durch die Datenbank veranlasst wird. Load steigt immer weiter an bis 40.(!) und wenn ich neustarte bleibt der Server ca. 20 Minuten "ruhig" bis es wieder anfängt langsam zu starten.

Zum System:

MD Opteron 1218 Dual Core
4 GB DDR2-667 RAM

(htop):
MySQL-Dienst: ca 90% CPU Auslastung
Apache-Dienst ca. 5%

Zu meinem System:
Debian Etch, Apache2.2, Mysql-Server5.0, PHP5

Das ganze hatte ich vor 2 Monaten bereits. Damals lag es an Indizes, die ich nicht gesetzt hatte. Jetzt habe ich im Slow-Query Logging keine Queries ausser einige, die keine Indizes nutzen - die gab es aber vor einer Woche auch schon mit normaler Auslastung.

Ich poste mal einige Ausgaben, hoffe es hilft euch - ich weiß nicht weiter wie ich da Troubleshooting betreiben soll. Jeder Ansatz wäre eine Hilfe! Danke

top:

Code: Select all

top - 17:18:33 up 13 days,  3:04,  2 users,  load average: 1.62, 1.66, 1.95
Tasks: 193 total,   2 running, 191 sleeping,   0 stopped,   0 zombie
Cpu(s): 42.8%us,  7.3%sy,  0.0%ni, 49.0%id,  0.3%wa,  0.2%hi,  0.4%si,  0.0%st
Mem:   3984972k total,  3874644k used,   110328k free,   125580k buffers
Swap:  2096376k total,       44k used,  2096332k free,  3001868k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
18456 mysql     15   0  644m 138m 6100 S   81  3.6  11:25.69 mysqld
18306 www-data  15   0  111m  10m 5668 R   15  0.3   1:15.98 apache2
18888 www-data  15   0  111m 9296 4360 S    4  0.2   0:00.02 apache2
18613 www-data  15   0  111m  10m 5600 S    2  0.3   0:00.70 apache2
18615 www-data  15   0  111m  10m 6064 S    2  0.3   0:00.79 apache2
18664 www-data  15   0  111m  10m 5752 S    2  0.3   0:00.41 apache2
18863 www-data  15   0  111m 9200 4200 S    2  0.2   0:00.06 apache2
18890 root      15   0 10724 1320  884 R    2  0.0   0:00.02 top
    1 root      15   0  6124  692  568 S    0  0.0   0:01.83 init
    2 root      RT   0     0    0    0 S    0  0.0   0:00.29 migration/0
    3 root      34  19     0    0    0 S    0  0.0   0:00.29 ksoftirqd/0
    4 root      RT   0     0    0    0 S    0  0.0   0:00.00 watchdog/0
    5 root      RT   0     0    0    0 S    0  0.0   0:00.02 migration/1
    6 root      34  19     0    0    0 S    0  0.0   0:00.13 ksoftirqd/1


my.cnf:

Code: Select all

[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

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

[mysqld]
#
# * Basic Settings
#
character-set-server  = utf8
default-character-set = utf8
init-connect='SET NAMES utf8'

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

bind-address            = 127.0.0.1

key_buffer              = 512M
max_allowed_packet      = 16M
thread_stack            = 128K
thread_cache_size       = 8
max_connections         = 1000
table_cache             = 1024
thread_concurrency      = 4

max_heap_table_size     = 32M
tmp_table_size          = 64M

#
# * Query Cache Configuration
#
query_cache_type        = 1
query_cache_limit       = 2M
query_cache_size        = 16M

log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time = 1
log-queries-not-using-indexes

skip-bdb

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]

[isamchk]
key_buffer              = 16M


SHOW GLOBAL STATUS;

Code: Select all

Aborted_clients     0
Aborted_connects    61
Binlog_cache_disk_use    0
Binlog_cache_use    0
Bytes_received    243466113
Bytes_sent    245967434
Com_admin_commands    2
Com_alter_db    0
Com_alter_table    69
Com_analyze    0
Com_backup_table    0
Com_begin    0
Com_change_db    12814
Com_change_master    0
Com_check    88
Com_checksum    0
Com_commit    0
Com_create_db    0
Com_create_function    0
Com_create_index    0
Com_create_table    14
Com_create_user    0
Com_dealloc_sql    0
Com_delete    730
Com_delete_multi    0
Com_do    0
Com_drop_db    0
Com_drop_function    0
Com_drop_index    0
Com_drop_table    0
Com_drop_user    0
Com_execute_sql    0
Com_flush    1
Com_grant    0
Com_ha_close    0
Com_ha_open    0
Com_ha_read    0
Com_help    0
Com_insert    722
Com_insert_select    0
Com_kill    0
Com_load    0
Com_load_master_data    0
Com_load_master_table    0
Com_lock_tables    0
Com_optimize    0
Com_preload_keys    0
Com_prepare_sql    0
Com_purge    0
Com_purge_before_date    0
Com_rename_table    0
Com_repair    0
Com_replace    0
Com_replace_select    0
Com_reset    0
Com_restore_table    0
Com_revoke    0
Com_revoke_all    0
Com_rollback    0
Com_savepoint    0
Com_select    1658771
Com_set_option    12815
Com_show_binlog_events    0
Com_show_binlogs    0
Com_show_charsets    1
Com_show_collations    1
Com_show_column_types    0
Com_show_create_db    0
Com_show_create_table    0
Com_show_databases    1
Com_show_errors    0
Com_show_fields    0
Com_show_grants    1
Com_show_innodb_status    0
Com_show_keys    0
Com_show_logs    0
Com_show_master_status    0
Com_show_ndb_status    0
Com_show_new_master    0
Com_show_open_tables    0
Com_show_privileges    0
Com_show_processlist    0
Com_show_slave_hosts    0
Com_show_slave_status    0
Com_show_status    1
Com_show_storage_engines    0
Com_show_tables    0
Com_show_triggers    0
Com_show_variables    3
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_reset    0
Com_stmt_send_long_data    0
Com_truncate    0
Com_unlock_tables    0
Variable_name    Value
Com_update    6967
Com_update_multi    1
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    12838
Created_tmp_disk_tables    497
Created_tmp_files    5
Created_tmp_tables    2129
Delayed_errors    0
Delayed_insert_threads    0
Delayed_writes    0
Flush_commands    1
Handler_commit    78
Handler_delete    123
Handler_discover    0
Handler_prepare    0
Handler_read_first    1172
Handler_read_key    13233212
Handler_read_next    3001443
Handler_read_prev    1793
Handler_read_rnd    114447
Handler_read_rnd_next    1907001
Handler_rollback    0
Handler_savepoint    0
Handler_savepoint_rollback    0
Handler_update    158721
Handler_write    342333
Innodb_buffer_pool_pages_data    400
Innodb_buffer_pool_pages_dirty    0
Innodb_buffer_pool_pages_flushed    228
Innodb_buffer_pool_pages_free    0
Innodb_buffer_pool_pages_latched    2
Innodb_buffer_pool_pages_misc    112
Innodb_buffer_pool_pages_total    512
Innodb_buffer_pool_read_ahead_rnd    96
Innodb_buffer_pool_read_ahead_seq    666
Innodb_buffer_pool_read_requests    21157304
Innodb_buffer_pool_reads    59388
Innodb_buffer_pool_wait_free    0
Innodb_buffer_pool_write_requests    708
Innodb_data_fsyncs    341
Innodb_data_pending_fsyncs    0
Innodb_data_pending_reads    0
Innodb_data_pending_writes    0
Innodb_data_read    1123782656
Innodb_data_reads    61117
Innodb_data_writes    424
Innodb_data_written    7591936
Innodb_dblwr_pages_written    228
Innodb_dblwr_writes    74
Innodb_log_waits    0
Innodb_log_write_requests    46
Innodb_log_writes    122
Innodb_os_log_fsyncs    190
Innodb_os_log_pending_fsyncs    0
Innodb_os_log_pending_writes    0
Innodb_os_log_written    86016
Innodb_page_size    16384
Innodb_pages_created    0
Innodb_pages_read    68456
Innodb_pages_written    228
Innodb_row_lock_current_waits    0
Innodb_row_lock_time    0
Innodb_row_lock_time_avg    0
Innodb_row_lock_time_max    0
Innodb_row_lock_waits    0
Innodb_rows_deleted    66
Innodb_rows_inserted    60
Innodb_rows_read    7300435
Innodb_rows_updated    0
Key_blocks_not_flushed    0
Key_blocks_unused    424287
Key_blocks_used    4403
Key_read_requests    38274779
Key_reads    8225
Key_write_requests    356382
Key_writes    11008
Last_query_cost    0.000000
Max_used_connections    23
Ndb_cluster_node_id    0
Ndb_config_from_host    
Ndb_config_from_port    0
Ndb_number_of_data_nodes    0
Not_flushed_delayed_rows    0
Open_files    193
Open_streams    0
Open_tables    155
Opened_tables    273
Prepared_stmt_count    0
Qcache_free_blocks    93
Qcache_free_memory    14350088
Qcache_hits    64750
Qcache_inserts    1654732
Qcache_lowmem_prunes    0
Qcache_not_cached    4048
Variable_name    Value
Qcache_queries_in_cache    2287
Qcache_total_blocks    4699
Questions    1770605
Rpl_status    NULL
Select_full_join    4
Select_full_range_join    0
Select_range    1635646
Select_range_check    0
Select_scan    1304
Slave_open_temp_tables    0
Slave_retried_transactions    0
Slave_running    OFF
Slow_launch_threads    0
Slow_queries    1296
Sort_merge_passes    0
Sort_range    3002
Sort_rows    820160
Sort_scan    2240
Ssl_accept_renegotiates    0
Ssl_accepts    0
Ssl_callback_cache_hits    0
Ssl_cipher    
Ssl_cipher_list    
Ssl_client_connects    0
Ssl_connect_renegotiates    0
Ssl_ctx_verify_depth    0
Ssl_ctx_verify_mode    0
Ssl_default_timeout    0
Ssl_finished_accepts    0
Ssl_finished_connects    0
Ssl_session_cache_hits    0
Ssl_session_cache_misses    0
Ssl_session_cache_mode    NONE
Ssl_session_cache_overflows    0
Ssl_session_cache_size    0
Ssl_session_cache_timeouts    0
Ssl_sessions_reused    0
Ssl_used_session_cache_entries    0
Ssl_verify_depth    0
Ssl_verify_mode    0
Ssl_version    
Table_locks_immediate    1692770
Table_locks_waited    3639
Tc_log_max_pages_used    0
Tc_log_page_size    0
Tc_log_page_waits    0
Threads_cached    3
Threads_connected    6
Threads_created    114
Threads_running    5
Uptime    1116


mytop:

Code: Select all

Queries: 2.0M   qps: 1687 Slow:     0.0         Se/In/Up/De(%):    04/00/00/00
             qps now: 3524 Slow qps: 0.0  Threads:   13 (   7/   2) 08/00/00/00
 Key Efficiency: 100.0%  Bps in/out:   0.1/ 13.6   Now in/out:   7.9/ 1.8k


Weitere Infos oder Systemausgaben poste ich gerne. :-)

oxygen
Posts: 2138
Joined: 2002-12-15 00:10
Location: Bergheim

Re: MySQL Hohe Load - Wie troubleshooting?

Post by oxygen » 2007-10-01 18:22

also ein qps Wert von 3524 ist schon sehr hart. Es kann sein, dass die Hardware damit einfach nicht mehr fertig wird. Du kannst mal das tuning primer script probieren und deine Konfiguration entsprechend anpassen:

Code: Select all

wget http://day32.com/MySQL/tuning-primer.sh -O- | sh

Aber allzu große Hoffung will ich dir da auch nicht machen.

kgbschnik
Posts: 10
Joined: 2006-06-28 15:02

Re: MySQL Hohe Load - Wie troubleshooting?

Post by kgbschnik » 2007-10-01 19:50

oxygen wrote:also ein qps Wert von 3524 ist schon sehr hart. Es kann sein, dass die Hardware damit einfach nicht mehr fertig wird. Du kannst mal das tuning primer script probieren und deine Konfiguration entsprechend anpassen:

Code: Select all

wget http://day32.com/MySQL/tuning-primer.sh -O- | sh

Aber allzu große Hoffung will ich dir da auch nicht machen.


Hardware muss das abkönnen. Immerhin ging es vor 2 Wochen ja auch noch und die Besucherzahlen sind nicht drastisch gestiegen bzw. sind gleich geblieben.

Tuning-primer.sh habe ich bereits getestet und dementsprechend angepasst.

kolibri
Posts: 32
Joined: 2006-09-06 17:08

Re: MySQL Hohe Load - Wie troubleshooting?

Post by kolibri » 2007-10-06 18:43

KGBschnik wrote:
oxygen wrote:also ein qps Wert von 3524 ist schon sehr hart. Es kann sein, dass die Hardware damit einfach nicht mehr fertig wird. Du kannst mal das tuning primer script probieren und deine Konfiguration entsprechend anpassen:

Code: Select all

wget http://day32.com/MySQL/tuning-primer.sh -O- | sh

Aber allzu große Hoffung will ich dir da auch nicht machen.


Hardware muss das abkönnen. Immerhin ging es vor 2 Wochen ja auch noch und die Besucherzahlen sind nicht drastisch gestiegen bzw. sind gleich geblieben.

Tuning-primer.sh habe ich bereits getestet und dementsprechend angepasst.


wie siehts denn mit den tabellengrössen aus?? ich meine die anzahl der einträge??

das selbe hatte ich auch.. allerdings ist mein load bis auf 12 "nur" gestiegen.. setzen von indizes hatte das problem für ein paar monate behoben, doch die grösse der datenbanken und aufgrund der software (chatsysteme) nötige tabellenscans haben den load dann wieder auf 1-1.5 hochgejubelt..

ein splitting der tabellen hat die nötige performance wieder gebracht.. ich halte die textdatenbanken jetzt in einem bereich von 60-80tausend einträgen, der rest geht in eine "alt-text" datenbank, auf die ich nur zugreife, wenns wirklich nötig ist..

vielleicht liegt ja dort der hase im pfeffer???

ich bin auch seinerzeit dazu übergegangen, (eher eine verzweiflungstat, als wissen jedoch bin ich heute froh, es getan zu haben) den apache zu clustern und vollkommen vom datenbank-server zu trennen, so hat er die volle cpu für sich allein und kann werkeln wie er will..

der load des db-servers (0.02-0.4) ist inzwischen unter den beiden pound-cluster-servern (0.5-0.8).