MySQL Forums
Forum List  »  InnoDB

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.)

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: large table design / query performance
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.