Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
    
Look up the tables you're using in the information_schema.tables table and check the sizes columns. Also, what engine are you using for your tables? –  golimar Jan 29 '13 at 14:20

2 Answers 2

up vote 2 down vote accepted

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.

share|improve this answer

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

Should get you started at least

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.