I'm running Mysql 5.5.28 and it seems at random times I get high CPU on mysql, during the high load I cant find anything wrong in the process list, as the load increases the longer it takes to run basic wordpress queries and the process list fills up but theres nothing in there that stands out as the culprit, nothing in the mysql error log, and the network traffic is not very high compared to normal.

Here's a screen shot of new relic with the incident: http://ift.tt/1yrgCnP

Here's the output of pt-mysql-summary.

Percona Toolkit MySQL Summary Report


        System time | 2015-01-05 10:47:08 UTC (local TZ: GMT +0000)

Instances

Port Data Directory Nice OOM Socket ===== ========================== ==== === ====== /var/lib/mysql/ 0 0 /var/lib/mysql/mysql.sock

MySQL Executable


Path to executable | /usr/sbin/mysqld
        Has symbols | No

Report On Port 3306


                User | root@localhost
                Time | 2015-01-05 10:47:08 (GMT)
            Hostname | db.domain.com
            Version | 5.5.28-29.1 Percona Server (GPL), Release rel29.1, Revision 335
            Built On | Linux x86_64
            Started | 2014-12-29 15:31 (up 6+19:15:10)
            Databases | 13
            Datadir | /var/lib/mysql/
            Processes | 15 connected, 4 running
        Replication | Is not a slave, has 0 slaves connected
            Pidfile | /var/lib/mysql//db.domain.com.pid (exists)

Processlist

Command COUNT(*) Working SUM(Time) MAX(Time)

Query 4 4 0 0 Sleep 15 0 2500 1500

User COUNT(*) Working SUM(Time) MAX(Time)

root 5 1 0 0 wordpress_wp 10 3 0 0

Host COUNT(*) Working SUM(Time) MAX(Time)

127.0.0.1 4 0 0 0 192.168.0.11 6 1 0 0 192.168.0.12 6 2 0 0 localhost 1 1 0 0

db COUNT(*) Working SUM(Time) MAX(Time)

NULL 3 1 0 0 wordpress_wp 15 3 0 0

State COUNT(*) Working SUM(Time) MAX(Time)


                                15       0         0         0

Copying to tmp table 2 2 0 0 NULL 1 1 0 0 Sorting result 1 1 0 0

Status Counters (Wait 10 Seconds)

Variable Per day Per second 11 secs Aborted_clients 2

Aborted_connects 175

Bytes_received 3500000000 40000 45000 Bytes_sent 90000000000 1000000 1250000 Com_admin_commands 45

Com_change_db 1000000 10 15 Com_delete 1500

Com_insert 8000

Com_lock_tables 1

Com_replace 50

Com_select 17500000 200 200 Com_set_option 80

Com_show_create_table 5

Com_show_databases 2

Com_show_fields 40

Com_show_keys 5

Com_show_plugins 1

Com_show_processlist 70

Com_show_slave_status 1

Com_show_status 600

Com_show_table_status 15

Com_show_tables 1

Com_show_triggers 10

Com_show_variables 4000

Com_unlock_tables 1

Com_update 40000

Connections 1000000 10 15 Created_tmp_disk_tables 1000000 10 10 Created_tmp_files 600

Created_tmp_tables 2500000 30 40 Handler_commit 15000000 175 200 Handler_delete 1500

Handler_read_first 35000

Handler_read_key 3500000000 40000 40000 Handler_read_last 1250

Handler_read_next 30000000000 350000 400000 Handler_read_prev 900000000 10000 12500 Handler_read_rnd 200000000 2500 4000 Handler_read_rnd_next 7000000000 80000 175000 Handler_update 70000000 800

Handler_write 500000000 6000 12500 Innodb_adaptive_hash_cells 5000000 60

Innodb_adaptive_hash_heap_buffers 900

Innodb_adaptive_hash_hash_searches 30000000000 350000 350000 Innodb_adaptive_hash_non_hash_searches 2250000000 25000 25000 Innodb_background_log_sync 90000 1 1 Innodb_buffer_pool_pages_flushed 200000 2 1 Innodb_buffer_pool_pages_made_young 10

Innodb_buffer_pool_pages_old 20000

Innodb_buffer_pool_read_ahead 3000

Innodb_buffer_pool_read_requests 90000000000 1000000 1000000 Innodb_buffer_pool_reads 25000

Innodb_buffer_pool_write_requests 5000000 60 10 Innodb_checkpoint_age 2500 1250 Innodb_checkpoint_max_age 30000000 350

Innodb_checkpoint_target_age 30000000 350

Innodb_data_fsyncs 150000 1 1 Innodb_data_read 450000000 5000

Innodb_data_reads 30000

Innodb_data_writes 300000 3 2 Innodb_data_written 7000000000 80000 40000 Innodb_dblwr_pages_written 200000 2 1 Innodb_dblwr_writes 9000

Innodb_dict_tables 45

Innodb_history_list_length 225 -10 Innodb_ibuf_merged_delete_marks 40

Innodb_ibuf_merged_deletes 7

Innodb_ibuf_merged_inserts 25

Innodb_ibuf_merges 50

Innodb_log_write_requests 1250000 15 6 Innodb_log_writes 100000 1 1 Innodb_lsn_current 22500000000 250000 3000 Innodb_lsn_flushed 22500000000 250000 3000 Innodb_lsn_last_checkpoint 22500000000 250000 1500 Innodb_master_thread_1_second_loops 90000 1 Innodb_master_thread_10_second_loops 9000

Innodb_master_thread_sleeps 90000 1 Innodb_max_trx_id 2500000000 30000 200 Innodb_mem_adaptive_hash 60000000 600

Innodb_mem_dictionary 10000000 125

