Sunday, December 03, 2006

MySQL 5.1 Partitioning - Part 3

Finally, it's time to start putting MySQL 5.1.12-beta through the wringer. First order of business, convert the existing table schema to one that supports partitioning...

I made some minor changes to the configuration for partitioning, namely innodb_file_per_table and innodb_open_files. I set innodb_open_files to 1000 based on the tables and partitions I plan on supporting.

This is what the new table schema looks like with partitioning:

CREATE TABLE `network_daily` (
`entity_id` int(11) NOT NULL default '0',
`buyer_entity_id` int(11) NOT NULL default '0',
`buyer_line_item_id` int(11) NOT NULL default '0',
`seller_entity_id` int(11) NOT NULL default '0',
`seller_line_item_id` int(11) NOT NULL default '0',
`size_id` int(11) NOT NULL default '0',
`pop_type_id` int(11) NOT NULL default '0',
`country_group_id` int(11) NOT NULL default '0',
`is_adjustment` tinyint(4) NOT NULL default '0',
`adv_learn_type` char(1) NOT NULL default '',
`pub_learn_type` char(1) NOT NULL default '',
`frequency` smallint(6) NOT NULL default '0',
`ymdh` datetime NOT NULL default '0000-00-00 00:00:00',
`imps` bigint(20) NOT NULL default '0',
`clicks` int(11) NOT NULL default '0',
`convs` int(11) NOT NULL default '0',
`id` int(10) unsigned NOT NULL default '0',
`checkpoint` int(11) default NULL,
PRIMARY KEY (`id`,`ymdh`),
KEY `ix_nsl_ymdh_buyerli` (`ymdh`,`buyer_line_item_id`),
KEY `ix_nsdl_ymdh_entity_buyer` (`ymdh`,`entity_id`,`buyer_entity_id`),
KEY `ix_nsdl_ymdh_entity_seller` (`ymdh`,`entity_id`,`seller_entity_id`)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(`ymdh`))
(
PARTITION p2005 VALUES LESS THAN (TO_DAYS('2006-01-01')),
PARTITION p200601 VALUES LESS THAN (TO_DAYS('2006-02-01')),
PARTITION P200602 VALUES LESS THAN (TO_DAYS('2006-03-01')),

...

PARTITION P200609 VALUES LESS THAN (TO_DAYS('2006-10-01')),
PARTITION P20061001 VALUES LESS THAN (TO_DAYS('2006-10-02')),
PARTITION P20061002 VALUES LESS THAN (TO_DAYS('2006-10-03')),
PARTITION P20061003 VALUES LESS THAN (TO_DAYS('2006-10-04')),

...

PARTITION P20061130 VALUES LESS THAN (TO_DAYS('2006-12-01'))
);

There are a lot of partitions that I have defined. I need to keep a rolling 60 days of daily partitions active. The plan is to use the ALTER TABLE REORGANIZE PARTITION statement to merge the older partitions together once per day, and to add a new partition once per day.

After the data was reloaded, it was time to test the performance, and the ability to add partitions and reorganize them (this was broken in version 5.1.11-beta).

Performance, surprisingly wasn't what I expected. Queries that ran on the partitions were about the same performance wise as those on the unpartitioned table. Hmm, I have to double check my results. I'll post all of the performance data.

The real good news is that the partition maintenance commands all worked with InnoDB! Dropping & Reorganizing partitions worked perfectly. I'll have to redo my testing and to see what happened to the performance.

12 comments:

gmax said...

Hi,
very intriguing.
I would like to see the data and the actual code you used to get your results.
Can you post them?

Thanks

Giuseppe

Partha Dutta said...

Giuseppe, I will post Part 4 with all of the details on the testing and how it was done. You'll find very interesting results.

Partha

Mikael Ronstrom said...

Hi Partha,
Very interesting to read.
I see no problems why you shouldn't
get a boost on performance when there
are queries involving ranges on
ymdh. However EXPLAIN PARTITIONS will
show you whether you actually get
a boost. Also remember that the first
query against the table will be fairly
slow given that it has to open all
partitions.

Mikael Ronstrom

Alexandru Toth said...

Hi,

There is a new Open Source OLAP project, called Cubulus OLAP. It is in early-alpha, none the less it can already show aggregated measures across hierarchical dimensions.

OLAP aggregation engine uses "hierarchical range-clustering of keys" (read "V. Markl, F.Ramsak, R. Bayer: Improving OLAP Performance by Multidimensional Hierarchical Clustering. Proc. of the Intl. Database Engineering and Applications Symposium, pp. 165-177, 1999") . Dr Volker Markl expressed in mail his consent to use the range clustering in an OSS project.
The main difference to the closest Open Source OLAP project - Mondrian a.k.a Pentaho, is the fact that Cubulus does all the aggregations inside the relational database (by generating multiple queries).
Even if there are multiple tables, there is no "star schema", no GROUP BY and no JOINS. Due to the "Multidimensional Hierarchical Clustering" all SQL statements are simple SELECT SUM(measure) with a range WHERE clause for each dimension. There is always a full-table scan, so indexes provide only little help.

