Is there any best way to reduce the size of ibdata in mysql.?
来源:互联网 发布:淘宝客服基本培训内容 编辑:程序博客网 时间:2024/06/05 18:31
Keep in mind the busiest file in the InnoDB infrastructure is /var/lib/mysql/ibdata1
This file normally houses four types of information
- Table Data
- Table Indexes
- MVCC (Multiversioning Concurrency Control) Data
- Table Metadata
Many people create multiple ibdata files hoping for better diskspace management and performance. It does not help.
Unfortunately, OPTIMIZE TABLE against an InnoDB table stored in ibdata1 does two things:
- Makes the table's data and indexes contiguous inside ibdata1
- It makes ibdata1 grow because the contiguous data is appended to ibdata1
You can segregate Table Data and Table Indexes from ibdata1 and manage them independently usinginnodb_file_per_table.To shrink ibdata1 once and for all you must do the following
Step 01) MySQLDump all databases into a SQL text file (call it SQLData.sql)
Step 02) Drop all databases (except mysql schema)
Step 03) Shutdown mysql
Step 04) Add the following lines to /etc/my.cnf
[mysqld]innodb_file_per_tableinnodb_flush_method=O_DIRECTinnodb_log_file_size=1Ginnodb_buffer_pool_size=4G
Sidenote: Whatever your set for innodb_buffer_pool_size, make sure innodb_log_file_size is 25% of innodb_buffer_pool_size.
Step 05) Delete ibdata1, ib_logfile0 and ib_logfile1
At this point, there should only be the mysql schema in /var/lib/mysql
Step 06) Restart mysql
This will recreate ibdata1 at 10MB, ib_logfile0 and ib_logfile1 at 1G each
Step 07) Reload SQLData.sql into mysql
ibdata1 will grow but only contain table metadata
Each InnoDB table will exist outside of ibdata1
Suppose you have an InnoDB table named mydb.mytable. If you go into /var/lib/mysql/mydb, you will see two files representing the table
- mytable.frm (Storage Engine Header)
- mytable.ibd (Home of Table Data and Table Indexes for mydb.mytable)
ibdata1 will never contain InnoDB data and Indexes anymore.
With the innodb_file_per_table option in /etc/my.cnf, you can run OPTIMIZE TABLE mydb.mytable
and the file/var/lib/mysql/mydb/mytable.ibd
will actually shrink.
I have done this many times in my career as a MySQL DBA
In fact, the first time I did this, I collapsed a 50GB ibdata1 file into 500MB.
Give it a try. If you have further questions on this, email me. Trust me. This will work in the short term and over the long haul. !!!
If you would like to see how much actual data is stored in MyISAM and InnoDB, please run this query:
SELECT IFNULL(B.engine,'Total') "Storage Engine",CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Data Size", CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Index Size", CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',SUBSTR(' KMGTP',pw+1,1),'B') "Table Size"FROM (SELECT engine,SUM(data_length) DSize,SUM(index_length) ISize,SUM(data_length+index_length) TSize FROM information_schema.tablesWHERE table_schema NOT IN ('mysql','information_schema','performance_schema')AND engine IS NOT NULL GROUP BY engine WITH ROLLUP) B,(SELECT 3 pw) A ORDER BY TSize;
- Is there any best way to reduce the size of ibdata in mysql.?
- Q:Is there any way to define what the "Open Resource" dialog in Eclipse should show?
- what is the best way to reduce stress?
- Is there any way to define a min and max value for edittext in android?
- Whats the best way to split an array in ruby into multiple smaller arrays of random size
- What is the best way to implement a heartbeat in C++ to check for socket connectivity?
- ADF: The best way to indicate for records in a table the existence of details
- What is the best way to paginate results in SQL Server
- The best way to get unique elements of a list in Python
- Is-there-any-way-to-prevent-an-android-app-from-being-removed
- Given an array of size N in which every number is between 1 and N, determine if there are any dupli
- How to reduce the font size of bibliography/reference
- Is there a limit to the number of columns in an HBase row?
- "There is only one way to do it, the right way” -------Guido van Rossum
- Is there a way to add a badge to an application icon in Android?
- Unable to connect to any of the specified MySQL hosts.
- Unable to connect to any of the specified MySQL hosts.
- MySQL Workbench there is no connection to the mySQL Server
- hdu 2073
- vc++ ADO 连接 access
- 转载一个hashmap的讲解
- Android权限大全
- SVN服务器搭建和使用(三)
- Is there any best way to reduce the size of ibdata in mysql.?
- C/C++连接MySql数据库
- ortoiseSVN客户端重新设置用户名和密码
- redis 源代码之数据结构(3)--hash表实现
- [个人记事]Web-GridView Update*
- spring3.1.1依赖包作用
- window.open第二个参数的作用d
- android Dom解析
- Android 开发之旅:短信的收发及在android模拟器之间实践(一)