MySQL Forums
Forum List  »  Quality Assurance

MySQL Heavy Load Problem (5.0.67)
Posted by: Igor Askarov
Date: August 12, 2008 12:56PM

Hello!

We have a web based project serving about 800 000 hits a day. The server is running FreeBSD 6.2 amd_64 on Quad Xeon system with 4G of RAM. It was tuned before, according the MySQL-huge sample file and worked fine. Sometimes it got overloaded and came to the load of 300%+ CPU but usually returned into a normal state with no help.

The same day we have launched a new version of the project, inluding the new database design and brand new php-scripts, we have encountered a problem in MySQL server 5.0.51 leading it to a 'hanging state', when it refused to quit on normal kill signals. What is did in the log file is showing messages like this.

InnoDB: ###### Diagnostic info printed to the standard error stream
InnoDB: Warning: a long semaphore wait:
--Thread 2809260544 has waited at btr0sea.c line 489 for 465.00 seconds the semaphore:
X-lock on RW-latch at 0x1e860a8 created in file btr0sea.c line 139
a writer (thread id 2809260544) has reserved it in mode wait exclusive
number of readers 0, waiters flag 1
Last time read locked in file btr0sea.c line 746
Last time write locked in file btr0sea.c line 489
InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info:
InnoDB: Pending preads 0, pwrites 0

After comes some standart output produced by SHOW ENGINE INNODB STATUS. It always contained hudreds of SELECT-transactions on InnoDB tables;

This was a kind of serious failure for us, so we were happy do discover the new version 5.0.67 is out. It is not shipped by FreeBSD ports yet, so I we decided to install binaries from mysql.com.

The situation got a little better but still we have problems. The only opportunity we have achieved is that now we can just restart the server with the rc.script when is goes bad.

After some time of running, server consumes more and more memory and just in a moments comes to a load of 150%, completely stopping answering the queries until it gets restarted. After each restart server keeps using 1-15% of CPU (for hour or two). I am sure, nothing special happens with the workload before is stucks.

The main difference between the project versions is that the new one uses transactions and have more innodb tables. The database design is much more efficient. The data content and amount is almost the same.

The thing is we have another server which is completely the same, running the same MySQL server (5.0.51), the same type of tables under the control of the same framework. It just about 20 times less loaded. And it works fine with no problems day by day.

Mybe someone has experienced the same kind of problems? Any suggestions? I can provide mode 'show innodb engined status' if it is necessary.

Thank you,
Igor.


------

my.cnf:

[mysqld]
default-character-set=utf8
character-set-server=utf8
collation-server=utf8_general_ci
init-connect="SET NAMES utf8"
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 16M
table_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size = 16M
query_cache_min_res_unit = 4096
query_cache_type = 1
# the next value switched to different smaller values (12, 8) doesn't fix the problem
thread_concurrency = 16
max_connections=150

myisam_repair_threads = 2

innodb_additional_mem_pool_size = 16M
# I also tried 1500M - no result
innodb_buffer_pool_size = 2500M
innodb_file_per_table = 1
innodb_open_files = 1024

log-bin
# Here I tried to disable binary logging but it doesnt help
#binlog-do-db = super
server-id = 2
auto_increment_increment = 1
auto_increment_offset = 2

expire-logs-days = 5

-----

Here the top screen for mysql when is goes to critical state:
last pid: 61286; load averages: 2.30, 2.22, 2.05
up
11+09:05:06 20:50:33
142 processes: 3 running, 139 sleeping
CPU states: 6.4% user, 0.0% nice, 17.8% system, 0.3% interrupt, 75.4% idle
Mem: 1846M Active, 714M Inact, 190M Wired, 168M Cache, 214M Buf, 873M Free
Swap: 1024M Total, 3040K Used, 1021M Free

PID USERNAME THR PRI NICE SIZE RES STATE C TIME WCPU COMMAND
61089 mysql 111 20 0 3447M 1805M kserel 4 120:08 148.34% mysqld

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL Heavy Load Problem (5.0.67)
7098
August 12, 2008 12:56PM


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.