But partitioning gives good performance boost, especially when partitioning over 3 dimensions.

Theoretical background for simple SELECTS with WHERE clauses can be derived from the article "Anastassia Ailamaki , David J. DeWitt , Mark D. Hill , David A. Wood, DBMSs on a Modern Processor: Where Does Time Go?, Proceedings of the 25th International Conference on Very Large Data Bases", p.266-277, September 07-10, 1999 . A C language case study is presented "Conjunctive Selection Conditions in Main Memory", Kenneth A. Ross, 2002 (?)

Cheers, Alex
http://sourceforge.net/projects/cubulus
alxtoth at users . sourceforge . net

Alexandru Toth said...

Hi, there is a brief presentation material at http://cubulus.sourceforge.net/

I think it is relevant in this forum because Cubulus uses MySQL partitions (hundreds of them) and parallel query.

Cheers, Alexandru

Harish said...

Hi,

I already have a huge database table which would benefit from partitition. I think so, as i expect this to avoid having full table scan.

By the way , no where i find how to alter a table to have partition. Please help me with a link if any.

Regards,

harish

ATField said...

runescape money
runescape gold
runescape money
runescape gold
buy runescape gold buy runescape money runescape items
runescape accounts
runescape gp
runescape money
runescape power leveling
runescape money
runescape gold
dofus kamas
cheap runescape money
cheap runescape gold
Guild Wars Gold
buy Guild Wars Gold
lotro gold
buy lotro gold
lotro gold
buy lotro gold
lotro gold
buy lotro gold

Hellgate Palladium
Hellgate London Palladium
Hellgate money
Tabula Rasa gold tabula rasa money
Tabula Rasa Credit
Tabula Rasa Credits
Hellgate gold
Hellgate London gold
wow power leveling
wow powerleveling
Warcraft PowerLeveling
Warcraft Power Leveling
World of Warcraft PowerLeveling World of Warcraft Power Leveling runescape power leveling
runescape powerleveling
eve isk
eve online isk
eve isk
eve online isk
tibia gold
Fiesta Silver
Fiesta Gold
Age of Conan Gold
buy Age of Conan Gold
aoc gold

呼吸机
无创呼吸机
家用呼吸机
呼吸机
家用呼吸机
美国呼吸机
篮球培训
篮球培训班
篮球夏令营
china tour
beijing tour
beijing travel
china tour
tibet tour
tibet travel
computer monitoring software
employee monitoring

Anonymous said...

wholesale jewelry
wholesale handmade jewelry
wholesale fashion jewelry
wholesale costume jewelry
handmade jewelry
fashion jewelry
costume jewelry
jewelry wholesale
wholesale pearl
wholesale crystal
discount jewelry
cheap jewelry
china jewelry wholesaler
wholesale china jewelry
handcrafted jewelry
wholesale jewellery
wholesale turquoise
wholesale swarovski
wholesale gemstone
wholesale coral
wholesale shell

Anonymous said...

看房子,買房子,建商自售,自售,台北新成屋,台北豪宅,新成屋,豪宅,美髮儀器,美髮,儀器,髮型,EMBA,MBA,學位,EMBA,專業認證,認證課程,博士學位,DBA,PHD,在職進修,碩士學位,推廣教育,DBA,進修課程,碩士學位,網路廣告,關鍵字廣告,關鍵字,廣告,課程介紹,學分班,文憑,牛樟芝,段木,牛樟菇,日式料理, 台北居酒屋,燒肉,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,台北結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,住宿,訂房,HOTEL,飯店,造型系列,學位,牛樟芝,腦磷脂,磷脂絲胺酸,SEO,婚宴,捷運,學區,美髮,儀器,髮型,牛樟芝,腦磷脂,磷脂絲胺酸,看房子,買房子,建商自售,自售,房子,捷運,學區,台北新成屋,台北豪宅,新成屋,豪宅,學位,碩士學位,進修,在職進修, 課程,教育,學位,證照,mba,文憑,學分班,網路廣告,關鍵字廣告,關鍵字,SEO,关键词,网络广告,关键词广告,SEO,关键词,网络广告,关键词广告,SEO,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,住宿,訂房,HOTEL,飯店,婚宴,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,住宿,訂房,HOTEL,飯店,婚宴,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,住宿,訂房,HOTEL,飯店,婚宴,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,居酒屋,燒烤,美髮,儀器,髮型,美髮,儀器,髮型,美髮,儀器,髮型,美髮,儀器,髮型,小套房,小套房,進修,在職進修,留學,證照

Anonymous said...

