Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
Hello,
We are having issues with extreme high load and low throughput on our database server. Our server is a HP DL380 G5 server, Intel(R) Xeon(R) CPU E5440 @ 2.83GHz, 4GB RAM, 4x 11K SAS HDDs in a RAID 10 configuration running SuSE Linux Enterprise 10 SP2 with the latest OS and HP updates available. The application server is using Hybris to interact with MySQL. The database server is *only* being used for queries from the application server. Our MySQL instance appears to be SELECT-heavy. We're using MySQL version 5.1.32-enterprise-gpl-advanced-log, which we just 'trialing' to see if there is any performance increase/difference. We've also tried version 5.0.26 which SuSE Enterprise Linux 10 SP2 distributes and version 5.1.32 Community Edition for SLES 10 x86_64.
Kernel: Linux mysql1 2.6.16.60-0.34-smp #1 SMP Fri Jan 16 14:59:01 UTC 2009 x86_64 x86_6
Under normal load (300-400 users) on the web application, system load on the MySQL server (using MyISAM tables) would reach 60 or above with 'top' reporting between 275-300% CPU for the mysqld process. There is almost no I/O wait, and no swap is being used. The query-cache is enabled and shows that we are inserting more queries into the cache then it is actually returning (more inserts than hits) - Hit:Insert 0.21:1 ( Hits approx. 10.2/s). The query throughput is very low with an average around 200/s. When load is high on the MySQL server, the web-application suffers becoming very slow. We are now trying the InnoDB engine, but it seems that we are having the same issues with throughput and load.
We also ran the same database on a HP DL380 machine with half the cores (2 cores), with lower load.
We ran some tests using JMeter (2000 threads, 100 connections), which showed great performance (up to 4000 queries per second, no load, very low CPU) using both MyISAM and InnoDB when MySQL was using only the query cache; however, as soon as the query cache was turned off, system load exploded to 90+, CPU utilization climbed to over 375% and throughput dropped to between 250-350 queries per second.
Our expectations would be that such a powerful dedicated machine setup would be able to handle queries for one web application very well. It seems that MySQL is not scaling to the hardware and there is obviously some type of bottleneck.
I've been working on this problem for weeks, have done loads of research on the web and in books such as High Performance MySQL; however, nothing has worked.
Any help or hints would be greatly appreciated!
Thank you all for your assistance!
// my.cnf
[client]
port = 3306
socket = /data/mysql/data/mysql.sock
[mysqld_safe]
socket = /data/mysql/data/mysql.sock
err-log = /var/lib/mysql/mysqld.log
open-files-limit = 8192
datadir = /data/mysql/data
[mysqld]
#
# * Basic Settings
#
user = mysql
pid-file = /data/mysql/data/mysqld.pid
socket = /data/mysql/data/mysql.sock
port = 3306
basedir = /usr
datadir = /data/mysql/data
tmpdir = /data/mysql/temp
skip-external-locking
max_connections = 400
thread_cache_size = 50
skip_name_resolve
log_warnings
slow_query_log
log_long_format
long_query_time = 5
slow_query_log_file = /var/lib/mysql/mysql1-slow-query.log
key_buffer = 384M
max_allowed_packet = 16M
thread_stack = 192K
table_cache = 2048
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 16M
join_buffer_size = 16M
query_cache_limit = 2M
query_cache_size = 64M
default_table_type = INNODB
transaction_isolation = READ-COMMITTED
tmp_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_max_extra_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
server-id = 1
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 2G
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 0
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 120
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 16M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M
[mysqlhotcopy]
interactive-timeout
//END my.cnf
Edited 1 time(s). Last edit at 03/19/2009 07:19AM by Timothy Karl.