I am facing an issue with MySQL server. I am not a primary DB admin, so i might need some assistance in tuning DB.
The CPU Utilization shoots up way beyond expectations. It just hovers around 70-90% of CPU. Where as the memory utilization levels are less than One GB. Memory at MySQL disposal is 8GB - 10GB.
i have increased the size of the most common variables recommended in serverfault, stackoverflow and others. However still the issue with High CPU and too low memory utilization still persists.
Attaching the config. Any recommendations. Application does read and write to database. Total number of entries written to db in each run are close to 200K. Each run is currently taking around 30 min. Any inputs are highly appreciated. Thank you.
Disk is an SSD, which is running at 30-40% utilization levels
Variable_name Value
- Bytes_received 1414
- Bytes_sent 560672
- Com_admin_commands 25
- Com_change_db 1
- Com_delete 1
- Com_select 5
- Com_set_option 1
- Com_show_databases 1
- Com_show_events 1
- Com_show_function_status 2
- Com_show_procedure_status 2
- Connections 1004
- Created_tmp_disk_tables 29
- Created_tmp_files 7
- Created_tmp_tables 136
- Delayed_errors 0
- Delayed_insert_threads 0
- Delayed_writes 0
- Flush_commands 1
- Handler_commit 2
- Handler_delete 62432
- Handler_discover 0
- Handler_external_lock 18
- Handler_mrr_init 0
- Handler_prepare 0
- Handler_read_first 14
- Handler_read_key 11
- Handler_read_last 0
- Handler_read_next 216
- Handler_read_prev 0
- Handler_read_rnd 0
- Handler_read_rnd_next 70195
- Handler_rollback 0
- Handler_savepoint 0
- Handler_savepoint_rollback 0
- Handler_update 0
- Handler_write 2373
- Innodb_buffer_pool_dump_status Dumping of buffer pool not started
- Innodb_buffer_pool_load_status Buffer pool(s) load completed at 161230 - 3:53:32
- Innodb_buffer_pool_resize_status
- Innodb_buffer_pool_pages_data 20100
- Innodb_buffer_pool_bytes_data 329318400
- Innodb_buffer_pool_pages_dirty 2652
- Innodb_buffer_pool_bytes_dirty 43450368
- Innodb_buffer_pool_pages_flushed 7786
- Innodb_buffer_pool_pages_free 372888
- Innodb_buffer_pool_pages_misc 228
- Innodb_buffer_pool_pages_total 393216
- Innodb_buffer_pool_read_ahead_rnd 0
- Innodb_buffer_pool_read_ahead 255
- Innodb_buffer_pool_read_ahead_evicted 0
- Innodb_buffer_pool_read_requests 3002463794
- Innodb_buffer_pool_reads 17421
- Innodb_buffer_pool_wait_free 0
- Innodb_buffer_pool_write_requests 6474793
- Innodb_data_fsyncs 292991
- Innodb_data_pending_fsyncs 1
- Innodb_data_pending_reads 0
- Innodb_data_pending_writes 0
- Innodb_data_read 289673728
- Innodb_data_reads 22831
- Innodb_data_writes 299547
- Innodb_data_written 1024508928
- Innodb_dblwr_pages_written 7593
- Innodb_dblwr_writes 993
- Innodb_log_waits 0
- Innodb_log_write_requests 760884
- Innodb_log_writes 290702
- Innodb_os_log_fsyncs 290737
- Innodb_os_log_pending_fsyncs 1
- Innodb_os_log_pending_writes 0
- Innodb_os_log_written 772521472
- Innodb_page_size 16384
- Innodb_pages_created 2425
- Innodb_pages_read 17675
- Innodb_pages_written 7786
- Innodb_row_lock_current_waits 0
- Innodb_row_lock_time 0
- Innodb_row_lock_time_avg 0
- Innodb_row_lock_time_max 0
- Innodb_row_lock_waits 0
- Innodb_rows_deleted 62432
- Innodb_rows_inserted 162705
- Innodb_rows_read 2421592738
- Innodb_rows_updated 794937
- Innodb_num_open_files 3000
- Innodb_truncated_status_writes 0
- Innodb_available_undo_logs 128
- Key_blocks_not_flushed 0
- Key_blocks_unused 3429467
- Key_blocks_used 5
- Key_read_requests 22
- Key_reads 5
- Key_write_requests 0
- Key_writes 0
- Last_query_cost 0
- Last_query_partial_plans 0
- Locked_connects 0
- Max_execution_time_exceeded 0
- Max_execution_time_set 0
- Max_execution_time_set_failed 0
- Max_used_connections 60
- Max_used_connections_time 12/30/2016 3:55
- Not_flushed_delayed_rows 0
- Ongoing_anonymous_transaction_count 0
- Open_files 18
- Open_streams 0
- Open_table_definitions 1400
- Open_tables 786
- Opened_files 15589
- Opened_table_definitions 4761
- Opened_tables 4763
- Queries 2148749
- Questions 22
- Select_scan 15
- Table_locks_immediate 108
- Table_locks_waited 0
- Table_open_cache_hits 5
- Table_open_cache_misses 4763
- Table_open_cache_overflows 4577
- Tc_log_max_pages_used 0
- Tc_log_page_size 0
- Tc_log_page_waits 0
- Threads_cached 0
- Threads_connected 60
- Threads_created 60
- Threads_running 56
- Uptime 1197
- Uptime_since_flush_status 1197