Archive

Archive for the ‘MySQL administration’ Category

Install MySQL from source with debugging option in Linux

Hörmətli oxucular, bugünkü mövzumuz aşkar edilmiş hər hansı problemi dərinləməsinə araşdırıb tapmaq və yaxud onu debug etməkdən ibarətdir.
Mövzuya biraz teoriya ilə başlamaqda fayda var.
İlk öncə MySQL üçün bəzi anlayişları irəli sürək:
1. MySQL Logging:
MySQL-də log-lamanın növləri aşağıdakılardır:

* General Log – bütün növ RDBMS fəaliyyətlərin hamısı (SQL-lərin hamısı) bu log faylında log-lanır. Production server-lərdə aktiv etmək məsləhət deyil. Səbəbi isə çox sadədir işlək database-də bu log həcmi qısa müddətdə bir neçə GB-yə çata bilər. General Log adətən 1-2 dəqiqəlik lazım olan aktivliyi scan etmək üçün istifadə olunur.

* Slow Quer Log – default olaraq 10 saniyədən daha çox işləyən sorğuların hamısı bu faylda log-lanır. Təbii ki, 10 saniyəni daha aşağı məsələn, 3 saniyə edib sizin database-inizdə 3 saniyədən çox işləyən, həmçinin index-siz çalışdırılan və.s sorğuları aşkarlaya bilərsiniz.

* Error Log – MySQL start olunandan bu yana baş vermiş bütün kritik dəyişikliklər və error-lar bu faylda qeyd olunur. Bizim üçün ən vacib məsələlərdən biri crash və yaxud BUG-la qarşılaşdıqda ilk edəcəyimiz iş bu fayla baxmaqdır. Çox böyük ehtimal ki, bu faylda stack trace qeyd olunub. Nümunə olaraq:

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
/usr/local/mysql/bin/mysqld(my_print_stacktrace+0x35)[0xab6ead]
/usr/local/mysql/bin/mysqld(handle_fatal_signal+0x404)[0x736ee0]
/lib64/libpthread.so.0[0x35df20f710]
/usr/local/mysql/bin/mysqld[0xd27e2e]
/usr/local/mysql/bin/mysqld[0xd1cc5e]
/usr/local/mysql/bin/mysqld[0xc5d5f6]
/usr/local/mysql/bin/mysqld[0xc5dca7]
/usr/local/mysql/bin/mysqld[0xc5e152]
/lib64/libpthread.so.0[0x35df2079d1]
/lib64/libc.so.6(clone+0x6d)[0x35deee8b5d]

Error Log-da verilən faydalı məlumata baxmayaraq, hər hansı BUG-ı report etdikdə MySQL(Oracle) -ın professional developer-lərinə (hansı ki MySQL-i onlar yazırlar) error-un məhz hansı kod faylının hansı funksiyasında baş verdiyi maraqlandırır. Səbəbi isə çox sadədir BUG-ı reproduce edə bilsinlər və də asanlıqla həmin kod hissəsində dəyişiklik edə bilsinlər. Bunun üçün isə sizdən Linux Admin-lərin çox gözəl bildiyi CORE DUMP tələb olunacaq.
Qeyd edək ki, core dump-ı yalnız və yalnız Debug aktivləşdirilmiş MySQL ilə generate edə biləcəksiniz. Debug mode heç bir halda hazır şəkildə, yüklənə bilən şəkildə (downloadable) saxlanılmır.
Burdan belə çıxır ki, biz MySQL-i source-dan özümüz yazmalı olacıq. CMAKE-ə isə DEBUG-ı aktiv etsin deyə, necə deyərlər bir əmr append edəcik.
Dependency-ləri install edirik:

[root@localhost ~]# yum groupinstall "Development Tools"
[root@localhost ~]# yum install cmake
[root@localhost ~]# yum install ncurses ncurses-devel
[root@localhost ~]# yum install wget

Daha sonra arxiv faylımızı download edirik:
mysql-5.6.19.tar.gz

Daha sonra da ardıclıllıqla:

1. [root@localhost ~]# wget http://dev.mysql.com/get/Downloads/MySQL-5.6/mysql-5.6.19.tar.gz
2. [root@localhost ~]# tar -xvf mysql-5.6.19.tar.gz
3. 
[root@localhost ~]# groupadd mysql
[root@localhost ~]# useradd -r -g mysql mysql
4. [root@localhost ~]# cd mysql-5.6.19
5. [root@localhost mysql-5.6.19]# cmake  -DWITH_DEBUG=1
6. [root@localhost mysql-5.6.19]# make
7. [root@localhost mysql-5.6.19]# make install

Install bitdikdən sonra:

8.  [root@localhost mysql-5.6.19]# cd /usr/local/mysql
9.  [root@localhost mysql]# chown -R mysql .
10. [root@localhost mysql]# chgrp -R mysql .
11. [root@localhost mysql]# scripts/mysql_install_db --user=mysql
2014-07-21 03:52:05 14280 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-07-21 03:52:05 7f6e8f793720 InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!
2014-07-21 03:52:05 7f6e8f793720 InnoDB: !!!!!!!! UNIV_SYNC_DEBUG switched on !!!!!!!!!

Fərqliliyə diqqət yetirin.
Daha sonra:

12. [root@localhost mysql]# chown -R root .
13. [root@localhost mysql]# chown -R mysql data
14. [root@localhost mysql]# bin/mysqld_safe --user=mysql &
[root@localhost mysql]# bin/mysqld_safe --user=mysql &
[1] 14525
[root@localhost mysql]# 140721 03:55:41 mysqld_safe Logging to '/var/log/mysqld.log'.
140721 03:55:41 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql

Gördüyünüz kimi artıq, debug MySQL start olundu və öz testlərinizə başlaya bilərsiniz.
Connect olduqda:

[root@localhost bin]# ./mysql -u root --socket=/usr/local/mysql/data/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.19-debug-log Source distribution

Server versiyasında fərqi dərhal görürük: 5.6.19-debug-log.

Deməli, biz artıq sizlərlə, DEBUG MySQL-i install etdik. İndi isə MySQL CORE DUMP-ın alınmasına baxacıq.
Bu məqsədlə biz, my.cnf (MySQL config faylı) faylında [mysqld] kataloqu altında yazırıq:
[mysqld]
core-file

Həmçinin faylın sonuna qeyd edirik:
[mysqld_safe]
core_file_size=unlimited

Bu şəkildə start olunmuş MySQL-də hər hansı crash baş verərsə aşağıdakına bənzər bir output görə bilərsiniz:

bin/mysqld_safe: line 166: 27247 Segmentation fault      (core dumped) 


(core dumped) –
yəni core file artıq yaradıldı.
Default olaraq core file-ı MySQL datadir-də yaradılır.

Sizlərlə MySQL-in source-dan debug option-la installına baxdıq. Həmçinin Core Dump-ın yaradılmasını da göstərdik.
Təşəkkürlər.

Copying Tablespaces to Another Server (Transportable Tablespaces)

May 17, 2014 2 şərh

Bugünkü mövzumuz MySQL 5.6 > versiyalar nəzərdə tutulub odur ki, test etməzdən əvvəl mütləq əmin olun ki, sizin MySQL versiyanlz müvafiqdir.
İndiki hald mən 5.6.17 versiya MySQL-i aşağıdakı opsiyalarla start etmişəm.
QEYD: Transportable Tablespaces-ə nail olmağınız üçün MySQL-i aşağıdakı dəyişənlərlə start etməlisiniz

[mysqld]
innodb_file_per_table          = 1
lower_case_table_names         = 1

Daha sonra test məqsədi ilə, bir kifayət qədər böyük cədvəl tapın.
Məndə artıq biri var təqribən 2.4 gb-dır.
O cədvəli rename edək:

[root@linuxsrv3 xxx]# ls -lthr
total 2,4G
-rw-rw----. 1 mysql mysql   65 2014-05-07 11:05 db.opt
-rw-rw----. 1 mysql mysql  20K 2014-05-12 12:07 test_table.frm
-rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table.ibd

