Əsas səhifə > MySQL administration, Performance tests and tips > Reclaim space after delete operation

Reclaim space after delete operation

Bu yaxınlarda maraqlı hadisəyə rast gəlmişəm…Məsələnin ümumi məğzi bundan ibarətdir:
~
Hər hansı cədvəldən məlumat silindikdən sonra onun tutduğu yer (hdd olaraq) silinmir.
Yəni, 250 MB-lik cədvəlin yarısını sildikdə məntiqlə düşündükdə yaddaş azalmalı idi, lakin azalmır.
~

Cədvəlimizin həcmi 252M:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales2.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 00:26 sales2.ibd

İçindəki məlumat sayı:

mysql> select count(*) from sales2;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (0.50 sec)

cədvəl strukturu:

CREATE TABLE `sales2` (
  `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=2500004 DEFAULT CHARSET=latin1

Cədvəldən sales_id-si 1000000-dan böyük olan yəni, 1.500.000 recordu silək:

mysql> delete from sales2 where sales_id>1000000;
Query OK, 1500003 rows affected (1 min 29.56 sec)

Cədvəl həcmimiz dəyişmədi 252 MB:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales2.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 00:39 sales2.ibd

Ümumi izahı:
When you remove rows, they are just marked as deleted on disk but space will be consumed by InnoDB files which can be re-used later when you insert/update more rows but it will never shrink.

Test edək:

mysql> select count(*) from sales3;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (41.34 sec)

Cədvəlimizin həcmi:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 00:31 sales3.ibd

Cədvəldə olan məlumatları silək və eyni qədərini təzədən insert edək:

mysql> delete from sales3 where sales_id>1000000;
Query OK, 1500003 rows affected (2 min 7.22 sec)

mysql> insert into sales3 select * from sales where sales_id>1000000;
Query OK, 1500003 rows affected (3 min 7.98 sec)
Records: 1500003  Duplicates: 0  Warnings: 0

mysql> select count(*) from sales3;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (0.48 sec)

Və həcmə baxırıq:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 01:27 sales3.ibd

Bəli həqiqətən də space sonrakı istifadə üçün saxlanılır.

Gəlin Truncate-ə baxaq. Truncate internal olaraq cədvəli drop edir, daha sonra da təzədən yaradır və bu da o deməkdir ki, tutulmuş yaddaş avtomatik boşalır:

mysql> truncate table sales2;
Query OK, 0 rows affected (0.81 sec)

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales2.ibd
-rw-rw---- 1 mysql mysql 112K Sep 28 01:45 sales2.ibd

Bəs deyək ki, biz cədvəldə olan məlumatları yaddaş boşalsın deyə silirik onda necə?(qeyd edək ki, system reboot-dan sonra belə, yaddaş boşalmır).
Truncate məntiqindən belə çıxdı ki,əgər mən cədvəli reorganize etsəm tutlmuş yaddaş boşaldılacaq. Cədvəli reorganize etməyin isə əsas 2 yolu mövcuddur (İnnoDB-dən gedir söhbət)
1. optimize table
2. “empty alter”

Gəlin hər 2 üsulu test edək:

mysql> delete from sales3 where sales_id>1000000;
Query OK, 1500003 rows affected (1 min 22.59 sec)

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql 252M Sep 28 01:52 sales3.ibd

Reorginizing:

mysql> optimize table sales3;
+-----------------+----------+----------+-------------------------------------------------------------------+
| Table           | Op       | Msg_type | Msg_text                                                          |
+-----------------+----------+----------+-------------------------------------------------------------------+
| mysqlspp.sales3 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| mysqlspp.sales3 | optimize | status   | OK                                                                |
+-----------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (1 min 31.52 sec)

Diqqət!
optimize table statement temporary cədvəl yaradaraq məlumatları oraya kopyalayır.
Bu da full table lock ilə müsaiyət olunur. Dolayısı ilə siz öz cədvəlinizə optimize table zamanı çata bilməyəcəksiniz. Bundan əlavə əgər sizdə tmp yaddaş kifayət qədər deyilsə out-of-memory də yaşaya bilərsiniz. Sübut:

mysql> show processlist;
+----+------+-----------+----------+---------+------+-------------------+-----------------------+
| Id | User | Host      | db       | Command | Time | State             | Info                  |
+----+------+-----------+----------+---------+------+-------------------+-----------------------+
|  5 | root | localhost | mysqlspp | Query   |   89 | copy to tmp table | optimize table sales3 |
|  6 | root | localhost | mysqlspp | Query   |    0 | init              | show processlist      |
+----+------+-----------+----------+---------+------+-------------------+-----------------------+
2 rows in set (0.00 sec)

Test edirik və görürük ki, space boşaldıldı 96 MB:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql  96M Sep 28 01:55 sales3.ibd

İkinci üsul “empty alter” dediyimiz üsuldur ki, onsuz da İnnoDB olan cədvəli yeniden alter edib engine İnnoDB versək, bu nəticədə eyni cədvəli verəcək bizə, lakin reorganize edilmiş şəkildə:

mysql> delete from sales3 where sales_id>1000000;
Query OK, 1500003 rows affected (51.99 sec)

mysql> alter table sales3 engine=innodb;
Query OK, 1000000 rows affected (2 min 42.82 sec)
Records: 1000000  Duplicates: 0  Warnings: 0

Dərhal yoxlayırıq və 96 M:

root@sh-v3:/var/lib/mysql/mysqlspp# ll -h | grep sales3.ibd
-rw-rw---- 1 mysql mysql  96M Sep 28 02:16 sales3.ibd

Bəli hər 2 üsul işləyir. 3-cü bir üsul da ağlıma gəlib ki, əgər cədvəldən müəyyən məlumatları silmək istəyiriksə, silib daha sonra reorganize etmek evezine:
1. eyni strukturda yeni boş cədvəl yaradırıq (table1)
2. məlumat silinməsi baş verəcək cədvəldən (table2) silinmədən sonra qalacaq məlumatları silinmədən əvvəl boş table1-imizə insert edirik.
3. insert bitdikdən sonra table2-ni drop edirik
4. table1-i rename edirik table2-yə.

Bu üsul da teorik olaraq işləməlidir.
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: