In my new role, I am playing around with mysql for managing our data, which is not very huge per se, but since we are running our db on tiny hosts (2GB memory etc), the performance tuning requirement is significant.
Now, being a former Sybase IQ developer, I cannot help but compare Sybase IQ with MySQL. Some of these comparison points are also inherent differences between a row-based and a column-based DBMS (column-based dbs rule!!), and I do not claim to be unbiased :) Being a amateur in MySQL, I might be missing some better ways in MySQL, feel free to point out!
1. In a row based DB, it's always a compromise between wide tables (and hence huge IO cost), and expensive joins.
We want to store de-normalized data in our datawarehouse tables, to accelerate our query performance. But that makes the table very wide, making the queries slow due to huge IO in even querying a few columns. While, if we break the table, we end of joining them while querying, again expensive. Column-based Sybase IQ is an answer!
2. In MySQL, your query will use only one index per table in a query!
Probably, this is something obvious for a row based DB, but it came as a shock to me. Being used to Sybase IQ way of using indexes, I was happily creating individual indexes on columns assuming that the query will use all that are required. (IQ gets found-sets using multiple indexes and use them for reducing selected sets). But MySQL does not do that. Result: my table has something like 6 indexes with various combinations of 3 columns, while all I would have need in IQ would be 3.
3. Mysql writers block readers.
We use INNODB, which claims to have row level locking. Also the mysql documentation says that if you are using a repeatable read isolation level, your reading transaction will work on a snapshot of data. But I never saw that in action. There are numerous "special cases" where INNODB takes a table-level lock for writing (auto-inc columns, probably duplicate key update), which ends up into the query getting blocked, waiting on lock to be released. IQ has "snapshot-versioning", not "locking", which means your readers will "never" be blocked on writers, unless you explicitly say so by locking table.
4. Mysql backup is great.
I can say that mysql is better than probably any other dbms in one thing. Simplicity. You can backup, migrate mysql dbs very easily by using mysqldump. IQ's backup procedure was confusing, even for me as a developer.
Subscribe to:
Post Comments (Atom)
Posts
4 comments:
You cannot compare InnoDB or MyISAM to Sybase IQ. MySQL has an open storage engine architecture: InnoDB and MyISAM are only 2 examples of what you can find on the market. If you want a comparable product to have to look to Infobright, the column-based storage engine for MySQL.
Have a look at it, and you'll change completely your post :D
Hey Massimo,
Thanks for pointing that out. I do know about Infobright. But, I did want to compare Mysql INNODB with IQ, because that is what we use in our org. So, it was actually meant to be more of a popular row-based vs column based DB comparison, than Mysql vs Sybase.
Though, I agree that Infobirght is worth a try for our apps. Last time I looked at it, I felt that there are serious limitations in the "free" version.
Absolutely, I can understand your point. But InnoDB is designed for OLTP applications, is not suitable at all for DWH... For small datamarts you can consider to use MyISAM instead due to the high load speed and fast reading...
You are right. We were using MyISAM earlier. But, our ETLs were blocking queries because of table-level-locking. That's why we switched to INNODB.
Post a Comment