Re: Optimized Indexing for a Schedule Table
Info requested:
innodb_buffer_pool_size is 8M, (512pg 16384B/pg)
autocommmit is enabled in the examples. note that there were no other accesses being performed on the tables
RAM = 640MB
There are 3 million things to do. Eventually all 3 million rows will be handled (e.g. when an additional server is available to handle them). The low priority items are not required to be handled on-time as part of the design.
The LIMIT value is not always 10 but is always a constant number. Low values are preferred so that latency does not become an issue between queries. LIMIT value is chosen to buffer data locally so that local processes have enough data to work until the next query is successful.
Performing 32k SQL queries is not reasonable for the problem. The existing perforance issue already behaves like that but it uses indexes so it is much faster than me trying to do it myself.
Detailed config info below:
# free
total used free shared buffers cached
Mem: 645940 638932 7008 0 4428 159908
-/+ buffers/cache: 474596 171344
Swap: 875500 207400 668100
mysql> show status;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 88 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Bytes_received | 277 |
| Bytes_sent | 13285 |
| Com_admin_commands | 0 |
| Com_alter_db | 0 |
| Com_alter_table | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_table | 0 |
| Com_create_user | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_table | 0 |
| Com_drop_user | 0 |
| Com_execute_sql | 0 |
| Com_flush | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| 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_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 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_revoke_all | 0 |
| Com_rollback | 0 |
| Com_savepoint | 0 |
| Com_select | 0 |
| Com_set_option | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_column_types | 0 |
| Com_show_create_db | 0 |
| Com_show_create_table | 0 |
| Com_show_databases | 1 |
| Com_show_errors | 0 |
| Com_show_fields | 12 |
| Com_show_grants | 0 |
| Com_show_innodb_status | 0 |
| Com_show_keys | 0 |
| 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 | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 2 |
| Com_show_storage_engines | 0 |
| Com_show_tables | 1 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| 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 | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| 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 | 554 |
| Created_tmp_disk_tables | 0 |
| Created_tmp_files | 16 |
| Created_tmp_tables | 7 |
| Delayed_errors | 0 |
| Delayed_insert_threads | 0 |
| Delayed_writes | 0 |
| Flush_commands | 1 |
| Handler_commit | 0 |
| Handler_delete | 0 |
| Handler_discover | 0 |
| Handler_prepare | 0 |
| Handler_read_first | 0 |
| Handler_read_key | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 280 |
| Handler_rollback | 0 |
| Handler_savepoint | 0 |
| Handler_savepoint_rollback | 0 |
| Handler_update | 0 |
| Handler_write | 409 |
| Innodb_buffer_pool_pages_data | 432 |
| Innodb_buffer_pool_pages_dirty | 150 |
| Innodb_buffer_pool_pages_flushed | 18271362 |
| Innodb_buffer_pool_pages_free | 73 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 7 |
| Innodb_buffer_pool_pages_total | 512 |
| Innodb_buffer_pool_read_ahead_rnd | 682052 |
| Innodb_buffer_pool_read_ahead_seq | 7962920 |
| Innodb_buffer_pool_read_requests | 1308144552 |
| Innodb_buffer_pool_reads | 259664812 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 454782467 |
| Innodb_data_fsyncs | 36701482 |
| Innodb_data_pending_fsyncs | 1 |
| Innodb_data_pending_reads | 0 |
| Innodb_data_pending_writes | 0 |
| Innodb_data_read | 3205353472 |
| Innodb_data_reads | 284279475 |
| Innodb_data_writes | 51329777 |
| Innodb_data_written | 1468697088 |
| Innodb_dblwr_pages_written | 18271362 |
| Innodb_dblwr_writes | 368360 |
| Innodb_log_waits | 0 |
| Innodb_log_write_requests | 34232154 |
| Innodb_log_writes | 35894017 |
| Innodb_os_log_fsyncs | 35970159 |
| Innodb_os_log_pending_fsyncs | 1 |
| Innodb_os_log_pending_writes | 0 |
| Innodb_os_log_written | 4008198656 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 718493 |
| Innodb_pages_read | 328399794 |
| Innodb_pages_written | 18271362 |
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 673810 |
| Innodb_row_lock_time_avg | 39635 |
| Innodb_row_lock_time_max | 51515 |
| Innodb_row_lock_waits | 17 |
| Innodb_rows_deleted | 0 |
| Innodb_rows_inserted | 22707197 |
| Innodb_rows_read | 2403593378 |
| Innodb_rows_updated | 40826571 |
| Key_blocks_not_flushed | 0 |
| Key_blocks_unused | 14497 |
| Key_blocks_used | 0 |
| Key_read_requests | 0 |
| Key_reads | 0 |
| Key_write_requests | 0 |
| Key_writes | 0 |
| Last_query_cost | 10.499000 |
| Max_used_connections | 9 |
| 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 | 54 |
| Open_streams | 0 |
| Open_tables | 64 |
| Opened_tables | 0 |
| Prepared_stmt_count | 0 |
| Qcache_free_blocks | 1 |
| Qcache_free_memory | 5648280 |
| Qcache_hits | 24053554 |
| Qcache_inserts | 7093485 |
| Qcache_lowmem_prunes | 3739268 |
| Qcache_not_cached | 411380 |
| Qcache_queries_in_cache | 10859 |
| Qcache_total_blocks | 21720 |
| Questions | 84795693 |
| Rpl_status | NULL |
| Select_full_join | 0 |
| Select_full_range_join | 0 |
| Select_range | 0 |
| Select_range_check | 0 |
| Select_scan | 4 |
| Slave_open_temp_tables | 0 |
| Slave_retried_transactions | 0 |
| Slave_running | OFF |
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
| Sort_merge_passes | 0 |
| Sort_range | 0 |
| Sort_rows | 0 |
| Sort_scan | 0 |
| 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 | 57531345 |
| Table_locks_waited | 3 |
| Tc_log_max_pages_used | 0 |
| Tc_log_page_size | 0 |
| Tc_log_page_waits | 0 |
| Threads_cached | 5 |
| Threads_connected | 4 |
| Threads_created | 9 |
| Threads_running | 2 |
| Uptime | 759397 |
| Uptime_since_flush_status | 759397 |
+-----------------------------------+------------+
253 rows in set (0.00 sec)
Subject
Views
Written By
Posted
3827
November 23, 2010 11:58PM
1270
November 25, 2010 02:35PM
1895
November 25, 2010 10:47PM
1332
November 25, 2010 11:05PM
1780
November 26, 2010 01:42PM
Re: Optimized Indexing for a Schedule Table
1405
November 26, 2010 11:36PM
1496
November 27, 2010 01:31PM
1405
November 27, 2010 02:44PM
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.