MySQL Database Problems - Growing too quick, Performance

Question

I have some problems with my database setup: The MySQL Database is written to every few seconds and it grows in size very quickly. My disk space is limited though, so I have to work around that. However, the data should not be that big. It's 3 tuples of type DOUBLE and their auto_increment Primary key which are written to the Database. It already contains about 12M rows (in one table!). The server has only 512MB of RAM and queries are taking very long.

Now, every time I mysqldump the DB, the dump file is way smaller than the database itself. How is that? Should I just remove all foreign key relations to increase performance? Is there a way to distribute the Database over multiple servers?

Thanks in advance.

Answer 1

mysqldump outputs the data as text, so the size of the data will almost always be different from the table size. The size of the table that you're seeing probably includes the indexes as well, which don't get dumped.

The dumps are probably smaller because the indexes are not in the dump and the data is probably relatively short i.e. 1.24 and not 168535.186434965355732. If the data is short, that can have a huge effect on your dumps. The DOUBLE data type uses 8 bytes. So in the table, 1.2 would require 8 bytes, but in a dump, for just the data, it needs just 3 (it is 3 text characters).

For the performance of your database, there is probably a lot that could be done, but exactly what could be done depends heavily on your system. EXPLAIN your main queries to make sure they are using indexes. I believe that in most situations, foreign keys are just detrimental to performance. If you can trust whatever is using your database to not input bad values, you probably don't need them. Any time you insert into a child foreign key column, the server has to check the parent to make sure the new value is valid, which probably won't cause a huge performance hit, but it usually isn't necessary.

You probably would have mentioned it if you were doing it, but dumps are usually compressed since they are highly compressible (usually to 10-20% of the original size with gzip). That would cause the dump to be significantly smaller.

Answer 2

Yes there is a way to distribute over multiple servers but heavily depends on budget the scale you want ..take a look at

• http://blog.rimuhosting.com/2011/07/06/building-database-clusters-with-mysql/
• http://beerpla.net/2008/03/26/setting-up-a-mysql-cluster/

Should get you started at least