MySQL Forums
Forum List  »  Performance

MySQL server "pauses" occasionally
Posted by: Eric Kienle
Date: October 17, 2008 03:23PM

We have been seeing ongoing performance issues with our primary MySQL server. We started seeing these issues several months ago. We have added more memory to the server and retuned the MySQL configuration, this has reduced the frequency of the issue. However, we still see significant issues, often several times a day. Here are the symptoms of the issue we are seeing:

* The MySQL server process appears to slow down or pause outright, not processing any questions or other operations
* The slow down appears to affect all connect client processes (our Jboss application servers in this case)
* The slowdown happens across database schemas, and doesn’t appear to correlate to locks or deadlocks
* The slowdowns typically last for about 1-2 minutes, based on observations in our error logs, and monitoring via our Hyperic monitors
* According to our log analysis, the slowdowns do not correlate with high load either in our application, or on the database server. They often happen at off-peak times, or even late at night, but can happen during the day as well.
* We have spent a good deal of time looking for correlating processes (backups, batch jobs, etc) that correspond to these slowdowns, but have found none that are running during the times we observe the issues.
* Upon analyzing the sar data for the server we have found that very large page outs, sar -B, greater than 10,000 kilobytes a second coincide with the slowdowns.
** We are not sure if the high page outs are a cause or an effect.


We are not sure how to solve this problem.


Here are the technical specifications of the impacted server:

Hardware:
Dell PowerEdge 2950
Dual Xeon Dual Core 2.0Ghz
16GB RAM (8x2GB)
(4)146GB SAS
RAID 10
The raid controller has Battery Backed Write Cache

OS:
Redhat EL 4 (64bit)

Kernel:
Linux 2.6.9-67.ELsmp #1 SMP Wed Nov 7 13:56:44 EST 2007 x86_64 x86_64
x86_64 GNU/Linux

Mysql:
mysql-5.0.45-linux-x86_64-glibc23

Mysql configuration:
[client]
port = 3306
socket = /tmp/mysql.sock

[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer_size = 32M
max_allowed_packet = 1M
table_cache = 2048
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 16
open-files-limit= 4096
binlog_cache_size = 1M
query_cache_min_res_unit=2048
query_cache_size = 256M
thread_concurrency = 8
default-storage-engine=innodb
max_connections=500
bind-address=10.120.0.60
long_query_time=1
log-slow-queries=/data/mysql/log-slow-queries.log
log-queries-not-using-indexes
log-bin=db3-bin
server-id=1

innodb_data_home_dir = /data/mysql
innodb_data_file_path =
ibdata1:2000M;ibdata2:20434M;ibdata3:5000M;ibdata4:5000M;ibdata5:10M:autoextend
innodb_log_group_home_dir = /data/mysql
innodb_log_arch_dir = /data/mysql
innodb_buffer_pool_size = 8606M
innodb_additional_mem_pool_size = 16M
innodb_log_file_size = 1250M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_log_files_in_group = 3

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL server "pauses" occasionally
3693
October 17, 2008 03:23PM
1903
October 19, 2008 09:35PM
2615
October 24, 2008 04:38PM
1735
December 12, 2008 03:32PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.