Hot answers tagged

6 votes

MariaDB: how to reduce ibdata file size

This topic has also been discussed in MDEV-21952. In the upcoming MariaDB Server 10.6.13 release, MDEV-29593 should improve reuse of undo log pages. Starting with MariaDB Server 10.11 (the newest long-...
Marko Mäkelä's user avatar
2 votes

How to configure InnoDB/MariaDB to not fill ibdata1 with (rollback/undo?) data that is useless in an in-memory database?

Doing smaller transactions and keeping connections not open (Open Transactions) for long time will probably do the job. And: The undo log is usually part of the physical system tablespace, but from ...
shinguz's user avatar
  • 59
2 votes

Big size difference in ibdata file from live to staging

One possibility is that you have or had in the past innodb_file_per_table=0, and despite separating them at a later stage into separate files (changing it to 1+rebuilding the tables), the space, as ...
jynus's user avatar
  • 14.8k
2 votes
Accepted

MySQL one file per table not working

First of all, innodb_file_per_table is enabled by default for MySQL 8. If you want to know what tables are sitting in ibdata1, run the following query select * from information_schema.innodb_tables; ...
RolandoMySQLDBA's user avatar
1 vote

Why Optimize Table does not shrink table size?

In my case, optimize TABLE took a very long time, and did not reduce the size as expected. I followed the @RickJames advice to switch to PARTITION BY RANGE(TO_DAYS(..)). After implementing and ...
Niyaz's user avatar
  • 218
1 vote

Why Optimize Table does not shrink table size?

Is this a time-series? And you are purging data older than 14 days? If so, I strongly recommend that you switch to PARTITION BY RANGE(TO_DAYS(..)) and DROP PARTITION and REORGANIZE PARTITION daily. ...
Rick James's user avatar
  • 77.7k
1 vote

Why Optimize Table does not shrink table size?

If you ran OPTIMIZE TABLE table1; multiple times and the the file table1.ibd is the same size, then it is already defragmented as much as possible. It is possible that the rows you deleted had very ...
RolandoMySQLDBA's user avatar
1 vote
Accepted

Restore database with only ibdata1 file

If your client is unable to give you a proper database dump, then whatever task they're asking of you will take far longer and cost much more than anyone should be willing to tolerate. Is there a way ...
matigo's user avatar
  • 2,018
1 vote
Accepted

MariaDB: how to reduce ibdata file size

The first thought that came to mind was "Wow !!! You must have rather big transactions." According to mysqlperformanceblog.com's Reasons for run-away main Innodb Tablespace, these are the ...
RolandoMySQLDBA's user avatar
1 vote
Accepted

How to set up mysql docker container with existing ib* files?

ib* locked is another process already is running on them. Restore the entire datadir, not just the ib* data files, to a directory. Use the volume mount option of the container with docker/podman -v ...
danblack's user avatar
  • 7,699
1 vote

MySQL one file per table not working

For simply finding out where a table is: Versions 5.6, 5.7, most of MariaDB: I created two tables in database ftp: mysql> SELECT name, space -> FROM information_schema....
Rick James's user avatar
  • 77.7k
1 vote
Accepted

Need only single table from ibdata1 file

I would put the whole backup file onto a testserver, is that possible for you? And then do: mysqldump -u root -p test1 catalog_product_index_price > dump_catalog_product_index_price.sql
Hackity Hack's user avatar
1 vote

cannot recover mysql idb file

Looks like one of the pages in movies.ibd is corrupt, so it can't read it. I see two options here. 1) There is a script fetch_data.sh that can copy as many records as possible from a corrupt InnoDB ...
akuzminsky's user avatar
  • 4,957
1 vote
Accepted

Restore MySQL DB from files

As provided by the OP: As stated in here, the way to proceed was to initiate mysql daemon in safe mode, just as follows: Start server bypassing privilege system $ sudo mysqld_safe --user=root --skip-...
1 vote

table_name.ibd file size is increasing ( MySQL 5.7.7 Enterprise) ( Innodb Engines)

Now that you have posted the top tablespace file sizes, here is my attempt to answer your questions: Yes, regardless of what value you have for innodb_file_per_table the files ibdata & ib_logfile ...
thatsaru's user avatar
  • 668
1 vote

shrink ibdata1 in Galera Cluster

I don't think you have many options here. If you want to maintain high availability I'd say shutdown on node and configure it as a replication slave of one of the cluster nodes. Rebuild it from a ...
jerichorivera's user avatar
1 vote

Purge old records from mysql ibdata1 file

Note to all, there is never a time you can safely delete the ibdata file unless you plan on restoring your database from scratch. This also applies to after you have enabled innodb_file_per_table. ...
TheWitness's user avatar

Only top scored, non community-wiki answers of a minimum length are eligible