看房子,買房子,建商自售,自售,台北新成屋,台北豪宅,新成屋,豪宅,美髮儀器,美髮,儀器,髮型,EMBA,MBA,學位,EMBA,專業認證,認證課程,博士學位,DBA,PHD,在職進修,碩士學位,推廣教育,DBA,進修課程,碩士學位,網路廣告,關鍵字廣告,關鍵字,廣告,課程介紹,學分班,文憑,牛樟芝,段木,牛樟菇,日式料理, 台北居酒屋,燒肉,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,台北結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,住宿,訂房,HOTEL,飯店,造型系列,學位,牛樟芝,腦磷脂,磷脂絲胺酸,SEO,婚宴,捷運,學區,美髮,儀器,髮型,牛樟芝,腦磷脂,磷脂絲胺酸,看房子,買房子,建商自售,自售,房子,捷運,學區,台北新成屋,台北豪宅,新成屋,豪宅,學位,碩士學位,進修,在職進修, 課程,教育,學位,證照,mba,文憑,學分班,網路廣告,關鍵字廣告,關鍵字,SEO,关键词,网络广告,关键词广告,SEO,关键词,网络广告,关键词广告,SEO,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,住宿,訂房,HOTEL,飯店,婚宴,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,住宿,訂房,HOTEL,飯店,婚宴,台北住宿,國內訂房,台北HOTEL,台北婚宴,飯店優惠,住宿,訂房,HOTEL,飯店,婚宴,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,台北結婚,婚宴場地,推車飲茶,港式點心,尾牙春酒,居酒屋,燒烤,美髮,儀器,髮型,美髮,儀器,髮型,美髮,儀器,髮型,美髮,儀器,髮型,小套房,小套房,進修,在職進修,留學,證照,MBA,EMBA,留學,MBA,EMBA,留學,進修,在職進修,牛樟芝,段木,牛樟菇,住宿,民宿,飯宿,旅遊,住宿,民宿,飯宿,旅遊,住宿,民宿,飯宿,旅遊,住宿,民宿,飯宿,旅遊,住宿,民宿,飯宿,旅遊,住宿,民宿,飯宿,旅遊,住宿,民宿,飯宿,旅遊,美容,美髮,整形,造型,美容,美髮,整形,造型,美容,美髮,整形,造型,美容,美髮,整形,造型,美容,美髮,整形,造型,美容,美髮,整形,造型,美容,美髮,整形,造型,設計,室內設計,裝潢,房地產,設計,室內設計,裝潢,房地產,設計,室內設計,裝潢,房地產,設計,室內設計,裝潢,房地產,設計,室內設計,裝潢,房地產,設計,室內設計,裝潢,房地產,設計,室內設計,裝潢,房地產,設計,室內設計,裝潢,房地產,進修,在職進修,MBA,EMBA,進修,在職進修,MBA,EMBA,進修,在職進修,MBA,EMBA,進修,在職進修,MBA,EMBA,進修,在職進修,MBA,EMBA,進修,在職進修,MBA,EMBA,進修,在職進修,MBA,EMBA,住宿,民宿,飯店,旅遊,美容,美髮,整形,造型,設計,室內設計,裝潢,房地產,進修,在職進修,MBA,EMBA,關鍵字排名,網路行銷,关键词排名,网络营销,網路行銷,關鍵字排名,关键词排名,网络营销,羅志祥,周杰倫,五月天,蔡依林,林志玲,羅志祥,周杰倫,五月天,蔡依林,林志玲,PMP,在職專班,研究所在職專班,碩士在職專班,PMP,證照,在職專班,研究所在職專班,碩士在職專班

huangtiao said...

You do not need any skills and more necessary rohan crone for a regular attack, but it still looks like you are using a skill. The armors and weapons which we can use our own rohan gold to buy our favorite is looking very nice. In some places you can not understand what you are doing and sometimes you did not know why and where need to spend the expensive and more rohan online crone. You can bring your own rohan money to buy Still Scroll from shop and use it on someone who you think might use bots in game. If he is confirmed to use bots later, you will get 3 equipments and some rohan online gold from him.
You need to go through long distances sometimes, making you buy a lot of Teleport scrolls with your Rose zuly just to go to the cities. The quests are OK and have a good storyline so far, and you will gain a good amount of experience by grinding and rose zulie too. Something I liked in this game was the nice design of the clothes can have from rose online zuly. There also armor also can gain from rose online zulie sets that can be wore by any class. Another nice thing that I would like to talk about, is that you can have nice Arua ROSE zuly in ROSE Online.

Anonymous said...

So to have a good attack, you should level your skill in cabal online alz. Also, get the highest level jewelry that you can equip in thecabal alz. This step depends on your skill rank, for lower magic ranks get 4 fast notice arrow skills and your cabal gold. Of course, if you have more cabal money, you can get more in the game. By the way, you can buy pet from other players in buy cabal alz.
Many people play the game maybe feel that they were too poor, some of my friend often ask, why you play games so there is ghost gen.
Say good pull so much I do not pull hedge with the US to talk about the game experience to make ghost online gen. I want to see if there is nothing worth ghost gold things and a very low price. The most important thing is you have ghost online gold. Also we have a good time happy in the game and hope earned more ghost money in the game.