asked on

Mysql server

hi everyone. I have this peculiar situation.

Just setup a fresh MYsql server. Xenon e3xxx server with 8 gig of ram. a pair of SATA drives. Centos 5 Xen.

We are running Mysql 5 on this fella and we are getting high cpu usage, especially in the %sy part.

wondering if anyone else has come across this as well. Tks.

*********** capture from top **************
Tasks: 133 total, 4 running, 129 sleeping, 0 stopped, 0 zombie
Cpu(s): 59.0%us, 41.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8182784k total, 2009352k used, 6173432k free, 125664k buffers
Swap: 16386168k total, 0k used, 16386168k free, 1304168k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
5039 mysql 15 0 162m 37m 4344 S 112 0.5 403:09.33 mysqld
25293 root 25 0 11076 6116 3184 R 88 0.1 0:04.08 rpmq
25280 root 18 0 80656 76m 1780 S 1 1.0 0:02.36 collectinfo.pl
1 root 15 0 2076 656 564 S 0 0.0 0:00.11 init
2 root RT -5 0 0 0 S 0 0.0 0:00.01 migration/0
3 root 34 19 0 0 0 R 0 0.0 0:00.00 ksoftirqd/0
4 root RT -5 0 0 0 S 0 0.0 0:00.00 watchdog/0
5 root RT -5 0 0 0 S 0 0.0 0:00.04 migration/1
6 root 34 19 0 0 0 S 0 0.0 0:00.04 ksoftirqd/1
7 root RT -5 0 0 0 S 0 0.0 0:00.00 watchdog/1
8 root 10 -5 0 0 0 S 0 0.0 0:00.00 events/0
9 root 10 -5 0 0 0 S 0 0.0 0:00.
***********end of capture ***************

mahome

Did you check what MySQL is doing?

SHOW FULL PROCESSLIST;

Open in new window

grayboo

ASKER

hi mahome.

this is the return,

