Another high cpu load topic

[Mitja Pirih]

Hello,

I started to experience high CPU load on my new installation of mysql v.5.0.51a. I am using InnoDB (for the primary job) and myisam.

Hardware in use:
DELL 2950 Intel Xeon CPU E5320 @ 1.86GHz (quad core)
2GB RAM, 6x SAS 300GB configure as 2 RAID5 arrays
DELL, PERC 5/i

OS: OpenBSD 4.3 GENERIC kernel with MP support enabled.

my.cnf
==================================================
[mysqld]
port = 3306
socket = /var/run/mysql/mysql.sock
skip-locking
safe-user-create
back_log = 50
max_connections = 100
table_cache = 2048
max_allowed_packet = 16M
binlog_cache_size = 1M
max_heap_table_size = 64M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 8
thread_concurrency = 8
transaction_isolation = REPEATABLE-READ
query_cache_size = 64M
query_cache_limit = 2M
tmp_table_size = 64M
log_slow_queries
key_buffer_size = 32M
read_buffer_size = 1M
read_rnd_buffer_size = 8M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 64M
max_connections = 200

set-variable = lower_case_table_names=1

#For replication purposes, currently replication is disabled
server-id = 10
auto_increment_increment = 10
auto_increment_offset = 1

log-error = /var/mysql/error.log

myisam_repair_threads = 1
myisam-recover
skip-bdb

innodb_data_home_dir = /var/mysql/
innodb_log_group_home_dir = /var/mysql/
innodb_log_arch_dir = /var/mysql/
innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 768M
innodb_data_file_path = ibdata1:10M:autoextend
innodb_file_io_threads = 4
innodb_thread_concurrency = 8
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 4M
write_buffer = 4M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 4096
==================================================

Kernel maxfiles is increased to 8192

mysql is running as user _mysql with the following settings:
datasize=infinity
maxproc=infinity
openfiles-cur=4096
openfiles-max=8192
stacksize-cur=8M

mysqlreport
==================================================

vmstat when doing a search:

procs memory page disks traps cpu
r b w avm fre flt re pi po fr sr sd0 sd1 int sys cs us sy id
1 0 0 147884 1661572 483 0 0 0 0 0 0 0 1022 1246 310 7 2 91
0 0 0 148148 1661308 95 0 0 0 0 0 0 0 2077 1890 527 10 4 86
1 0 0 148320 1661132 69 0 0 0 0 0 0 0 1666 1598 433 6 5 89
1 0 0 148564 1660888 87 0 0 0 0 0 0 0 2373 2100 575 11 4 86
1 0 0 148816 1660640 89 0 0 0 0 0 0 0 2481 2110 599 10 6 84
1 0 0 149076 1660372 91 0 0 0 0 0 0 0 2491 2255 613 12 5 83
1 0 0 149324 1660132 92 0 0 0 0 0 0 0 2530 2324 646 10 7 83
1 0 0 149496 1659960 69 0 0 0 0 0 0 0 2554 2267 644 10 6 84

vmstat after the search:

0 0 0 149176 1660052 321 0 0 0 0 0 0 0 123 1471 56 0 1 99
0 0 0 148824 1660612 51 0 0 0 0 0 0 0 17 194 37 0 0 100
0 0 0 148824 1660616 26 0 0 0 0 0 0 0 6 72 16 0 0 100
0 0 0 148820 1660624 29 0 0 0 0 0 0 0 6 71 17 0 0 100
0 0 0 148824 1660616 26 0 0 0 0 0 0 0 9 96 21 0 0 100
0 0 0 148824 1660620 26 0 0 0 0 0 0 0 6 60 16 0 0 100
0 0 0 148820 1660624 25 0 0 0 0 0 0 0 11 65 16 0 0 100
0 0 0 148820 1660620 25 0 0 0 0 0 0 0 11 114 20 0 0 100
0 0 0 148824 1660616 30 0 0 0 0 0 0 0 7 75 18 0 0 100
0 0 0 148824 1660620 26 0 0 0 0 0 0 0 12 67 18 0 0 100
0 0 0 148820 1660624 25 0 0 0 0 0 0 0 20 69 18 0 0 100
0 0 0 148824 1660616 26 0 0 0 0 0 0 0 10 59 24 0 0 100

top:
load averages: 0.42, 0.27, 0.23 16:25:55
31 processes: 30 idle, 1 on processor
CPU0 states: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 54.0% idle
CPU1 states: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 100% idle
CPU2 states: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 100% idle
CPU3 states: 0.0% user, 0.0% nice, 0.0% system, 0.0% interrupt, 100% idle
Memory: Real: 154M/400M act/tot Free: 1609M Swap: 0K/2055M used/tot

