Hi,
I have (what I thought) was a fairly small mysql setup. It's backing a
vpopmail installation, and basically just handling alot of SELECTs.
In the process of delivering mail, the db gets hit a few times:
-initial check that the user exists during smtp conversation
-lookup for homedir during delivery
-optional lookup again if doing spam filtering
Additionally, everytime someone authenticates via smtp-auth, pop or imap,
there's a SELECT to grab the password.
So I haven't really done much to optimize things, as this seems like a
fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux
Threads).
However, I'm starting to have problems. The various vpopmail programs
that perform all these lookups are timing out, which leads to login
failures (and worse). I see a number of simple things like "PINGs" from a
local cronjob that graphs queries/sec timing out.
Server load can swing from .30 during a calm period to brief spikes of
10.0-15.0 (generally during a spam run).
I'm not sure what exactly to look at. I've bumped my max connections up
to 1000, and based my my.cnf on the "my-large.cnf" file and I'm still
seeing plenty of slow queries. I'm not swapping to disk at all, I'm not
really taxing the CPU, and iostat shows that the raid array isn't
struggling.
It's been at least 4 years since I had to get very in-depth with MySQL,
and it seems plenty has changed (for the better). If anyone can give me a
push in the right direction, it would be much appreciated.
Thanks,
Charles
Here's some stats, perhaps this will make more sense to someone. I know
the "Aborted_clients" is high because vpopmail doesn't bother with
"mysql_close()". The "Aborted_connects" increment when the problems
start. The db server is local to the mail server, so there's no network
issues here.
mysql> show status;
+--------------------------------+------------+
| Variable_name | Value |
+--------------------------------+------------+
| Aborted_clients | 15450 |
| Aborted_connects | 780 |
| Bytes_received | 8502062 |
| Bytes_sent | 18432035 |
| Com_admin_commands | 8736 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 3181 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 454 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 1 |
| Com_delete | 3 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 1 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 20 |
| 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_purge | 0 |
| Com_rename_table | 0 |
| Com_repair | 0 |
| Com_replace | 7495 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 13646 |
| Com_set_option | 1 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 5 |
| Com_show_fields | 20 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 4 |
| Com_show_slave_status | 0 |
| Com_show_status | 22 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 4 |
| Com_show_variables | 2 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 0 |
| Com_unlock_tables | 0 |
| Com_update | 6190 |
| Connections | 16602 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 246 |
| Handler_read_first | 2 |
| Handler_read_key | 12335 |
| Handler_read_next | 20600 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 7458 |
| Handler_read_rnd_next | 4204106 |
| Handler_rollback | 0 |
| Handler_update | 7416 |
| Handler_write | 7513 |
| Key_blocks_used | 388 |
| Key_read_requests | 63965 |
| Key_reads | 386 |
| Key_write_requests | 437 |
| Key_writes | 213 |
| Max_used_connections | 139 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 29 |
| Open_files | 49 |
| Open_streams | 0 |
| Opened_tables | 36 |
| Questions | 62362 |
| Qcache_queries_in_cache | 5551 |
| Qcache_inserts | 5993 |
| Qcache_hits | 23284 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 7599 |
| Qcache_free_memory | 10942944 |
| Qcache_free_blocks | 45 |
| Qcache_total_blocks | 11153 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 97 |
| Select_range_check | 0 |
| Select_scan | 7643 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 190 |
| Slow_queries | 149 |
| Sort_merge_passes | 0 |
| Sort_range | 8 |
| Sort_rows | 42 |
| Sort_scan | 0 |
| Ssl_accepts | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_accept_renegotiates | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_client_connects | 0 |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 128 |
| Ssl_session_cache_mode | SERVER |
| Ssl_sessions_reused | 0 |
| Ssl_ctx_verify_mode | 5 |
| Ssl_ctx_verify_depth | 4294967295 |
| Ssl_verify_mode | 0 |
| Ssl_verify_depth | 0 |
| Ssl_version | |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_default_timeout | 0 |
| Table_locks_immediate | 27215 |
| Table_locks_waited | 89 |
| Threads_cached | 6 |
| Threads_created | 2017 |
| Threads_connected | 57 |
| Threads_running | 5 |
| Uptime | 5027 |
+--------------------------------+------------+
155 rows in set (0.01 sec)
mysql> show variables\G
*************************** 1. row ***************************
Variable_name: back_log
Value: 50
*************************** 2. row ***************************
Variable_name: basedir
Value: /usr/local/
*************************** 3. row ***************************
Variable_name: bdb_cache_size
Value: 8388600
*************************** 4. row ***************************
Variable_name: bdb_log_buffer_size
Value: 131072
*************************** 5. row ***************************
Variable_name: bdb_home
Value: /var/db/mysql/
*************************** 6. row ***************************
Variable_name: bdb_max_lock
Value: 10000
*************************** 7. row ***************************
Variable_name: bdb_logdir
Value:
*************************** 8. row ***************************
Variable_name: bdb_shared_data
Value: OFF
*************************** 9. row ***************************
Variable_name: bdb_tmpdir
Value: /var/tmp/
*************************** 10. row ***************************
Variable_name: bdb_version
Value: Sleepycat Software: Berkeley DB 3.2.9a: (May 14, 2004)
*************************** 11. row ***************************
Variable_name: binlog_cache_size
Value: 32768
*************************** 12. row ***************************
Variable_name: bulk_insert_buffer_size
Value: 8388608
*************************** 13. row ***************************
Variable_name: character_set
Value: latin1
*************************** 14. row ***************************
Variable_name: character_sets
Value: latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251
danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250
croat cp1257 latin5
*************************** 15. row ***************************
Variable_name: concurrent_insert
Value: ON
*************************** 16. row ***************************
Variable_name: connect_timeout
Value: 5
*************************** 17. row ***************************
Variable_name: convert_character_set
Value:
*************************** 18. row ***************************
Variable_name: datadir
Value: /var/db/mysql/
*************************** 19. row ***************************
Variable_name: default_week_format
Value: 0
*************************** 20. row ***************************
Variable_name: delay_key_write
Value: ON
*************************** 21. row ***************************
Variable_name: delayed_insert_limit
Value: 100
*************************** 22. row ***************************
Variable_name: delayed_insert_timeout
Value: 300
*************************** 23. row ***************************
Variable_name: delayed_queue_size
Value: 1000
*************************** 24. row ***************************
Variable_name: flush
Value: OFF
*************************** 25. row ***************************
Variable_name: flush_time
Value: 0
*************************** 26. row ***************************
Variable_name: ft_boolean_syntax
Value: + -><()~*:""&|
*************************** 27. row ***************************
Variable_name: ft_min_word_len
Value: 4
*************************** 28. row ***************************
Variable_name: ft_max_word_len
Value: 254
*************************** 29. row ***************************
Variable_name: ft_max_word_len_for_sort
Value: 20
*************************** 30. row ***************************
Variable_name: ft_stopword_file
Value: (built-in)
*************************** 31. row ***************************
Variable_name: have_bdb
Value: YES
*************************** 32. row ***************************
Variable_name: have_crypt
Value: YES
*************************** 33. row ***************************
Variable_name: have_innodb
Value: YES
*************************** 34. row ***************************
Variable_name: have_isam
Value: YES
*************************** 35. row ***************************
Variable_name: have_raid
Value: NO
*************************** 36. row ***************************
Variable_name: have_symlink
Value: NO
*************************** 37. row ***************************
Variable_name: have_openssl
Value: YES
*************************** 38. row ***************************
Variable_name: have_query_cache
Value: YES
*************************** 39. row ***************************
Variable_name: init_file
Value:
*************************** 40. row ***************************
Variable_name: innodb_additional_mem_pool_size
Value: 1048576
*************************** 41. row ***************************
Variable_name: innodb_buffer_pool_size
Value: 8388608
*************************** 42. row ***************************
Variable_name: innodb_data_file_path
Value: ibdata1:10M:autoextend
*************************** 43. row ***************************
Variable_name: innodb_data_home_dir
Value:
*************************** 44. row ***************************
Variable_name: innodb_file_io_threads
Value: 4
*************************** 45. row ***************************
Variable_name: innodb_force_recovery
Value: 0
*************************** 46. row ***************************
Variable_name: innodb_thread_concurrency
Value: 8
*************************** 47. row ***************************
Variable_name: innodb_flush_log_at_trx_commit
Value: 1
*************************** 48. row ***************************
Variable_name: innodb_fast_shutdown
Value: ON
*************************** 49. row ***************************
Variable_name: innodb_flush_method
Value:
*************************** 50. row ***************************
Variable_name: innodb_lock_wait_timeout
Value: 50
*************************** 51. row ***************************
Variable_name: innodb_log_arch_dir
Value: ./
*************************** 52. row ***************************
Variable_name: innodb_log_archive
Value: OFF
*************************** 53. row ***************************
Variable_name: innodb_log_buffer_size
Value: 1048576
*************************** 54. row ***************************
Variable_name: innodb_log_file_size
Value: 5242880
*************************** 55. row ***************************
Variable_name: innodb_log_files_in_group
Value: 2
*************************** 56. row ***************************
Variable_name: innodb_log_group_home_dir
Value: ./
*************************** 57. row ***************************
Variable_name: innodb_mirrored_log_groups
Value: 1
*************************** 58. row ***************************
Variable_name: innodb_max_dirty_pages_pct
Value: 90
*************************** 59. row ***************************
Variable_name: interactive_timeout
Value: 28800
*************************** 60. row ***************************
Variable_name: join_buffer_size
Value: 131072
*************************** 61. row ***************************
Variable_name: key_buffer_size
Value: 268435456
*************************** 62. row ***************************
Variable_name: language
Value: /usr/local/share/mysql/english/
*************************** 63. row ***************************
Variable_name: large_files_support
Value: ON
*************************** 64. row ***************************
Variable_name: license
Value: GPL
*************************** 65. row ***************************
Variable_name: local_infile
Value: ON
*************************** 66. row ***************************
Variable_name: log
Value: OFF
*************************** 67. row ***************************
Variable_name: log_update
Value: OFF
*************************** 68. row ***************************
Variable_name: log_bin
Value: ON
*************************** 69. row ***************************
Variable_name: log_slave_updates
Value: OFF
*************************** 70. row ***************************
Variable_name: log_slow_queries
Value: ON
*************************** 71. row ***************************
Variable_name: log_warnings
Value: ON
*************************** 72. row ***************************
Variable_name: long_query_time
Value: 10
*************************** 73. row ***************************
Variable_name: low_priority_updates
Value: OFF
*************************** 74. row ***************************
Variable_name: lower_case_file_system
Value: OFF
*************************** 75. row ***************************
Variable_name: lower_case_table_names
Value: 0
*************************** 76. row ***************************
Variable_name: max_allowed_packet
Value: 1047552
*************************** 77. row ***************************
Variable_name: max_binlog_cache_size
Value: 4294967295
*************************** 78. row ***************************
Variable_name: max_binlog_size
Value: 104857600
*************************** 79. row ***************************
Variable_name: max_connections
Value: 2000
*************************** 80. row ***************************
Variable_name: max_connect_errors
Value: 10
*************************** 81. row ***************************
Variable_name: max_delayed_threads
Value: 20
*************************** 82. row ***************************
Variable_name: max_insert_delayed_threads
Value: 20
*************************** 83. row ***************************
Variable_name: max_heap_table_size
Value: 16777216
*************************** 84. row ***************************
Variable_name: max_join_size
Value: 4294967295
*************************** 85. row ***************************
Variable_name: max_relay_log_size
Value: 0
*************************** 86. row ***************************
Variable_name: max_seeks_for_key
Value: 4294967295
*************************** 87. row ***************************
Variable_name: max_sort_length
Value: 1024
*************************** 88. row ***************************
Variable_name: max_user_connections
Value: 0
*************************** 89. row ***************************
Variable_name: max_tmp_tables
Value: 32
*************************** 90. row ***************************
Variable_name: max_write_lock_count
Value: 4294967295
*************************** 91. row ***************************
Variable_name: myisam_max_extra_sort_file_size
Value: 268435456
*************************** 92. row ***************************
Variable_name: myisam_max_sort_file_size
Value: 2147483647
*************************** 93. row ***************************
Variable_name: myisam_repair_threads
Value: 1
*************************** 94. row ***************************
Variable_name: myisam_recover_options
Value: OFF
*************************** 95. row ***************************
Variable_name: myisam_sort_buffer_size
Value: 67108864
*************************** 96. row ***************************
Variable_name: net_buffer_length
Value: 1048576
*************************** 97. row ***************************
Variable_name: net_read_timeout
Value: 30
*************************** 98. row ***************************
Variable_name: net_retry_count
Value: 1000000
*************************** 99. row ***************************
Variable_name: net_write_timeout
Value: 60
*************************** 100. row ***************************
Variable_name: new
Value: OFF
*************************** 101. row ***************************
Variable_name: open_files_limit
Value: 11095
*************************** 102. row ***************************
Variable_name: pid_file
Value: /var/db/mysql/xena.pid
*************************** 103. row ***************************
Variable_name: log_error
Value:
*************************** 104. row ***************************
Variable_name: port
Value: 3306
*************************** 105. row ***************************
Variable_name: protocol_version
Value: 10
*************************** 106. row ***************************
Variable_name: query_alloc_block_size
Value: 8192
*************************** 107. row ***************************
Variable_name: query_cache_limit
Value: 1048576
*************************** 108. row ***************************
Variable_name: query_cache_size
Value: 16777216
*************************** 109. row ***************************
Variable_name: query_cache_type
Value: ON
*************************** 110. row ***************************
Variable_name: query_prealloc_size
Value: 8192
*************************** 111. row ***************************
Variable_name: range_alloc_block_size
Value: 2048
*************************** 112. row ***************************
Variable_name: read_buffer_size
Value: 131072
*************************** 113. row ***************************
Variable_name: read_only
Value: OFF
*************************** 114. row ***************************
Variable_name: read_rnd_buffer_size
Value: 262144
*************************** 115. row ***************************
Variable_name: rpl_recovery_rank
Value: 0
*************************** 116. row ***************************
Variable_name: server_id
Value: 10
*************************** 117. row ***************************
Variable_name: slave_net_timeout
Value: 3600
*************************** 118. row ***************************
Variable_name: skip_external_locking
Value: ON
*************************** 119. row ***************************
Variable_name: skip_networking
Value: OFF
*************************** 120. row ***************************
Variable_name: skip_show_database
Value: OFF
*************************** 121. row ***************************
Variable_name: slow_launch_time
Value: 2
*************************** 122. row ***************************
Variable_name: socket
Value: /tmp/mysql.sock
*************************** 123. row ***************************
Variable_name: sort_buffer_size
Value: 1048568
*************************** 124. row ***************************
Variable_name: sql_mode
Value: 0
*************************** 125. row ***************************
Variable_name: table_cache
Value: 256
*************************** 126. row ***************************
Variable_name: table_type
Value: MYISAM
*************************** 127. row ***************************
Variable_name: thread_cache_size
Value: 8
*************************** 128. row ***************************
Variable_name: thread_stack
Value: 196608
*************************** 129. row ***************************
Variable_name: tx_isolation
Value: REPEATABLE-READ
*************************** 130. row ***************************
Variable_name: timezone
Value: EDT
*************************** 131. row ***************************
Variable_name: tmp_table_size
Value: 33554432
*************************** 132. row ***************************
Variable_name: tmpdir
Value: /var/tmp/
*************************** 133. row ***************************
Variable_name: transaction_alloc_block_size
Value: 8192
*************************** 134. row ***************************
Variable_name: transaction_prealloc_size
Value: 4096
*************************** 135. row ***************************
Variable_name: version
Value: 4.0.20-log
*************************** 136. row ***************************
Variable_name: version_comment
Value: FreeBSD port: mysql-server-4.0.20
*************************** 137. row ***************************
Variable_name: version_compile_os
Value: portbld-freebsd4.8
*************************** 138. row ***************************
Variable_name: wait_timeout
Value: 28800
138 rows in set (0.00 sec)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