mysql> show full processlist;
+-------+-------------+----------------------+---------------------+---------+------+-------+-----------------------+
| Id | User | Host | db | Command | Time | State | Info |
+-------+-------------+----------------------+---------------------+---------+------+-------+-----------------------+
| 81973 | singap2_Pal | 203.211.143.84:56524 | singap2_condo | Sleep | 1000 | | NULL |
| 83350 | singap2_Pal | localhost | singap2_ads | Sleep | 110 | | NULL |
| 84488 | singap2_Pal | 203.211.143.84:38238 | singap2_condo | Sleep | 329 | | NULL |
| 84548 | singap2_Pal | 203.211.143.84:38561 | singap2_condo | Sleep | 314 | | NULL |
| 85407 | singap2_Pal | 203.211.143.84:40198 | singap2_powersearch | Sleep | 45 | | NULL |
| 85412 | singap2_Pal | 203.211.143.84:40244 | singap2_powersearch | Sleep | 29 | | NULL |
| 85450 | root | localhost | NULL | Query | 0 | NULL | show full processlist |
| 85452 | singap2_Pal | 203.211.143.84:40392 | singap2_forum | Sleep | 36 | | NULL |
| 85453 | singap2_Pal | 203.211.143.84:40396 | singap2_forum | Sleep | 36 | | NULL |
| 85458 | singap2_Pal | 203.211.143.84:40407 | singap2_forum | Sleep | 36 | | NULL |
| 85469 | singap2_Pal | 203.211.143.84:40443 | singap2_forum | Sleep | 34 | | NULL |
| 85470 | singap2_Pal | 203.211.143.84:40446 | singap2_forum | Sleep | 34 | | NULL |
| 85474 | singap2_Pal | 203.211.143.84:40460 | singap2_powersearch | Sleep | 32 | | NULL |
| 85482 | singap2_Pal | 203.211.143.84:40506 | singap2_powersearch | Sleep | 9 | | NULL |
| 85495 | singap2_Pal | 203.211.143.84:40604 | singap2_condo | Sleep | 20 | | NULL |
| 85515 | singap2_Pal | 203.211.143.84:40696 | singap2_forum | Sleep | 13 | | NULL |
| 85519 | singap2_Pal | 203.211.143.84:40700 | singap2_forum | Sleep | 13 | | NULL |
| 85524 | singap2_Pal | 203.211.143.84:40717 | singap2_forum | Sleep | 12 | | NULL |
| 85525 | singap2_Pal | 203.211.143.84:40719 | singap2_forum | Sleep | 12 | | NULL |
| 85535 | singap2_Pal | 203.211.143.84:40768 | singap2_forum | Sleep | 9 | | NULL |
| 85540 | singap2_Pal | 203.211.143.84:40796 | singap2_powersearch | Sleep | 8 | | NULL |
| 85543 | singap2_Pal | 203.211.143.84:40807 | singap2_forum | Sleep | 8 | | NULL |
| 85544 | singap2_Pal | 203.211.143.84:40808 | singap2_forum | Sleep | 8 | | NULL |
| 85545 | singap2_Pal | 203.211.143.84:40810 | singap2_forum | Sleep | 7 | | NULL |
| 85548 | singap2_Pal | 203.211.143.84:40822 | singap2_forum | Sleep | 6 | | NULL |
| 85549 | singap2_Pal | 203.211.143.84:40824 | singap2_forum | Sleep | 6 | | NULL |
| 85550 | singap2_Pal | 203.211.143.84:40827 | singap2_powersearch | Sleep | 6 | | NULL |
| 85551 | singap2_Pal | 203.211.143.84:40828 | singap2_condo | Sleep | 6 | | NULL |
| 85553 | singap2_Pal | 203.211.143.84:40834 | singap2_forum | Sleep | 5 | | NULL |
| 85554 | singap2_Pal | 203.211.143.84:40836 | singap2_forum | Sleep | 5 | | NULL |
| 85555 | singap2_Pal | 203.211.143.84:40852 | singap2_powersearch | Sleep | 4 | | NULL |
| 85556 | singap2_Pal | 203.211.143.84:40853 | singap2_condo | Sleep | 4 | | NULL |
| 85557 | singap2_Pal | 203.211.143.84:40859 | singap2_forum | Sleep | 4 | | NULL |
| 85558 | singap2_Pal | 203.211.143.84:40861 | singap2_forum | Sleep | 3 | | NULL |
| 85559 | singap2_Pal | 203.211.143.84:40865 | singap2_forum | Sleep | 3 | | NULL |
| 85560 | singap2_Pal | 203.211.143.84:40866 | singap2_forum | Sleep | 3 | | NULL |
| 85561 | singap2_Pal | 203.211.143.84:40869 | singap2_forum | Sleep | 3 | | NULL |
| 85566 | singap2_Pal | 203.211.143.84:40881 | singap2_forum | Sleep | 1 | | NULL |
| 85567 | singap2_Pal | 203.211.143.84:40882 | singap2_forum | Sleep | 1 | | NULL |

Umesh

Post the output of below + my.cnf

show global status;
show global variables;

mahome

Did you execute show full processlist while you had high CPU usage?

What I see is that you have some open connection, may be from a connection pool in your app, but none of them is doing anything though that can't be reason for CPU usage.

grayboo

ASKER

hi. Tks! :)

my.cnf

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

global status:

| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 54 |
| Com_create_db | 4 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 3055 |
| 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 | 19 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 11664 |
| Com_insert_select | 59 |
| 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 | 82 |
| 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 | 457492 |
| Com_set_option | 133 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 2 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 56 |
| Com_show_errors | 0 |
| Com_show_fields | 42 |
| 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 | 11 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 5 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 228 |
| Com_show_triggers | 0 |
| Com_show_variables | 18 |
| 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 | 39454 |
| Com_update_multi | 15 |
| 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 | 90778 |
| Created_tmp_disk_tables | 462 |
| Created_tmp_files | 9 |
| Created_tmp_tables | 37266 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 2 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 16558 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 49462 |
| Handler_read_key | 35399916 |
| Handler_read_next | 1091118874 |
| Handler_read_prev | 527273824 |
| Handler_read_rnd | 7214884 |
| Handler_read_rnd_next | 3206018015 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 7002563 |
| Handler_write | 2480257 |
| Innodb_buffer_pool_pages_data | 19 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 493 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 77 |
| Innodb_buffer_pool_reads | 12 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 3 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 2494464 |
| Innodb_data_reads | 25 |
| Innodb_data_writes | 3 |
| Innodb_data_written | 1536 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 1 |
| Innodb_os_log_fsyncs | 3 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 512 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 19 |
| Innodb_pages_written | 0 |
| 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 | 0 |
| Innodb_rows_inserted | 0 |
| Innodb_rows_read | 0 |
| Innodb_rows_updated | 0 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 0 |
| Key_blocks_used | 7248 |
| Key_read_requests | 197271435 |
| Key_reads | 1698222 |
| Key_write_requests | 100738 |
| Key_writes | 76482 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 91 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 279 |
| Open_streams | 0 |
| Open_tables | 169 |
| Opened_tables | 3651 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 0 |
| Qcache_free_memory | 0 |
| Qcache_hits | 0 |
| Qcache_inserts | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_queries_in_cache | 0 |
| Qcache_total_blocks | 0 |
| Questions | 695228 |
| Rpl_status | NULL |
| Select_full_join | 54 |
| Select_full_range_join | 0 |
| Select_range | 41983 |
| Select_range_check | 0 |
| Select_scan | 244199 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 46 |
| Slow_queries | 124 |
| Sort_merge_passes | 2 |
| Sort_range | 49317 |
| Sort_rows | 25238162 |
| Sort_scan | 98503 |
| Ssl_accept_renegotiates | 0 |
| Ssl_accepts | 0 |
| Ssl_callback_cache_hits | 0 |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_client_connects | 0 |
| Ssl_connect_renegotiates | 0 |
| Ssl_ctx_verify_depth | 0 |
| Ssl_ctx_verify_mode | 0 |
| Ssl_default_timeout | 0 |
| Ssl_finished_accepts | 0 |
| Ssl_finished_connects | 0 |
| Ssl_session_cache_hits | 0 |
| Ssl_session_cache_misses | 0 |
| Ssl_session_cache_mode | NONE |
| Ssl_session_cache_overflows | 0 |
| Ssl_session_cache_size | 0 |
| Ssl_session_cache_timeouts | 0 |
| Ssl_sessions_reused | 0 |
| Ssl_used_session_cache_entries | 0 |
| Ssl_verify_depth | 0 |
| Ssl_verify_mode | 0 |
| Ssl_version | |
| Table_locks_immediate | 684393 |
| Table_locks_waited | 1780 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 36 |
| Threads_connected | 5 |
| Threads_created | 66391 |
| Threads_running | 1 |
| Uptime | 36413 |
| Uptime_since_flush_status | 36413 |

global variables:
| local_infile | ON |
| locked_in_memory | OFF |
| log | OFF |
| log_bin | OFF |
| log_bin_trust_function_creators | OFF |
| log_error | |
| log_queries_not_using_indexes | OFF |
| log_slave_updates | OFF |
| log_slow_queries | OFF |
| log_warnings | 1 |
| long_query_time | 10 |
| low_priority_updates | OFF |
| lower_case_file_system | OFF |
| lower_case_table_names | 0 |
| max_allowed_packet | 159744 |
| max_binlog_cache_size | 4294967295 |
| max_binlog_size | 1073741824 |
| max_connect_errors | 10000 |
| max_connections | 300 |
| max_delayed_threads | 20 |
| max_error_count | 64 |
| max_heap_table_size | 16777216 |
| max_insert_delayed_threads | 20 |
| max_join_size | 4294967295 |
| max_length_for_sort_data | 1024 |
| max_prepared_stmt_count | 16382 |
| max_relay_log_size | 0 |
| max_seeks_for_key | 4294967295 |
| max_sort_length | 1024 |
| max_sp_recursion_depth | 0 |
| max_tmp_tables | 32 |
| max_user_connections | 0 |
| max_write_lock_count | 4294967295 |
| multi_range_count | 256 |
| myisam_data_pointer_size | 6 |
| myisam_max_sort_file_size | 2147483647 |
| myisam_recover_options | OFF |
| myisam_repair_threads | 1 |
| myisam_sort_buffer_size | 8388608 |
| myisam_stats_method | nulls_unequal |
| net_buffer_length | 16384 |
| net_read_timeout | 30 |
| net_retry_count | 10 |
| net_write_timeout | 60 |
| new | OFF |
| old_passwords | ON |
| open_files_limit | 1024 |
| optimizer_prune_level | 1 |
| optimizer_search_depth | 62 |
| pid_file | /var/run/mysqld/mysqld.pid |
| port | 3306 |
| preload_buffer_size | 32768 |
| profiling | OFF |
| profiling_history_size | 15 |
| protocol_version | 10 |
| query_alloc_block_size | 8192 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| query_prealloc_size | 8192 |
| range_alloc_block_size | 2048 |
| read_buffer_size | 131072 |
| read_only | OFF |
| read_rnd_buffer_size | 262144 |
| relay_log_purge | ON |
| relay_log_space_limit | 0 |
| rpl_recovery_rank | 0 |
| secure_auth | OFF |
| secure_file_priv | |
| server_id | 0 |
| skip_external_locking | ON |
| skip_networking | OFF |
| skip_show_database | OFF |
| slave_compressed_protocol | OFF |
| slave_load_tmpdir | /tmp/ |
| slave_net_timeout | 3600 |
| slave_skip_errors | OFF |
| slave_transaction_retries | 10 |
| slow_launch_time | 2 |
| socket | /var/lib/mysql/mysql.sock |
| sort_buffer_size | 2097144 |
| sql_big_selects | ON |
| sql_mode | |
| sql_notes | ON |
| sql_warnings | OFF |
| ssl_ca | |
| ssl_capath | |
| ssl_cert | |
| ssl_cipher | |
| ssl_key | |
| storage_engine | MyISAM |
| sync_binlog | 0 |
| sync_frm | ON |
| system_time_zone | SGT |
| table_cache | 1024 |
| table_lock_wait_timeout | 50 |
| table_type | MyISAM |
| thread_cache_size | 40 |
| thread_stack | 196608 |
| time_format | %H:%i:%s |
| time_zone | SYSTEM |
| timed_mutexes | OFF |
| tmp_table_size | 33554432 |
| tmpdir | /tmp/ |
| transaction_alloc_block_size | 8192 |
| transaction_prealloc_size | 4096 |
| tx_isolation | REPEATABLE-READ |
| updatable_views_with_limit | YES |
| version | 5.0.45 |
| version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (July 4, 2007) |
| version_comment | Source distribution |
| version_compile_machine | i686 |
| version_compile_os | redhat-linux-gnu |
| wait_timeout | 28800

