MySQL slow with innodb table of +-55MB

MySQL & MariaDB Other MySQL® Questions
1

Hello,

I have a problem with mysql and my innodb database.
MySQL is really slow at handling my 55mb db with 200.000 records.

Could you guys give me some advise how i could make it go faster and drop the load on the server?

Thanks!!

mysql> show global status;±----------------------------------±-----------+| Variable_name | Value |±----------------------------------±-----------+| Aborted_clients | 189 || Aborted_connects | 80 || Binlog_cache_disk_use | 0 || Binlog_cache_use | 599140 || Bytes_received | 491582192 || Bytes_sent | 1265903251 || Com_admin_commands | 631 || Com_alter_db | 0 || Com_alter_table | 71 || Com_analyze | 2 || Com_backup_table | 0 || Com_begin | 0 || Com_change_db | 32052 || Com_change_master | 0 || Com_check | 34 || Com_checksum | 0 || Com_commit | 55 || Com_create_db | 11 || Com_create_function | 0 || Com_create_index | 44 || Com_create_table | 314 || Com_create_user | 2 || Com_dealloc_sql | 0 || Com_delete | 2397 || Com_delete_multi | 0 || Com_do | 0 || Com_drop_db | 7 || Com_drop_function | 0 || Com_drop_index | 0 || Com_drop_table | 5 || Com_drop_user | 5 || Com_execute_sql | 0 || Com_flush | 18 || Com_grant | 33 || Com_ha_close | 0 || Com_ha_open | 0 || Com_ha_read | 0 || Com_help | 0 || Com_insert | 3455 || Com_insert_select | 598829 || Com_kill | 4 || Com_load | 0 || Com_load_master_data | 0 || Com_load_master_table | 0 || Com_lock_tables | 2 || Com_optimize | 5 || Com_preload_keys | 0 || Com_prepare_sql | 0 || Com_purge | 0 || Com_purge_before_date | 0 || Com_rename_table | 0 || Com_repair | 2 || Com_replace | 0 || Com_replace_select | 0 || Com_reset | 0 || Com_restore_table | 0 || Com_revoke | 12 || Com_revoke_all | 0 || Com_rollback | 554 || Com_savepoint | 0 || Com_select | 1583743 || Com_set_option | 7435 || Com_show_binlog_events | 2 || Com_show_binlogs | 183 || Com_show_charsets | 810 || Com_show_collations | 810 || Com_show_column_types | 0 || Com_show_create_db | 0 || Com_show_create_table | 176 || Com_show_databases | 202 || Com_show_errors | 0 || Com_show_fields | 1102 || Com_show_grants | 384 || Com_show_innodb_status | 5 || Com_show_keys | 100 || 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 | 20 || Com_show_slave_hosts | 0 || Com_show_slave_status | 0 || Com_show_status | 10338 || Com_show_storage_engines | 7 || Com_show_tables | 944 || Com_show_triggers | 120 || Com_show_variables | 1792 || 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 | 2 || Com_unlock_tables | 2 || Com_update | 1640 || Com_update_multi | 1 || 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 | 26730 || Created_tmp_disk_tables | 3792 || Created_tmp_files | 239 || Created_tmp_tables | 15993 || Delayed_errors | 0 || Delayed_insert_threads | 0 || Delayed_writes | 0 || Flush_commands | 1 || Handler_commit | 1198548 || Handler_delete | 2156 || Handler_discover | 0 || Handler_prepare | 1198278 || Handler_read_first | 5338171 || Handler_read_key | 28747990 || Handler_read_next | 3754336036 || Handler_read_prev | 0 || Handler_read_rnd | 1278 || Handler_read_rnd_next | 66499665 || Handler_rollback | 258 || Handler_savepoint | 0 || Handler_savepoint_rollback | 0 || Handler_update | 7863091 || Handler_write | 38327555 || Innodb_buffer_pool_pages_data | 511 || Innodb_buffer_pool_pages_dirty | 0 || Innodb_buffer_pool_pages_flushed | 491522 || Innodb_buffer_pool_pages_free | 0 || Innodb_buffer_pool_pages_latched | 0 || Innodb_buffer_pool_pages_misc | 1 || Innodb_buffer_pool_pages_total | 512 || Innodb_buffer_pool_read_ahead_rnd | 486 || Innodb_buffer_pool_read_ahead_seq | 317524 || Innodb_buffer_pool_read_requests | 1273517571 || Innodb_buffer_pool_reads | 43350477 || Innodb_buffer_pool_wait_free | 0 || Innodb_buffer_pool_write_requests | 20049901 || Innodb_data_fsyncs | 1242172 || Innodb_data_pending_fsyncs | 0 || Innodb_data_pending_reads | 0 || Innodb_data_pending_writes | 0 || Innodb_data_read | 1860108288 || Innodb_data_reads | 44234001 || Innodb_data_writes | 1633178 || Innodb_data_written | 425447424 || Innodb_dblwr_pages_written | 491349 || Innodb_dblwr_writes | 11648 || Innodb_log_waits | 0 || Innodb_log_write_requests | 2214673 || Innodb_log_writes | 1212225 || Innodb_os_log_fsyncs | 1219019 || Innodb_os_log_pending_fsyncs | 0 || Innodb_os_log_pending_writes | 0 || Innodb_os_log_written | 1498223104 || Innodb_page_size | 16384 || Innodb_pages_created | 24350 || Innodb_pages_read | 47561596 || Innodb_pages_written | 491522 || 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 | 388398 || Innodb_rows_inserted | 1570116 || Innodb_rows_read | 3783684179 || Innodb_rows_updated | 117 || Key_blocks_not_flushed | 0 || Key_blocks_unused | 14473 || Key_blocks_used | 82 || Key_read_requests | 16840 || Key_reads | 537 || Key_write_requests | 3113 || Key_writes | 2469 || Last_query_cost | 0.000000 || Max_used_connections | 29 || Ndb_cluster_node_id | 0 || Ndb_config_from_host | || Ndb_config_from_port | 0 || Ndb_number_of_data_nodes | 0 || Not_flushed_delayed_rows | 0 || Open_files | 67 || Open_streams | 0 || Open_tables | 64 || Opened_tables | 2303 || Prepared_stmt_count | 0 || Qcache_free_blocks | 47 || Qcache_free_memory | 11378792 || Qcache_hits | 1689440 || Qcache_inserts | 1502083 || Qcache_lowmem_prunes | 1090480 || Qcache_not_cached | 92660 || Qcache_queries_in_cache | 5238 || Qcache_total_blocks | 10545 || Questions | 3959053 || Rpl_status | NULL || Select_full_join | 2108 || Select_full_range_join | 0 || Select_range | 128 || Select_range_check | 0 || Select_scan | 28653 || Slave_open_temp_tables | 0 || Slave_retried_transactions | 0 || Slave_running | OFF || Slow_launch_threads | 0 || Slow_queries | 106 || Sort_merge_passes | 1172 || Sort_range | 119 || Sort_rows | 30469730 || Sort_scan | 635 || 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 | 2801958 || Table_locks_waited | 5 || Tc_log_max_pages_used | 0 || Tc_log_page_size | 0 || Tc_log_page_waits | 0 || Threads_cached | 6 || Threads_connected | 8 || Threads_created | 408 || Threads_running | 1 || Uptime | 955942 |±----------------------------------±-----------+251 rows in set (0.01 sec)mysql> select version();±-------------------------+| version() |±-------------------------+| 5.0.32-Debian_7etch1-log |±-------------------------+1 row in set (0.00 sec)atlas:~# free -m total used free shared buffers cachedMem: 503 462 40 0 45 127-/+ buffers/cache: 290 213Swap: 2047 85 1961atlas:~# cat /proc/cpuinfo | egrep "processor|vendor_id|model name|cpu MHz|cpu cores"processor : 0vendor_id : GenuineIntelmodel name : Pentium III (Coppermine)cpu MHz : 996.841

2

One of your main problem is that you are only allowing InnoDB to use 8MB for cache.
You should add something like this to your my.cnf:

innodb_buffer_pool_size=80M

I choose 80MB since it sounded as a good compromise if your DB was 55MB. But you don’t seem to have so much memory in that machine so watch out for setting this value too high.

Another figure that looks pretty odd to me is that 25% of your queries seems to be “INSERT … SELECT …;” queries.
Usually the figures for using only INSERT, DELETE or UPDATE statements are higher and this is lower.

But step 2 above shows that 1/4 of your queries are writes and that means that you should look into setting:

innodb_flush_log_at_trx_commit=2

Because if your queries are auto commited then InnoDB as default needs to write and flush the transaction log file for each commit.
Which due to disk speed usually turns out to only about 140 times per second.
With the setting above InnoDB will only flush the transaction log about 1 time each second. Which usually speeds up INSERT/UPDATES a lot.

Apart from that you also seem to have a few queries with JOIN’s in them where you are lacking indexes for the join condition.

But start with changing the two parameter changes that I suggested and see what happens.