Re: MySQL huge CPU usage
Posted by: Asdrúbal Suárez
Date: June 08, 2014 08:24AM
Date: June 08, 2014 08:24AM
Hello Rick, thank you so much for answering :)
Rick James Wrote:
-------------------------------------------------------
> STATUS, without VARIABLES --> not enough
> information.
>
I have this information now. Here I post it
Aborted_clients 8788
Aborted_connects 198
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 182
Bytes_sent 117
Com_admin_commands 0
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_call_procedure 0
Com_change_db 0
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_create_user 0
Com_dealloc_sql 0
Com_delete 0
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_drop_user 0
Com_execute_sql 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 0
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_rename_table 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_savepoint 0
Com_select 0
Com_set_option 3
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 0
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 0
Com_show_databases 0
Com_show_errors 0
Com_show_fields 0
Com_show_grants 0
Com_show_innodb_status 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_ndb_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_privileges 0
Com_show_processlist 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 0
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_unlock_tables 0
Com_update 0
Com_update_multi 0
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 406566
Created_tmp_disk_tables 0
Created_tmp_files 5
Created_tmp_tables 1
Delayed_errors 0
Delayed_insert_threads 1
Delayed_writes 8794
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 132
Innodb_buffer_pool_pages_data 509
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 65352
Innodb_buffer_pool_pages_free 1
Innodb_buffer_pool_pages_misc 2
Innodb_buffer_pool_pages_total 512
Innodb_buffer_pool_read_ahead_rnd 64
Innodb_buffer_pool_read_ahead_seq 7
Innodb_buffer_pool_read_requests 55081603
Innodb_buffer_pool_reads 1607
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 158978
Innodb_data_fsyncs 104006
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 33689600
Innodb_data_reads 1815
Innodb_data_writes 126273
Innodb_data_written 2175549952
Innodb_dblwr_pages_written 65352
Innodb_dblwr_writes 17624
Innodb_log_waits 0
Innodb_log_write_requests 19575
Innodb_log_writes 30382
Innodb_os_log_fsyncs 47960
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 25098240
Innodb_page_size 16384
Innodb_pages_created 627
Innodb_pages_read 1923
Innodb_pages_written 65352
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 47
Innodb_row_lock_time_avg 7
Innodb_row_lock_time_max 46
Innodb_row_lock_waits 6
Innodb_rows_deleted 46
Innodb_rows_inserted 639
Innodb_rows_read 20867006
Innodb_rows_updated 29252
Key_blocks_not_flushed 0
Key_blocks_unused 110330
Key_blocks_used 5650
Key_read_requests 760963
Key_reads 379
Key_write_requests 215413
Key_writes 215131
Last_query_cost 0.000000
Max_used_connections 39
Not_flushed_delayed_rows 0
Open_files 145
Open_streams 0
Open_tables 440
Opened_tables 0
Prepared_stmt_count 0
Qcache_free_blocks 543
Qcache_free_memory 265183192
Qcache_hits 4467460
Qcache_inserts 118420
Qcache_lowmem_prunes 0
Qcache_not_cached 2094030
Qcache_queries_in_cache 1488
Qcache_total_blocks 3590
Queries 9693198
Questions 4
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 1
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Table_locks_immediate 287253
Table_locks_waited 0
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 29
Threads_connected 11
Threads_created 39
Threads_running 9
Uptime 525259
> Query cache --> rarely much impact on CPU.
>
> Find the "slow" queries -- either through SHOW
> PROCESSLIST or the slowlog. Then show us one or
> two queries, together with SHOW CREATE TABLE and
> EXPLAIN SELECT ...;
>
Actually I got this from the SHOW FULL PROCESSLIST
Id User Host db Command Time State Info
408193 diariode localhost NULL Query 0 NULL SHOW FULL PROCESSLIST
Any other information that I should post?
> We may be able to help you improve the queries to
> avoid high CPU. Inadequate indexes is a common
> cause. Or lack of understanding "compound
> indexes". Or poorly formulated queries.
Rick James Wrote:
-------------------------------------------------------
> STATUS, without VARIABLES --> not enough
> information.
>
I have this information now. Here I post it
Aborted_clients 8788
Aborted_connects 198
Binlog_cache_disk_use 0
Binlog_cache_use 0
Bytes_received 182
Bytes_sent 117
Com_admin_commands 0
Com_alter_db 0
Com_alter_table 0
Com_analyze 0
Com_backup_table 0
Com_begin 0
Com_call_procedure 0
Com_change_db 0
Com_change_master 0
Com_check 0
Com_checksum 0
Com_commit 0
Com_create_db 0
Com_create_function 0
Com_create_index 0
Com_create_table 0
Com_create_user 0
Com_dealloc_sql 0
Com_delete 0
Com_delete_multi 0
Com_do 0
Com_drop_db 0
Com_drop_function 0
Com_drop_index 0
Com_drop_table 0
Com_drop_user 0
Com_execute_sql 0
Com_flush 0
Com_grant 0
Com_ha_close 0
Com_ha_open 0
Com_ha_read 0
Com_help 0
Com_insert 0
Com_insert_select 0
Com_kill 0
Com_load 0
Com_load_master_data 0
Com_load_master_table 0
Com_lock_tables 0
Com_optimize 0
Com_preload_keys 0
Com_prepare_sql 0
Com_purge 0
Com_purge_before_date 0
Com_rename_table 0
Com_repair 0
Com_replace 0
Com_replace_select 0
Com_reset 0
Com_restore_table 0
Com_revoke 0
Com_revoke_all 0
Com_rollback 0
Com_savepoint 0
Com_select 0
Com_set_option 3
Com_show_binlog_events 0
Com_show_binlogs 0
Com_show_charsets 0
Com_show_collations 0
Com_show_column_types 0
Com_show_create_db 0
Com_show_create_table 0
Com_show_databases 0
Com_show_errors 0
Com_show_fields 0
Com_show_grants 0
Com_show_innodb_status 0
Com_show_keys 0
Com_show_logs 0
Com_show_master_status 0
Com_show_ndb_status 0
Com_show_new_master 0
Com_show_open_tables 0
Com_show_privileges 0
Com_show_processlist 0
Com_show_slave_hosts 0
Com_show_slave_status 0
Com_show_status 1
Com_show_storage_engines 0
Com_show_tables 0
Com_show_triggers 0
Com_show_variables 0
Com_show_warnings 0
Com_slave_start 0
Com_slave_stop 0
Com_stmt_close 0
Com_stmt_execute 0
Com_stmt_fetch 0
Com_stmt_prepare 0
Com_stmt_reset 0
Com_stmt_send_long_data 0
Com_truncate 0
Com_unlock_tables 0
Com_update 0
Com_update_multi 0
Com_xa_commit 0
Com_xa_end 0
Com_xa_prepare 0
Com_xa_recover 0
Com_xa_rollback 0
Com_xa_start 0
Compression OFF
Connections 406566
Created_tmp_disk_tables 0
Created_tmp_files 5
Created_tmp_tables 1
Delayed_errors 0
Delayed_insert_threads 1
Delayed_writes 8794
Flush_commands 1
Handler_commit 0
Handler_delete 0
Handler_discover 0
Handler_prepare 0
Handler_read_first 0
Handler_read_key 0
Handler_read_next 0
Handler_read_prev 0
Handler_read_rnd 0
Handler_read_rnd_next 0
Handler_rollback 0
Handler_savepoint 0
Handler_savepoint_rollback 0
Handler_update 0
Handler_write 132
Innodb_buffer_pool_pages_data 509
Innodb_buffer_pool_pages_dirty 0
Innodb_buffer_pool_pages_flushed 65352
Innodb_buffer_pool_pages_free 1
Innodb_buffer_pool_pages_misc 2
Innodb_buffer_pool_pages_total 512
Innodb_buffer_pool_read_ahead_rnd 64
Innodb_buffer_pool_read_ahead_seq 7
Innodb_buffer_pool_read_requests 55081603
Innodb_buffer_pool_reads 1607
Innodb_buffer_pool_wait_free 0
Innodb_buffer_pool_write_requests 158978
Innodb_data_fsyncs 104006
Innodb_data_pending_fsyncs 0
Innodb_data_pending_reads 0
Innodb_data_pending_writes 0
Innodb_data_read 33689600
Innodb_data_reads 1815
Innodb_data_writes 126273
Innodb_data_written 2175549952
Innodb_dblwr_pages_written 65352
Innodb_dblwr_writes 17624
Innodb_log_waits 0
Innodb_log_write_requests 19575
Innodb_log_writes 30382
Innodb_os_log_fsyncs 47960
Innodb_os_log_pending_fsyncs 0
Innodb_os_log_pending_writes 0
Innodb_os_log_written 25098240
Innodb_page_size 16384
Innodb_pages_created 627
Innodb_pages_read 1923
Innodb_pages_written 65352
Innodb_row_lock_current_waits 0
Innodb_row_lock_time 47
Innodb_row_lock_time_avg 7
Innodb_row_lock_time_max 46
Innodb_row_lock_waits 6
Innodb_rows_deleted 46
Innodb_rows_inserted 639
Innodb_rows_read 20867006
Innodb_rows_updated 29252
Key_blocks_not_flushed 0
Key_blocks_unused 110330
Key_blocks_used 5650
Key_read_requests 760963
Key_reads 379
Key_write_requests 215413
Key_writes 215131
Last_query_cost 0.000000
Max_used_connections 39
Not_flushed_delayed_rows 0
Open_files 145
Open_streams 0
Open_tables 440
Opened_tables 0
Prepared_stmt_count 0
Qcache_free_blocks 543
Qcache_free_memory 265183192
Qcache_hits 4467460
Qcache_inserts 118420
Qcache_lowmem_prunes 0
Qcache_not_cached 2094030
Qcache_queries_in_cache 1488
Qcache_total_blocks 3590
Queries 9693198
Questions 4
Rpl_status NULL
Select_full_join 0
Select_full_range_join 0
Select_range 0
Select_range_check 0
Select_scan 1
Slave_open_temp_tables 0
Slave_retried_transactions 0
Slave_running OFF
Slow_launch_threads 0
Slow_queries 0
Sort_merge_passes 0
Sort_range 0
Sort_rows 0
Sort_scan 0
Table_locks_immediate 287253
Table_locks_waited 0
Tc_log_max_pages_used 0
Tc_log_page_size 0
Tc_log_page_waits 0
Threads_cached 29
Threads_connected 11
Threads_created 39
Threads_running 9
Uptime 525259
> Query cache --> rarely much impact on CPU.
>
> Find the "slow" queries -- either through SHOW
> PROCESSLIST or the slowlog. Then show us one or
> two queries, together with SHOW CREATE TABLE and
> EXPLAIN SELECT ...;
>
Actually I got this from the SHOW FULL PROCESSLIST
Id User Host db Command Time State Info
408193 diariode localhost NULL Query 0 NULL SHOW FULL PROCESSLIST
Any other information that I should post?
> We may be able to help you improve the queries to
> avoid high CPU. Inadequate indexes is a common
> cause. Or lack of understanding "compound
> indexes". Or poorly formulated queries.
Subject
Written By
Posted
Sorry, you can't reply to this topic. It has been closed.
Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.