grayboo

ASKER

hi mahome.

I am puzzled too. i can see that they are connections from an app from a remote machine. They are legit connections....

cld this be a hardware issue?

Umesh

Seems you have set max_user_connections unlimited.. is there any specific reason for this?

Pls backup your config file and check with these settings ( change one at a time otherwise you won't come to know)

thread_cache_size = 60
wait_timeout = 30
max_user_connections = 100

Also, before changing above let me know if you ahve amily MyISAM tables or mixed & what is the RAM size?? is this server dedicated to MySQL?

Umesh

Also.. you didn't give complete output of show global variables.. some parameters are missing from the list..

ai_ja_nai

Couldn't be that simply it's rebuilding indexes?

grayboo

ASKER

hi ushastry,

the machine is dedicated to Mysql. 8 gig of memory. tks.

Umesh

also,

Table_locks_waited = 1780

which should be near to zero.. pls check out which queries are causing this and tune them...

Pls let me know complete output of

sow global variables;

grayboo

ASKER

hi.

the global variables as follows:

Variable_name      Value
auto_increment_increment       1
auto_increment_offset       1
automatic_sp_privileges       ON
back_log       50
basedir       /usr/
bdb_cache_size       8388600
bdb_home       /var/lib/mysql/
bdb_log_buffer_size       32768
bdb_logdir
bdb_max_lock       10000
bdb_shared_data       OFF
bdb_tmpdir       /tmp/
binlog_cache_size       32768
bulk_insert_buffer_size       8388608
character_set_client       latin1
character_set_connection       latin1
character_set_database       latin1
character_set_filesystem       binary
character_set_results       latin1
character_set_server       latin1
character_set_system       utf8
character_sets_dir       /usr/share/mysql/charsets/
collation_connection       latin1_swedish_ci
collation_database       latin1_swedish_ci
collation_server       latin1_swedish_ci
completion_type       0
concurrent_insert       1
connect_timeout       5
datadir       /var/lib/mysql/
date_format       %Y-%m-%d
datetime_format       %Y-%m-%d %H:%i:%s
default_week_format       0
delay_key_write       ON
delayed_insert_limit       100
delayed_insert_timeout       300
delayed_queue_size       1000
div_precision_increment       4
engine_condition_pushdown       OFF
expire_logs_days       0
flush       OFF
flush_time       0
ft_boolean_syntax       + -><()~*:""&|
ft_max_word_len       84
ft_min_word_len       4
ft_query_expansion_limit       20
ft_stopword_file       (built-in)
group_concat_max_len       1024
have_archive       NO
have_bdb       YES
have_blackhole_engine       NO
have_compress       YES
have_crypt       YES
have_csv       NO
have_dynamic_loading       YES
have_example_engine       NO
have_federated_engine       NO
have_geometry       YES
have_innodb       YES
have_isam       NO
have_merge_engine       YES
have_ndbcluster       NO
have_openssl       DISABLED
have_ssl       DISABLED
have_query_cache       YES
have_raid       NO
have_rtree_keys       YES
have_symlink       YES
hostname       localhost.localdomain
init_connect
init_file
init_slave
innodb_additional_mem_pool_size       1048576
innodb_autoextend_increment       8
innodb_buffer_pool_awe_mem_mb       0
innodb_buffer_pool_size       8388608
innodb_checksums       ON
innodb_commit_concurrency       0
innodb_concurrency_tickets       500
innodb_data_file_path       ibdata1:10M:autoextend
innodb_data_home_dir
innodb_doublewrite       ON
innodb_fast_shutdown       1
innodb_file_io_threads       4
innodb_file_per_table       OFF
innodb_flush_log_at_trx_commit       1
innodb_flush_method
innodb_force_recovery       0
innodb_lock_wait_timeout       50
innodb_locks_unsafe_for_binlog       OFF
innodb_log_arch_dir
innodb_log_archive       OFF
innodb_log_buffer_size       1048576
innodb_log_file_size       5242880
innodb_log_files_in_group       2
innodb_log_group_home_dir       ./
innodb_max_dirty_pages_pct       90
innodb_max_purge_lag       0
innodb_mirrored_log_groups       1
innodb_open_files       300
innodb_rollback_on_timeout       OFF
innodb_support_xa       ON
innodb_sync_spin_loops       20
innodb_table_locks       ON
innodb_thread_concurrency       8
innodb_thread_sleep_delay       10000
interactive_timeout       28800
join_buffer_size       131072
key_buffer_size       8388600
key_cache_age_threshold       300
key_cache_block_size       1024
key_cache_division_limit       100
language       /usr/share/mysql/english/
large_files_support       ON
large_page_size       0
large_pages       OFF
lc_time_names       en_US
license       GPL
local_infile       ON
locked_in_memory       OFF
log       OFF
log_bin       OFF
log_bin_trust_function_creators       OFF
log_error
log_queries_not_using_indexes       OFF
log_slave_updates       OFF
log_slow_queries       OFF
log_warnings       1
long_query_time       10
low_priority_updates       OFF
lower_case_file_system       OFF
lower_case_table_names       0
max_allowed_packet       159744
max_binlog_cache_size       4294967295
max_binlog_size       1073741824
max_connect_errors       10000
max_connections       300
max_delayed_threads       20
max_error_count       64
max_heap_table_size       16777216
max_insert_delayed_threads       20
max_join_size       4294967295
max_length_for_sort_data       1024
max_prepared_stmt_count       16382
max_relay_log_size       0
max_seeks_for_key       4294967295
max_sort_length       1024
max_sp_recursion_depth       0
max_tmp_tables       32
max_user_connections       200
max_write_lock_count       4294967295
multi_range_count       256
myisam_data_pointer_size       6
myisam_max_sort_file_size       2147483647
myisam_recover_options       OFF
myisam_repair_threads       1
myisam_sort_buffer_size       8388608
myisam_stats_method       nulls_unequal
net_buffer_length       16384
net_read_timeout       30
net_retry_count       10
net_write_timeout       60
new       OFF
old_passwords       ON
open_files_limit       1024
optimizer_prune_level       1
optimizer_search_depth       62
pid_file       /var/run/mysqld/mysqld.pid
port       3306
preload_buffer_size       32768
profiling       OFF
profiling_history_size       15
protocol_version       10
query_alloc_block_size       8192
query_cache_limit       1048576
query_cache_min_res_unit       4096
query_cache_size       0
query_cache_type       ON
query_cache_wlock_invalidate       OFF
query_prealloc_size       8192
range_alloc_block_size       2048
read_buffer_size       131072
read_only       OFF
read_rnd_buffer_size       262144
relay_log_purge       ON
relay_log_space_limit       0
rpl_recovery_rank       0
secure_auth       OFF
secure_file_priv
server_id       0
skip_external_locking       ON
skip_networking       OFF
skip_show_database       OFF
slave_compressed_protocol       OFF
slave_load_tmpdir       /tmp/
slave_net_timeout       3600
slave_skip_errors       OFF
slave_transaction_retries       10
slow_launch_time       2
socket       /var/lib/mysql/mysql.sock
sort_buffer_size       2097144
sql_big_selects       ON
sql_mode
sql_notes       ON
sql_warnings       OFF
ssl_ca
ssl_capath
ssl_cert
ssl_cipher
ssl_key
storage_engine       MyISAM
sync_binlog       0
sync_frm       ON
system_time_zone       SGT
table_cache       1024
table_lock_wait_timeout       50
table_type       MyISAM
thread_cache_size       60
thread_stack       196608
time_format       %H:%i:%s
time_zone       SYSTEM
timed_mutexes       OFF
tmp_table_size       33554432
tmpdir       /tmp/
transaction_alloc_block_size       8192
transaction_prealloc_size       4096
tx_isolation       REPEATABLE-READ
updatable_views_with_limit       YES
version       5.0.45
version_bdb       Sleepycat Software: Berkeley DB 4.1.24: (July 4, 2007)
version_comment       Source distribution
version_compile_machine       i686
version_compile_os       redhat-linux-gnu
wait_timeout       30

grayboo

ASKER

hi.

this is the global status readout i have:

Output from SQL command show global status; ..

Variable_name      Value
Aborted_clients       2850
Aborted_connects       636
Binlog_cache_disk_use       0
Binlog_cache_use       0
Bytes_received       345019171
Bytes_sent       411391690
Com_admin_commands       127
Com_alter_db       0
Com_alter_table       4
Com_analyze       0
Com_backup_table       0
Com_begin       137
Com_call_procedure       0
Com_change_db       291998
Com_change_master       0
Com_check       0
Com_checksum       0
Com_commit       137
Com_create_db       7
Com_create_function       0
Com_create_index       95
Com_create_table       147
Com_create_user       0
Com_dealloc_sql       18
Com_delete       5674
Com_delete_multi       0
Com_do       0
Com_drop_db       5
Com_drop_function       0
Com_drop_index       1
Com_drop_table       90
Com_drop_user       0
Com_execute_sql       1031
Com_flush       21
Com_grant       0
Com_ha_close       0
Com_ha_open       0
Com_ha_read       0
Com_help       1
Com_insert       60952
Com_insert_select       127
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       28
Com_purge       0
Com_purge_before_date       0
Com_rename_table       0
Com_repair       0
Com_replace       133
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       1220749
Com_set_option       43177
Com_show_binlog_events       0
Com_show_binlogs       25
Com_show_charsets       425
Com_show_collations       423
Com_show_column_types       0
Com_show_create_db       0
Com_show_create_table       51
Com_show_databases       507
Com_show_errors       0
Com_show_fields       229
Com_show_grants       167
Com_show_innodb_status       0
Com_show_keys       378
Com_show_logs       0
Com_show_master_status       0
Com_show_ndb_status       0
Com_show_new_master       0
Com_show_open_tables       2
Com_show_privileges       0
Com_show_processlist       15
Com_show_slave_hosts       0
Com_show_slave_status       0
Com_show_status       8
Com_show_storage_engines       0
Com_show_tables       2734
Com_show_triggers       0
Com_show_variables       1394
Com_show_warnings       0
Com_slave_start       0
Com_slave_stop       0
Com_stmt_close       18
Com_stmt_execute       1031
Com_stmt_fetch       0
Com_stmt_prepare       28
Com_stmt_reset       0
Com_stmt_send_long_data       0
Com_truncate       1
Com_unlock_tables       0
Com_update       120161
Com_update_multi       24
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       287048
Created_tmp_disk_tables       1086
Created_tmp_files       72
Created_tmp_tables       79493
Delayed_errors       32727
Delayed_insert_threads       1
Delayed_writes       32943
Flush_commands       1
Handler_commit       0
Handler_delete       31437
Handler_discover       0
Handler_prepare       0
Handler_read_first       128661
Handler_read_key       68856724
Handler_read_next       2301910797
Handler_read_prev       897898429
Handler_read_rnd       34042422
Handler_read_rnd_next       337504227
Handler_rollback       0
Handler_savepoint       0
Handler_savepoint_rollback       0
Handler_update       15423443
Handler_write       5605627
Innodb_buffer_pool_pages_data       19
Innodb_buffer_pool_pages_dirty       0
Innodb_buffer_pool_pages_flushed       1
Innodb_buffer_pool_pages_free       493
Innodb_buffer_pool_pages_latched       0
Innodb_buffer_pool_pages_misc       0
Innodb_buffer_pool_pages_total       512
Innodb_buffer_pool_read_ahead_rnd       1
Innodb_buffer_pool_read_ahead_seq       0
Innodb_buffer_pool_read_requests       94
Innodb_buffer_pool_reads       12
Innodb_buffer_pool_wait_free       0
Innodb_buffer_pool_write_requests       1
Innodb_data_fsyncs       7
Innodb_data_pending_fsyncs       0
Innodb_data_pending_reads       0
Innodb_data_pending_writes       0
Innodb_data_read       2494464
Innodb_data_reads       25
Innodb_data_writes       7
Innodb_data_written       35328
Innodb_dblwr_pages_written       1
Innodb_dblwr_writes       1
Innodb_log_waits       0
Innodb_log_write_requests       0
Innodb_log_writes       2
Innodb_os_log_fsyncs       5
Innodb_os_log_pending_fsyncs       0
Innodb_os_log_pending_writes       0
Innodb_os_log_written       1024
Innodb_page_size       16384
Innodb_pages_created       0
Innodb_pages_read       19
Innodb_pages_written       1
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       0
Innodb_rows_inserted       0
Innodb_rows_read       0
Innodb_rows_updated       0
Key_blocks_not_flushed       0
Key_blocks_unused       0
Key_blocks_used       7248
Key_read_requests       412623548
Key_reads       3730337
Key_write_requests       5236702
Key_writes       626437
Last_query_cost       0.000000
Max_used_connections       94
Not_flushed_delayed_rows       0
Open_files       624
Open_streams       0
Open_tables       396
Opened_tables       4364
Prepared_stmt_count       0
Qcache_free_blocks       0
Qcache_free_memory       0
Qcache_hits       0
Qcache_inserts       0
Qcache_lowmem_prunes       0
Qcache_not_cached       0
Qcache_queries_in_cache       0
Qcache_total_blocks       0
Questions       2036268
Rpl_status       NULL
Select_full_join       110
Select_full_range_join       0
Select_range       190970
Select_range_check       5
Select_scan       625735
Slave_open_temp_tables       0
Slave_retried_transactions       0
Slave_running       OFF
Slow_launch_threads       46
Slow_queries       136
Sort_merge_passes       65
Sort_range       202001
Sort_rows       68865006
Sort_scan       253908
Ssl_accept_renegotiates       0
Ssl_accepts       0
Ssl_callback_cache_hits       0
Ssl_cipher
Ssl_cipher_list
Ssl_client_connects       0
Ssl_connect_renegotiates       0
Ssl_ctx_verify_depth       0
Ssl_ctx_verify_mode       0
Ssl_default_timeout       0
Ssl_finished_accepts       0
Ssl_finished_connects       0
Ssl_session_cache_hits       0
Ssl_session_cache_misses       0
Ssl_session_cache_mode       NONE
Ssl_session_cache_overflows       0
Ssl_session_cache_size       0
Ssl_session_cache_timeouts       0
Ssl_sessions_reused       0
Ssl_used_session_cache_entries       0
Ssl_verify_depth       0
Ssl_verify_mode       0
Ssl_version
Table_locks_immediate       1743978
Table_locks_waited       3117
Tc_log_max_pages_used       0
Tc_log_page_size       0
Tc_log_page_waits       0
Threads_cached       52
Threads_connected       16
Threads_created       66542
Threads_running       10
Uptime       74413
Uptime_since_flush_status       74413

Umesh

Are you using only MyISAM engine or InnoDB? or mix of these.. pls let me know

Do you see any changes in CPU usage? must be dropped a little bit...

grayboo

ASKER

hi ushastry.

its MyISAM only. The numbers seems the same. really puzzling. the owner of the db said some optimization to the queries were done, but i cant see the diff in the numbers.

ASKER CERTIFIED SOLUTION

ai_ja_nai

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Umesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial