Əsas səhifə > MySQL administration > ALTER TABLE zamanı lock və performance problemi

ALTER TABLE zamanı lock və performance problemi

Bu mövzumuz çoxlarımızın istifadə etdiyi ALTER TABLE komandasının maraqlı və eyni zamanda ziyanlı xüsusiyyətindən danışacıq. Təbii ki bu kiçik ölçülü cədvəllərdə bir çətinlik yaratmır amma olduqca böyük cədvəllər üçün baş ağrısı ola bilər. Hətta bir forumda oxuduğuma görə 1 gün və daha artıq davam edən ALTER var imiş…

ALTER TABLE nə edir? Bu komandanın iş prinsipi belədir. Məsələn bizdə table A var. A ibarətdir 3 sütundan və 100 row-dan. indi biz A-ya index əlavə etmək istəyirik(məsələn: alter table A add index(id)).
Bu zaman MySQL
*) A-nı lock edəcək(yəni A-ya heç bir insert,update,select getmiyəcək).
*) A-ya bənzər boş bir table yaradacaq (təbii ki A adlı).
*) A-da olan bütün məlumatları (indiki halda 100 row-nu) az öncə yaratdığı boş table-a copy\insert edəcək.
*) bütün insert-ləri bitirdikdən sonra köhnə A-ni drop edəcək (siləcək)

Qısa olaraq Alter bunu edir. Təbii ki bu 40-50 milyon row-luq cədvəllərdə dəhşətli bir vaxt aparacaq. Ən pis yanı da odur ki, əgər Alter 1 gün davam etməli olsa onda belə çıxır ki sizin table 1 gün select\update\insert -lərə bağlı olacaq.

İnanmıyanlara əyani olaraq göstərək…Sınaq üçün mənim əlimdə olan 2.5 milyon row-luq table olan sales-dən istifadə edəcik. Sales dump faylını burdan yükləyə bilərsiniz Sales table dump

Sales table-ı yaratdıqdan sonra və məlumatları insert etdikdən sonra original sales-ə dəyməmək məqsədilə. sales_test1 cədvəli yaradırıq.

Original sales strukturumuz budur:

