Home › Blogs › Christoph C. Cemper's blog
Mysql

Shrinking Innodb Data- and Logfiles - the hardcore way

<< MD5 Password cracking web service | Mysql Insert Delayed = Oracle Insert Behaviour ? >>
Sun, 05/15/2005 - 12:46 — Christoph C. Cemper

A little cleanup – after testing out MySql's ACID compliant storage engine InnoDb some months ago I had a 3 Gigabytes large IBDATA1 cluttering my server's 10k SCSI disks… compared to the 300GB desktop drives this is just 1%, but for a 72 GB high end disk that's around 5% – on a partition not too oversized…

No Innodb data was stored, so I thought they have a bug in there not freeing up unused space... right.

The solution would be an update to an Cpanel unsupported MySql 4.1.1 to use the new setting _innodb_file_per_table_ to create a datafile per table (myisam behaviour) and having congestions in once single file in one – up to the filesystem max filesize – then you die.

but….

see this

Quote:

A critical bug found: if one uses the 4.1 my.cnf option

innodb_file_per_table

to create tables, and some of the secondary index records are inserted to

the InnoDB 'insert buffer ', then after a normal mysqld shutdown InnoDB loses

all those secondary index records! CHECK TABLE will print to the mysqld .err

log that there are less records in the secondary index than in the clustered

index.

Doesn't sound worth an upgrade to get rid of old datafiles… after all I ant to disable innodb on this server after cleanup.

so I though – "just delete the crap and restart"...

Quote:

cd /var/lib/mysql

mkdir old-inno

mv ./ib* old-inno

and then restart mysql

you should find this

Quote:

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

050515 11:00:33 InnoDB: Setting file ./ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait…

050515 11:00:34 InnoDB: Log file ./ib_logfile0 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile0 size to 5 MB

InnoDB: Database physically writes the file full: wait…

050515 11:00:35 InnoDB: Log file ./ib_logfile1 did not exist: new to be created

InnoDB: Setting log file ./ib_logfile1 size to 5 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

050515 11:00:38 InnoDB: Started

/usr/sbin/mysqld: ready for connections.

Version: '4.0.22-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Official MySQL RPM

and NOT this

Quote:

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

050515 10:56:52 InnoDB: Setting file ./ibdata1 size to 10 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Error: all log files must be created at the same time.

InnoDB: All log files must be created also in database creation.

InnoDB: If you want bigger or smaller log files, shut down the

InnoDB: database and make sure there were no errors in shutdown.

InnoDB: Then delete the existing log files. Edit the .cnf file

InnoDB: and start the database again.

050515 10:56:53 Can't init databases

050515 10:56:53 Aborting

050515 10:56:53 InnoDB: Warning: shutting down a not properly started

InnoDB: or created database!

050515 10:56:53 /usr/sbin/mysqld: Shutdown Complete

050515 10:56:53 mysqld ended

log output… if it was the latter, then you missed one of the log files… (just as I did :-)

Remember: this operation will DESTROY all your innodb data – make sure you don't have some transaction data left in there, at least to a logical mysql dump before…

This is however a supported (at least documented) way to create new ibdata datafiles – without upgrading to 4.1.1 or hassling around with other bugs for a subsystem you don't need now… just the defensive way necessary for production systems (altough the catchy Hardcore title might suggest it different :-)

Disabling INNODB is easy, just add in the [mysqld] section

Quote:

skip-innodb

to your /etc/my.cnf and restart mysql (e.g. /scripts/restartsrv_mysql)

there you go… This will save memory and disk

space and speed up some things.

Average rating
 
 
 
 
 
(0 votes)

Similar entries

  • MyTop mysql Top Processes viewer

    MyTop mysql Top Processes viewer

  • Oracle buys Innobase

    Oracle buys Innobase — Mysql dies?

  • Why are you using MYSQL? - High Performance MySql for optimization, backup, replicaton and load balancing

    Q: Why are you using MYSQL? A: Because it was there (ed: on the webspace) and free I guess that's how many zillions of people started using MYSQL – including me. ordering a 40$/yr space already includes mySql and…

  • MySql backup yourself - Cpanel corrupts MySql backup

    After weeks of investigation by the admin support staff we only came to the single conclusion … the mysql backup of the WHM/Cpanel backup feature simply corrupts the backup after some megabytes or a few million records… not designed for…

  • How Drupal does Harakiri (aka Kill Itself)

    Over the last 2 weeks I’ve had problems on three sites with the exact same problem.

    The watchdog table grew FASTER than the cron job (which should delete old log entries older than e.g. 3 days) would have deleted it.

    This again ended in a “device full” last night when one watchdog table became 5 gigs.

    (and I had a “table full” problem on another site last week – tough 64bit MYSQL should be able to do 4TB spaces, that one was 4GB only)

    Now, it’s obvious that disk full problems shouldn’t be a problem for a content management system like Drupal to take care about,