Cədvəlimizi test_table-dan test_table2-yə rename edək.:

[root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table to xxx.test_table2'
Enter password: 

real	0m1.973s
user	0m0.002s
sys	0m0.004s

Bu cəmi 1.9 saniyə çəkdi.

Rename-in başqa istifadə yeri də var. Deyək ki, biz bir database-dən cədvəli digərinə move etmək istəyirik bu zaman cədvəlin backup-ını alıb, digər database-də import etməyə ehtiyac yoxdur, aşağıdakı şəkildə bunu edə bilərik:

[root@linuxsrv3 xxx]# time mysql -u root -p -e 'rename table xxx.test_table2 to test.test_table2'
Enter password: 

real	0m1.876s
user	0m0.005s
sys	0m0.003s

Və bizim cədvəlimiz artıq, test database-indədir:

[root@linuxsrv3 test]# ls -lthr
total 2,4G
-rw-rw----. 1 mysql mysql 8,4K 2014-05-12 11:32 ff.frm
-rw-rw----. 1 mysql mysql  96K 2014-05-12 11:33 ff.ibd
-rw-rw----. 1 mysql mysql   65 2014-05-12 11:34 db.opt
-rw-rw----. 1 mysql mysql  20K 2014-05-12 12:07 test_table2.frm
-rw-rw----. 1 mysql mysql 2,4G 2014-05-12 14:05 test_table2.ibd

Deməli gördüyünüz kimi, bir cədvəli bir database-dən digərinə move etməyin sürətli və asan yolunu göstərmiş olduq.
Lakin belə bir sual yaranır, bəs biz bir cədvəli digər server-dəki database-ə copy edə bilərik mi?
Transportable Tablespaces məhz burda bizim köməyimizə gəlir.
Bir daha xatırlatmaqda fayda var ki, buna nail olmanız üçün my.cnf faylında yazının əvvəlində göstərdiyim kimi, dəyişənləri qeyd edib, MySQL-i start etmək lazımdır.
Həmçinin, sizin remote server-də artıq eyni adlı database və eyni adlı (strukturlu) cədvəliniz olmalıdır.

Bizim serverlər aşağıdakılardır:

source server : linuxsrv3
Destination server : linuxsrv4

Destination server-də artıq eyni adlı database və cədvəlimiz var.

Destination server-də biz mövcud boş cədvəlimizin tablespace-ini discard edirik:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table test_table2 discard tablespace;
Query OK, 0 rows affected (0,05 sec)

Əgər yoxlasanız görərsiniz ki, .ibd faylı databse direktoriyamızda yoxdur.

[root@linuxsrv4 test]# ls
db.opt  test_table2.frm

Source serverimizdə:

mysql> use test;
Database changed
mysql> FLUSH TABLES test_table2 FOR EXPORT;
Query OK, 0 rows affected (0,00 sec)

.ibd faylını destination server-ə copy edirik:

[root@linuxsrv3 test]# scp test_table2.ibd root@192.168.1.88:/var/lib/mysql/test
root@192.168.1.88's password: 
test_table2.ibd                       100% 2444MB  15.4MB/s   02:39 

Source server-də:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0,00 sec)

destination server-də:

[root@linuxsrv4 test]# ls
db.opt  test_table2.frm  test_table2.ibd

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> alter table test_table2 IMPORT TABLESPACE;
ERROR 1146 (42S02): Table 'test.test_table2' doesn't exist

Əgər bu error-u gördünüzsə demək ki, owner:

[root@linuxsrv4 test]# ls -l
total 2502684
-rw-rw----. 1 mysql mysql         65 2014-05-12 15:03 db.opt
-rw-rw----. 1 mysql mysql      20166 2014-05-12 15:05 test_table2.frm
-rw-r-----. 1 root  root  2562719744 2014-05-12 15:16 test_table2.ibd

Owner-i mysql user-ə dəyişək:

[root@linuxsrv4 test]# chown mysql:mysql test_table2.ibd
[root@linuxsrv4 test]# ls -l
total 2502684
-rw-rw----. 1 mysql mysql         65 2014-05-12 15:03 db.opt
-rw-rw----. 1 mysql mysql      20166 2014-05-12 15:05 test_table2.frm
-rw-r-----. 1 mysql mysql 2562719744 2014-05-12 15:16 test_table2.ibd

İndi isə bir daha sınayaq:

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed

mysql> alter table test_table2 IMPORT TABLESPACE;
Query OK, 0 rows affected, 1 warning (2 min 29,38 sec)

And checking :

mysql> select idvisit from test_table2 order by idvisit desc limit 1;
+---------+
| idvisit |
+---------+
| 7016951 |
+---------+
1 row in set (0,04 sec)

Bununla da gördüyünüz kimi 2.4 gb-lik bizim cədvəl artıq başqa bir server-də eyni data-larla mövcuddur. Bu əməliyyat toplamda 2 dəqiqə çəkir.
Təşəkkürlər.

Overview of Physical Database Structure in MySQL

Oracle Physical Structure oxuyarkən MySQl ilə müqayisə etmək qərarına gəldim.
Ümumiyyətlə RDBMS-i müqayisəli şəkildə öyrənmək çox faydalıdır.
Bu yazımızda MySQL 5.6.15-in Fiziki data strukturu haqqında danışacıq.
Default MySQL installation-dan sonra Linux-da aşağıdakı struktur yaranır:
MySQL Installation Directory:

[root@linuxsrv1 ~]# which mysql
/usr/bin/mysql

MySQL Configuration File:

[root@linuxsrv1 ~]# locate my.cnf
/etc/my.cnf

MySQL Data Directory:

[root@linuxsrv1 ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

mysql> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.00 sec)

Bu 3 Directory-dən bizə lazım olan təbii ki, configuration faylı və Data Directory-dir. Biraz təriflərlə izah edək.
Data Directory — MySQL-in data file-larının saxladığı ərazi, direktoriya.
Data File-ın izahını mövzu irəlilədikcə verəcik.

Data Directory-yə gedək:

[root@linuxsrv1 ~]# cd /var/lib/mysql
.
.
[root@linuxsrv1 mysql]# ls -l
total 176144
-rw-rw----. 1 mysql mysql       56 Jan  4 02:53 auto.cnf
-rw-rw----. 1 mysql mysql 79691776 Feb  1 21:45 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Feb  1 21:45 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Jan  4 02:53 ib_logfile1
drwx------. 2 mysql mysql     4096 Jan  4 02:53 mysql
srwxrwxrwx. 1 mysql mysql        0 Feb  1 21:13 mysql.sock
drwx------. 2 mysql mysql     4096 Jan  4 02:53 performance_schema

1. auto.cnf — bu fayl MySQL installation zamanı avtomatik olaraq yaranır və daxilində server üçün Unique İD saxlayır.
[auto]
server-uuid=ecf27935-74c9-11e3-bc6a-080027f84458

2. mysql, performance_schema — bu iki database system database-ləridir, hər installation zamanı yaranır və System məlumatlarını özlərində saxlayırlar. Drop olunması qətiyyən.

3. ibdata1 — Tərcümə etmədən Dokumentasiyadan birbaşa bura paste edirəm:
A set of files with names such as ibdata1, ibdata2, and so on, that make up the InnoDB system tablespace. These files contain metadata about InnoDB tables, (the data dictionary), and the storage areas for the undo log, the change buffer, and the doublewrite buffer. They also can contain some or all of the table data also (depending on whether the file-per-table mode is in effect when each table is created). When the innodb_file_per_table option is enabled, data and indexes for newly created tables are stored in separate .ibd files rather than in the system tablespace.

4. ib_logfile0 və ib_logfile1 — Redo Log fayllar.


Ümumiləşdirmə N1,
ibdata1 faylı logical olaraq System Tablespace-idir. Həmçinin default MySQL configuration faylından istifadə etdikdə, bu fayl həm də cədvəl data və index-lərini saxlayır. Dolayısı ilə ibdata1 data file-dır.

