MySQL Architecture -2(Disk space & Error logs).
How MySQL will use disk space
Disk space used primarily for files in the server’s data directory
–> A sub directory for each database in the data directory.
- Data directory depends on distribution platform ,operating system.
- For Windows , for example
<mysql-home\data or the AppData directory ,for example :
|
1 |
c:\Documents and Settings\All Users\Application Data\MySQL\MySQL-Server 5.6 |
- Linux, for example :
|
1 |
/usr/local/mysql/data or <mysql-home>/data |
- Current setting in @@datadir server variable
- Table and view format files(.frm)
- Server log files and status files.
- Trigger storage
- System database (mysql)
At the time of installation if you are not specifying any path MySQL will be installed in /var/lib/mysql location.
ibdata1 file is a table space file in MySQL, this file is specific to InnoDB tables this doesn’t contain any information about InnoDB.
ib_logfile0 and ib_logfile1 are transaction redo log files.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
root@ip-172-31-35-134:~# cd /var/lib/mysql root@ip-172-31-35-134:/var/lib/mysql# ls -la total 122920 drwx------ 6 mysql mysql 4096 Oct 17 15:06 . drwxr-xr-x 39 root root 4096 Oct 16 17:27 .. -rw-r----- 1 mysql mysql 56 Oct 16 17:27 auto.cnf -rw-r--r-- 1 root root 0 Oct 16 17:27 debian-5.7.flag -rw-r----- 1 mysql mysql 337 Oct 17 11:38 ib_buffer_pool -rw-r----- 1 mysql mysql 50331648 Oct 17 15:09 ib_logfile0 -rw-r----- 1 mysql mysql 50331648 Oct 16 17:27 ib_logfile1 -rw-r----- 1 mysql mysql 12582912 Oct 17 15:09 ibdata1 -rw-r----- 1 mysql mysql 12582912 Oct 17 14:16 ibtmp1 drwxr-x--- 2 mysql mysql 4096 Oct 16 17:27 mysql drwxr-x--- 2 mysql mysql 4096 Oct 16 17:27 performance_schema drwxr-x--- 2 mysql mysql 12288 Oct 16 17:27 sys drwxr-x--- 2 mysql mysql 4096 Oct 17 15:37 <span style="color: #0000ff;">testEngines</span> |
Whenever we create a database, the related folder same as database name which we crated will be created in your /var/lib/mysql location and if you go in that database directory you see db.opt (option file) file and if you open that file you have default character set and latin collation as shown below :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
root@ip-172-31-35-134:/var/lib/mysql# cd testEngines/ root@ip-172-31-35-134:/var/lib/mysql/testEngines# ls db.opt testsample.ibd testsample1.MYI testsample.frm testsample1.MYD testsample1.frm root@ip-172-31-35-134:/var/lib/mysql/testEngines# ls -la total 136 drwxr-x--- 2 mysql mysql 4096 Oct 17 15:37 . drwx------ 6 mysql mysql 4096 Oct 17 15:06 .. -rw-r----- 1 mysql mysql 65 Oct 17 15:06 <span style="color: #0000ff;">db.opt</span> -rw-r----- 1 mysql mysql 8556 Oct 17 15:09 testsample.frm -rw-r----- 1 mysql mysql 98304 Oct 17 15:09 testsample.ibd -rw-r----- 1 mysql mysql 0 Oct 17 15:37 testsample1.MYD -rw-r----- 1 mysql mysql 1024 Oct 17 15:37 testsample1.MYI -rw-r----- 1 mysql mysql 8556 Oct 17 15:37 testsample1.frm |
Read the content in db.opt file.
|
1 2 3 |
root@ip-172-31-35-134:/var/lib/mysql/testEngines# cat db.opt default-character-set=latin1 default-collation=latin1_swedish_ci |
If we create a table in a database 2 types of files will be created for each table 1) <table_name>.frm 2) <table_name>.ibd as shown below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 |
mysql> use testengines; ERROR 1049 (42000): Unknown database 'testengines' mysql> use testEngines; 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> create table testdiskspace (Id INT); Query OK, 0 rows affected (0.02 sec) mysql> exit Bye root@ip-172-31-35-134:~# cd /var/lib/mysql/testEngines/ root@ip-172-31-35-134:/var/lib/mysql/testEngines# ls -la total 244 drwxr-x--- 2 mysql mysql 4096 Oct 19 13:56 . drwx------ 6 mysql mysql 4096 Oct 17 15:06 .. -rw-r----- 1 mysql mysql 65 Oct 17 15:06 db.opt -rw-r----- 1 mysql mysql 8556 Oct 19 13:56 testdiskspace.frm -rw-r----- 1 mysql mysql 98304 Oct 19 13:56 testdiskspace.ibd -rw-r----- 1 mysql mysql 8556 Oct 17 15:09 testsample.frm -rw-r----- 1 mysql mysql 98304 Oct 17 15:09 testsample.ibd -rw-r----- 1 mysql mysql 0 Oct 17 15:37 testsample1.MYD -rw-r----- 1 mysql mysql 1024 Oct 17 15:37 testsample1.MYI -rw-r----- 1 mysql mysql 8556 Oct 17 15:37 testsample1.frm |
Let’s create table with specified engine called MyISM.
|
1 2 |
mysql> create table testdiskspac2(ID int) Engine=MyISAM; Query OK, 0 rows affected (0.00 sec) |
check how many files are created for testdiskspace2 object.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
root@ip-172-31-35-134:~# cd /var/lib/mysql root@ip-172-31-35-134:/var/lib/mysql# cd testEngines/ root@ip-172-31-35-134:/var/lib/mysql/testEngines# ls -la total 260 drwxr-x--- 2 mysql mysql 4096 Oct 22 13:20 . drwx------ 6 mysql mysql 4096 Oct 17 15:06 .. -rw-r----- 1 mysql mysql 65 Oct 17 15:06 db.opt <span style="color: #0000ff;">-rw-r----- 1 mysql mysql 0 Oct 22 13:20 testdiskspac2.MYD -rw-r----- 1 mysql mysql 1024 Oct 22 13:20 testdiskspac2.MYI -rw-r----- 1 mysql mysql 8556 Oct 22 13:20 testdiskspac2.frm</span> -rw-r----- 1 mysql mysql 8556 Oct 19 13:56 testdiskspace.frm -rw-r----- 1 mysql mysql 98304 Oct 19 13:56 testdiskspace.ibd -rw-r----- 1 mysql mysql 8556 Oct 17 15:09 testsample.frm -rw-r----- 1 mysql mysql 98304 Oct 17 15:09 testsample.ibd -rw-r----- 1 mysql mysql 0 Oct 17 15:37 testsample1.MYD -rw-r----- 1 mysql mysql 1024 Oct 17 15:37 testsample1.MYI -rw-r----- 1 mysql mysql 8556 Oct 17 15:37 testsample1.frm |
There is three files has created for MyISAM engine objects with the extension called “.MYD” , “MYI” ,””.FRM”.
|
1 2 3 4 5 6 |
oot@ip-172-31-35-134:~# cd /var/lib/mysql root@ip-172-31-35-134:/var/lib/mysql# ls auto.cnf ib_logfile0 ibtmp1 sys debian-5.7.flag ib_logfile1 mysql testEngines ib_buffer_pool <span style="color: #ff0000;">ibdata1</span> performance_schema root@ip-172-31-35-134:/var/lib/mysql# |
There is a file called ibdata1 which is called tablespace in mysql databases.
For InnoDB databases the default tablespace is ibdata1
The default location of MySQL is /var/lib/mysql
|
1 2 3 4 5 6 |
root@ip-172-31-35-134:~# cd /var/lib/mysql root@ip-172-31-35-134:/var/lib/mysql# ls auto.cnf ib_logfile0 ibtmp1 sys debian-5.7.flag ib_logfile1 mysql testEngines ib_buffer_pool ibdata1 performance_schema root@ip-172-31-35-134:/var/lib/mysql# |
To check in MySQL prompt
|
1 2 3 4 5 6 7 |
mysql> show variables like '%datadir%' ; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | <span style="color: #0000ff;">/var/lib/mysql/</span> | +---------------+-----------------+ 1 row in set (0.00 sec) |
To change the location of MySQL databases we need to change configuration file.
|
1 |
root@ip-172-31-35-134:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf |
First need to stop MySQL server then copy the content into new directory location then only need to change datadir variable.
Types of log files
Error log file:
It contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running.
Slow log file:
The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. It can be used to find queries that take a long time to execute and can be used for optimization. We can process a slow query log file using the mysqldumpslow command to summarize it.
mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order. Also, the slow query log should be protected because logged statements might contain passwords. We need to enable to use this option.
To set time for long running quires.
|
1 2 3 4 5 6 7 8 9 10 11 |
mysql> show variables like '%long%'; +----------------------------------------------------------+-----------+ | Variable_name | Value | +----------------------------------------------------------+-----------+ | long_query_time | 10.000000 | | performance_schema_events_stages_history_long_size | 10000 | | performance_schema_events_statements_history_long_size | 10000 | | performance_schema_events_transactions_history_long_size | 10000 | | performance_schema_events_waits_history_long_size | 10000 | +----------------------------------------------------------+-----------+ 5 rows in set (0.00 sec) |
Binary log file:
contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done.
General log file:
This log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. It can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld.
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform
Loading...
