Category: MySQL

MySQL InnoDB, one file problem

I installed new server about 6 months ago and now found problem with MySQL InnoDB. I add and delete a few big InnoDB tables, but space of InnoDB still grow. I found a lot ideas how to fix it (dump all DB and restore back), but the best way is to add to MySQL config file “innodb_file_per_table”.

TODO:

  • Stop the MySQL server (usually “/etc/init.d/mysql stop” on Linux).
  • Edit the my.cnf file (located at somewhere like /etc/my.cnf or /etc/mysql/my.cnf)
  • Add “innodb_file_per_table” on a single line by itself into the my.cnf file.
  • Start the MySQLserver (“/etc/init.d/mysql start”).

Any new InnoDB tables that are created will now be created in the database’s directory with a single file per database table. Doing this does not affect any existing InnoDB tables they will still use the common data file.

My 2 first projects with PHP and MySQL in 2001 and 2002!

I found DVD disk with 2002 backup from server and I found 2 my first projects:

1. Ozas.bst.lt – this project was made in 2001, with PHP, JavaScript and etc. No MySQL, all data was saved in TXT file’s. That is my first internet project and it still looks very good!

2. Ozas.bst.lt/WAP – it was time, when Nokia 3310 was the best phone in the world, we also had GSM data transfer (PC at that time had 56K), but then came to Lithuania GPRS data transfer.. and also came Nokia 3510i! That was amazing phone, TFT color screen, very good battery and etc, so I buy this new phone and for him made WML (no xHTML support at that time) WAP site with HTML support for PC browsers (that works very good and now). This site had every night about 100-150 peoples online with phones and they talk in chat rooms, forums, PM messages and more.

Sorry, but site’s have only Lithuanian language support, but still, you can check how it looks in 2001-2002, amazing.

Then I open both sites.. it something.. I don’t know how to say, that was made about 10 years ago, at school, home.. is just fantastic. I will try to save for future.

Index on big MySQL table? No problem!

I know a lot companies in Lithuania who if need index on big table call sysadmin, but when we try to call our sysadmin to add index on big table.. that was bad idea.. because we got a lot of locked queries and after 10-30 min server was dead.

Now add index on big MySQL table is easy, just try this tool:

http://code.google.com/p/openarkkit/source/browse/trunk/openarkkit/src/oak/oak-online-alter-table.py

Example in console:

./oak-online-alter-table.py -a “DROP INDEX old_index_name, ADD INDEX ( \`OrderID index\`, \`OrderID\` )” -d database -t table -H localhost -u root –ask-pa

We try on XX GB tables, works fine! Just try it!