Members Members   Search Search   Help Help   Register Register   Login Login   Home Home
Home » Performance » MySQL » MySQL slow with innodb table of +-55MB
MySQL slow with innodb table of +-55MB [message #2174] Thu, 08 November 2007 05:55 Go to next message
kurt is currently offline kurt
Messages: 3
Registered: November 2007
Location: Belgium
Junior Member
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     cached
Mem:           503        462         40          0         45        127
-/+ buffers/cache:        290        213
Swap:         2047         85       1961

atlas:~# cat /proc/cpuinfo | egrep "processor|vendor_id|model name|cpu MHz|cpu cores"
processor       : 0
vendor_id       : GenuineIntel
model name      : Pentium III (Coppermine)
cpu MHz         : 996.841
Report message to a moderator
Re: MySQL slow with innodb table of +-55MB [message #2179 is a reply to message #2174 ] Thu, 08 November 2007 18:44 Go to previous message
sterin is currently offline sterin
Messages: 324
Registered: March 2007
Location: Sweden
Senior Member
1.
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.

2.
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.

3.
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.

4.
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.
Report message to a moderator
Previous Topic:Build a Spatial index like r-tree
Next Topic:Joining multiple tables turns an indexed query into a temp/filesort
Goto Forum:
  

[ Generate printable PDF ] [ Syndicate this forum (XML) ] [ ]

Current Time: Fri Jul 10 03:08:18 EDT 2009

Total time taken to generate the page: 0.20127 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 2.7.7.
Copyright ©2001-2007 FUD Forum Bulletin Board Software

MySQL is a trademark of Sun Microsystems.
InnoDB is a trademark of Oracle Corp.

Percona Performance Forums are a service of Percona, Inc.
Not affiliated with Sun Microsystems or Oracle Corp.