MySQL Forums
Forum List  »  Partitioning

Re: Minimum number of rows for using partitioning
Posted by: Ramesh Kumar
Date: December 13, 2009 09:57PM

Hi Rick James,

Thanks for your response. Kindly find my inline response for your questions.

***How much RAM do you have?

I have 32 GB of RAM.

***MyISAM or InnoDB?

My tables are MyISAM tables.

***Do you insert into the 'last' partition only?

My application monitors large number of m/c's and stored it's stats data like CPU,Memory in the mysql MyISAM tables. The data collected from all the m/c with 5 seconds interval. So there are 720 records for every one hour for one m/c. All the stats tables are daily table(one table per day). All the stats tables have the following 2 common columns,
- Machine_ID - Machine Id for the m/c
- TIMESTAMP - time in millis (ie) Time in milliseconds since January 1 1970 00:00:00 GMT.


Initially I thought, I should use range partition by using TIMESTAMP column hourly (ie) 24 partitions for every daily tables. Following are my earlier plan for the partition.

.....
partition by range (HOUR(from_unixtime(TIMESTAMP/1000)))
PARTITION p1 VALUES LESS THAN(1),
PARTITION p2 VALUES LESS THAN(2),
PARTITION p3 VALUES LESS THAN(3),
...
PARTITION p24 VALUES LESS THAN(24),
.....

Since the above partition design requires execution of '(HOUR(from_unixtime(TIMESTAMP/1000)))' expression for every insert/select, I thought it could be costly operations. But I am not sure. So, I planned to use key partition as below,

....
PARTITION BY KEY(Machine_ID)
PARTITIONS 10;
....


Kindly validate my above assumptions. If I use 1'st approach, the insert will happen only in last partition. But if I go with 2'nd approach, all the partitions will be used.


***What are your SELECTs like?

Most of the select queries used Machine_ID and TIMESTAMP columns. For example,

select CPU_USED, MEM_USED from xxx where MACHINE_ID=xx AND ((TIMESTAMP > xxx) AND (TIMESTAMP < xxx))


Please let me know, if you require any other info.

Thanks,
Ramesh

Options: ReplyQuote


Subject
Views
Written By
Posted
Re: Minimum number of rows for using partitioning
2649
December 13, 2009 09:57PM


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.