CREATE TABLE `sales` (
  `SALES_ID` int(8) NOT NULL AUTO_INCREMENT,
  `CUSTOMER_ID` decimal(8,0) NOT NULL,
  `PRODUCT_ID` decimal(8,0) NOT NULL,
  `SALE_DATE` datetime NOT NULL,
  `QUANTITY` decimal(8,0) NOT NULL,
  `SALE_VALUE` decimal(8,0) NOT NULL,
  `DEPARTMENT_ID` decimal(8,0) DEFAULT '0',
  `SALES_REP_ID` decimal(8,0) DEFAULT '0',
  `GST_FLAG` decimal(8,0) DEFAULT NULL,
  `sale_status` char(1) DEFAULT NULL,
  `FREE_SHIPPING` char(1) DEFAULT '',
  `DISCOUNT` decimal(8,0) unsigned DEFAULT '0',
  PRIMARY KEY (`SALES_ID`),
  KEY `sales_cust_idx` (`CUSTOMER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2500005 DEFAULT CHARSET=latin1

Yeni sales_test1 cədvəlini yaradaq:

mysql> create table sales_test1 as select * from mysqlspp.sales;
Query OK, 2500004 rows affected (1 min 11.30 sec)
Records: 2500004  Duplicates: 0  Warnings: 0

sales_test1 cədvəl strukturumuz bu cürdür:

CREATE TABLE `sales_test1` (
  `SALES_ID` int(8) NOT NULL DEFAULT '0',
  `CUSTOMER_ID` decimal(8,0) NOT NULL,
  `PRODUCT_ID` decimal(8,0) NOT NULL,
  `SALE_DATE` datetime NOT NULL,
  `QUANTITY` decimal(8,0) NOT NULL,
  `SALE_VALUE` decimal(8,0) NOT NULL,
  `DEPARTMENT_ID` decimal(8,0) DEFAULT '0',
  `SALES_REP_ID` decimal(8,0) DEFAULT '0',
  `GST_FLAG` decimal(8,0) DEFAULT NULL,
  `sale_status` char(1) DEFAULT NULL,
  `FREE_SHIPPING` char(1) DEFAULT '',
  `DISCOUNT` decimal(8,0) unsigned DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |

Gördüyümüz kimi bizim yeni cədvəlimiz olan sales_test1-də original sales cədvəlində olan 2 məlumat yoxdur
PRIMARY KEY (`SALES_ID`) və KEY `sales_cust_idx` (`CUSTOMER_ID`).
Bunları əlavə etmək lazımdır ki, ALTER TABLE bayaqdan elə öz vaxtını gözləyir😛
Bəli primary key əlavə edək:

mysql> alter table sales_test1 add primary key(sales_id);
Query OK, 0 rows affected (1 min 27.46 sec)
Records: 0  Duplicates: 0  Warnings: 0

İşləmə vaxtı 1 min 27.46 sec
Alter-lə eyni vaxtda bir dənə select versək:

mysql> select * from sales_test1 where sales_id=1;
.
.
.
1 row in set (1 min 28.44 sec)

İşləmə vaxtı 1 min 28.44 sec
Gördüyümüz kimi bizim select işləməyə başlamaq üçün Alter-in bitməyini gözlədi.Yəni həqiqətən də Alter bizim table-ı lock etdi.

Sözsüz ki, bu arzu olunan hal deyil. Yəni bizə elə bir şey lazımdır ki, Alter zamanı digər istifadəçilər rahatlıqla öz update,insert və select-lərini verə bilsinlər.
Bunun üçün pt-online-schema-change tool-u mövcuddur. Bu cür tool-ların əsas məntiqi ondan ibarətdir ki, lock etmədən alter etmək.
pt-online-schema-change Percona Toolkit-in tərkibinə daxildir.
Daha ətraflı: pt-online-schema-change
Buna oxşar 3 əlavə tool var buyurun baxın:
1. Facebook Online Schema Change for MySQL
2. Large Hadron Migrator
3. oak-online-alter-table

Mən Percona Toolkit-dən istifadə edəcəm. İni isə pt-online-schema-change necə gözəl bir tool olduğuna baxin:

[root@sh ~]# pt-online-schema-change --progress time,1 --execute --user root --password 12345 --alter "add index(customer_id)" D=my_new_db,t=sales_test1
Altering `my_new_db`.`sales_test1`...
Creating new table...
Created new table my_new_db._sales_test1_new OK.
Altering new table...
Altered `my_new_db`.`_sales_test1_new` OK.
Creating triggers...
Created triggers OK.
Copying approximately 2500273 rows...
Copying `my_new_db`.`sales_test1`:   1% 00:58 remain
Copying `my_new_db`.`sales_test1`:   2% 01:13 remain
Copying `my_new_db`.`sales_test1`:   3% 01:13 remain
Copying `my_new_db`.`sales_test1`:   4% 01:24 remain
.
.
.
.
Copying `my_new_db`.`sales_test1`:  99% 00:02 remain
Copying `my_new_db`.`sales_test1`:  99% 00:01 remain
Copying `my_new_db`.`sales_test1`:  99% 00:01 remain
Copying `my_new_db`.`sales_test1`:  99% 00:00 remain
Copied rows OK.
Swapping tables...
Swapped original and new tables OK.
Dropping old table...
Dropped old table `my_new_db`.`_sales_test1_old` OK.
Dropping triggers...
Dropped triggers OK.
Successfully altered `my_new_db`.`sales_test1`.

Bəli məncə aydın olur hər şey. Bu tool-un vasitəsilə ALTER TABLE-ın nələr etdiyini incə-incə izləmək olur…
Və həmçinin eyni zamanda verdiyimiz select-ə baxın:

mysql> select * from sales_test1 where sales_id=5;
.
.
.
1 row in set (0.00 sec)

Gördüyümüz kimi heç bir lock baş vermədi…Məhz buna görə də Select-in müddəti 0.00 sec

MySQL-də Transaction və Lock haqqında daha ətraflı: Transactions and Locks

Ümidvaram ki, faydalı oldu… Təşəkkürlər🙂

  1. Hələlik heç bir şərh yoxdur
  1. No trackbacks yet.

Bir cavab yazın

Sistemə daxil olmaq üçün məlumatlarınızı daxil edin və ya ikonlardan birinə tıklayın:

WordPress.com Loqosu

WordPress.com hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Twitter rəsmi

Twitter hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Facebook fotosu

Facebook hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

Google+ foto

Google+ hesabınızdan istifadə edərək şərh edirsinz. Çıxış / Dəyişdir )

%s qoşulma

%d bloqqer bunu bəyənir: