Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Why is the ibdata1 file growing in MySQL?     - Cancel Root Command History under Linux (Linux)

- Oracle 11g DataGuard service to start automatically (Database)

- Use HugePages optimize memory performance (Database)

- C ++ class implementation date operator overloading (Programming)

- Shell generated using automated configuration script Orabbix (Database)

- Java reflection mechanism explained in detail and Method.invoke explanation (Programming)

- Lua4.0 interpreter documents (Programming)

- Vi (Vim) keyboard map and its basic commands (Linux)

- The Java way to stop a thread of execution (Programming)

- Linux development management utility command (Linux)

- Depth understanding of Python character set encoding (Programming)

- Learn to read the source code of vmstat (Linux)

- Father of Python: Why Python zero-based index (Programming)

- Using Linux command line and execute PHP code (Programming)

- Adding SSH to Github (Linux)

- How x2g0 install Remote Desktop on Linux VPS (Server)

- To install Jetty server configuration in detail (Server)

- Java open source monitoring platform Zorka basic use (Linux)

- Iptables small summary (Linux)

- Jump table (skiplist) of code (Programming)

 
         
  Why is the ibdata1 file growing in MySQL?
     
  Add Date : 2018-11-21      
         
         
         
  We support columns in Percona often receive files on MySQL ibdata1 this problem.

When the monitor server sends a warning about the MySQL server storage when the panic began - that the disk is almost full.

After some investigation you realize that most of the site space is InnoDB shared tablespace ibdata1 use. And you have enabled innodbfileper_table, so the question is:

ibdata1 saved what?

When you enable innodb_file_per_table, the table is stored in their own table space, but the shared table space is still in the memory of other InnoDB internal data:

Data dictionary, which is the metadata InnoDB table
Change buffer
Double write buffer
Undo logs
Some of which are on the Percona server can be configured to avoid growing too large. For example, you can change the maximum set by innodbibufmax_size buffer, or set innodbdoublewritefile to double write buffer stored in a separate file.

MySQL version 5.6 you can also create an external undo table space, so they can put their files to alternative storage to ibdata1. You can look at this document.

What causes ibdata1 rapid growth?

When the first command MySQL problem Usually we need to do is:

SHOW ENGINE INNODB STATUS / G
This will show some of our valuable information. We ** TRANSACTION (transaction) ** and began to check, and then we will find this:

--- TRANSACTION 36E, ACTIVE 1256288 sec
MySQL thread id 42, OS thread handle 0x7f8baaccc700, query id 7900290 localhost root
show engine innodb status
Trx read view will not see trx with id> = 36F, sees < 36F
This is one of the most common reasons for a fairly old 14 days before the transaction created. This state is active, which means that InnoDB has created a snapshot of the data, it is necessary to maintain the old page in the revocation of the log in order to safeguard a consistent view of the database until the transaction begins. If your database has a large number of write task, it means that stores a lot of undo page.

If you can not find any long-running transactions, you can monitor INNODB STATUS of the other variables, "History list length (the length of the history list)" shows some waiting for clear operation. In this case the problem occurs frequently because the cleanup thread (the main thread or older) can not come in as fast as the speed of these records processed revoked.

How I check what is stored in the ibdata1 Lane?

Unfortunately, MySQL does not provide a view of what is stored in the shared table space ibdata1 information, but there are two tools would be helpful. The first one is Marc Callaghan made a modified version innochecksum, publish it in this bug report.

It is quite easy to use:

# ./innochecksum / Var / lib / mysql / ibdata1
0 bad checksum
13 FIL_PAGE_INDEX
19272 FIL_PAGE_UNDO_LOG
230 FIL_PAGE_INODE
1 FIL_PAGE_IBUF_FREE_LIST
892 FIL_PAGE_TYPE_ALLOCATED
2 FIL_PAGE_IBUF_BITMAP
195 FIL_PAGE_TYPE_SYS
1 FIL_PAGE_TYPE_TRX_SYS
1 FIL_PAGE_TYPE_FSP_HDR
1 FIL_PAGE_TYPE_XDES
0 FIL_PAGE_TYPE_BLOB
0 FIL_PAGE_TYPE_ZBLOB
0 other
3 max index_id
All 20608 19272 undo log in page. It takes up 93% of the table space.

The second look-up table space the way content is produced by Jeremy Cole InnoDB Ruby tools. It is a check of the internal structure of InnoDB more advanced tools. For example, we can use the space-summary parameter to get a list of each page and its data type. We can use standard Unix tools to count the number of undo log page:

# Innodb_space -f / var / lib / mysql / ibdata1 space-summary | grep UNDO_LOG | wc -l
19272
Although in this particular case, innochedcksum faster and easier to use, but I recommend that you use Jeremy tools to learn more about the InnoDB internal data distribution and internal structure.

Well, now we know where the problem lies. The next question:

How can I solve the problem?

The answer to this question is very simple. If you can commit statement, you do it. If not, you have to kill the thread to begin the rollback process. That will stop ibdata1 growth, but it is clear that there will be loopholes in your software, some people will encounter an error. Now that you know how to identify the problem, you need to use your own debugging tools or general query log to find out who or what caused the problem.

If a problem occurs in the cleanup thread, the solution usually is to upgrade to the new version, the new version uses a separate thread clear alternative to the main thread. For more information view the document

Is there any way reclaim the space that has been used it?

No, there is not an easy and fast way. InnoDB table space never shrink ... see 10-year-old bug reports, the latest updates from James Day (thank you):

When you delete some rows, the page is marked as deleted later reuse, but the space will not be recovered from. The only way is to use the new ibdata1 start the database. To do this you should use mysqldump need to do a full backup logic, then stop and delete all the MySQL database, ib_logfile, ibdata1 file. When you restart MySQL time will create a new shared table space. Then restore the logical backup.

to sum up

When ibdata1 file grows too fast, it is often forgotten in the long-running transaction MySQL caused. Try to solve the problem as quickly as possible (to submit or kill the transaction), because they do not go through the pain of mysqldump slow process, you can not reclaim wasted disk space.
     
         
         
         
  More:      
 
- SSH security note (Linux)
- CentOS7 install JDK (Linux)
- Android Application Development: an argument between Activity (Programming)
- Java is simple to read and write HDFS Demo (Programming)
- Based on Python: OpenCV simple image manipulation (Programming)
- Windows environment Android Studio v1.0 Installation Guide (Linux)
- Install Unity 8 preview version of the desktop in Ubuntu (Linux)
- Enterprise Hadoop cluster architecture - DNS installation (Server)
- mysqldump implement database logical backup (Database)
- CentOS 6.6 command-line automatic completion (Linux)
- Erlang concurrency and foundation (Programming)
- Teamviewer not start in Linux (Linux)
- Ubuntu 32 compile Android 4.0.4 Problems (Linux)
- 6 common PHP security attacks (Linux)
- Which file system is most suitable for your Linux system (Linux)
- Simple to install Tor browser to Ubuntu 14.04 and Linux Mint 17 (Linux)
- Eclipse remove all comments and code spaces (Linux)
- Switching Virtual Machine + Ubuntu graphical interface and terminal interface (Linux)
- Encrypted with GnuPG signature to verify the authenticity and integrity of downloaded file (Linux)
- Zorin OS: Linux novice most personal desktop system should be used (Linux)
     
           
     
  CopyRight 2002-2022 newfreesoft.com, All Rights Reserved.