Home PC Games Linux Windows Database Network Programming Server Mobile  
           
  Home \ Database \ Why is the ibdata1 file growing in MySQL?     - How to view the Linux QPS (Linux)

- Ubuntu 14.04 to install file editor KKEdit 0.1.5 version (Linux)

- Oracle 11g forget approach SYS and SYSTEM password (Database)

- Windows Server 2012 R2 Datacenter install SQL Server 2016 CTP (Database)

- Ubuntu 15.10 15.04 14.10 14.04 Install Ubuntu Tweak (Linux)

- About Python default character set (Linux)

- Linux module mechanism of (Programming)

- Open container cluster management system architecture and components introduced Kubernetes (Server)

- JDK comes with tools jinfo (Linux)

- MySQL 5.5 on master-slave copy filter (Database)

- Ubuntu 12.04 / 14.04 users to install software LyX document processing (Linux)

- Java Performance Tuning the proxy mode (Programming)

- Fedora 20 users install the Mate 1.8 desktop (Linux)

- Linux operating system security management skills (Linux)

- wget command examples (Linux)

- Dom4j change XML coding (Programming)

- Partition and file system under Linux (Linux)

- Ubuntu Server (Ubuntu 14.04 LTS 64-bit) installation libgdiplus 2.10.9 error solution (Linux)

- Ubuntu 10.10 install Oracle 10g Installation Guide (Database)

- Ceph cluster disk is no workaround for the remaining space (Server)

 
         
  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:      
 
- Iptables principle (Linux)
- Ubuntu 12.04 commonly use shortcuts finishing Share (Linux)
- REDO LOG records management (Database)
- DIY security of Linux platform (Linux)
- Ubuntu install image browser and manager Phototonic 1.6.17 (Linux)
- To upgrade combat MongoDB database replication sets and split versions (Database)
- How to find out a Unix system library files are 32-bit or 64-bit (Linux)
- Linux environment variables inside (Linux)
- How to upgrade to Oracle 11g Oracle 12c (Database)
- How to install Git client in Ubuntu (Linux)
- How do you turn on and off IPv6 address on Fedora (Linux)
- Linux tool curl and wget advanced use (Linux)
- jQuery plugin dynamic label generation (Linux)
- Setting Lns firewall against ARP attacks (Linux)
- CentOS7 installed VMware 10 (Linux)
- Linux host system monitoring implement the cgi (Server)
- Linux PXE unattended installation PXE-E32: TFTP OPen timeout the solution (Linux)
- Java memory-mapped file MappedByteBuffer (Programming)
- Ubuntu 14.04 LTS installed Hadoop 1.2.1 (distributed cluster mode) (Server)
- The array of C language (Programming)
     
           
     
  CopyRight 2002-2020 newfreesoft.com, All Rights Reserved.