Big load average Dell PowerEdge 6850 and MySQL 5.0.88-ius-log Distributed by The IUS Community Project

[Ivan Korsun]

Hello.

Few days ago, I launch server Dell PowerEdge 6850 (4xIntel(R) Xeon(TM) MP CPU 3.66GHz (8 cores), Memory 8x2GB, HDD 4x73GB - server A102 inner name). Server used for hosting the site (more than 1 million hits per day).
On server I use httpd, nginx and MySQL. Then it's start to work I see big load average: 14.50, 18.58, 21.70 and more. This time pages loading very slow.

Before I use two servers: Dell PowerEdge 1750 2xIntel(R) Xeon(TM) MP CPU 3.00GHz (4 cores) - inner name A100, Memory 6GB, HDD 140GB; nonbrand server Intel(R) Core(TM)2 Quad CPU Q6600 2.40GHz (4 cores) - server inner name A101, Memory 6GB, HDD 280GB. In this configuration Dell used for httpd, php and etc (with load average: 1.55, 1.68, 1.73) and A101 server used for MySQL (load average: 3.26, 3.91, 4.78).

My question is:
why on Dell PowerEdge 6850 (A102) so big la? It contains 8 cores on server A102 and 8 cores in summary on servers A100 and A101.
Maby somebody can help me to fix config file or some hard fixes (if it's because of 8 cores) for server A102.

All data was checked at highest loads.
My my.cnf files at bottom.

A102 - MySQL version 5.0.88-ius-log Distributed by The IUS Community Project
A101 - MySQL version 5.0.86-log Source distribution

Also I test database by query result time:
-------------------
SELECT t1.*, t4.name AS name1, t4.name AS name2, t3.name AS name3, t3.shortname AS name4, t5.name AS name5, t5.shortname AS name6, t5.bigname AS name7, t2.name AS name8, t2.shortname AS name9
FROM table1 AS t1
LEFT JOIN table2 AS t2 ON t2.pid = t1.id
LEFT JOIN table3 AS t3 ON t3.id = t1.pid
LEFT JOIN table4 AS t4 ON t4.id = t3.pid
LEFT JOIN table5 AS t5 ON t5.id = t1.pid
WHERE t1.enable = 1
ORDER BY t1.datetime DESC
LIMIT 0,50

server - onload first - onload second - without load
A102 - 110.1307 - 118.0584 - 0.7104
A101 - 1.0192 - 0.5303 - 0.6743
-------------------

Best regards.


----------------------------A102-----------------------------

#BEGIN CONFIG INFO
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries
#TYPE: SYSTEM
#END CONFIG INFO (edited from my-innodb-heavy-4G.cnf)

[client]
#password = [your_password]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock

default-character-set = cp1251
character-set-server = cp1251
collation-server = cp1251_general_ci
init-connect = "SET NAMES cp1251"
skip-character-set-client-handshake

log-queries-not-using-indexes
skip-name-resolve

#sql-mode = TRADITIONAL
#concurrent_insert = 2
low_priority_updates = 1
#max_connections = 200
wait_timeout = 10
interactive_timeout = 25

back_log = 50
#skip-networking
max_connections = 400
max_connect_errors = 10
table_cache = 2048
#external-locking
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 18
thread_concurrency = 24
query_cache_size = 64M
query_cache_limit = 8M
ft_min_word_len = 4
#memlock
default_table_type = MYISAM
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M
#log-bin=mysql-bin
#log_slave_updates
#log
#log_warnings
log_slow_queries
long_query_time = 10
log_long_format
#tmpdir = /tmp

# *** Replication related settings
server-id = 1
#master-host = <hostname>
#master-user = <username>
#master-password = <password>
#master-port = <port>
#read_only

#*** MyISAM Specific options
key_buffer_size = 256M
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

# Disable Federated by default
skip-federated

# *** BDB Specific options ***
skip-bdb

# *** INNODB Specific options ***
#skip-innodb
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 1G
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_data_home_dir = <directory>
innodb_file_io_threads = 4
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
#innodb_fast_shutdown
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
#safe-updates

[isamchk]
key_buffer = 512M
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

[mysqld_safe]
open-files-limit = 8192

----------------------------A101-----------------------------

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1

default-character-set = cp1251
character-set-server = cp1251
collation-server = cp1251_general_ci
init-connect = "SET NAMES cp1251"
skip-character-set-client-handshake

max_connections = 450
slow_query_log_file=/var/log/mysql_slow_log
slow_query_log=1

skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 4M
join_buffer_size=2M
myisam_sort_buffer_size = 64M
thread_cache_size = 18
query_cache_size = 80M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8

skip-federated
# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_data_file_path = ibdata1:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 512M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 128M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 2
#innodb_lock_wait_timeout = 50

skip-name-resolve

# To allow mysqld to connect to a MySQL Cluster management daemon, uncomment
# these lines and adjust the connectstring as needed.
#ndbcluster
#ndb-connectstring="nodeid=4;host=localhost:1186"

# To enable the InnoDB Plugin, uncomment the 2 next lines
#ignore-builtin-innodb
#plugin-load=innodb=ha_innodb_plugin.so

# To enable InnoDB-related INFORMATION_SCHEMA tables
# Join the following options to above directive
;innodb_trx=ha_innodb_plugin.so
;innodb_locks=ha_innodb_plugin.so
;innodb_cmp=ha_innodb_plugin.so
;innodb_cmp_reset=ha_innodb_plugin.so
;innodb_cmpmem=ha_innodb_plugin.so
;innodb_cmpmem_reset=ha_innodb_plugin.so

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[ndbd]
# If you are running a MySQL Cluster storage daemon (ndbd) on this machine,
# adjust its connection to the management daemon here.
# Note: ndbd init script requires this to include nodeid!
connect-string="nodeid=2;host=localhost:1186"

[ndb_mgm]
# connection string for MySQL Cluster management tool
connect-string="host=localhost:1186"