Ümumiləşdirmə N2,
Yuxarıdakı tərifə əsasən default MySQL configuration ilə işlədikdə, bütün cədvəllər System Tablespace-də saxlanılır.

Bütün danışdıqlarımıza əsasən Yeri gəlmişkən, System Tablespace haqqında izah verək.

System Tablespace — Data File-ların toplusudur ki (ibdata1, ibdata2, ibdata3 və.s), özündə saxlayır:
Data Dictionary,
Undo Log,
Change Buffer,
Doublewrite Buffer.

Bütün bunları yadda saxladıqdan sonra System Tablespace-də yerləşən yuxarıda adı çəkilən hissələri izah edək.
Data Dictionary — table,column və index-lər haqqında metadata məlumatlarını saxlayır.
Undo Log — A storage area that holds copies of data modified by active transactions. If another transaction needs to see the original data (as part of a consistent read operation), the unmodified data is retrieved from this storage area. Ümumi olaraq Rollback Segment.
Change Buffer — Daha ərtaflı: Change Buffer
Doublewrite Buffer — Daha ətraflı: Doublewrite Buffer

ib_logfile0 və ib_logfile1 fayllarının Redo Log olduğunu demişdik.
Redo Log haqqında daha ərtaflı:
Redo Log

Ümumiləşdirmə N3,
MySQL-də default config faylı ilə start olunduqda yalnız və yalnız 1 ümumi TableSpace olacaq. System TableSpace.
1 tablespace və 1 data file.

Gəlin bu halı ətraflı araşdıraq. İlk öncə MySQL-i innodb_file_per_table = 0 olaraq start edək. Bu məqsədlə,
my.cnf faylını açırıq və [mysqld] kataloqu altında aşağıdakını yazırıq. Həmçinin hər bir commit-dən sonra dərhal data fayla flush olunması üçün biz əlavə 2 parameter də təyin edirik:

[root@linuxsrv1 ~]# nano /etc/my.cnf
.
.
[mysqld]
innodb_file_per_table          = 0
innodb_flush_method            = O_DIRECT
innodb_flush_log_at_trx_commit = 1 

Save edirik və daha sonra start\stop:

