Re: Slow query on partitioned tables with over 200M rows
Posted by:
Hong Yang
Date: February 12, 2014 06:41PM
Hi Rick,
Thank you so much for your help. I finally made some progress.
I created an identical table with primary key in different order(device_key, component_key, fct_ts) and coped all the rows in a partition to the new table. The same query returned within seconds with cold cache. The column order made a huge difference.
Here are the variables you asked about.
+------------------------------------------+------------------------+
| Variable_name | Value |
+------------------------------------------+------------------------+
| innodb_adaptive_flushing | ON |
| innodb_adaptive_flushing_lwm | 10 |
| innodb_adaptive_hash_index | OFF |
| innodb_adaptive_max_sleep_delay | 150000 |
| innodb_additional_mem_pool_size | 33554432 |
| innodb_api_bk_commit_interval | 5 |
| innodb_api_disable_rowlock | OFF |
| innodb_api_enable_binlog | OFF |
| innodb_api_enable_mdl | OFF |
| innodb_api_trx_level | 0 |
| innodb_autoextend_increment | 64 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_dump_at_shutdown | OFF |
| innodb_buffer_pool_dump_now | OFF |
| innodb_buffer_pool_filename | ib_buffer_pool |
| innodb_buffer_pool_instances | 8 |
| innodb_buffer_pool_load_abort | OFF |
| innodb_buffer_pool_load_at_startup | OFF |
| innodb_buffer_pool_load_now | OFF |
| innodb_buffer_pool_size | 107374182400 |
| innodb_change_buffer_max_size | 25 |
| innodb_change_buffering | all |
| innodb_checksum_algorithm | innodb |
| innodb_checksums | ON |
| innodb_cmp_per_index_enabled | OFF |
| innodb_commit_concurrency | 0 |
| innodb_compression_failure_threshold_pct | 5 |
| innodb_compression_level | 6 |
| innodb_compression_pad_pct_max | 50 |
| innodb_concurrency_tickets | 5000 |
| innodb_data_file_path | ibdata1:12M:autoextend |
| innodb_data_home_dir | |
| innodb_disable_sort_file_cache | OFF |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_format | Barracuda |
| innodb_file_format_check | ON |
| innodb_file_format_max | Antelope |
| innodb_file_per_table | ON |
| innodb_flush_log_at_timeout | 1 |
| innodb_flush_log_at_trx_commit | 2 |
| innodb_flush_method | O_DIRECT |
Subject
Views
Written By
Posted
2637
February 11, 2014 05:10PM
939
February 12, 2014 01:48AM
1010
February 12, 2014 08:33AM
1068
February 12, 2014 08:47AM
970
February 12, 2014 09:59AM
1132
February 12, 2014 02:31PM
1093
February 12, 2014 02:53PM
1337
February 12, 2014 04:33PM
Re: Slow query on partitioned tables with over 200M rows
1055
February 12, 2014 06:41PM
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.