MySQL Forums
Forum List  »  Performance

Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
Posted by: Timothy Karl
Date: March 19, 2009 07:17AM


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 #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
port = 3306
socket = /data/mysql/data/mysql.sock

socket = /data/mysql/data/mysql.sock
err-log = /var/lib/mysql/mysqld.log
open-files-limit = 8192
datadir = /data/mysql/data

# * Basic Settings
user = mysql
pid-file = /data/mysql/data/
socket = /data/mysql/data/mysql.sock
port = 3306
basedir = /usr
datadir = /data/mysql/data
tmpdir = /data/mysql/temp


max_connections = 400

thread_cache_size = 50


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


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_lock_wait_timeout = 120

max_allowed_packet = 16M


key_buffer = 16M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M


//END my.cnf

Edited 1 time(s). Last edit at 03/19/2009 07:19AM by Timothy Karl.

Options: ReplyQuote

Written By
Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM
March 19, 2009 07:17AM

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.