[root@linuxsrv1 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Və yoxlayırıq Bizdə System Database-lərdən başqa Database yoxdur və bütün cədvəllər də məhz system cədvəlləridir. Dolayısı ilə:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
3 rows in set (0.00 sec)

[root@linuxsrv1 ~]# cd /var/lib/mysql/
[root@linuxsrv1 mysql]# ls -l
total 176144
-rw-rw----. 1 mysql mysql       56 Jan  4 02:53 auto.cnf
-rw-rw----. 1 mysql mysql 79691776 Feb  2 12:20 ibdata1
-rw-rw----. 1 mysql mysql 50331648 Feb  2 12:20 ib_logfile0
-rw-rw----. 1 mysql mysql 50331648 Jan  4 02:53 ib_logfile1
drwx------. 2 mysql mysql     4096 Jan  4 02:53 mysql
srwxrwxrwx. 1 mysql mysql        0 Feb  2 12:18 mysql.sock
drwx------. 2 mysql mysql     4096 Jan  4 02:53 performance_schema

Metadata-ya sorğu göndərdikdə də nəticə gözləniləndir çünki MySQL-də başqa cədvəl yoxdur:

SELECT count(*) tables,
       concat(round(sum(table_rows)/1000000,2),'M') rows,
       concat(round(sum(data_length)/(1024*1024*1024),2),'G') data,
       concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
       concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size,
       round(sum(index_length)/sum(data_length),2) idxfrac
       FROM information_schema.TABLES
       WHERE  table_schema not in ('mysql','information_schema','performance_schema');

+--------+------+------+------+------------+---------+
| tables | rows | data | idx  | total_size | idxfrac |
+--------+------+------+------+------------+---------+
|      0 | NULL | NULL | NULL | NULL       |    NULL |
+--------+------+------+------+------------+---------+
1 row in set (0.00 sec)

Bir daha xatırladıram ki biz server-i innodb_file_per_table = 0 halda start etmişik, yəni bütün cədvəl və dataları yerləşir bizim 1 datafile-ımızda = System Tablespace-imizdə.

[root@linuxsrv1 mysql]# ls -l | grep ibdata*
-rw-rw----. 1 mysql mysql 79691776 Feb  2 12:20 ibdata1

Size = 79691776 = 76 MB

Bu həcmi yadda saxlayıb Yeni database və Cədvəllər yaradaq. MƏn sample Database olan Employees-den istifadə etdim.
Database import-dan sonra yoxlayaq:

mysql> show databases like 'emp%';
+-----------------+
| Database (emp%) |
+-----------------+
| employees       |
+-----------------+
1 row in set (0.00 sec)

Dərhal System Tablespace və onun istifadə etdiyi ibdata1 data faylının həcmini yenidən yoxlayaq:

[root@linuxsrv1 mysql]# ls -l | grep ibdata1 
-rw-rw----. 1 mysql mysql 281018368 Feb  2 15:30 ibdata1

Yeni size = 281018368 = 268 MB

Gördüyümüz kimi, yeni yaratdığımız cədvəllər ibdata1 = System TableSpace-də saxlandı. İddia edirik ki, yeni yaradılmış cədvəllərin toplam həcmi TƏQRİBƏN 268 – 76 = 192 MB-dir.
Metadata-dan öyrənək:

SELECT count(*) tables,
       concat(round(sum(table_rows)/1000000,2),'M') rows,
       concat(round(sum(data_length)/(1024),2),'K') data_K,
       concat(round(sum(index_length)/(1024),2),'K') idx_K,
       concat(round(sum(data_length+index_length)/(1024),2),'K') total_size_K,
       concat(round(sum(data_length)/(1024*1024),2),'M') data_M,
       concat(round(sum(index_length)/(1024*1024),2),'M') idx_M,
       concat(round(sum(data_length+index_length)/(1024*1024),2),'M') total_size_M,
       concat(round(sum(data_length)/(1024*1024*1024),2),'G') data_G,
       concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx_G,
       concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size_G,
       round(sum(index_length)/sum(data_length),2) idxfrac
       FROM information_schema.TABLES
       WHERE  table_schema not in ('mysql','information_schema','performance_schema');
+--------+-------+------------+-----------+--------------+---------+--------+--------------+--------+-------+--------------+---------+
| tables | rows  | data_K     | idx_K     | total_size_K | data_M  | idx_M  | total_size_M | data_G | idx_G | total_size_G | idxfrac |
+--------+-------+------------+-----------+--------------+---------+--------+--------------+--------+-------+--------------+---------+
|      6 | 3.91M | 138496.00K | 54464.00K | 192960.00K   | 135.25M | 53.19M | 188.44M      | 0.13G  | 0.05G | 0.18G        |    0.39 |
+--------+-------+------------+-----------+--------------+---------+--------+--------------+--------+-------+--------------+---------+
1 row in set (0.00 sec)

188.44 MB Təqribi qiymət düzgün gəldi. Bu da bir daha sübut etdi ki, Default olaraq MySQL-də hər şey System Tablespace-də və 1 data file-da saxlanılır.

Araşdırmamızı davam etdirək.
İkinci bir anlayışı irəli sürək.
Tərif:
MySQL-də 2 növ TableSpace var. System TableSpace-i ki, biz artıq onun haqqında danışdıq və “Hər Cədvəlin Öz TableSpace”-i. Buna biz innodb_file_per_table = 0 yox, məhz innodb_file_per_table = 1 etməklə nail oluruq.
Dolayısı ilə default olmayan config faylı ilə MySQL-li start etməklə biz per-table-tablespace əldə etmiş oluruq

Əyani surətdə göstərək:
İlk öncə test database-imizi drop edək:

mysql> drop database employees;
Query OK, 6 rows affected (0.85 sec)

Daha sonra my.cnf faylında dəyişiklik edək və MySQL restart edək.

[root@linuxsrv1 ~]# nano /etc/my.cnf
.
.
innodb_file_per_table          = 1

[root@linuxsrv1 ~]# service mysqld restart
Stopping mysqld:                                           [  OK  ]
Starting mysqld:                                           [  OK  ]

Employee database-i yenidən import edək. Bu andan etibarən employee directory-nin daxilində bəzi digər faylların yarandığını görürük:

[root@linuxsrv1 mysql]# cd /var/lib/mysql/employees/
[root@linuxsrv1 employees]# ll -h | grep .ibd
-rw-rw----. 1 mysql mysql 112K Feb  2 16:13 departments.ibd
-rw-rw----. 1 mysql mysql  29M Feb  2 16:13 dept_emp.ibd
-rw-rw----. 1 mysql mysql 128K Feb  2 16:13 dept_manager.ibd
-rw-rw----. 1 mysql mysql  22M Feb  2 16:13 employees.ibd
-rw-rw----. 1 mysql mysql 140M Feb  2 16:15 salaries.ibd
-rw-rw----. 1 mysql mysql  40M Feb  2 16:14 titles.ibd

Məhz bu .ibd fayllar hər cədvəlin öz TableSpace\Data File-ıdır. Faktiki olaraq burdan belə nəticə çıxır ki, innodb_file_per_table = 1 halında hər cədvəlin öz DATA File-ı olur.

Bunu biz Metadata-dan da öyrənə bilərik.
information_schema-ya məxsus 3 cədvəl var ki, bizim üçün faydalı məlumatları özündə saxlayır:

INNODB_SYS_TABLES , INNODB_SYS_DATAFILES, INNODB_SYS_TABLESPACES

Məsələn, yuxarıda iddia etdiyimiz kimi, hər cədvəlin öz data file-ı olduğunu sübut etmək üçün:

mysql> select * from information_schema.INNODB_SYS_DATAFILES where path like './emp%';
+-------+------------------------------+
| SPACE | PATH                         |
+-------+------------------------------+
|    52 | ./employees/employees.ibd    |
|    53 | ./employees/departments.ibd  |
|    54 | ./employees/dept_manager.ibd |
|    55 | ./employees/dept_emp.ibd     |
|    56 | ./employees/titles.ibd       |
|    57 | ./employees/salaries.ibd     |
+-------+------------------------------+
6 rows in set (0.00 sec)

System Tablespace-də olmayan cədvəllərin siyahısını öyrənmək üçün:

SELECT name, flag, space
  FROM information_schema.INNODB_SYS_TABLES
 WHERE space != 0;
+----------------------------+------+-------+
| name                       | flag | space |
+----------------------------+------+-------+
| employees/departments      |    1 |    53 |
| employees/dept_emp         |    1 |    55 |
| employees/dept_manager     |    1 |    54 |
| employees/employees        |    1 |    52 |
| employees/salaries         |    1 |    57 |
| employees/titles           |    1 |    56 |
| mysql/innodb_index_stats   |    1 |     2 |
| mysql/innodb_table_stats   |    1 |     1 |
| mysql/slave_master_info    |    1 |     4 |
| mysql/slave_relay_log_info |    1 |     3 |
| mysql/slave_worker_info    |    1 |     5 |
+----------------------------+------+-------+
11 rows in set (0.00 sec)

TableSpace haqqından maraqlı məlumatları əldə etmək üçün isə:

mysql> SELECT *
    ->   FROM information_schema.INNODB_SYS_TABLESPACES
    ->  WHERE name LIKE 'emp%';
+-------+------------------------+------+-------------+----------------------+-----------+---------------+
| SPACE | NAME                   | FLAG | FILE_FORMAT | ROW_FORMAT           | PAGE_SIZE | ZIP_PAGE_SIZE |
+-------+------------------------+------+-------------+----------------------+-----------+---------------+
|    52 | employees/employees    |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    53 | employees/departments  |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    54 | employees/dept_manager |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    55 | employees/dept_emp     |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    56 | employees/titles       |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
|    57 | employees/salaries     |    0 | Antelope    | Compact or Redundant |     16384 |             0 |
+-------+------------------------+------+-------------+----------------------+-----------+---------------+
6 rows in set (0.00 sec)

General Difference Oracle vs. MySQL (part 1) : USER\SCHEMA\DATABASE

Oracle, MySQL Physical Data Structure və Logical Data Structure mövzularına girməzdən əvvəl bir sıra ümumi fərqlikləri ayrıca yazılar şəklində yazmağı məqsədə uyğun hesab edirəm…

Ilkin olaraq insanları çaşdıran məqamlardan biri , USER\SCHEMA\Database anlayışlarıdır.
Bu anlayışlar 2 RDBMS-də fərqlidir..Dolayısı ilə araşdırmaqda fayda var.

Bütün digər fərqlilikləri yazmazdan əvvəl mənim üçün əsas olan, DATABASE anlayışını izah edək:
Oracle-da:

database

Organized collection of data treated as a unit. The purpose of a database is to store and retrieve related information. Every Oracle database instance accesses only one database in its lifetime.

instance

The combination of the system global area (SGA) and background processes. An instance is associated with one and only one database.

Qısa olaraq:

Instance = Background Processes + Memory Region called SGA (System Global Area)
Database = Data files + Control Files + Online Redolog files

Database daimidir. Diskdə yerləşir.
Instance müvəqqətidir. RAM-da yerləşir.

Database aktiv deyil. Sadəcə disk-də yerləşən fayllar toplusudur.
Instance aktivdir. Əməliyyat sistemində hər hansı digər program kimi prosess şəklində fəaliyyət göstərir. Dolayısı ilə instance OS process-dir.

Oracle-da bir başa Database-ə qoşulma yoxdur, siz instance-a qoşulursuz, Database-i instance idarə edir.

İnstance-sı “startup” və “shutdown” edə bilərsiniz. Database-i isə “mount” və “open” , həmçinin “dismount” və “closed” edə bilərsiniz. Dolayısı ilə bir kimsə Oracle üçün “Oracle Start olundu” və yaxud “Oracle shutdown oldu” dedikdə çox güman ki, birinci halda “İnstance start olundu və Database open oldu” ikinci halda isə “Database closed oldu və İnstance shutdown oldu” demək istəmişdir.

Müqayisəli şəkildə irəliləyək:
Əyani şəkildə Oracle Database Start-ı göstərək:

-- 1
[oracle@localhost ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Sun Jan 26 02:42:59 2014

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> 

--2 
SQL> startup nomount 
ORACLE instance started.

Total System Global Area  826753024 bytes
Fixed Size                  2217872 bytes
Variable Size             583010416 bytes
Database Buffers          239075328 bytes
Redo Buffers                2449408 bytes

Əgər diqqətlə output-a baxsaq görərik ki, ORACLE instance started.. Yani hələ database yoxdur.
Davam edək:

SQL> alter database mount;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DB01      MOUNTED

Indi isə gördüyünüz kimi, Databse mount olundu.
Daha sonra da:

SQL> alter database open;

Database altered.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DB01      READ WRITE

Ən sonda da Database open olundu və istifadəyə hazırdır.
Burda daha bir anlayışı irəli sürək ki, 1 instance yalnız 1 database-ə connect ola bilər. Dolayısı ilə instanc-a qoşulmuş hər hansı user öz object-ini məhz o Database-də yaradacaq.
Yəni USER-in object-ləri məhz connect olduğu Database-də saxlanılacaq. MySQL-də isə bu belə deyildir. Gəlin araşdıraq.

MySQL-də Database anlayışına gəldikdə orda da həmçinin, Oracle-ın database tərifi geçərlidir. Yəni ki, MySQL-də də database-də fiziki məlumatlar saxlanılır. Diskdə saxlanılır və.s Lakin prinsipial fərqlərə gəlin birlikdə baxaq.
Əsas etibarilə onu qeyd etməliyik ki, MySQL-də Oracle-ın İnstance anlayışı yoxdur həmçinin MySQL-də İnstance start olunmur, Database OPEN və yaxud CLOSE olunmur.

MySQL OS-də Service kimi start olunur. Bu START-ı siz Oracle-ın yuxardakı addımlarının toplusu olaraq qəbul edə bilərsiniz.
MySQL service Start olunduqdan sonra USER session açır Session Thread, Server Thread-ə connect olur və işləməyə başlayır.
Daha ətraflı izah üçün gəlin əyani olaraq baxaq:

[root@localhost ~]# service mysqld start
Starting mysqld:                                           [  OK  ]

Bu bizim həm bir növü Oracle-dakı İnstance start və Database open-umuzdur 😉

Daha sonra connect oluruq:

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Default SUPER user olan root-la qoşulduq. İndi isə hər hansı USER yaradaq:

mysql> create user xxx identified by '12345';
Query OK, 0 rows affected (0.00 sec)

Disconnect olaq və yeni yaranmış USER-lə connect olaq:

mysql> exit
Bye
[root@localhost ~]# mysql -u xxx -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

İndi isə hər hansı əməliyyat etməyə çalışaq:

mysql> create table t1(x date);
ERROR 1046 (3D000): No database selected


ERROR 1046 (3D000): No database selected
— ERROR onu göstərir ki, cədvəlimizin hansı Database-də yerləşəcəyini biz seçməmişik.

Davam edək, MySQL Server-də hansı Database-lərin olduğuna baxaq:

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)

Bizim yeni yaradılmış USER-imiz Metadata (information_schema adından da göründüyü kimi) database-indən başqa heçnəyi görmür. Dolayısı ilə yeni yaradılmış USER heç bir əməliyyat yerinə yetirə bilməyəcək.
İndi isə eyni əməliyyatları Oracle-da edək:

SQL> create user xxx identified by 12345;

User created.

SQL> grant create session to xxx;

Grant succeeded.

SQL> grant create table to xxx;

Grant succeeded.

SQL> connect xxx/12345
Connected.
SQL> create table t1(x date);

Table created.

İndi isə Ümumiləşdirək. Əsas Fərqləndirmə:
Diqqət yetirdinizsə MySQL-də USER-in connect olması üçün hər hansı Database-in varlığına ehtiyac yoxdur, connect olduqdan sonra mövcud sonsuz sayda database-lərdən birini seçib işləyə bilər. Yəni MySQL user connect olduqdan sonra database-ini özü seçir.
Oracle-da isə əgər diqqət yetirdinizsə, Oracle install olunduqda və yaxud daha sonra Database create olunub, İnstance start-dır , Database open-dır. Və yeni yaradılmış bu xxx USER-i artıq məhz həmin Database-də fəaliyyət göstərir. Müvafiq GRANT-lar verdikdən sonra, session aça bilir cədvəlini create edə bilir.

Yenidən MySQL ilə davam edək…Super USER ilə connect olaq və database yaradaq:

[root@localhost ~]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.15 MySQL Community Server (GPL)

mysql> create database blog_test;
Query OK, 1 row affected (0.00 sec)

Yeni yaratdığımız USER-ə bu database-dən istifadə üçün GRANT verək:

mysql> create database blog_test;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on blog_test.* to xxx;
Query OK, 0 rows affected (0.00 sec)

mysql> show grants for xxx;
+----------------------------------------------------------------------------------------------------+
| Grants for xxx@%                                                                                   |
+----------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'xxx'@'%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' |
| GRANT ALL PRIVILEGES ON `blog_test`.* TO 'xxx'@'%'                                                 |
+----------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)

Grant-dan göründüyü kimi, xxx user-ini artıq blog_test database-indən istifadə hüququ vardır.
xxx useri ilə qoşulaq:

[root@localhost ~]# mysql -u xxx -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| blog_test          |
+--------------------+
2 rows in set (0.00 sec)

mysql> use blog_test;
Database changed
mysql> create table t1(x date);
Query OK, 0 rows affected (0.23 sec)

DATABASE məntiqini izah etdikdən sonra istərdim ki, USER anlayışına da qısaca izah verək.
USER Oracle-da gördüyümüz kimi, connect olur və hər hansı object-ini yaratdıqda , həmin object-lər evvelceden connect olduğu database-də yaranaraq həmin USER tərəfindən OWN olur. Yəni hər USER öz objectinin OWNER-idir.
MySQL-də isə USER hər hansı object-in OWNER-i deyil hətta, OWNER-lik anlayışı yoxdur.

Əyani olaraq göstərək.
Oracle-da USER + objects = SCHEMA. Dolayısı ilə digər SCHEMA-dan (USER-in OWN etdiyi obyektlərdə) hər hansı məlumat oxumaq istəyək:
Bu məqsədlə ikinci nümunə user yaradaq:

SQL> create user xxx2 identified by 12345;

User created.

SQL> grant create session to xxx2;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO xxx;

Grant succeeded.

SQL> GRANT UNLIMITED TABLESPACE TO xxx2;

Grant succeeded.

Daha sonra xxx2 USER-lə connect olub cədvəl yaradaq və məlumat daxil edək:

SQL> create table t2(x date);

Table created.
SQL> insert into t2(x) values((select sysdate from dual));

1 row created.

SQL> select * from t2;

X
---------
26-JAN-14

Həmçinin xxx user-lə connect olub daha əvvəl yaratdığımız cədvələ məlumat daxil edək:

SQL> connect xxx/12345
Connected.
SQL> insert into t1(x) values((select sysdate from dual));

1 row created.

SQL> select * from t1;

X
---------
26-JAN-14

xxx user ilə xxx2 user-in cədvəlinə sorğu göndərək. SYS-lə müvafiq GRANT verək:

SQL> grant select on xxx2.t2 to xxx;

Grant succeeded.

xxx-lə connect olaq və select verək:

SQL> select * from xxx2.t2;

X
---------
26-JAN-14

Gördüyümüz kimi Oracle-da 1 Database-də bir neçə SCHEMA olur. SCHEMA isə hər bir USER və onun objectləri deməkdir.

Burda da MySQL fərqlənir…MySQL-də DATABASE = SCHEMA. Dolayısı ilə sonsuz sayda USER sonsuz sayda DATABASE\SCHEMA-dakı müvafiq GRANT verməklə oxuya bilər. Yəni bir daha qeyd edirəm ki, USER-in object-ləri yoxdur MySQL-də. Database-lərdə yerləşir bütün məlumatlar və hər hansı USER digər DATABASE\SCHEMA-dan məlumat istəyirsə ona bunun privilige-i olmalıdır.

mysql> create user xxx2 identified by '12345';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all on blog_database.* to xxx2;
Query OK, 0 rows affected (0.00 sec)

İndi isə hər 2 user-lə connect olaq və DATABASE\SCHEMA-dan nümunə cədvələ select verək:
xxx user ilə:

[root@localhost ~]# mysql -u xxx -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> insert into blog_test.t1 values((select curdate()));
Query OK, 1 row affected (0.09 sec)

mysql> select * from blog_test.t1;
+------------+
| x          |
+------------+
| 2014-01-26 |
+------------+
1 row in set (0.00 sec)

xxx2 useri ilə:

[root@localhost ~]# mysql -u xxx2 -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.6.15 MySQL Community Server (GPL)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from blog_test.t1;
+------------+
| x          |
+------------+
| 2014-01-26 |
+------------+
1 row in set (0.00 sec)

Gördüyümüz kimi 2 müxtəlif USER eyni SCHEMA-dan select etdi.
Ümumiləşdirsək:
MySQL-də DATABASE = SCHEMA
Oracle-da USER + objects = SCHEMA

Ümumi və əsas fərqlilikləri başa düşmək vacib məsələdir.
Bu yazıda sizlərlə , Oracle və MySQL-də DATABASE ,USER və SCHEMA anlayışlarına baxdıq.
Ümidvaram ki, faydalı oldu.

