MySQL Forums
Forum List  »  MyISAM

MySQL reports no errors on inserts and updates but takes no action
Posted by: Joao Silva
Date: July 26, 2006 04:20AM

Hello everyone:

I have a c application that preforms inserts and updates into around 5 diferent MyISAM tables. The insert/update rate can easly read the 500 to 700 queries (inserts and updates) per second, so it has some load this application works 24 over 7.

But sometimes like once a week, sometimes less, and out of the blue my MySQL databse reports success and rows affected to all my queries (inserts and updates) but writes nothing into the tables.

The number of selects is relativelly small.

The size of some tables can reach the 5 million recods per day, but are truncated every day after we collect the data from it.When the problem occurs, we need to restart MySQL or sometimes just rebuild the indexes in the database, that's the only two solutions we can use in order to solve the problem, which causes service downtime.

The computer is a dual xeon HP with 4 GB memory running Debian. When the problem occurs the load is very small (server is sleeping), there is no significant IO too.

Here I send a status during normal operation, since I need to wait to see if the server gets to the described situation to send you the same information at that time.

+--------------------------+------------+
| Variable_name | Value |
+--------------------------+------------+
| Aborted_clients | 6251 |
| Aborted_connects | 1 |
| Bytes_received | 775255534 |
| Bytes_sent | 2250374800 |
| Com_admin_commands | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_change_db | 155410 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 1 |
| Com_delete | 462892 |
| Com_delete_multi | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_flush | 1 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_insert | 24694818 |
| Com_insert_select | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_load_master_data | 0 |
| Com_load_master_table | 0 |
| Com_lock_tables | 2 |
| Com_optimize | 0 |
| Com_purge | 0 |
| Com_rename_table | 2 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_restore_table | 0 |
| Com_revoke | 0 |
| Com_rollback | 18476 |
| Com_savepoint | 0 |
| Com_select | 635785 |
| Com_set_option | 507800 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_create | 2 |
| Com_show_databases | 29 |
| Com_show_fields | 1096 |
| 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 | 1 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2034 |
| Com_show_innodb_status | 0 |
| Com_show_tables | 45 |
| Com_show_variables | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_truncate | 9 |
| Com_unlock_tables | 0 |
| Com_update | 106667223 |
| Com_update_multi | 0 |
| Connections | 665269 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_tables | 5565 |
| Created_tmp_files | 3 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Delayed_errors | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 468586 |
| Handler_read_first | 37 |
| Handler_read_key | 240595636 |
| Handler_read_next | 3694607953 |
| Handler_read_prev | 910264 |
| Handler_read_rnd | 793033 |
| Handler_read_rnd_next | 1411771089 |
| Handler_rollback | 0 |
| Handler_update | 205933152 |
| Handler_write | 25076084 |
| Key_blocks_used | 498760 |
| Key_read_requests | 1677204119 |
| Key_reads | 329705 |
| Key_write_requests | 158551335 |
| Key_writes | 158519687 |
| Max_used_connections | 6 |
| Not_flushed_key_blocks | 0 |
| Not_flushed_delayed_rows | 0 |
| Open_tables | 55 |
| Open_files | 95 |
| Open_streams | 0 |
| Opened_tables | 97 |
| Questions | 133845135 |
| Qcache_queries_in_cache | 210 |
| Qcache_inserts | 633313 |
| Qcache_hits | 39012 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 2394 |
| Qcache_free_memory | 526945192 |
| Qcache_free_blocks | 119 |
| Qcache_total_blocks | 548 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 1553 |
| Select_range_check | 0 |
| Select_scan | 94278 |
| Slave_open_temp_tables | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 27 |
| Sort_merge_passes | 0 |
| Sort_range | 5510 |
| Sort_rows | 1409828 |
| Sort_scan | 5746 |
| Table_locks_immediate | 132502107 |
| Table_locks_waited | 9750 |
| Threads_cached | 3 |
| Threads_created | 7 |
| Threads_connected | 4 |
| Threads_running | 1 |
| Uptime | 609629 |
+--------------------------+------------+
133 rows in set (0.00 sec)

Threads: 4
Questions: 133860645
Slow queries: 27
Opens: 97
Flush tables: 1
Open tables: 55
Queries per second avg: 219.562

I hope anyone can help me on this.

Best regards
João Silva

Options: ReplyQuote


Subject
Views
Written By
Posted
MySQL reports no errors on inserts and updates but takes no action
2048
July 26, 2006 04:20AM


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.