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 |
+--------------------------+------------+