PID USERNAME PRI NICE SIZE RES STATE WAIT TIME CPU COMMAND
15396 _mysql 2 0 975M 141M sleep/0 poll 0:58 31.64% mysqld

On this kind of hw the mysqld process should't pass 20%, now I am getting an average of 20% cpu load and raising upto 100%. Same HW last week worked perfectly OK, the change was raid array from RAID1 to RAID5 and a fresh OpenBSD install (from 4.2 -> 4.3) and from mysql 5.0.45 -> 5.0.51a


mysqlreport:

MySQL 5.0.51a-log uptime 0 0:25:28 Mon Jul 21 16:43:28 2008

__ Key _________________________________________________________________
Buffer used 2.00k of 32.00M %Used: 0.01
Current 3.69M %Usage: 11.52
Write hit 0.00%
Read hit 30.77%

__ Questions ___________________________________________________________
Total 4.61k 3.0/s
Com_ 3.31k 2.2/s %Total: 71.81
DMS 1.19k 0.8/s 25.75
QC Hits 555 0.4/s 12.05
-Unknown 485 0.3/s 10.53
COM_QUIT 42 0.0/s 0.91
Slow 10 s 0 0/s 0.00 %DMS: 0.00 Log: ON
DMS 1.19k 0.8/s 25.75
SELECT 964 0.6/s 20.93 81.28
UPDATE 171 0.1/s 3.71 14.42
REPLACE 29 0.0/s 0.63 2.45
DELETE 14 0.0/s 0.30 1.18
INSERT 8 0.0/s 0.17 0.67
Com_ 3.31k 2.2/s 71.81
change_db 1.46k 1.0/s 31.79
show_fields 1.29k 0.8/s 28.08
admin_comma 477 0.3/s 10.36

__ SELECT and Sort _____________________________________________________
Scan 1.89k 1.2/s %SELECT: 195.85
Range 139 0.1/s 14.42
Full join 17 0.0/s 1.76
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 56 0.0/s
Sort range 0 0/s
Sort mrg pass 8 0.0/s

__ Query Cache _________________________________________________________
Memory usage 1.66M of 64.00M %Used: 2.59
Block Fragmnt 0.48%
Hits 555 0.4/s
Inserts 384 0.3/s
Insrt:Prune 384:1 0.3/s
Hit:Insert 1.45:1

__ Table Locks _________________________________________________________
Waited 0 0/s %Total: 0.00
Immediate 1.09k 0.7/s

__ Tables ______________________________________________________________
Open 28 of 2048 %Cache: 1.37
Opened 34 0.0/s

__ Connections _________________________________________________________
Max used 9 of 200 %Max: 4.50
Total 44 0.0/s

__ Created Temp ________________________________________________________
Disk table 1.30k 0.8/s
Table 1.38k 0.9/s Size: 64.0M
File 21 0.0/s

__ Threads _____________________________________________________________
Running 1 of 9
Cached 0 of 8 %Hit: 79.55
Created 9 0.0/s
Slow 0 0/s

__ Aborted _____________________________________________________________
Clients 0 0/s
Connects 0 0/s

__ Bytes _______________________________________________________________
Sent 201.48M 131.9k/s
Received 293.21k 191.9/s

__ InnoDB Buffer Pool __________________________________________________
Usage 37.88M of 768.00M %Used: 4.93
Read hit 100.00%
Pages
Free 46.73k %Total: 95.07
Data 2.31k 4.70 %Drty: 0.00
Misc 112 0.23
Latched 0 0.00
Reads 20.84M 13.6k/s
From file 592 0.4/s 0.00
Ahead Rnd 1 0.0/s
Ahead Sql 26 0.0/s
Writes 552 0.4/s
Flushes 136 0.1/s
Wait Free 0 0/s

__ InnoDB Lock _________________________________________________________
Waits 0 0/s
Current 0
Time acquiring
Total 0 ms
Average 0 ms
Max 0 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
Reads 651 0.4/s
Writes 236 0.2/s
fsync 128 0.1/s
Pending
Reads 0
Writes 0
fsync 0

Pages
Created 1 0.0/s
Read 2.31k 1.5/s
Written 136 0.1/s

Rows
Deleted 1 0.0/s
Inserted 16 0.0/s
Read 18.37M 12.0k/s
Updated 134 0.1/s



Any suggestions what to check, optimizations, what I am missing?

Thank you!



Mitja