?
Solved

MySQL data files and log files

Posted on 2008-11-10
15
Medium Priority
?
1,790 Views
Last Modified: 2012-06-22
In Data folder I can see a folder named with my database name. Inside it I can see few .FRM files. The largest size of a .FRM file is 11 KB. In the Data folder itself, I can see some files whose name are like this:

ibLogFile0    -  10 MB
ibLogFile1    -  10 MB
ibData1        -  18 MB
impserver.pid  - 1 KB

I want to know, what is the maximum size of the .FRM file in Windows XP SP2 (FAT32 and NTFS). I also want to know why log files have reached to 10 MB when the database size (total of .FRM files) is 132 KB.

For what these 'ib...' files are used?
0
Comment
Question by:rpkhare
  • 7
  • 5
  • 3
15 Comments
 
LVL 7

Expert Comment

by:Chrisedebo
ID: 22921170
The maximum size of a .frm file is 64kb and is related to the number of columns you have in a table. see here for more information http://dev.mysql.com/doc/refman/5.1/en/column-count-limit.html

The log files will grow fairly quickly as they will store all the transactions performed on the database. You can shrink the files with a Flush command. see here for more information http://dev.mysql.com/doc/refman/5.1/en/log-files.html
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22921235
What is the name of my data file which is storing real data? Is it "ibData1"? Are the ".frm" files the structure information of a table?
0
 
LVL 7

Assisted Solution

by:Chrisedebo
Chrisedebo earned 600 total points
ID: 22921251
Yes, thats correct. All data will be stored in the ibData1 file, unless you or the MySQL db create additional datafiles later on.
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 7

Expert Comment

by:Chrisedebo
ID: 22921255
if you are using the innodb engine the files will be approximatley twice the size of the data they contain.
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22921418
In case I am planning to create a Data-warehouse where in I want the "ibData1" file to grow freely without limits, how I can do that? Does MySQL 5.1 community edition support this?
0
 
LVL 26

Assisted Solution

by:Umesh
Umesh earned 1400 total points
ID: 22921464
>>>In Data folder I can see a folder named with my database name. Inside it I can see few .FRM files. The largest size of a .FRM file is 11 KB. In the Data folder itself, I can see some files whose name are like this:

Regardless of the storage engine you choose, every MySQL table you create is represented, on disk, by a .frm file, which describes the table's format (i.e. the table definition). The file bears the same name as the table, with a .frm extension. The .frm format is the same on all platforms but in the description of the .frm format that follows, the examples come from tables created under the Linux operating system.


>>ibLogFile0    -  10 MB - InnoDB log files (primary)
>>ibLogFile1    -  10 MB - InnoDB log files (Secondary)
>>ibData1        -  18 MB - InnoDB data file (tablespace)
>>impserver.pid  - 1 KB  - Process identification number associated with a service/daemon




>>I want to know, what is the maximum size of the .FRM file in Windows XP SP2 (FAT32 and NTFS). I also want to know why log files have reached to 10 MB when the database size (total of .FRM files) is 132 KB.


Can you post InnoDB specific settings from my.cnf here? which solve your above query

0
 
LVL 8

Author Comment

by:rpkhare
ID: 22921508
I searched for my.cnf file but I couldn't find it.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22921545
Are you on Windows box then pls look for my.ini
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22921601
You may notice below lines..in my.ini/my.cnf

# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = "E:/MySQLMaster/data"
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = "E:/MySQLMaster/data"
innodb_log_arch_dir = "E:/MySQLMaster/data"
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50


***Data file

innodb_data_file_path = ibdata1:10M:autoextend  

This setting configures a single 10MB data file named ibdata1 that is auto-extending. No location for the file is given, so by default, InnoDB  creates it in the MySQL data directory.

0
 
LVL 8

Author Comment

by:rpkhare
ID: 22921602
Ya. Found it. Please see the attached document.
my.txt
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22921664
I cannot see that "auto-extend" line in "my.ini" file. See the attached file above.
0
 
LVL 26

Expert Comment

by:Umesh
ID: 22921678
I see these settings for InnoDB in your my.ini

#*** INNODB Specific options ***


innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=15M
innodb_log_file_size=10M
innodb_thread_concurrency=8


To see rest of the InnoDB specific details.. pls run this from mysql prompt.

mysql>show global variables;

or even this

mysql>show variables like 'innodb%';

Pls take a look at the parameters.. you will see all the details
0
 
LVL 8

Author Comment

by:rpkhare
ID: 22924183
To backup manually, I need to take backup of:

ibLogFile0
ibLogFile1
ibData1

Database folder containing ".FRM" files and the "my.ini " file. Is it correct?
0
 
LVL 26

Accepted Solution

by:
Umesh earned 1400 total points
ID: 22927517

No need to copy my.ini every time if you haven't done significant changes.

   1. Shut down your MySQL server and make sure that it shuts down without errors.
   2.Copy all your data files (ibdata files and .ibd files) into a safe place.
   3.Copy all your ib_logfile files to a safe place.
   4.Copy your my.ini configuration file or files to a safe place.
   5.Copy all the .frm files for your InnoDB tables to a safe place.

I assume per table data file is not enabled for InnoDB
0
 
LVL 8

Author Closing Comment

by:rpkhare
ID: 31515008
Thanks.
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question