MySQL Forums
Forum List  »  Performance

High cpu load problem
Posted by: mike wylie
Date: July 18, 2005 03:00AM

Hi,

I've been a mysql user for a few users but never administered it. I am having problems with high cpu load.

I am running mysql 4.0.24 which I'm afraid I'm stuck with for the near future. It's running on a Redhad Enterprise 1.2 system.

My system basically opens a single database connection, then pulls out every record in turn for my program to use. The data collected can contain 11Meg clobs, but on average the size of data collected will be Below 1 Meg.

My system was multi-threaded, and the data collected from the database was passed on to many other processes but after the system has been running for a while (over 4 hours or so) the mysql process is running at 100% cpu usage and the other processes are ticking over waiting for it.

Is there something I can do to improve my performance? Am I keeping a single database connection open too long?

Any advice would be appreciated.

following the advice from http://www.mysql.com/news-and-events/newsletter/2004-01/a0000000301.html
I have looked at the admin information but it doesn't mean much to me. I have added it here in case it may be helpful.

Many thanks

--

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 38 |
| Aborted_connects | 0 |
| Bytes_received | 1899746098 |
| Bytes_sent | 3458140469 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 32 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 1 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 9 |
| Com_delete | 6 |
| Com_delete_multi | 0 |
| Com_drop_db | 1 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 1964745 |
| 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_purge | 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_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 4527132 |
| Com_set_option | 40 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 0 |
| Com_show_databases | 3 |
| Com_show_fields | 10 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_logs | 0 |
| Com_show_master_status | 0 |
| Com_show_new_master | 0 |
| Com_show_open_tables | 0 |
| Com_show_processlist | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 6 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 2 |
| Com_show_variables | 20 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 40 |
| Com_unlock_tables | 0 |
| Com_update | 198297 |
| Com_update_multi | 0 |
| Connections | 40 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 0 |
| Created_tmp_files | 5 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_read_first | 4747 |
| Handler_read_key | 3109895 |
| Handler_read_next | 387708 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 2080372 |
| Handler_read_rnd_next | 679836207 |
| Handler_rollback | 2 |
| Handler_update | 198297 |
| Handler_write | 1964745 |
| Key_blocks_used | 7793 |
| Key_read_requests | 19318752 |
| Key_reads | 700 |
| Key_write_requests | 2227397 |
| Key_writes | 2227397 |
| Max_used_connections | 1 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 8 |
| Open_files | 16 |
| Open_streams | 0 |
| Opened_tables | 94 |
| Questions | 6690364 |
| Qcache_queries_in_cache | 0 |
| Qcache_inserts | 0 |
| Qcache_hits | 0 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 0 |
| Qcache_free_memory | 0 |
| Qcache_free_blocks | 0 |
| Qcache_total_blocks | 0 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 897493 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 2 |
| Sort_merge_passes | 1 |
| Sort_range | 0 |
| Sort_rows | 2080372 |
| Sort_scan | 897487 |
| Table_locks_immediate | 6690186 |
| Table_locks_waited | 0 |
| Threads_cached | 0 |
| Threads_created | 39 |
| Threads_connected | 2 |
| Threads_running | 2 |
| Uptime | 328329 |
+--------------------------+------------+

Options: ReplyQuote


Subject
Views
Written By
Posted
High cpu load problem
25905
July 18, 2005 03:00AM
9466
July 18, 2005 05:16AM
9057
July 18, 2005 04:46PM
9552
July 19, 2005 12:49AM
9220
July 19, 2005 04:04AM
7843
July 19, 2005 06:35AM
8209
July 19, 2005 06:22PM
10480
July 19, 2005 10:01AM
6616
July 25, 2005 03:15AM
7246
July 18, 2005 05:50PM
6162
July 19, 2005 12:59AM


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.