MySQL vs SQL Server in Data File Management
December 22nd, 2007 by Andrew Chen
Both MySQL and SQL Server are very powerful database software. SQL server is Microsoft product. MySQL is an open source database solution. Both databases are widely used. SQL Server is favored by many financial organizations. MySQL server is used my virtually every start up companies and the DotComs. In fact MySQL is the best open source database in the market and you can download it from mysql.org and use the software for free.
If you ask which database is better? I would say SQL Server is definitely better if money is not a factor in consideration.
I say that based on my own experience with SQL Server and MySQL. One of the differences between MySQL and SQL Server is how they mange their data files. For SQL server you can place the data file of a database wherever you want. You can move the file of the databases with relatively easy operation like this. Detach your database, move the database file to your target location and then attach it. That is it. For MySQL server you can not just move the data file of a single database. You have to move all of your database file together. Because the data file location for MySQL is not per database. It is per instance. All data file of a MySQL instance has to be located in one file system directory. Usually the directory is called Data. And each database is a subdirectory of the data directory. Each SQL server database has its own log file but MySQL server database has one log file for all databases.
Another thing that MySQL server is inferior to MS SQL server is that it is very difficult to shrink a data file. It is virtually impossible to do that for very large table in a production environment that support transaction.
This is my own experience again. I originally set up a MySQL database with most of the tables are InnoDB type. The MyISAM database engine of MySQL doesn’t support transaction. There was a very large InnoDB table containing 100 million rows. The size of the table was about 10G of data and 18G of index. The table grew very fast and in order to prevent it from growing too fast I delete the data from table if the data is older than certain period of time. I thought if I did that the data file would not grew but it was not true. It turns out that MySQL will not release the empty space of a data file to the OS. The only way to shrink a data file is to dump the table out and restore it back to MySQL. Backup and restore operation in MySQL is substantially longer than backup and restore a MS SQL database. That is something I can’t do for a 24/7 system. MySQL does reuse the empty space in the data file though so it make sense that if you delete the data the data file will not grow. But in fact the empty space can be reuse only when it is not a fragmentation. If you delete the data randomly then you will leave a lot empty gap within the data file. Fragmented empty space can not be reused. So you will have to defragment the data file first. But defragmentation again a costly operation, impossible for large table in 24/7 system. It seems to me the only solution is to keep adding disk space when it is necessary or to certain point when you can not sustain it take down the system for a day. Another way to deal with it is to delete the data form the table according to the primary key sequentially then you will leave contiguous empty space in the data file so that MySQL can reuse it. Complex isn’t it. For SQL Server I can optimize (degragment) the entire 300G database in 8 hours. It is a straight forward operation. MySQL still has long way to catch up with that.
Related Posts:
(No Ratings Yet)
Loading ...
Thanks for the info. I’m a SQL Server fanboy myself, but also need to support MySQL. I kept searching for how to move my data files around. Alas, it seems I cannot.