alter table opitemrece engine = innodb partition by range columns (vstdate) (partition yr_2000 values less than ('2001-01-01'), partition yr_2001 values less than ('2002-01-01'), partition yr_2002 values less than ('2003-01-01'), partition yr_2003 values less than ('2004-01-01'), partition yr_2004 values less than ('2005-01-01'), partition yr_2005 values less than ('2006-01-01'), partition yr_2006 values less than ('2007-01-01'), partition yr_2007 values less than ('2008-01-01'), partition yr_2008 values less than ('2009-01-01'), partition yr_2009 values less than ('2010-01-01'), partition yr_2010 values less than ('2011-01-01'), partition yr_current_month values less than ('2011-01-31') , partition yr_2011 values less than ('2012-01-01') , partition yr_other values less than (maxvalue) );
หัวข้อ: Re: High performance with partition table
เริ่มหัวข้อโดย: draran ที่ กุมภาพันธ์ 20, 2007, 05:20:19 AM
Mysql 5.0 ใช้ได้ไหมครับ ช่วยทำให้การใช้งานเร็วขึ้นมากไหม และมีข้อจำกัดอะไรบ้าง
หัวข้อ: Re: High performance with partition table
เริ่มหัวข้อโดย: manoi ที่ กุมภาพันธ์ 20, 2007, 06:07:25 AM
หัวข้อ: Re: High performance with partition table
เริ่มหัวข้อโดย: ธีรเวทย์ สิริสุวรรณกิจ ที่ กุมภาพันธ์ 20, 2014, 11:28:38 AM
ลองแล้วครับ 1. การ ซอยถี่ๆ มากเกินไป จะทำให้ช้าลง มากๆ เลยครับ หรือว่า ต้องเพิ่มค่า config ให้รองรับ Partition ที่มากขึ้นด้วยก็ไม่รู้ครับ 2. การ upgrade version ของ HosXP จะมีการกวาดล้าง PRIMARY KEY และ UNIQUE INDEX ที่ถูกปรับแต่งแก้ไขใหม่หมด ซึ่งจะทำให้ Partition table ที่ถูกสร้างไว้ ด้วย PRIMARY KEY ที่เราได้ทำการปรับแต่ง หายหมดครับ 3. ทางเลือกที่ดีที่สุด ในการ Partition table สำหรับ HosXP ก็คือ ใช้ PRIMARY KEY ที่มีอยู่แล้ว ซึ่ง จากการทดสอบหลายรอบ พบว่า การ upgrade version ของ HosXP ไม่มีผลกระทบใดๆ กับ Partition table ที่ถูกสร้างขึ้นเลย 4. ผมแบ่ง opitemrece 16 ส่วน ด้วยคำสั่งนี้ กำลังทดสอบอยู่ครับ ผ่านไป 1 สัปดาห์ ก็ยังไม่รู้สึกอะไรนะครับ ALTER TABLE opitemrece remove partitioning; ALTER TABLE opitemrece partition by range columns (hos_guid) ( partition p1 values less than ('{10000000-0000-0000-0000-000000000000}') , partition p2 values less than ('{20000000-0000-0000-0000-000000000000}') , partition p3 values less than ('{30000000-0000-0000-0000-000000000000}') , partition p4 values less than ('{40000000-0000-0000-0000-000000000000}') , partition p5 values less than ('{50000000-0000-0000-0000-000000000000}') , partition p6 values less than ('{60000000-0000-0000-0000-000000000000}') , partition p7 values less than ('{70000000-0000-0000-0000-000000000000}') , partition p8 values less than ('{80000000-0000-0000-0000-000000000000}') , partition p9 values less than ('{90000000-0000-0000-0000-000000000000}') , partition pa values less than ('{a0000000-0000-0000-0000-000000000000}') , partition pb values less than ('{b0000000-0000-0000-0000-000000000000}') , partition pc values less than ('{c0000000-0000-0000-0000-000000000000}') , partition pd values less than ('{d0000000-0000-0000-0000-000000000000}') , partition pe values less than ('{e0000000-0000-0000-0000-000000000000}') , partition pf values less than ('{f0000000-0000-0000-0000-000000000000}') , partition pz values less than maxvalue );
ผมมีคำสั่งในตารางอื่นอีก จะทยอยลงให้นะครับ
หัวข้อ: Re: High performance with partition table
เริ่มหัวข้อโดย: ธีรเวทย์ สิริสุวรรณกิจ ที่ กุมภาพันธ์ 20, 2014, 11:39:05 AM
ALTER TABLE opitemrece_log remove partitioning; ALTER TABLE opitemrece_log partition by range (opitemrece_log_id) ( partition p07 values less than (700000) , partition p08 values less than (800000) , partition p09 values less than (900000) , partition p10 values less than (1000000) , partition p11 values less than (1100000) , partition p12 values less than (1200000) , partition p13 values less than (1300000) , partition p14 values less than (1400000) , partition p15 values less than (1500000) , partition p99 values less than maxvalue );
ALTER TABLE opitemrece_sticker_stat remove partitioning; ALTER TABLE opitemrece_sticker_stat partition by range columns (hos_guid) ( partition p1 values less than ('{10000000-0000-0000-0000-000000000000}') , partition p2 values less than ('{20000000-0000-0000-0000-000000000000}') , partition p3 values less than ('{30000000-0000-0000-0000-000000000000}') , partition p4 values less than ('{40000000-0000-0000-0000-000000000000}') , partition p5 values less than ('{50000000-0000-0000-0000-000000000000}') , partition p6 values less than ('{60000000-0000-0000-0000-000000000000}') , partition p7 values less than ('{70000000-0000-0000-0000-000000000000}') , partition p8 values less than ('{80000000-0000-0000-0000-000000000000}') , partition p9 values less than ('{90000000-0000-0000-0000-000000000000}') , partition pa values less than ('{a0000000-0000-0000-0000-000000000000}') , partition pb values less than ('{b0000000-0000-0000-0000-000000000000}') , partition pc values less than ('{c0000000-0000-0000-0000-000000000000}') , partition pd values less than ('{d0000000-0000-0000-0000-000000000000}') , partition pe values less than ('{e0000000-0000-0000-0000-000000000000}') , partition pf values less than ('{f0000000-0000-0000-0000-000000000000}') , partition pz values less than maxvalue );
ALTER TABLE opitemrece_summary remove partitioning; ALTER TABLE opitemrece_summary partition by range columns (hos_guid) ( partition p1 values less than ('{10000000-0000-0000-0000-000000000000}') , partition p2 values less than ('{20000000-0000-0000-0000-000000000000}') , partition p3 values less than ('{30000000-0000-0000-0000-000000000000}') , partition p4 values less than ('{40000000-0000-0000-0000-000000000000}') , partition p5 values less than ('{50000000-0000-0000-0000-000000000000}') , partition p6 values less than ('{60000000-0000-0000-0000-000000000000}') , partition p7 values less than ('{70000000-0000-0000-0000-000000000000}') , partition p8 values less than ('{80000000-0000-0000-0000-000000000000}') , partition p9 values less than ('{90000000-0000-0000-0000-000000000000}') , partition pa values less than ('{a0000000-0000-0000-0000-000000000000}') , partition pb values less than ('{b0000000-0000-0000-0000-000000000000}') , partition pc values less than ('{c0000000-0000-0000-0000-000000000000}') , partition pd values less than ('{d0000000-0000-0000-0000-000000000000}') , partition pe values less than ('{e0000000-0000-0000-0000-000000000000}') , partition pf values less than ('{f0000000-0000-0000-0000-000000000000}') , partition pz values less than maxvalue );
ALTER TABLE screen_doctor remove partitioning; ALTER TABLE screen_doctor partition by range columns (vn) ( partition p52 values less than ('520000000000') , partition p53 values less than ('530000000000') , partition p54 values less than ('540000000000') , partition p55 values less than ('550000000000') , partition p56 values less than ('560000000000') , partition p57 values less than ('570000000000') , partition p58 values less than ('580000000000') , partition p59 values less than ('590000000000') , partition p99 values less than maxvalue );
ALTER TABLE er_image remove partitioning; ALTER TABLE er_image partition by range columns (vn) ( partition p52 values less than ('520000000000') , partition p53 values less than ('530000000000') , partition p54 values less than ('540000000000') , partition p55 values less than ('550000000000') , partition p56 values less than ('560000000000') , partition p57 values less than ('570000000000') , partition p58 values less than ('580000000000') , partition p59 values less than ('590000000000') , partition p99 values less than maxvalue );
ALTER TABLE hipdata remove partitioning; ALTER TABLE hipdata partition by range columns (cid) ( partition p1 values less than ('1000000000000') , partition p2 values less than ('2000000000000') , partition p3 values less than ('3000000000000') , partition p4 values less than ('4000000000000') , partition p5 values less than ('5000000000000') , partition p6 values less than ('6000000000000') , partition p7 values less than ('7000000000000') , partition p8 values less than ('8000000000000') , partition p9 values less than ('9000000000000') , partition pz values less than maxvalue );
ALTER TABLE incoth remove partitioning; ALTER TABLE incoth partition by range columns (vn) ( partition p52 values less than ('520000000000') , partition p53 values less than ('530000000000') , partition p54 values less than ('540000000000') , partition p55 values less than ('550000000000') , partition p56 values less than ('560000000000') , partition p57 values less than ('570000000000') , partition p58 values less than ('580000000000') , partition p59 values less than ('590000000000') , partition p99 values less than maxvalue );
ALTER TABLE ipt_nurse_oper remove partitioning; ALTER TABLE ipt_nurse_oper partition by range (nurse_oper_id) ( partition p04 values less than (400000) , partition p06 values less than (600000) , partition p08 values less than (800000) , partition p10 values less than (1000000) , partition p12 values less than (1200000) , partition p14 values less than (1400000) , partition p99 values less than maxvalue );
ALTER TABLE ksklog remove partitioning; ALTER TABLE ksklog partition by range (ksklog_id) ( partition p31 values less than (3100000) , partition p32 values less than (3200000) , partition p33 values less than (3300000) , partition p34 values less than (3400000) , partition p35 values less than (3500000) , partition p36 values less than (3600000) , partition p37 values less than (3700000) , partition p38 values less than (3800000) , partition p39 values less than (3900000) , partition p40 values less than (4000000) , partition p99 values less than maxvalue );
ALTER TABLE lab_head remove partitioning; ALTER TABLE lab_head partition by range (lab_order_number) ( partition p10 values less than (100000) , partition p15 values less than (150000) , partition p20 values less than (200000) , partition p25 values less than (250000) , partition p30 values less than (300000) , partition p35 values less than (350000) , partition p40 values less than (400000) , partition p99 values less than maxvalue ); ALTER TABLE lab_order remove partitioning;
ALTER TABLE lab_order partition by range (lab_order_number) ( partition p10 values less than (100000) , partition p15 values less than (150000) , partition p20 values less than (200000) , partition p25 values less than (250000) , partition p30 values less than (300000) , partition p35 values less than (350000) , partition p40 values less than (400000) , partition p99 values less than maxvalue );
ALTER TABLE provis_card remove partitioning; ALTER TABLE provis_card partition by range (provis_card_id) ( partition p05 values less than (500000) , partition p10 values less than (1000000) , partition p15 values less than (1500000) , partition p20 values less than (2000000) , partition p25 values less than (2500000) , partition p30 values less than (3000000) , partition p35 values less than (3500000) , partition p40 values less than (4000000) , partition p99 values less than maxvalue );
ALTER TABLE provis_person remove partitioning; ALTER TABLE provis_person partition by range (provis_person_id) ( partition p05 values less than (500000) , partition p10 values less than (1000000) , partition p15 values less than (1500000) , partition p20 values less than (2000000) , partition p25 values less than (2500000) , partition p30 values less than (3000000) , partition p35 values less than (3500000) , partition p40 values less than (4000000) , partition p99 values less than maxvalue );
ALTER TABLE vital_sign_data remove partitioning; ALTER TABLE vital_sign_data partition by range (vital_sign_data_id) ( partition p05 values less than (500000) , partition p10 values less than (1000000) , partition p15 values less than (1500000) , partition p20 values less than (2000000) , partition p25 values less than (2500000) , partition p30 values less than (3000000) , partition p35 values less than (3500000) , partition p40 values less than (4000000) , partition p99 values less than maxvalue );
ALTER TABLE vn_stat_log remove partitioning; ALTER TABLE vn_stat_log partition by range (vn_stat_log_id) ( partition p01 values less than (100000) , partition p02 values less than (200000) , partition p03 values less than (300000) , partition p04 values less than (400000) , partition p05 values less than (500000) , partition p06 values less than (600000) , partition p07 values less than (700000) , partition p08 values less than (800000) , partition p99 values less than maxvalue );
หัวข้อ: Re: High performance with partition table
เริ่มหัวข้อโดย: ธีรเวทย์ สิริสุวรรณกิจ ที่ กุมภาพันธ์ 20, 2014, 13:15:02 PM
ALTER TABLE ovstdiag remove partitioning; ALTER TABLE ovstdiag DROP PRIMARY KEY , ADD PRIMARY KEY (ovst_diag_id,vn); ALTER TABLE ovstdiag partition by range columns (vn) ( partition p52 values less than ('520000000000') , partition p53 values less than ('530000000000') , partition p54 values less than ('540000000000') , partition p55 values less than ('550000000000') , partition p56 values less than ('560000000000') , partition p57 values less than ('570000000000') , partition p58 values less than ('580000000000') , partition p59 values less than ('590000000000') , partition p99 values less than maxvalue );
ALTER TABLE patient remove partitioning; ALTER TABLE patient DROP PRIMARY KEY , ADD PRIMARY KEY (hos_guid,hn); ALTER TABLE patient partition by range columns (hn) ( partition p01 values less than ('10000') , partition p02 values less than ('20000') , partition p03 values less than ('30000') , partition p04 values less than ('40000') , partition p05 values less than ('50000') , partition p06 values less than ('60000') , partition p07 values less than ('70000') , partition p08 values less than ('80000') , partition p09 values less than ('90000') , partition p99 values less than maxvalue );