MySQL Forums
Forum List  »  InnoDB

Re: large table design / query performance
Posted by: Peter Kanas
Date: March 06, 2013 12:29PM

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.

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: large table design / query performance
905
March 06, 2013 12:29PM


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.