MySQL Forums
Forum List  »  Performance

Help me out here - eating too much CPU
Posted by: mike
Date: October 26, 2005 10:04PM

I just upgraded to MySQL 5, and I'm using the instance manager, which is great (although it has some odd quirks) - anyway, due to not being able to use LinuxThreads on my distribution, it shows up as a single process (which is fine) in the process list.

However, the server is only doing 27 queries per second, and is the only thing running (except for ssh) on an Opteron 1.6ghz, 1 gig RAM, XFS filesystem on an 160 gig 7200 rpm WD ATA100 drive.

mysql 11700 0.0 0.0 13236 960 ? Ss 05:28 0:00 /usr/local/mysql/bin/mysqlmanager --user=mysql --pid-file=/tmp/manager.pid --log=/tmp/manager.log --run-as-service

mysql 11701 50.0 0.1 46004 1420 ? S 05:28 463:08 /usr/local/mysql/bin/mysqlmanager --user=mysql --pid-file=/tmp/manager.pid --log=/tmp/manager.log --run-as-service

mysql 11839 49.9 3.1 159020 32240 ? S 05:29 461:58 /usr/local/mysql/bin/mysqld --no-defaults --port=3310 --user=mysql --socket=/tmp/mysql-babeware.sock --datadir=/usr/local/mysql/var-babeware --pid-file=/usr/local/mysql/var-babeware/web05.pid --myisam-recover=FORCE --max_connections=300 --server-id=1 --skip-concurrent-insert --skip-name-resolve --skip-thread-priority --skip-external-locking --skip-innodb --skip-bdb --flush --expire_logs_days=7 --key_buffer=128M --max_allowed_packet=1M --table_cache=64 --sort_buffer_size=8M --net_buffer_length=8K --myisam_sort_buffer_size=128M --max_heap_table_size=256M --tmp_table_size=256M --read_rnd_buffer_size=16M --max_binlog_size=128M

The reason for all the arugments is because that is how MySQL IM launches it, apparently. It takes all the my.cnf configuration variables I have and puts them into command line options.

Anyway - first off, the instance manager seems to eat up all available CPU (as you see, 50% above) - and the daemon is using 49% CPU. It shouldn't be. Originally I was able to do something like 11,000 queries per second on these Opterons... nowadays I can't even do 100-200 without some odd performance/resource hogging quirks. It -seems- tied to NPTL / libc6 on Debian-amd64.

Hopefully someone can help me here. Here's more details:

I'm using the latest MySQL binary from mysql.com.

Distro: Debian-AMD64 / stable (sarge)

[root@web05 root]# uname -a
Linux web05 2.6.11.12 #1 Wed Jun 29 04:18:14 UTC 2005 x86_64 GNU/Linux

[root@web05 root]# cat /proc/cpuinfo*
processor : 0
vendor_id : AuthenticAMD
cpu family : 15
model : 5
model name : AMD Opteron(tm) Processor 242
stepping : 8
cpu MHz : 1593.044
cache size : 1024 KB
fpu : yes
fpu_exception : yes
cpuid level : 1
wp : yes
flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush mmx fxsr sse sse2 pni syscall nx mmxext lm 3dnowext 3dnow
bogomips : 3129.34
TLB size : 1024 4K pages
clflush size : 64
cache_alignment : 64
address sizes : 40 bits physical, 48 bits virtual
power management: ts ttp

/etc/my.cnf:

# Was an example mysql config file for medium systems.
[manager]
socket = /tmp/manager.sock
pid-file = /tmp/manager.pid
monitoring-interval = 2
bind-address = 192.168.0.15
port = 2273
password-file = /usr/local/mysql/etc/mysqlmanager.passwd

[mysqld]
port = 3310
user = mysql
socket = /tmp/mysql-babeware.sock
#nonguarded
datadir = /usr/local/mysql/var-babeware
pid-file = /usr/local/mysql/var-babeware/web05.pid
#log = /tmp/mysql-babeware.log
#log-error
myisam-recover = FORCE
max_connections = 300
server-id = 1
skip-concurrent-insert
skip-name-resolve
skip-thread-priority
skip-external-locking
skip-innodb
skip-bdb
flush
expire_logs_days = 7

key_buffer = 128M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 8M
net_buffer_length = 8K
myisam_sort_buffer_size = 128M
max_heap_table_size = 256M
tmp_table_size = 256M
read_rnd_buffer_size = 16M
max_binlog_size = 128M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M

mysql> show status;
+-----------------------------------+----------+
| Variable_name | Value |
+-----------------------------------+----------+
| Aborted_clients | 0 |
| Aborted_connects | 32362 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 91 |
| Bytes_sent | 78 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 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_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 | 0 |
| 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 |
| Connections | 98221 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 0 |
| Created_tmp_tables | 1 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| 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 | 129 |
| Innodb_buffer_pool_pages_data | 0 |
| Innodb_buffer_pool_pages_dirty | 0 |
| Innodb_buffer_pool_pages_flushed | 0 |
| Innodb_buffer_pool_pages_free | 0 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 0 |
| Innodb_buffer_pool_pages_total | 0 |
| Innodb_buffer_pool_read_ahead_rnd | 0 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 0 |
| Innodb_buffer_pool_reads | 0 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 0 |
| Innodb_data_fsyncs | 0 |
| Innodb_data_pending_fsyncs | 0 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 0 |
| Innodb_data_reads | 0 |
| Innodb_data_writes | 0 |
| Innodb_data_written | 0 |
| Innodb_dblwr_pages_written | 0 |
| Innodb_dblwr_writes | 0 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 0 |
| Innodb_log_writes | 0 |
| Innodb_os_log_fsyncs | 0 |
| Innodb_os_log_pending_fsyncs | 0 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 0 |
| Innodb_page_size | 0 |
| Innodb_pages_created | 0 |
| Innodb_pages_read | 0 |
| 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 | 106785 |
| Key_blocks_used | 393 |
| Key_read_requests | 71279376 |
| Key_reads | 11273 |
| Key_write_requests | 752300 |
| Key_writes | 79962 |
| Last_query_cost | 0.000000 |
| Max_used_connections | 19 |
| Not_flushed_delayed_rows | 0 |
| Open_files | 60 |
| Open_streams | 0 |
| Open_tables | 46 |
| Opened_tables | 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 | 1529154 |
| 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 | 322 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| Table_locks_immediate | 893249 |
| Table_locks_waited | 914 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 0 |
| Threads_connected | 1 |
| Threads_created | 98220 |
| Threads_running | 1 |
| Uptime | 56025 |
+-----------------------------------+----------+
221 rows in set (0.00 sec)

mysql> status;
--------------
mysql Ver 14.12 Distrib 5.0.15, for unknown-linux-gnu (x86_64) using readline 5.0

Connection id: 98215
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.15-debug
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql-babeware.sock
Uptime: 15 hours 34 min 1 sec

Threads: 1 Questions: 1529421 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 46 Queries per second avg: 27.291
--------------


any help is appreciated....

please reply to my thread about mysql usage - i'd like to collect some data! thanks!
http://forums.mysql.com/read.php?11,57080

Options: ReplyQuote


Subject
Views
Written By
Posted
Help me out here - eating too much CPU
2130
October 26, 2005 10:04PM
1194
November 04, 2005 11:51PM


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.