MySQL Forums
Forum List  »  Optimizer & Parser

Re: Optimized Indexing for a Schedule Table
Posted by: Wes Thompson
Date: November 26, 2010 11:36PM

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)

Options: ReplyQuote


Subject
Views
Written By
Posted
3742
November 23, 2010 11:58PM
Re: Optimized Indexing for a Schedule Table
1354
November 26, 2010 11:36PM


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.