Re: large table design / query performance
Rick,
Sorry for the lack of details - this was my first ever blog post/request for help! After my post I partioned the table into date chunks and this appears to have solved the problem for some simple queries that just select and count lines in a specified data range. I have not tried the big joins I need for this project and may still have a query performance problem. I can improve the hardware (improve I/O) or upgrade to 5.6 if that will help.
Loading data with mysqlimport is reasonably fast.
innodb_buffer_pool_size=112197632
RAM=12GB
queries have inner joins with with conditions on primary keys only (which are indexed) and a timestamp constraint (now utilizing partition pruning - maybe the fix).
Here is information from the most problematic table after adding the partitions. I am working on the queries with joins and tables containing parametric data (doubles, ints and severial of the priamry keys in the table below) so they are not included.
*************************** 1. row ***************************
*************************** 1. row ***************************
Name: metadata
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 4871899
Avg_row_length: 1532
Data_length: 7466139648
Max_data_length: 0
Index_length: 583106560
Data_free: 42388684800
Auto_increment: NULL
Create_time: NULL
Update_time: NULL
Check_time: NULL
Collation: latin1_swedish_ci
Checksum: NULL
Create_options: partitioned
Comment:
Edited 1 time(s). Last edit at 04/25/2013 05:28PM by Peter Kanas.
Subject
Views
Written By
Posted
2481
March 04, 2013 02:47PM
1115
March 05, 2013 11:23PM
Re: large table design / query performance
905
March 06, 2013 12:29PM
959
March 07, 2013 07:37PM
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.