Re: large table design / query performance
Posted by:
Rick James
Date: March 07, 2013 07:37PM
> innodb_buffer_pool_size=112197632
> RAM=12GB
Rather small buffer_pool for that RAM. 8G would be better.
> upgrade to 5.6 if that will help
Maybe. 5.6 has a lot of obscure optimization improvements.
> I can improve the hardware (improve I/O)
Recommended: (caveat: $$$): RAID controller with batter-backed-write-cache in front of multiple drives, configured RAID-5 or -10.
> PRIMARY KEY (`DRV_SBR`,`DRV_SERIAL`,`HEAD_PHYS_PSN`,`SURFACE`,`OPERATION`,`TRANS_SEQ`,`EVENT_DATE`),
> KEY `DRV_SBR` (`DRV_SBR`,`DRV_SERIAL`,`HEAD_PHYS_PSN`,`SURFACE`,`OPERATION`,`TRANS_SEQ`,`EVENT_DATE`)
A PRIMARY KEY is a KEY. Therefore, the KEY is redundant, and should be dropped.
> char(128) DEFAULT NULL
Is it always exactly 128 characters long? (Else, use VARCHAR)
Can it really be NULL? (Else use NOT NULL)
(These apply to most of your fields.)
> int(11) DEFAULT NULL
Most smell like INT UNSIGNED.
Many smell like NOT NULL.
Many smell like SMALLINT UNSIGNED (etc).
Smaller --> more cacheable --> less I/O --> faster.
> PRIMARY KEY (..., `EVENT_DATE`),
> /*!50100 PARTITION BY RANGE ( UNIX_TIMESTAMP(event_date))
Good -- that is usually the best way to deal with a partition key.
50 partitions? Most of them empty because they are in the future? Iffy. All partitions are opened for many operations, even when only a few partition(s) are actually touched. (This is a 'bug'.) So, extra partitions is an overhead. I find it better to build new partitions just before they are needed.
> PARTITION p50 VALUES LESS THAN MAXVALUE
Good -- a catcher in case you fail to build a new partition in time. Be sure to REORGANIZE p50 into the new partition + p50. That way anything that spills into it will be properly handled. ('future' would be a better name than 'p50'.)
Is innodb_file_per_table=OFF ? You may regret that later.
> Loading data with mysqlimport is reasonably fast.
It will be faster if the data is already sorted in PRIMARY KEY order. (Or the PK fields are ordered appropriately.)