Təşəkkürlər 😉

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 😉

Equality Range Optimization(eq_range_index_dive_limit system variable)

Bu mövzu şəxsi araşdırmalarımdan biridir ki, `Equality RANGE` scan zamanı 5.6 və 5.5 versiyaları arasında performance fərqinə baxacıq.
İlk öncə Equality RANGE nədir? 2 növ bizə tanış olan query təsəvvür edək:

col_name IN(val1, …, valN)
col_name = val1 OR … OR col_name = valN

Yuxarıdakı sorğular o zaman TRUE olur ki, col_name val-ların hər hansı birinə bərabər olsun. Dolayısı ilə bu müqayisə, bərabərliyi yoxlayır və məhz buna görə `Bərabərlik Aralığı`=`Equality RANGE` adlanır.
Teoriyası biraz aydın olmasa belə praktik olaraq izah edəcik bunu.
İlk öncə MySQL-imizə baxaq. MySQL 5.6.12 və MySQL 5.5.33-dir Qısa olaraq 5.6 və 5.5 olaraq qeyd edəcik.
Birinci olaraq 5.5 versiyada bu tip sorğuların necə çalışdığına baxacıq:

mysql [localhost] {msandbox} (mysqlspp) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.5.33    |
+-----------+
1 row in set (0.00 sec)

Cədvəlmiz isə hər 2 tərəfdə eynidir və aşağıdakı strukturdadır:

mysql [localhost] {msandbox} (mysqlspp) > show create table sales;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
| sales | 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`),
  KEY `PRODUCT_ID` (`PRODUCT_ID`,`CUSTOMER_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=2500004 DEFAULT CHARSET=latin1 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Toplam row sayı 2500003-dir:

mysql [localhost] {msandbox} (mysqlspp) > select count(*) from sales;
+----------+
| count(*) |
+----------+
|  2500003 |
+----------+
1 row in set (14.88 sec)

Gördüyümüz kimi, cədvəldə 2500003 row var. Biraz da cədvəlimizi araşdıraq. Cədvəl statusumuza baxdıqda görürük ki, MySQL 5.5 cədvəli full scan etdikdə təqribi row sayını 2500273 hesablayır:

Bundan əlavə cədvəl index-lərinin cardinality-sini öyrənək. Bizim üçün əsas PRODUCT_ID index-idir( KEY `PRODUCT_ID`):

cardinality=200, burdan belə nəticə çıxır ki product_id təqribən 200 unique scan-la tapıla bilər.
Bu rəqəm təqribi rəqəmdir və bunu başqa yolla da tapa bilərik:

mysql [localhost] {msandbox} (mysqlspp) > select count(product_id) from sales;
+-------------------+
| count(product_id) |
+-------------------+
|           2500003 |
+-------------------+
1 row in set (0.32 sec)

mysql [localhost] {msandbox} (mysqlspp) > select count(distinct product_id) from sales;
+----------------------------+
| count(distinct product_id) |
+----------------------------+
|                        150 |
+----------------------------+
1 row in set (0.00 sec)

Burdan belə nəticə çıxardırıq ki, MySQL 5.5-də full table scan zamanı 2500273 row examine olunacaq! bundan əlavə product_id-nin seçkinliyi(cardinality) 200-dür

MySQL 5.6-da isə bu qiymətlər aşağıdakı kimidir:
Table status:

Index cardinality:

Burdan belə nəticə çıxardırıq ki, MySQL 5.6-də full table scan zamanı 2351755 row examine olunacaq!(optimizer belə qərara gəlib təbii ki bu nisbi rəqəmdir) bundan əlavə product_id-nin seçkinliyi(cardinality) 286-dır

Hər 2 versiya MySQL-də cədvəl və index-lər haqqında məlumatı əldə etdikdən sonra mövzumuzu izah etməyə başlaya bilərik. sınaqdan keçirəcəyimiz sorğu:
select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10)

MySQL 5.5 üçün:

mysql [localhost] {msandbox} (mysqlspp) > select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10);
+----------+
| count(*) |
+----------+
|   166659 |
+----------+
1 row in set (0.06 sec)

MySQL 5.6 üçün:

mysql> select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10);
+----------+
| count(*) |
+----------+
|   166659 |
+----------+
1 row in set (0.05 sec)

1-dən 10-a qədər range üçün QEP MySQL 5.5-də:

mysql [localhost] {msandbox} (mysqlspp) > explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 332662
        Extra: Using where; Using index
1 row in set (0.00 sec)

MySQL 5.6-da:

mysql> explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 82220
        Extra: Using where; Using index
1 row in set (0.00 sec)

Bura diqqətlə nəzər yetirsək görərik ki, 5.5 versiya MySQL-də 332662 row scan olunur, 5.6 versiyada isə row scan sayı 82220-dir. Böyük fərq göz önündədir.

Query-imizə əlavələrlə yenidən çalışdıraq.
5.5 versiyada:

mysql [localhost] {msandbox} (mysqlspp) > explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10,11,12)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 398890
        Extra: Using where; Using index
1 row in set (0.01 sec)

5.6 versiyada:

mysql> explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9,10,11,12)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 98664
        Extra: Using where; Using index
1 row in set (0.00 sec)

398890 və 98664 yenidən fərq göz önündədir.
Lakin maraqlısı odur ki, product_id-ni 10-dan aşağı range-də müqayisə etdikdə nəticələr bərabər olur:

5.5 versiyada:

mysql [localhost] {msandbox} (mysqlspp) > explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 300112
        Extra: Using where; Using index
1 row in set (0.00 sec)

5.6 versiyada:

mysql> explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 300112
        Extra: Using where; Using index
1 row in set (0.00 sec)

Gördüyümüz kimi hər 2 halda 300112 rows examine olunacaq!
Ümumiləşdirmə aparaq, 5.6 versiyada 10-dan yuxarı range zamanı performance qazancı yaşanır lakin 10-dan aşağı range zamanı heç bir fərq baş vermir.
Buna səbəb 5.6 versiyada ilk dəfə olaraq təqdim olunmuş eq_range_index_dive_limit sistem dəyişənidir. 5.5-də bu dəyişən yoxdur:

mysql [localhost] {msandbox} (mysqlspp) > select @@eq_range_index_dive_limit;
ERROR 1193 (HY000): Unknown system variable 'eq_range_index_dive_limit'

5.6-da isə bu dəyişənin qiyməti default olaraq 10-dur:

mysql> select @@eq_range_index_dive_limit;
+-----------------------------+
| @@eq_range_index_dive_limit |
+-----------------------------+
|                          10 |
+-----------------------------+
1 row in set (0.00 sec)

Yuxarıdakı ümumiləşdirmədə göstərdiyimiz 10-nun səbəbi məhz bu dəyişənin default qiymətidir. Sınaq üçün bu dəyişənin qiymətini 9 edək və query-mizi 1-dən 9-a qədər range-lə run edək:

mysql> set @@eq_range_index_dive_limit=9;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select count(*) from sales force index(product_id) where product_id in (1,2,3,4,5,6,7,8,9)\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales
         type: range
possible_keys: PRODUCT_ID
          key: PRODUCT_ID
      key_len: 4
          ref: NULL
         rows: 73998
        Extra: Using where; Using index
1 row in set (0.00 sec)

Daha əvvəl row sayı 300112 idi, indi isə 73998 oldu. Göründüyü kimi Equality Range Optimization məqsədimizə çatmış olduq. Ən sonda eq_range_index_dive_limit dəyişəninin təsir etdiyi row sayının hesablanması alqoritmini də diqqətinizə çatdırım. Əgər xatırlayırsınızsa table status-dan row sayı bərabər idi 2351755-a. İndex cardinality isə 286-ya.
Hesablanma: 2351755/286*9 (range sayı)
Cavab:

mysql> select 2351755/286*9;
+---------------+
| 2351755/286*9 |
+---------------+
|    74006.2762 |
+---------------+
1 row in set (0.03 sec)

