MySQL Forums :: Performance :: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM


Advanced Search

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

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.

Options: ReplyQuote


Subject Views Written By Posted
Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 7183 Timothy Karl 03/19/2009 07:17AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2928 Aftab Khan 03/19/2009 09:10AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2480 Rick James 03/19/2009 11:05AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2451 Timothy Karl 03/20/2009 08:46AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 3272 Timothy Karl 03/19/2009 01:30PM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2268 Aftab Khan 03/20/2009 06:42AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2754 Timothy Karl 03/20/2009 07:25AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2119 Aftab Khan 03/20/2009 11:29AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2468 Rick James 03/20/2009 08:25PM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2255 Timothy Karl 03/20/2009 07:57AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2307 Aftab Khan 03/20/2009 08:56AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2319 Timothy Karl 03/20/2009 10:52AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 1934 Aftab Khan 03/20/2009 11:10AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2065 Aftab Khan 03/23/2009 04:01AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2254 Timothy Karl 03/19/2009 01:36PM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2552 Timothy Karl 03/19/2009 01:42PM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 1910 Timothy Karl 03/19/2009 01:48PM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2092 Aftab Khan 03/20/2009 04:11AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2898 Timothy Karl 04/15/2009 03:24AM
Re: Extreme high load and low throughput using MySQL & Hybris on an HP DL380G5, quad-core Intel, 4GB RAM 2270 Rick James 04/15/2009 09:55AM


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.