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ı: http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_change_buffer
Doublewrite Buffer — Daha ətraflı: “http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_doublewrite_buffer
ib_logfile0 və ib_logfile1 fayllarının Redo Log olduğunu demişdik.
Redo Log haqqında daha ərtaflı: http://dev.mysql.com/doc/refman/5.6/en/glossary.html#glos_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)
Şərhlər ( 2 )
Təşəkkürlər. Maraqlı alınıb.
Çox sağolun müəllim. Minnətdaram 🙂