Həqiqətən də 74006.2762 təqribən bərabərdir 73998-a.
Nəticə olaraq onu deyə bilərik ki, MySQL 5.6 özü ilə bərabər bir çox yeniliklər gətirmişdir. bu yeniliklər əsasə etibarı ilə 3 qrupa bölünür:
1. Replication( GTİD based and crash-safe)
2. İnnoDB (performance and safety optimization)
3. MySQL query optimizer improvements

Bu tip yeniliklərdən istifadə edə bilməniz üçün 5.6 versiyaya upgrade etməniz məsləhətdir.

Təşəkkürlər 😉

Union optimization (perfomance test)

*MySQL version: 5.6.12
Linux version: Ubuntu 12.04
Engine: InnoDB*

MySQL Performance test seriyalı yazılarımızdan növbətisi UNİON və UNİON ALL-dur.
İlk öncə biraz union-nun özündən bəhs edək.
sadə olaraq Union birləşdirmə aparır və result set olaraq 2 və yaxud daha artıq sorğunun birləşmiş halını qaytarır. Deyək ki, bizim cədvəl1-də aşağıdakı məlumatlar var:

blue
green
gray
black

və cədvəl2-də isə:

red
green
yellow
blue

UNION etsək MySQL internal olaraq bir temporary cədvələ hər 2 cədvəldən gələn result-ları sort edib yığacaq. Yəni bizim temprorary cədvəldə aşağıdakı məlumatlar toplanacaq:

black
blue
blue
gray
green
green
red
yellow

Daha sonra da məhz bu temporary cədvəldə olan təkrar məlumatlar silinəcək və son nəticədə user-ə aşağıdakı məlumatlar qaytarılacaq:

black
blue
gray
green
red
yellow

Dolayısı ilə yadda saxlamaq lazımdır ki, UNİON və UNİON ALL hər 2si temporary table-dan istifadə edir.

Test-lərimiz üçün biz sales adlı cədvəldən istifadə edəcik. 2.5 mln row-luq bu cədvəlin oxşarını yaradaq:

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=2500004 DEFAULT CHARSET=latin1


create table sales2 like sales;

Daha sonra da sales_id-lərin hər 2 cədvəldə eyni olmaması(fərqli yerlərdə təkrarlanması üçün) məqsədilə onları procedure-la update edirik:

delimiter $$
 
CREATE PROCEDURE `insert_table`()
begin
	-- column deyisenleri
    declare v_SALES_ID int(8);
	declare v_CUSTOMER_ID decimal(8,0);
	declare v_PRODUCT_ID decimal(8,0);
	declare v_SALE_DATE datetime default NOT NULL;
	declare v_QUANTITY decimal(8,0) default NOT NULL;
    declare v_SALE_VALUE decimal(8,0) default NOT NULL;
    declare v_DEPARTMENT_ID decimal(8,0) DEFAULT '0';
    declare v_SALES_REP_ID decimal(8,0) DEFAULT '0';
    declare v_GST_FLAG decimal(8,0) DEFAULT NULL;
    declare v_sale_status char(1) DEFAULT NULL;
    declare v_FREE_SHIPPING char(1) DEFAULT '';
    declare v_DISCOUNT decimal(8,0) unsigned DEFAULT '0';
    
	-- sonuncu fetch olunmus row-nu tapan deyisen
    declare v_last_row_fetched int default 0;
     
    declare cursor1 cursor for select * from sales;
        
    declare continue handler for not found set v_last_row_fetched=1;
 
    set v_last_row_fetched=0;
    open cursor1;
    cursor_loop: loop
    fetch cursor1 
			into v_SALES_ID,v_CUSTOMER_ID,v_PRODUCT_ID,
					v_SALE_DATE,v_QUANTITY,v_SALE_VALUE,v_DEPARTMENT_ID,v_SALES_REP_ID,
					v_GST_FLAG,v_sale_status,v_FREE_SHIPPING,v_DISCOUNT;
            if v_last_row_fetched=1 then
                    leave cursor_loop;
            end if;
			set v_SALES_ID=v_SALES_ID+1;

            insert into sales2()
			values(v_SALES_ID,v_CUSTOMER_ID,v_PRODUCT_ID,v_SALE_DATE,
						v_QUANTITY,v_SALE_VALUE,v_DEPARTMENT_ID,v_SALES_REP_ID,
						v_GST_FLAG,v_sale_status,v_FREE_SHIPPING,v_DISCOUNT);
            
            
            
    end loop cursor_loop;
    close cursor1;
    set v_last_row_fetched=0;
     
end$$

Və dərhal ilk query-mizi işə salaq yoxlayaq:

mysql> (select sales_id from sales)
    -> union
    -> (select sales_id from sales2);
.
.
.

2500004 rows in set (14.44 sec)

Nəyə görə 2500004 rows?
Axı bizdə:

mysql> select count(sales_id) from sales;
+-----------------+
| count(sales_id) |
+-----------------+
|         2500003 |
+-----------------+
1 row in set (0.96 sec)

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

Lakin diqqətlə baxdıqda:

mysql> select max(sales_id) from sales2;
+---------------+
| max(sales_id) |
+---------------+
|       2500004 |
+---------------+
1 row in set (0.02 sec)

mysql> select max(sales_id) from sales;
+---------------+
| max(sales_id) |
+---------------+
|       2500003 |
+---------------+
1 row in set (0.00 sec)

dolayısı ilə bizdə 2500003 təkrar olunur lakin sonuncu id 2500004(1) distinct olduğu üçün,
2500003+1 olaraq toplam 2500004 row qaytarır. Bu da ona sübütdur ki, UNİON duplicate-ləri silir və yalnız DİSTİNCT məlumatları qaytarır. Temporary cədvəl-in yaranıb yaranmadığına baxmaq üçen isə status dəyişənlərinə diqqət yetirmək lazımdır:

1. mysql> flush status;

2. mysql> (select sales_id from sales) union (select sales_id from sales2);

3. mysql> show status Where variable_name like 'Created%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 1     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.00 sec)

Gördüyümüz kimi, Created_tmp_tables=1 temporary cədvəlimiz yaradıldı. Lakin burda daha təhlükəli hadisənin şahidi oluruq Created_tmp_disk_tables=1. Bu o deməkdir ki, query-nin işlədilməsi zamanı RAM yetərsizliyindən temporary table in-memory şəklindən disk şəklinə convert olunmuşdur. Yəni artıq temp table-ımız disk table olmuşdur ki, bu həmçinin əlavə vaxt itkisi deməkdir. Müvəqqəti olaraq yaradılan bu disk table həmçinin query-nin bitimindən sonra drop olunur ki, bu da əlavə vaxt tələb edir. Daha da incələməyə çalışsaq və profile etsək yuxarıda dediyimizin doğru olduğunu sübut edərik.

mysql> show profile for query 1;
+---------------------------+----------+
| Status                    | Duration |
+---------------------------+----------+
| starting                  | 0.000125 |
| checking permissions      | 0.000010 |
| checking permissions      | 0.000008 |
| Opening tables            | 0.000110 |
| System lock               | 0.058612 |
| optimizing                | 0.000037 |
| statistics                | 0.000027 |
| preparing                 | 0.000019 |
| optimizing                | 0.000005 |
| statistics                | 0.000006 |
| preparing                 | 0.000007 |
| executing                 | 0.000005 |
| Sending data              | 1.568980 |
| converting HEAP to MyISAM | 2.008669 |
| Sending data              | 3.861828 |
| executing                 | 0.000027 |
| Sending data              | 6.042862 |
| optimizing                | 0.000029 |
| statistics                | 0.000020 |
| preparing                 | 0.000016 |
| executing                 | 0.000005 |
| Sending data              | 1.377518 |
| removing tmp table        | 0.555878 |
| Sending data              | 0.000028 |
| query end                 | 0.000016 |
| closing tables            | 0.000031 |
| freeing items             | 0.054344 |
| logging slow query        | 0.146203 |
| cleaning up               | 0.000048 |
+---------------------------+----------+
29 rows in set, 1 warning (0.09 sec)

