>Description:
This busy mysql server will run well for about one week,
with 95% idle cpu. Then at some point it will get "stuck," so
that it is still very responsive, but cpu utilization changes
to 95% system cpu and 0% idle cpu.
When the server is in this condition and I use the ktrace utility
to trace system calls, the output of ktrace consists of about 44%
calls to poll() and 44% calls to gettimeofday(), in a pattern like
this:
bash-2.05# kdump -Rf ktrace.stuck | more
[...]
45689 mysqld 0.000011 CALL gettimeofday(0x821ae28,0)
45689 mysqld 0.000014 RET gettimeofday 0
45689 mysqld 0.000024 CALL poll(0x8270000,0x92,0x4c)
45689 mysqld 0.000041 RET poll 1
45689 mysqld 0.000010 CALL gettimeofday(0x821ae28,0)
45689 mysqld 0.000015 RET gettimeofday 0
45689 mysqld 0.000024 CALL poll(0x8270000,0x92,0x4c)
45689 mysqld 0.000041 RET poll 1
A ktrace of a normal server, in comparison, contains less than 1%
each of gettimeofday() and poll().
Here is the output of "show status" from server in the stuck
condition, and the m.cnf file
mysql> show status;
+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 1333 |
| Aborted_connects | 66 |
| Bytes_received | 643612919 |
| Bytes_sent | 1691709087 |
| Connections | 323428 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 13686 |
| Created_tmp_files | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 5 |
| Handler_delete | 2 |
| Handler_read_first | 12791 |
| Handler_read_key | 20974993 |
| Handler_read_next | 190435337 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 28883776 |
| Handler_read_rnd_next | 1290867357 |
| Handler_update | 7457 |
| Handler_write | 28333553 |
| Key_blocks_used | 145471 |
| Key_read_requests | 421502449 |
| Key_reads | 145451 |
| Key_write_requests | 1236 |
| Key_writes | 551 |
| Max_used_connections | 353 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 446 |
| Open_files | 859 |
| Open_streams | 0 |
| Opened_tables | 928 |
| Questions | 5605574 |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 141218 |
| Select_range_check | 0 |
| Select_scan | 358753 |
| Slave_running | OFF |
| Slave_open_temp_tables | 0 |
| Slow_launch_threads | 0 |
| Slow_queries | 328 |
| Sort_merge_passes | 0 |
| Sort_range | 43265 |
| Sort_rows | 28883773 |
| Sort_scan | 85805 |
| Table_locks_immediate | 5306255 |
| Table_locks_waited | 11 |
| Threads_cached | 3 |
| Threads_created | 15889 |
| Threads_connected | 145 |
| Threads_running | 2 |
| Uptime | 1054801 |
+--------------------------+------------+
bash-2.05# cat /etc/my.cnf
# MySQL config file for greatschools.net
# Based on samples/my-large.cnf
#
# "for large system with memory = 512M where the system runs mainly
# MySQL."
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock
# Here follows entries for some specific programs
# The wrapper around mysqld
[safe_mysqld]
err-log = /var/log/mysql/errorlog
# The MySQL server
[mysqld]
log-bin = /var/log/mysql/binlog
log-slow-queries = /var/log/mysql/slow_sql
pid-file = /var/log/mysql/mysqld.pid
bdb-logdir = /var/log/mysql/berkeley-db
port = 3306
socket = /tmp/mysql.sock
skip-locking
# Variables
set-variable = long_query_time=1
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=1000
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=8
set-variable = max_connections=600
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=2
server-id = 5
# Check & Repair tables on startup
myisam-recover = BACKUP,FORCE
# Uncomment the following if you are using BDB tables
set-variable = bdb_cache_size=64M
set-variable = bdb_max_lock=100000
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
>How-To-Repeat:
>Fix:
>Submitter-Id: <submitter ID>
>Originator: John David Duncan
>Organization:
greatschools.net
>MySQL support: none
>Synopsis: excessive system cpu utilization
>Severity: non-critical
>Priority: medium
>Category: mysql
>Class: sw-bug
>Release: mysql-3.23.39 (yes)
>Environment:
System: FreeBSD great5 4.3-RELEASE FreeBSD 4.3-RELEASE #0: Sat Jun 30 06:45:57 PDT
2001 [EMAIL PROTECTED]:/usr/src/sys/compile/GREAT i386
Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc
GCC: Using builtin specs.
gcc version 2.95.3 [FreeBSD] 20010315 (release)
Compilation info: CC='gcc' CFLAGS='-O3 -mpentiumpro' CXX='gcc' CXXFLAGS='-O3
-mpentiumpro -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS=''
LIBC:
-r--r--r-- 1 root wheel 1174570 Apr 21 02:05 /usr/lib/libc.a
lrwxrwxrwx 1 root wheel 9 Jun 27 20:55 /usr/lib/libc.so -> libc.so.4
-r--r--r-- 1 root wheel 561580 Apr 21 02:05 /usr/lib/libc.so.4
Configure command: ./configure --with-mysqld-user=mysql --enable-assembler
--with-comment --with-berkeley-db --localstatedir=/var/mysql
--with-mysqld-ldflags=-all-static --disable-shared --with-gnu-ld --with-charset=latin1
Perl: This is perl, version 5.005_03 built for i386-freebsd
---------------------------------------------------------------------
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
