Əsas səhifə > MySQL, MySQL 5.6, MySQL administration > Overview of Physical Database Structure in MySQL

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)
  1. Hələlik heç bir şərh yoxdur
  1. No trackbacks yet.

Bir cavab yazın

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

WordPress.com Loqosu

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

Twitter rəsmi

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

Facebook fotosu

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

Google+ foto

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

%s qoşulma

%d bloqqer bunu bəyənir: