Arxiv
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)
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
və
(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_size və max_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 😉
Basic MySQL replication setup
Bu yazıda sadə MySQL replication-nun qurulmasından danışacıq.
Sadə qurulma o deməkdir ki, 2 fresh install olunmuş MySQL server-imiz var və bunları replicate edirik. Yani hər iki MySQL-də data yoxdur.
Replication anlayışı haqqında danışmayacam. Kifayət qədər bol material var bu barədə internetdə.
Master-imiz CentOS 6.4-ə qurulu MySQL 5.5.30-dur.
mysql> select version(); +-----------+ | version() | +-----------+ | 5.5.30 | +-----------+
Slave-imiz isə VirtualBox-da Ubuntu 12.04 üzərindəki MySQL 5.5.29-dur.
mysql> select version(); +-------------------------+ | version() | +-------------------------+ | 5.5.29-0ubuntu0.12.04.2 | +-------------------------+
Ümumi qayda: Slave(qul) yəni replicate olunan tərəf həmişə versiya cəhətdən daha yuxarı olmalıdır. Yəni məsələn Master(ağa) 5.5.30 Slave isə 5.1.6 ola bilməz olmamalıdır. Lakın əksi mümkündür. İndiki halda 5.5.30 ilə 5.5.29 arasında fərq olmadığı üçün bizim bu şəkildə replication qurmamız düzgündür
Başlayaq. 1-ci addım Slave üçün replication user yaradırıq və grant veririk:
create user 'ubuntu_repl'@'192.168.1.%' identified by '12345';
və connection-u test edirik:
shahriyar@ubuntu-vb:~$ mysql -u ubuntu_repl --host=192.168.1.3 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 12 Server version: 5.5.30 MySQL Community Server (GPL) by Remi
Grant veririk:
mysql> grant replication slave,replication client on *.*
-> to 'ubuntu_repl'@'192.168.1.%' identified by '12345';
Query OK, 0 rows affected (0.00 sec)
Grant-ı test edirik:
mysql> show grants for 'ubuntu_repl'@'192.168.1.%'; +------------------------------------------------------------------------------------------------------------------------------------------------------+ | Grants for ubuntu_repl@192.168.1.% | +------------------------------------------------------------------------------------------------------------------------------------------------------+ | GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'ubuntu_repl'@'192.168.1.%' IDENTIFIED BY PASSWORD '*00A51F3F48415C7D4E8908980D443C29C69B60C9' | +------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
2-ci addım hər 2 server-də binary log-u aktiv edirik. Bu haqda bir yazım var Binary Log-un aktiv edilməsi
Qısa olaraq Master-də my.cnf faylına əlavə edirik:
log_bin=/var/lib/mysql/data/mysql-bin expire_logs_days=14 sync_binlog=1 log_bin_index=/var/lib/mysql/data/mysql-bin server_id=10
Slave-də isə yenə my.cnf faylına aşağıdakıları əlavə edirik:
log_bin=/var/lib/mysql/data/mysql-bin expire_logs_days=14 sync_binlog=1 log_bin_index=/var/lib/mysql/data/mysql-bin server_id=2 relay_log=/var/lib/mysql/data/mysql-relay-bin log_slave_updates=1 read_only=1
Burda əsas olaraq diqqətivizi çəkmək istəyirəm 2 dəyişənə. log_slave_updates bu slave-i məcbur edir ki bütün dəyişiklikləri həm də oz binary log-unda qeyd etsin. Səbəb: ola bilsin sabah bu slave, master-in crash-inden sonra çöndərilib olsun master. və artıq bu master olmuş slave-in üzərindən də replication qura biləcəksiniz!
read_only isə hər hansı səhv yönləndirilmiş app-ə salve-də dəyişiklik etməyə icazə vermir.
Əgər siz bu şəkildə deyil də başqa şəkildə setup etmisinizsə demək ki, sizin replication sabah başınıza problem aça bilər.
Hər 2 my.cnf-i save edirik və MySQL Server-ləri restart edirik.
3-cü mərhələ:
Slave-ə Master-ı necə deyərlər izah edirik. Bəzən bu tip məlumatları my.cnf-ə yerləşdirirlər lakin qətiyyən belə bir şey etməyin! Sadəcə aşağıdakı komandaları keçirdin.
mysql> change master to master_host='192.168.1.3',
-> master_user='ubuntu_repl',
-> master_password='12345',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=0;
Query OK, 0 rows affected (0.01 sec)
Və slave statusa baxırıq:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.1.3
Master_User: ubuntu-repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: No
Slave_SQL_Running: No
İndi isə diqqətlə baxaq bildiyimiz kimi slave-də 2 process olur İO və SQL. Lakin status-un dediyinə görə nə İO nə də SQL process işləmir. (sonuncu sətrdə hamısının qarşısında NO yazılıb).
Məhz buna görə də biz Slave-i start edirik:
mysql> start slave; Query OK, 0 rows affected (0.01 sec)
Və yenidən status-a baxırıq:
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.3
Master_User: ubuntu_repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 107
Relay_Log_File: mysql-relay-bin.000002
Relay_Log_Pos: 253
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
YES+YES 😉
Və replication-nun işlədiyinə baxaq. Master-də database və table yaradaq və Slave-də yoxlayaq:
Master-də:
mysql> create database replication_test; Query OK, 1 row affected (0.09 sec) mysql> create table tested(id int); Query OK, 0 rows affected (0.18 sec)
Slave-də:
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | data | | mysql | | performance_schema | | replication_test | +--------------------+ 5 rows in set (0.07 sec) mysql> show tables; +----------------------------+ | Tables_in_replication_test | +----------------------------+ | tested | +----------------------------+ 1 row in set (0.00 sec)
100% işlədi… Təbii ki, bu sadə setup-dır. Yəni 2 yeni install olunmuş data-sız MySQL-i replicate etmək qədər asan iş ola bilməz.
Böyül cədvəlləri olan MySQL replication qurmaq haqqında irəliki zamanlarda bir yazı olacaq.
Təşəkkürlər 😉
count() optimization (performance test)
Bu yazıda count(column_name) və count(*) optimizasiyası haqqında danışacıq. Sadə yollarla count(*) sərf edilən vaxtı bir neçə dəfə azalda bilərik. Lakin bu mövzunu daha yaxşı anlamanız üçün bəzi MySQL internals mövzularına qısaca olaraq toxunmalıyıq.
İnnoDB-də primary key və secondary index(istənilən digər key) anlayışı. primary key hər cədvəldə 1 dənə olur secondary index isə çoxsaylı. Lakin İnnoDB-nin clustered index strukturu hər secondary key-də sanki bir primary key saxlayır. Bunu sadə sözlərnən belə izah etmək olar ki, hər secondary index=secondary index+primary key. Və istənilən query-də MySQL istənilən məlumatı primary key vasitəsilə tapır. Yəni select etdikdə sanki ilk öncə həmin row-nun primary key-si daha sonra secondary key-si və daha sonra məlumatın özü tapılır. Az öncə qeyd etdiyim kimi hər secondary key özlüyündə primary key-dən ~parça~ saxlayır dolayısı ilə burdan belə nəticə çıxarmaq olar ki, göstərilən qaydanın əksi də doğrudur.
Yəni secondary index-dən istifadə etməklə primary key-i tapmaq olar! Çox uzatmıram keçək işə 😉
İlk öncə cədvəlimizə baxaq
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
Gördüyümüz kimi cədvəlimizdə sales_id=primary key, sales_cust_idx=secondary key.
İndi isə sadə və həmişə gördüyümüz select count(*)-a baxaq yəni bütün row sayını tapaq.
mysql> select count(*) from sales; +----------+ | count(*) | +----------+ | 2500003 | +----------+ 1 row in set (0.58 sec)
Vaxt: 0.58 saniyə
Yuxarıda kiçik nəzəriyyə girişinə sübut kimi QEP-ə baxdıqda görürük ki, həqiqətən də MySQL count üçün secondary key-dən istifadə edir.
mysql> explain select count(*) from sales\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: index
possible_keys: NULL
key: sales_cust_idx
key_len: 4
ref: NULL
rows: 2489938
Extra: Using index
1 row in set (0.00 sec)
key: sales_cust_idx = secondary index.
Sual: biz görmüşük ki, həmişə primary key digər key-lərdən daha sürətlidir. Niyə primary key istifadə etmirik? Axı həmişə köməyimizə gəlir
Cavab: MySQL optimizer bizdən daha yaxşı bilir.
Primary key vasitəsilə count edək:
mysql> select count(*) from sales ignore index(sales_cust_idx); +----------+ | count(*) | +----------+ | 2500003 | +----------+ 1 row in set (0.71 sec)
QEP:
mysql> explain select count(*) from sales ignore index(sales_cust_idx)\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales
type: index
possible_keys: NULL
key: PRIMARY
key_len: 4
ref: NULL
rows: 2489938
Extra: Using index
1 row in set (0.00 sec)
key: PRIMARY
Vaxt : 0.71 saniyə
0.71 və 0.58 bilənlər bilir ki, hətta 0.001-in əhəmiyyəti var.
Daha sonra count(*)-un istifadə edildiyi 2-ci ümumi hala baxaq. İD-si 50-dən böyük olanları count-layaq.
mysql> select count(*) from sales where sales_id>50; +----------+ | count(*) | +----------+ | 2499953 | +----------+ 1 row in set (0.92 sec)
Vaxt 0.92 saniyə
Bu query-ni dəyişirik, aşağıdakı kimi yazırıq və fərqə baxırıq:
mysql> select (select count(*) from sales)-count(*) as count from sales where sales_id<=50; +---------+ | count | +---------+ | 2499953 | +---------+ 1 row in set (0.60 sec)
Vaxt: 0.60
0.92 – 0.60=0.32 daha sürətlidir…
Davam edərək count()-un istifadə edildiyi daha bir yerə baxaq.
create table colors(
color_name varchar(15) not null
);
insert into colors values('blue'),('blue'),('blue'),('yellow'),('yellow'),('yellow'),('yellow');
Sual: cədvəldə hər rəngdən neçə ədəd var?
Sınayaq:
mysql> select count(color_name) from colors where color_name='blue' or color_name='yellow'; +-------------------+ | count(color_name) | +-------------------+ | 7 | +-------------------+ 1 row in set (0.00 sec) mysql> select count(color_name) from colors where color_name='blue' and color_name='yellow'; +-------------------+ | count(color_name) | +-------------------+ | 0 | +-------------------+ 1 row in set (0.00 sec)
Alınmadı 😉
Üsullar:
-- 1 mysql> select sum(if(color_name='blue',1,0)) as blue, count(if(color_name='yellow',1,0)) as yellow from colors; +------+--------+ | blue | yellow | +------+--------+ | 3 | 7 | +------+--------+ 1 row in set (0.00 sec) -- 2 mysql> select sum(color_name='blue') as blue, sum(color_name='yellow') as yellow from colors; +------+--------+ | blue | yellow | +------+--------+ | 3 | 4 | +------+--------+ 1 row in set (0.00 sec) -- 3 count()-lu üsul mysql> select count(color_name='blue' or NULL) as blue, count(color_name='yellow' or NULL) as yellow from colors; +------+--------+ | blue | yellow | +------+--------+ | 3 | 4 | +------+--------+ 1 row in set (0.00 sec)
Təşəkkürlər 😉
MySQL 5.6.10 Query optimizer (performance test)
Təbii ki 5.6.10 versiya ilə bir çox yeniliklər gəlib.
Və əsas səs gətirən hadisələrdən biri Query Optimizer-in inkişafıdır. Çox dərinliyə getmədən nə qədər əlavələrin olduğuna siz də baxa bilərsiniz:
5.5.30 versiya üçün:
mysql [localhost] {msandbox} (employees) > select @@optimizer_switch\G
*************************** 1. row ***************************
@@optimizer_switch:
index_merge=on,
index_merge_union=on,
index_merge_sort_union=on,
index_merge_intersection=on,
engine_condition_pushdown=on
1 row in set (0.00 sec)
5.6.10 versiya üçün:
mysql> select @@optimizer_switch\G *************************** 1. row *************************** @@optimizer_switch: index_merge=on, index_merge_union=on, index_merge_sort_union=on, index_merge_intersection=on, engine_condition_pushdown=on, index_condition_pushdown=on, mrr=on, mrr_cost_based=on, block_nested_loop=on, batched_key_access=off, materialization=on, semijoin=on, loosescan=on, firstmatch=on, subquery_materialization_cost_based=on, use_index_extensions=on 1 row in set (0.00 sec)
Bəli MySQL optimizer indi daha ağıllıdır necə deyərlər. Və test üçün biz employees database-ini götürəcik. Bu database-i siz də yükləyə bilərsiniz:
employees_db
Və bu database-dəki employees cədvəli. Cədvəldə 300.024 record var.Cədvəl strukturu aşağıdakı kimidir:
mysql> desc employees;
+------------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+---------------+------+-----+---------+-------+
| emp_no | int(11) | NO | PRI | NULL | |
| birth_date | date | NO | | NULL | |
| first_name | varchar(14) | NO | | NULL | |
| last_name | varchar(16) | NO | | NULL | |
| gender | enum('M','F') | NO | | NULL | |
| hire_date | date | NO | | NULL | |
+------------+---------------+------+-----+---------+-------+
6 rows in set (0.00 sec)
Dolayısı ilə 1 dənə index var o da primary key.
Test 1:
select emp_no from employees where first_name='Marco'
MySQL 5.6.10:
MySQL 5.5.30:
və yuxarıdakı QEP-dən də aydın olduğu kimi MySQL 5.6.10 test edilmiş query-nin nəticəsini bizə hazırlayıb verməsi üçün 299,645 row-nu oxumalıdır. 5.5.30 isə 300,856.
Lakin query response time-da elə də fərq yoxdur hər 2-si 0.10 sec çəkdi.
Test 2:
Sözsüz ki, məntiqlə düşündükdə belə nəticəyə gəlmək olar ki, əgər biz first_name-lə axtarış ediriksə onu index-ləməliyik:
alter table employees add index(first_name); select emp_no from employees where first_name='Marco'
MySQL 5.6.10:
MySQL 5.5.30:
QEP-lər tamamilə eynidir. Gördüyümüz kimi emp_no və first_name hər ikisi index-ləndiyi üçün MySQL məlumatı birbaşa index-dən oxuyur.
Bu həmçinin aşağıdakı query-lər üçün də keçərlidir:
select emp_no from employees where first_name like '%mar%'; select emp_no from employees where first_name like 'Mar%';
Təbii ki scan olunacaq row sayı artacaq çünki ehtimallar çoxalır lakin istənilən halda index-dən istifadə olunacaq.
Test 3:
İndi isə query-mizə 1 dənə index-lənməmiş column əlavə edək:
select emp_no,last_name from employees where first_name='Marco';
indiki halda bu index-siz column last_name-dir.
Yazının da yazılma səbəbi məhz şəxsən müəyyən etdiyim MySQL 5.6.10-nun bu gözəl xüsusiyyətdir. Aşağıya diqqət 😉
MySQL 5.6.10:
-- 1 select last_name,emp_no from employees where first_name='Marco';
-- 2 select emp_no,last_name from employees where first_name like 'Marc%';
-- 3 select emp_no,last_name from employees where first_name like 'Mar%';
-- 4 select emp_no,last_name from employees where first_name like 'Ma%';
-- 5 select emp_no,last_name from employees where first_name like 'M%';
Şəkillərdəki EXTRA və rows -lara fikir versəniz görərsiniz ki, MySQL 5.6.10 5 variantın 4-ündə İndex Condition-dan istifadə etdi. Yalnız sonuncu like ‘M%’-dən başqa. Bunun da səbəbi çox aşkardı. M% böyük ehtimal verir bu zaman MySQL Optimizer M-lə başlayan-ları index vasitəsilə yoxlamaq əvəzinə sadəcə full scan etməyi üstün tutur. Optimizer bunun daha faydalı olacağına qənaət gətirib elə biz də həmçinin 🙂
İndi isə eyni testləri MySQL 5.5.30 üçün edək.
MySQL 5.5.30:
-- 1 select last_name,emp_no from employees where first_name='Marco';
-- 2 select emp_no,last_name from employees where first_name like 'Marc%';
-- 3 select emp_no,last_name from employees where first_name like 'Mar%';
-- 4 select emp_no,last_name from employees where first_name like 'Ma%';
-- 5 select emp_no,last_name from employees where first_name like 'M%';
Gördüyünüz kimi istənilən halda Using İndex Condition istifadə olunmadı. Səbəb? Çünki bu xüsusiyyət yalnız MySQL 5.6.10-da var. Ümumiyyətlə bu versiya çox səs gətirəcək deyə bilərik. Həmçinin siz MariaDB 10.0-a da baxa bilərsiniz. Bu MySQL 5.6.10 ilə eynidir.
Bəs nədir bu Using İndex Condition? Daha ətraflı: index-condition-pushdown-optimization
Qısa olaraq: ICP can reduce the number of times the storage engine must access the base table and the number of times the MySQL server must access the storage engine.
Onu da qeyd etməkdə fayda var ki, siz də öz localhost/server-inizdə 2 ve daha artıq MySQL versiyanı qurub test edə bilərsiniz. Bunun üçün xüsusi tool MySQL Sandbox-dan istifadə edə bilərsiniz. Bu haqda artıq məqalə yazmışam:
Sandbox on Centos
Təşəkkürlər 😉
Oracle/MySQL Blog 