Innodb_mem_total 2500000000 30000

Innodb_mutex_os_waits 1000000 10 2 Innodb_mutex_spin_rounds 225000000 2500 700 Innodb_mutex_spin_waits 150000000 1750 800 Innodb_oldest_view_low_limit_trx_id 2500000000 30000 175 Innodb_os_log_fsyncs 100000 1 1 Innodb_os_log_written 600000000 7000 3500 Innodb_pages_created 30000

Innodb_pages_read 30000

Innodb_pages_written 200000 2 1 Innodb_purge_trx_id 2500000000 30000 150 Innodb_row_lock_time 15

Innodb_row_lock_waits 2

Innodb_rows_deleted 1500

Innodb_rows_inserted 1000000 10

Innodb_rows_read 40000000000 450000 600000 Innodb_rows_updated 40000

Innodb_s_lock_os_waits 9000000 100 20 Innodb_s_lock_spin_rounds 450000000 5000 1750 Innodb_s_lock_spin_waits 45000000 500 450 Innodb_x_lock_os_waits 3000000 35 25 Innodb_x_lock_spin_rounds 225000000 2500 2250 Innodb_x_lock_spin_waits 3000000 35 20 Key_read_requests 800000 9

Key_reads 1

Key_write_requests 150000 1

Open_table_definitions 60

Opened_files 4000000 45 50 Opened_table_definitions 150

Opened_tables 450

Qcache_not_cached 17500000 200 200 Queries 20000000 225 225 Questions 20000000 225 225 Select_range 2500000 30 35 Select_scan 45000 1 Slow_queries 3000

Sort_merge_passes 300

Sort_range 1750000 20 20 Sort_rows 450000000 5000 8000 Sort_scan 2500000 30 30 Table_locks_immediate 50000000 600 600 Table_locks_waited 15

Threads_created 350

Uptime 90000 1 1

Table cache


                Size | 4096
                Usage | 15%

Key Percona Server features


Table & Index Stats | Disabled
Multiple I/O Threads | Enabled
Corruption Resilient | Enabled
Durable Replication | Disabled
Import InnoDB Tables | Disabled
Fast Server Restarts | Disabled
    Enhanced Logging | Disabled
Replica Perf Logging | Disabled
Response Time Hist. | Disabled
    Smooth Flushing | Enabled
HandlerSocket NoSQL | Not Supported
    Fast Hash UDFs | Unknown

Percona XtraDB Cluster

Plugins


InnoDB compression | ACTIVE

Query cache


    query_cache_type | OFF
                Size | 64.0M
                Usage | 0%
    HitToInsertRatio | 0%

Schema

Specify --databases or --all-databases to dump and summarize schemas

Noteworthy Technologies


                SSL | No
Explicit LOCK TABLES | Yes
    Delayed Insert | No
    XA Transactions | No
        NDB Cluster | No
Prepared Statements | No

Prepared statement count | 0

InnoDB


            Version | 1.1.8-rel29.1
    Buffer Pool Size | 16.0G
    Buffer Pool Fill | 35%
    Buffer Pool Dirty | 0%
    File Per Table | ON
            Page Size | 16k
        Log File Size | 2 * 128.0M = 256.0M
    Log Buffer Size | 8M
        Flush Method | O_DIRECT
Flush Log At Commit | 1
        XA Support | ON
            Checksums | ON
        Doublewrite | ON
    R/W I/O Threads | 4 4
        I/O Capacity | 200
Thread Concurrency | 0
Concurrency Tickets | 500
Commit Concurrency | 0
Txn Isolation Level | REPEATABLE-READ
    Adaptive Flushing | ON
Adaptive Checkpoint | 
    Checkpoint Age | 17k
        InnoDB Queue | 0 queries inside InnoDB, 0 queries in queue
Oldest Transaction | 0 Seconds
    History List Len | 1502
        Read Views | 3
    Undo Log Entries | 0 transactions, 0 total undo, 0 max undo
    Pending I/O Reads | 0 buf pool reads, 0 normal AIO, 0 ibuf AIO, 0 preads
Pending I/O Writes | 0 buf pool (0 LRU, 0 flush list, 0 page); 0 AIO, 0 sync, 0 log IO (0 log, 0 chkp); 0 pwrites
Pending I/O Flushes | 0 buf pool, 0 log
Transaction States | 2xACTIVE, 8xnot started

MyISAM


            Key Cache | 32.0M
            Pct Used | 20%
            Unflushed | 0%

Security


                Users | 22 users, 2 anon, 6 w/o pw, 6 old pw
        Old Passwords | OFF

Binary Logging

Noteworthy Variables


Auto-Inc Incr/Offset | 1/1

Configuration File


        Config File | /etc/my.cnf

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

[mysqld] user = mysql default_storage_engine = InnoDB socket = /var/lib/mysql/mysql.sock pid_file = /var/lib/mysql/mysql.pid skip-name-resolve key_buffer_size = 32M myisam_recover = FORCE,BACKUP max-allowed-packet = 16M max-connect-errors = 1000000 skip-name-resolve innodb = FORCE datadir = /var/lib/mysql/ tmp_table_size = 32M max_heap_table_size = 32M query_cache_type = 0 query_cache_size = 0 max-connections = 500 thread-cache-size = 50 open-files-limit = 65535 table-definition-cache = 1024 table-open-cache = 2048 innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_log_file_size = 128M innodb_flush_log_at_trx_commit = 1 innodb_file_per_table = 1 innodb_buffer_pool_size = 16G log_error = /var/log/mysql/error.log log_queries_not_using_indexes = 1 slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow.log

Database

lundi 5 janvier 2015

What could be causing mysql to spike CPU load?