5 ms·

I initially wanted to settle on MySQL for the sake of simplicity. But got bitten twice by it and have since changed my mind. a) Once I had to store a large chu

by 1gor 18y ago

I initially wanted to settle on MySQL for the sake of simplicity. But got bitten twice by it and have since changed my mind. a) Once I had to store a large chunk of time series data in MySQL 'TEXT' field. Got errors in the application, could not figure out where they are coming from. Turned out, MySQL has 1 to 65535 characters limit for TEXT field, and I should have used LONGTEXT type. MySQL has simply bitten the tail off my data and happily stored the rest without any errors or warnings. OK, I should have RTFM but still, the idea of a database corrupting my data without any warning makes me very uncomfortable... b) Second issue that I've discovered is simply the ability to make 'hot backups' to SQL text files. pg_dump will produce consistent backup from working db server, mysqldump requires you to stop the server or to lock its tables etc. 'Hot backup' of mysql databases can be achieved through other methods/tools but at the cost of greater complexity.

rbanffy 18y ago
"MySQL has simply bitten the tail off my data and happily stored the rest without any errors or warnings. OK, I should have RTFM but still, the idea of a database corrupting my data without any warning makes me very uncomfortable..." That is just insane. I heard about it a couple months ago and wondered what were those folks smoking when they wrote it like that. The hot backups thing never crossed my mind, but is very interesting. Of course, it relies on transaction consistency and MySQL has the option (shivers) of not having it, thus the locking demands. From what I know (I am a PostgreSQL guy too), when you use a transaction-aware data store in MySQL, these behaviours are gone (along with any performance edge it may have over PostgreSQL, but that's another story), so, you may want to give it a try.
apathy 18y ago
> the ability to make 'hot backups' to SQL text files. pg_dump will produce consistent backup from working db server, mysqldump requires you to stop the server or to lock its tables etc. 'Hot backup' of mysql databases can be achieved through other methods/tools but at the cost of greater complexity. Run a slave, stop it, do mysqldump --single-transaction --master-data yourdbname off the slave to get a hot backup, and then restart the slave to get hot copies without shutting down writes. (I am going to assume that you round-robin your data into memcached from the read slaves if you are worried about a read lock on the master) Not saying that you shouldn't use Postgres, but if you have to work with MySQL, the technique above produces consistent hot backups from 4.0.18 onwards.

by 1gor 18y ago

rbanffy 18y ago

"MySQL has simply bitten the tail off my data and happily stored the rest without any errors or warnings. OK, I should have RTFM but still, the idea of a database corrupting my data without any warning makes me very uncomfortable..." That is just insane. I heard about it a couple months ago and wondered what were those folks smoking when they wrote it like that. The hot backups thing never crossed my mind, but is very interesting. Of course, it relies on transaction consistency and MySQL has the option (shivers) of not having it, thus the locking demands. From what I know (I am a PostgreSQL guy too), when you use a transaction-aware data store in MySQL, these behaviours are gone (along with any performance edge it may have over PostgreSQL, but that's another story), so, you may want to give it a try.

apathy 18y ago

> the ability to make 'hot backups' to SQL text files. pg_dump will produce consistent backup from working db server, mysqldump requires you to stop the server or to lock its tables etc. 'Hot backup' of mysql databases can be achieved through other methods/tools but at the cost of greater complexity. Run a slave, stop it, do mysqldump --single-transaction --master-data yourdbname off the slave to get a hot backup, and then restart the slave to get hot copies without shutting down writes. (I am going to assume that you round-robin your data into memcached from the read slaves if you are worried about a read lock on the master) Not saying that you shouldn't use Postgres, but if you have to work with MySQL, the technique above produces consistent hot backups from 4.0.18 onwards.