Scribd
Upload
101 views18 pages

CBT MySQL Architecture 2

- The data directory stores MySQL files and varies based on operating system (/usr/local/mysql/var, /var/lib/mysql, C:\mysql). - MyISAM tables use .MYD, .MYI, and .FRM files to store data, indexes, and schema while InnoDB uses a shared tablespace file and transaction logs. - MySQL uses memory buffers to cache frequently used data like open tables, privileges, indexes, and query plans to improve performance. Logs are also stored in the data directory.

Uploaded by

Stephen Efange
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
101 views18 pages

CBT MySQL Architecture 2

- The data directory stores MySQL files and varies based on operating system (/usr/local/mysql/var, /var/lib/mysql, C:\mysql). - MyISAM tables use .MYD, .MYI, and .FRM files to store data, indexes, and schema while InnoDB uses a shared tablespace file and transaction logs. - MySQL uses memory buffers to cache frequently used data like open tables, privileges, indexes, and query plans to improve performance. Logs are also stored in the data directory.

Uploaded by

Stephen Efange
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PPTX, PDF, TXT or read online on Scribd
You are on page 1/ 18

MySQL Architecture 2/2

• Disk space Usage


• Memory usage
The Data Directory

• /usr/local/mysql/var (source)
• /var/lib/mysql (pkg)
• \PF\MYSQL 5.1\ Data (Win)
[MySQLD]
• Datadir=/var/lib/mysql2
• Datadir=c:/mysql
• Shell> mysql –verbose –help ==reveals opt.files  parse
order
MyISAM File Structures

• Each DB gets own folder under datadir


• Myd: table data file
• Myi: table index file
• Frm : table schema file
• Schema = database =logical container =
physical folder
InnoDB File Structures
• Files Stored in datadir root
• Tablespace (data + index)
--ibdata
• Transaction log files – ib_log file1, ib_log file2
• Table format files -. Frm
 
[MySQLD]
• Innodb_data_per_table
Mysql logs
• Stored in datadir
• Accessed through mysql client
MySQL Memory usage

• Thread = process = connection = client  thread handlers


• Buffers (memory catches)
– --table cache : open tables
– --grant table buffers : privileges
– --key buffers : index blocks
– --query cache : execution plans
• Demo
• Examining Table File Structures
• Customizing ‘’ “ “
• Working with datadir…. Login…
…

MySQL> show create table city;


…
…
…
…
…
…
• After datadir change to newdatadir
…

You might also like