Under normal operating conditions our v3.23.47 MySql db server handles about
60 queries per second using 50 threads or less. The host is a dual processor
700MHz Intel machine running the 2.2.14-5.0smp Linux kernel with 256M of
RAM.
Here's the problem: during spikes of activity where about 90-100 queries/sec
are being processed, the number of slow queries ties up an ever-increasing
number of threads until our max connection limit is reached. This eventually
will cause clients to wait for an inactive thread which effectively freezes
our application. The slow query SQL statements are optimized - usually they
take < .5 sec (~10-20K rows being examined), but when the server is in this
state execution time ranges from 10-120 seconds.
Questions:
1. Are an abundance of slow queries symptomatic of an overloaded DB server?
(or are they simply a contributing factor?)
2. Is there a way to examine the state of a slow query besides the
slow-query log? I.e. is there a way to tell exactly why a query is running
slow? Running "explain" shows our SQL to be as optimized as it can get.
1. I've read threads in this group about similar server configurations
handling > 200 queries/sec. What are the most effective ways of benchmarking
a server against an application?
I've included the MySql setting below. Any help would be greatly
appreciated.
mysql> show variables;
+---------------------------------+-----------------------------------------
----------------------------------------------------------------------------
--------------------------------------+
| Variable_name | Value
|
+---------------------------------+-----------------------------------------
----------------------------------------------------------------------------
--------------------------------------+
| back_log | 50
|
| basedir | /opt/mysql-3.23.47/
|
| binlog_cache_size | 32768
|
| character_set | latin1
|
| character_sets | latin1 dec8 dos german1 hp8 koi8_ru
latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr
win1251ukr greek win1250 croat cp1257 latin5 |
| concurrent_insert | ON
|
| connect_timeout | 5
|
| datadir | /opt/mysql-3.23.47/var/
|
| delay_key_write | ON
|
| delayed_insert_limit | 100
|
| delayed_insert_timeout | 300
|
| delayed_queue_size | 1000
|
| flush | OFF
|
| flush_time | 0
|
| have_bdb | NO
|
| have_gemini | NO
|
| have_innodb | NO
|
| have_isam | YES
|
| have_raid | NO
|
| have_openssl | NO
|
| init_file |
|
| interactive_timeout | 28800
|
| join_buffer_size | 131072
|
| key_buffer_size | 8388600
|
| language | /opt/mysql-3.23.47/share/mysql/english/
|
| large_files_support | ON
|
| locked_in_memory | OFF
|
| log | OFF
|
| log_update | OFF
|
| log_bin | ON
|
| log_slave_updates | OFF
|
| log_long_queries | ON
|
| long_query_time | 10
|
| low_priority_updates | OFF
|
| lower_case_table_names | 0
|
| max_allowed_packet | 16776192
|
| max_binlog_cache_size | 4294967295
|
| max_binlog_size | 1073741824
|
| max_connections | 500
|
| max_connect_errors | 10
|
| max_delayed_threads | 20
|
| max_heap_table_size | 16777216
|
| max_join_size | 4294967295
|
| max_sort_length | 1024
|
| max_user_connections | 0
|
| max_tmp_tables | 32
|
| max_write_lock_count | 4294967295
|
| myisam_max_extra_sort_file_size | 256
|
| myisam_max_sort_file_size | 2047
|
| myisam_recover_options | 0
|
| myisam_sort_buffer_size | 8388608
|
| net_buffer_length | 16384
|
| net_read_timeout | 30
|
| net_retry_count | 10
|
| net_write_timeout | 60
|
| open_files_limit | 0
|
| pid_file | /opt/mysql-3.23.47/var/winston.pid
|
| port | 3306
|
| protocol_version | 10
|
| record_buffer | 131072
|
| record_rnd_buffer | 131072
|
| query_buffer_size | 0
|
| safe_show_database | OFF
|
| server_id | 1
|
| slave_net_timeout | 3600
|
| skip_locking | ON
|
| skip_networking | OFF
|
| skip_show_database | OFF
|
| slow_launch_time | 2
|
| socket | /tmp/mysql.sock
|
| sort_buffer | 2097144
|
| sql_mode | 0
|
| table_cache | 64
|
| table_type | MYISAM
|
| thread_cache_size | 0
|
| thread_stack | 65536
|
| transaction_isolation | READ-COMMITTED
|
| timezone | EDT
|
| tmp_table_size | 3145728
|
| tmpdir | /tmp/
|
| version | 3.23.47-log
|
| wait_timeout | 28800
|
+---------------------------------+-----------------------------------------
----------------------------------------------------------------------------
--------------------------------------+
82 rows in set (0.00 sec)
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
