0

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
kris123456
  • 101
  • 2
  • Well, have you profiled your database queries to ensure you have created proper indices? What iowait are you seeing? – EEAA Dec 29 '16 at 23:11
  • 30-40% doesn't mean anything...what CPU iowait (if any) are you seeing? – EEAA Dec 29 '16 at 23:14
  • Disk is a SSD. SSD utilization is 20-40% max. Minimum falls to ~5% too sometimes. I hope this is the expected answer. – kris123456 Dec 29 '16 at 23:14
  • You don't seem to have much of any caching enabled. Do some basic MySQL tuning and come back in a couple of days. – Michael Hampton Dec 29 '16 at 23:21
  • i am using transactions in code, so i didnt enable caching to ensure data consistency. Any suggestions on what needs to be changed? – kris123456 Dec 29 '16 at 23:28
  • I am on windows. getting io data seems not that easy when compared to linux. Trying to get it. – kris123456 Dec 29 '16 at 23:42
  • Use the slow query log and query profiling to determine what is taking the most time. Read the manual regarding optimization, especially buffering and caching. Use perfmon to get storage performance counters on Windows. – John Mahowald Dec 31 '16 at 19:05
  • We can not see your configuration file. – Wilson Hauck Oct 18 '17 at 15:46
  • @kris123456 If you do not post your complete my.cnf/ini we will not be in a position to offer any suggestions. More questions would be answered if you could also post complete results of SHOW GLOBAL VARIABLES; Then we would know how many threads are cached, data is cached, how you have changed io_capacities and max's, just tons of things to consider. Need your assistance, please. – Wilson Hauck Feb 22 '18 at 14:47
  • Unfortunately I am not allowed to. Sorry. – kris123456 Feb 22 '18 at 14:48

0 Answers0