Hi!
I am trying to understand and fix a severe performance problem I am having
with MySQL for some weeks now, but to no avail. So I am coming here, hoping
you understand more than I do (which is not very hard to do :-)
Im am running mysql 3.23.41 (from the mysql-server-3.23.41-1 debian
package) on a linux 2.2.19pre17 system with 512MB RAM and an 1GHz Pentium
III Processor together witch apache 1.3.20 and php4.0.6.
Every requested page required a mysql connection and does on the average 5
queries. Most, if not all of the system processing resources are being used
by the various mysld demons that fork; the apache/php workload is less than
1% of total CPU (i use apc for php caching).
Currently, mysql maxes out at about 50 queries per second, which translates
to a system load of about 8. I have restricted apache to start a maximum of
60 httpds, if I leave apache at the standard setting of 150 servers, the
system load jumps to about 200 (!) and everything grinds to a complete halt
for maybe half an hour.
Of course, I would like to have more users concurrently accessing the
system. To my understanding, mysql should be able to handle 1000 req/sec on
that kind of machine easily.
Looking in the process table reveals (when the page is fast) lots of
sleeping processes, sometimes (when the page is slow -- 30sec to load a web
page) lots (20+) processes that are locked. Usually they are some SELECTs
and UPDATEswaiting for a single table that is the most update intensive
(one update per web page served, changing one to three columns in one row
that is indexed by a primary key). The complete DB size 380MB, the "problem
table" is 4MB large.
I tried changing every parameter that I could think of. Now I am absolutely
stuck. Or am I wrong in thinking that my machine should be fast enough for
that kind of application?
I have included below all diagnostic output i could think of; if you need
more please ask.
Thank you very much for your help in advance
Henning Schroeder
----------- variables -------------------------------------------------
+---------------------------------+---------------------------------
| Variable_name |
Value
+---------------------------------+---------------------------------
| back_log | 50
| basedir | /usr/
| bdb_cache_size | 8388600
| bdb_log_buffer_size | 262144
| bdb_home | /var/lib/mysql/
| bdb_max_lock | 10000
| bdb_logdir |
| bdb_shared_data | OFF
| bdb_tmpdir | /tmp/
| bdb_version | Sleepycat Software: Berkeley DB 3.2.9a:
(August 11, 2001)
| binlog_cache_size | 32768
| character_set | latin1
| character_sets | latin1 big5 cp1251 cp1257 croat czech
danish dec8 dos estonia euc_kr gb2312 gbk german1 greek hebrew hp8
hungarian koi8_ru koi8_ukr latin2 latin5 swe7 usa7 win1250 win1251
win1251ukr ujis sjis tis620
| concurrent_insert | ON
| connect_timeout | 5
| datadir | /var/lib/mysql/
| delay_key_write | ON
| delayed_insert_limit | 100
| delayed_insert_timeout | 300
| delayed_queue_size | 1000
| flush | OFF
| flush_time | 0
| have_bdb | YES
| have_gemini | NO
| have_innodb | DISABLED
| have_isam | YES
| have_raid | YES
| have_ssl | NO
| init_file |
| innodb_data_file_path |
| innodb_data_home_dir |
| innodb_flush_log_at_trx_commit | OFF
| innodb_log_arch_dir |
| innodb_log_archive | OFF
| innodb_log_group_home_dir |
| innodb_flush_method |
| interactive_timeout | 28800
| join_buffer_size | 131072
| key_buffer_size | 134213632
| language | /usr/share/mysql/english/
| large_files_support | ON
| locked_in_memory | OFF
| log | OFF
| log_update | OFF
| log_bin | OFF
| log_slave_updates | OFF
| log_long_queries | ON
| long_query_time | 10
| low_priority_updates | OFF
| lower_case_table_names | 0
| max_allowed_packet | 1047552
| max_binlog_cache_size | 4294967295
| max_binlog_size | 1073741824
| max_connections | 200
| 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_recover_options | 0
| myisam_max_extra_sort_file_size | 256
| myisam_max_sort_file_size | 2047
| myisam_sort_buffer_size | 67108864
| net_buffer_length | 16384
| net_read_timeout | 30
| net_retry_count | 10
| net_write_timeout | 60
| open_files_limit | 0
| pid_file | /var/run/mysqld/mysqld.pid
| port | 0
| protocol_version | 10
| record_buffer | 1044480
| record_rnd_buffer | 1044480
| query_buffer_size | 0
| safe_show_database | OFF
| server_id | 0
| slave_net_timeout | 3600
| skip_locking | ON
| skip_networking | ON
| skip_show_database | OFF
| slow_launch_time | 2
| socket | /var/run/mysqld/mysqld.sock
| sort_buffer | 4194296
| sql_mode | 0
| table_cache | 512
| table_type | MYISAM
| thread_cache_size | 16
| thread_stack | 131072
| transaction_isolation | READ-COMMITTED
| timezone | CEST
| tmp_table_size | 134217728
| tmpdir | /tmp/
| version | 3.23.41-log
| wait_timeout | 360
+---------------------------------+------------------------------------
-------------- extended status
--------------------------------------------------
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 9 |
| Aborted_connects | 4 |
| Bytes_received | 27180989 |
| Bytes_sent | 184910491 |
| Connections | 2768 |
| Created_tmp_disk_tables | 7726 |
| Created_tmp_tables | 157666 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 1 |
| Delayed_writes | 6135 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 8099 |
| Handler_read_first | 1 |
| Handler_read_key | 573768 |
| Handler_read_next | 73686819 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 370919 |
| Handler_read_rnd_next | 47216977 |
| Handler_update | 27369 |
| Handler_write | 1681512 |
| Key_blocks_used | 11686 |
| Key_read_requests | 7726157 |
| Key_reads | 11278 |
| Key_write_requests | 88063 |
| Key_writes | 0 |
| Max_used_connections | 61 |
| Not_flushed_key_blocks | 3458 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 248 |
| Open_files | 270 |
| Open_streams | 0 |
| Opened_tables | 254 |
| Questions | 256590 |
| Select_full_join | 0 |
| Select_full_range_join | 170 |
| Select_range | 46664 |
| Select_range_check | 0 |
| Select_scan | 12011 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 31 |
| Slow_queries | 11 |
| Sort_merge_passes | 0 |
| Sort_range | 20829 |
| Sort_rows | 370916 |
| Sort_scan | 7648 |
| Table_locks_immediate | 248904 |
| Table_locks_waited | 6543 |
| Threads_cached | 2 |
| Threads_created | 133 |
| Threads_connected | 55 |
| Threads_running | 35 |
| Uptime | 5011 |
+--------------------------+-----------+
------------- my.cnf
-----------------------------------------------------------
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[safe_mysqld]
err-log = /var/log/mysql.err
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
#log = /var/log/mysql.log
log-slow-queries = /var/log/mysql.slow
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-locking
skip-networking
delay-key-write-for-all-tables
set-variable = key_buffer=128M
set-variable = max_allowed_packet=1M
set-variable = thread_stack=128K
set-variable = table_cache=512
set-variable = sort_buffer=4M
set-variable = record_buffer=1M
set-variable = wait_timeout=360
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=16
set-variable = max_connections=200
set-variable = tmp_table_size=128M
[mysqldump]
quick
set-variable = max_allowed_packet=1M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
set-variable = key_buffer=16M
----------------- vmstat 1 output
----------------------------------------------
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us
sy id
30 0 0 0 14064 40088 309524 0 0 7 9 6 30 8 3 25
34 0 0 0 13008 40088 309524 0 0 4 33 963 912 99 1 0
8 0 0 0 7772 40160
309632 0 0 22 8 161 812 83 16 1
33 0 0 0 9432 40160 309632 0 0 4 0 563 865 97 3 0
4 0 0 0 11128 40160
309632 0 0 0 28 152 141 92 8 0
6 0 0 0 9960 40160
309632 0 0 2 0 143 168 96 4 0
5 0 0 0 10304 40160
309632 0 0 1 0 154 123 95 4 1
5 0 0 0 10708 40160
309640 0 0 2 0 177 173 94 6 0
4 0 0 0 10980 40160
309640 0 0 0 0 144 131 96 4 0
5 0 0 0 11780 40160
309656 0 0 5 29 239 559 96 4 0
4 0 0 0 11916 40160
309656 0 0 0 0 172 168 94 5 1
8 0 0 0 16140 40160
309656 0 0 1 0 219 195 97 3 0
13 0 0 0 16120 40160 309656 0 0 0 2 170 106 97 3 0
20 0 0 0 15188 40160 309680 0 0 31 50 1159 856 98 2 0
17 0 0 0 16028 40216 309796 0 0 4 0 211 754 81 18 1
5 0 0 0 17192 40216
309796 0 0 0 0 171 936 93 7 0
5 0 0 0 18224 40216
309796 0 0 0 2 177 301 95 5 0
6 0 0 0 16008 40216
309796 0 0 0 4 145 199 96 3 1
5 0 0 0 14180 40216
309808 0 0 4 0 196 248 95 5 0
3 0 0 0 13680 40216
309808 0 0 0 0 294 432 92 8 0
4 0 0 0 15984 40216
309808 0 0 1 37 255 260 96 4 0
10 0 0 0 15920 40216 309808 0 0 0 0 147 76 97 3 1
15 0 0 0 15876 40216 309808 0 0 0 0 135 79 96 4 0
19 0 0 0 15852 40216 309808 0 0 9 47 899 632 99 1 0
[...]
--------- free
-------------------------------------------------------------------
total used free shared buffers cached
Mem: 517612 500768 16844 232228 40976 320756
-/+ buffers/cache: 139036 378576
Swap: 1992040 0 1992040
---------------------------------------------------------------------
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
