Hi all, this is my first question on this list and I hope that you can
help me. I've got one dedicated box for MySQL and it's going downhill just
by starting the DB. The box is a dual PIII 800Mhz, 1 Ghz RAM, Linux RedHat
6.2 w/ kernel 2.4.6SMP, SCSI Drives and plugged to a 100Mb internal
network. MySQL is latest rpm version (3.23.42). There are 3 frontends
(apache 1.3.20 + php4 / running ezPublish) shooting questions at this box.
This evening it has started to raise the load of the machine (even
over 50) and it's going useless. Here is the main configuration of MySQL:
/etc/my.cnf
[mysqld]
log-bin
server-id=1
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=256
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=4
set-variable = wait_timeout=300
set-variable = max_connect_errors=1000
set-variable = max_connections=300
log-slow
From SHOW VARIABLES
| max_allowed_packet | 1047552
| max_connections | 300
| max_connect_errors | 1000
| 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_sort_buffer_size | 67108864
| net_buffer_length | 16384
| record_buffer | 1044480
| record_rnd_buffer | 1044480
| query_buffer_size | 0
| sort_buffer | 1048568
| table_cache | 256
| table_type | MYISAM
| thread_cache_size | 8
| thread_stack | 65536
| tmp_table_size | 33554432
| version | 3.23.42-log
| wait_timeout | 300
From SHOW extended-status
+--------------------------+-----------+
| Variable_name | Value |
+--------------------------+-----------+
| Aborted_clients | 11 |
| Aborted_connects | 0 |
| Bytes_received | 3276773 |
| Bytes_sent | 12091463 |
| Connections | 508 |
| Created_tmp_disk_tables | 21 |
| Created_tmp_tables | 1187 |
| Created_tmp_files | 32 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_delete | 0 |
| Handler_read_first | 149 |
| Handler_read_key | 546266 |
| Handler_read_next | 140968 |
| Handler_read_prev | 80 |
| Handler_read_rnd | 24511 |
| Handler_read_rnd_next | 536386110 |
| Handler_update | 202796 |
| Handler_write | 204321 |
| Key_blocks_used | 845 |
| Key_read_requests | 310986 |
| Key_reads | 834 |
| Key_write_requests | 1214 |
| Key_writes | 993 |
| Max_used_connections | 174 |
| Not_flushed_key_blocks | 6 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 256 |
| Open_files | 325 |
| Open_streams | 0 |
| Opened_tables | 340 |
| Questions | 23070 |
| Select_full_join | 1533 |
| Select_full_range_join | 0 |
| Select_range | 366 |
| Select_range_check | 0 |
| Select_scan | 10150 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 1 |
| Slow_queries | 16 |
| Sort_merge_passes | 16 |
| Sort_range | 328 |
| Sort_rows | 2566631 |
| Sort_scan | 1625 |
| Table_locks_immediate | 21913 |
| Table_locks_waited | 3026 |
| Threads_cached | 0 |
| Threads_created | 175 |
| Threads_connected | 175 |
| Threads_running | 100 |
| Uptime | 417 |
+--------------------------+-----------+
After 10 minutes running this is the output of top and vmstat 1:
6:54pm up 2:04, 5 users, load average: 51.63, 42.91, 36.94
273 processes: 236 sleeping, 37 running, 0 zombie, 0 stopped
CPU states: 59.3% user, 40.6% system, 0.0% nice, 0.0% idle
Mem: 1028968K av, 313836K used, 715132K free, 0K shrd, 13408K buff
Swap: 2064376K av, 0K used, 2064376K free 227364K cached
PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND
5089 mysql 15 0 66872 65M 1584 S 0 3.5 6.4 0:01 mysqld
4926 mysql 13 0 40796 39M 1584 S 0 3.3 3.9 0:06 mysqld
5046 mysql 20 0 88076 86M 1584 R 0 3.3 8.5 0:01 mysqld
4906 mysql 20 0 74476 72M 1584 R 0 3.2 7.2 0:07 mysqld
4919 mysql 11 0 40672 39M 1584 S 0 3.2 3.9 0:03 mysqld
4949 mysql 20 0 90196 88M 1584 R 0 3.2 8.7 0:06 mysqld
4966 mysql 20 0 56400 55M 1584 R 0 3.2 5.4 0:09 mysqld
5026 mysql 18 0 40500 39M 1584 R 0 3.2 3.9 0:04 mysqld
5038 mysql 11 0 40500 39M 1584 S 0 3.2 3.9 0:02 mysqld
5040 mysql 15 0 40500 39M 1584 S 0 3.2 3.9 0:02 mysqld
4866 mysql 19 0 77192 75M 1584 R 0 3.1 7.5 0:05 mysqld
4922 mysql 13 0 40796 39M 1584 S 0 3.1 3.9 0:09 mysqld
4924 mysql 13 0 40796 39M 1584 S 0 3.1 3.9 0:07 mysqld
4931 mysql 10 0 40796 39M 1584 S 0 3.1 3.9 0:08 mysqld
[...]
Around 250 mysqld processes running...
(back2)-~# vmstat 1
procs memory swap io system
cpu
r b w swpd free buff cache si so bi bo in cs us sy id
49 0 0 0 681308 13456 233472 0 0 13 44 147 796 56 36 8
48 0 0 0 659200 13456 233472 0 0 0 0 232 136 59 41 0
33 0 0 0 690660 13456 233472 0 0 0 0 642 403 63 37 0
49 0 0 0 667828 13456 233472 0 0 0 428 440 757 58 42 0
51 0 0 0 656772 13456 233472 0 0 0 0 490 268 58 42 0
38 0 0 0 691880 13456 234116 0 0 0 0 586 660 60 40 0
28 0 1 0 686508 13456 234116 0 0 0 860 429 829 57 42 0
14 0 0 0 699224 13456 234116 0 0 0 0 440 300 67 33 0
54 1 0 0 676552 13456 234120 0 0 0 0 403 612 60 40 0
56 0 0 0 653892 13456 234120 0 0 0 0 135 92 52 48 0
The programs are doing persistent connections, but if I change it to
normal connections, they do the same. I've been trying with different
configuration values starting from the default and nothing.
I've been looking at this list archives, mysql.com and many google
searchs and to no avail. Do any of you see something strange looking at
this data? Any ideas about where to look?
If you have any questions regarding the system please ask... thanks in
advance.
Cheers.
--
Luis Calero Muņoz
$email{luis} = '[EMAIL PROTECTED]'
$who{luis} = 'sysadm at ociojoven dot com'
---------------------------------------------------------------------
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
