I can't help you with your problem, because I have almost the same thing
happening to my server every 2-3 days. It is exactly as you described....
Please someone offer us suggestions??
Ryan Shrout
Owner - Amdmb.com
http://www.amdmb.com/
[EMAIL PROTECTED]
----- Original Message -----
From: "Henning Schroeder" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, September 05, 2001 5:10 PM
Subject: MySQL Performance Problem
> 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
>
---------------------------------------------------------------------
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