Diqqətlə baxdıqda:
converting HEAP to MyISAM | 2.008669 – – – Yəni in-memory temp cədvəlin disk MyiSAM cədvələ çevrilməsi.
removing tmp table | 0.555878 – – – tmp cədvəlin silinməsi.

Təqribən 2.5-3 saniyə burada vaxt gedib.

Testlərimizə davam edək. Yuxarıda qeyd etdiyimiz kimi UNİON sort edib tmp table-a yığır nəticəni. Məntiqlə düşündükdə yaxşı olmaz mi ki, özümüz query-də sort edək? Bir növü UNİON-a kömək edək:

(select sales_id from sales order by sales_id)
union
(select sales_id from sales2 order by sales_id);
.
.
2500004 rows in set (13.99 sec)

Başqa şəkildə yazıb test etdikdə:

(select sales_id from sales)
union
(select sales_id from sales2)
order by sales_id;
.
.
2500004 rows in set (14.62 sec)

14.44, 13.99 və 14.62 sonuncu order by-lı query-nin ümumiyyətlə order by-sıza yaxın sürətdə işləməsi hətta bəzən geri qalması çox maraqlıdır. Yəqin ki bu ona görədir ki 1 dəfə əlavə olaraq order edir. Bunun sübutunu Explain plana baxdıqda görmüş oluruq:

(select sales_id from sales)
union
(select sales_id from sales2)
order by sales_id;

*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary; Using filesort


(select sales_id from sales)
union
(select sales_id from sales2);

*************************** 3. row ***************************
           id: NULL
  select_type: UNION RESULT
        table: <union1,2>
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Using temporary
3 rows in set (0.00 sec)

Extra hissəsində olan Using filesort görülmüş əlavə sort-dan(order) xəbər verir.

Davam edək. Deyək ki bizə yalnız ilk 100 məlumat lazımdır. Biz bunu müxtəlif cür yaza bilərik.

(select sales_id from sales)
union
(select sales_id from sales2)
limit 100;
.
.
100 rows in set (13.61 sec)


(select sales_id from sales)
union
(select sales_id from sales2)
order by sales_id limit 100;
.
.
100 rows in set (13.73 sec)

select * from(
(select sales_id from sales order by sales_id asc)
union
(select sales_id from sales2 order by sales_id asc)
) as s where sales_id between 1 and 100;
.
.
100 rows in set (14.29 sec)


select sales_id from (select sales_id from sales order by sales_id asc limit 100) as s
union
select sales_id from (select sales_id from sales2 order by sales_id asc limit 100) as s2
limit 100;
.
.
100 rows in set (0.00 sec)


(select sales_id from sales order by sales_id limit 100)
union
(select sales_id from sales2 order by sales_id limit 100)
limit 100;
.
.
100 rows in set (0.00 sec)

13.61, 13.73 , 14.29 və 0.00,0.00 sec. Sözsüz ki, fərq göz önündədir. Son 2 sorğu demək olar ki eynidirlər.
Davam edək. Desc olaraq order etmək istədikdə:

(select sales_id from sales order by sales_id desc)
union
(select sales_id from sales2 order by sales_id desc);

1
2
3
.
.
2500003
2500004

2,500,004 rows found. Duration for 1 query: 13.272 sec

Gördüyümüz kimi bu işləmədi. Subquery-lərin özündə desc olmasına baxmayaraq UNİON yenə də həmişəki kimi bizə asc qaytarır. Lakin aşağıdakı kimi yazdıqda:

(select sales_id from sales)
union
(select sales_id from sales2) order by sales_id desc;

2,500,004 rows found. Duration for 1 query: 14.892 sec

(select sales_id from sales order by sales_id desc)
union
(select sales_id from sales2 order by sales_id desc)
order by sales_id desc;

2,500,004 rows found. Duration for 1 query: 14.471 sec

Düzgün nəticə qaytarır.
İndi isə istəyirik ki, ilk sales-dən ilk 100-ü, sales2-dən isə son 100-ü union edək.

select sales_id from (select sales_id from sales order by sales_id asc limit 100) as s
union
select sales_id from (select sales_id from sales2 order by sales_id desc limit 100) as s2;

(select sales_id from sales order by sales_id asc limit 100)
union
(select sales_id from sales2 order by sales_id desc limit 100);

200 rows found. Duration for 1 query: 0.001 sec.

Mövzunu genişləndirə bilərik amma mən ən son UNİON-nun tmp table problemi üçün bəzi həll yollarını göstərmək istərdim. Xüsusən də converting HEAP to MyISAM-ın qarşısını almaq üçün görülə biləcək tədbirlərə baxacıq.
Tmp table size-ı MySQL-də 2 system dəyişəni ilə idarə edə bilərsiniz:
tmp_table_sizemax_heap_table_size

İndiki halda bizim sorğumuz üçün my.cnf-də
tmp_table_size=128M
max_heap_table_size=128M

qeyd edirik və MySQL-ə restart veririk.

Daha sonra da sorğumuzu bir daha çalışdırırırq:

mysql> (select sales_id from sales order by sales_id) union (select sales_id from sales2 order by sales_id);
.
.
2500004 rows in set (9.53 sec)

Və dərhal status counter-lərər baxırıq ki, bəli bizim istədiyimiz kimi disk table yaradılmadı:
Created_tmp_disk_tables=0

mysql> show status Where variable_name like 'Created%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 0     |
| Created_tmp_tables      | 1     |
+-------------------------+-------+
3 rows in set (0.32 sec)

Profiling-də baxdıqda da bunun şahidi oluruq:

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000105 |
| checking permissions | 0.000011 |
| checking permissions | 0.000008 |
| Opening tables       | 0.000122 |
| System lock          | 0.000022 |
| optimizing           | 0.000011 |
| statistics           | 0.000037 |
| preparing            | 0.000020 |
| optimizing           | 0.000006 |
| statistics           | 0.000009 |
| preparing            | 0.000009 |
| executing            | 0.000007 |
| Sending data         | 4.705173 |
| executing            | 0.000030 |
| Sending data         | 3.784227 |
| optimizing           | 0.000036 |
| statistics           | 0.000020 |
| preparing            | 0.000017 |
| executing            | 0.000004 |
| Sending data         | 1.043749 |
| removing tmp table   | 0.003071 |
| Sending data         | 0.000051 |
| query end            | 0.000019 |
| closing tables       | 0.000035 |
| freeing items        | 0.000040 |
| cleaning up          | 0.000043 |
+----------------------+----------+
26 rows in set, 1 warning (0.00 sec)

Gördüyümüz kimi əvvəlki kimi converting HEAP to MyISAM yoxdur.
Həmçinin query-nin çalışma zamanında da çox azalma baş verdi:
13.99 – 9.53 = 4.46 sec

Əgər sizin də UNİON-la başınız ağrayırsa yuxarıda göstərdiyim üsullardan birindən istifadə edə bilərsiniz. Lakin tmp_table_size və max_heap_table_size-a toxunmazdan əvvəl ciddi düşünməniz lazımdır. Dokumentasiyada da deyildiyi kimi:

Increase the value of tmp_table_size (and max_heap_table_size if necessary) if you do many advanced GROUP BY queries and you have lots of memory.

Dolayısı ilə ~çox həmişə yaxşıdır~ anlayışını bu dəyişənlər üçün işlətmək düzgün deyildir. Kifayət qədər RAM varsa və həqiqətən də inanirsınızsa ki, bu dəyişənlərin qiymətlərini dəyişmək sizə fayda verəcək o zaman sınaya bilərsiniz.

Ümumiyyətlə isə beynəlxalq məsləhət bu yöndədir ki, UNİON və UNİON ALL-dan mümkün mərtəbə qaçılsın. Çünki nə qədər sürətli çalışır çalışsın temp table-sız çalışmır.

Təşəkkürlər 😉