Optim MySQL: OLAP Paradise – WITH ROLLUP
Though this is an age old concept, but after seeing the ignorance factor of many regarding this, I thought of writing a bit about it. ROLLUP is a GROUP BY modifier that adds extra rows to the output which contain summary data. At each level of aggregation the aggregated column is filled with a NULL value.
MySQL AB :: Follow Up on Rolling Sums – Self Joins vs User Variables
What about using a user variable to make the rolling sum from the beginning? Something simple enough like this:
When you do a SHOW SLAVE STATUSG and you see errors you need to resolve them. One way is to simply skip the offending statement.
SLAVE STOP;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
SLAVE START;
Repeat as desired.
CocoaMySQL and access into a MySQL Database:
CocoaMySQL is a tool similar to what InstantSQL was for Rdb
CocoaMySQL is a tool similar to what InstantSQL was for Rdb; a tool where you could access and modify the contents of the database remotely. This is very useful for exploring an existing database, and also useful for patching a database.
MySQL and the The Death of RAID:
RAID is dying. Shocked? The prediction might be a bit early for some folks. It’s still somewhat conventional for some people to think that RAID is a conservative way to scale your IO.
I’d like to assert that in 3-5 years RAID will be a thing of the past.
Want some evidence? Google doesn’t use RAID. They’ve build a database infrastructure which avoids expensive and proprietary hardware controllers.
You could call it a redundant array of inexpensive servers.
Other scale out shops which don’t have access to such toys have built out sharded MySQL installations. LiveJournal, Flickr, Facebook. These shops are using RAID in some situations but they are only using them due to the nature of MySQL scalability limitations.
For example, most MySQL shops don’t have failover master replication setups so they usually invest in more expensive RAID 10 controllers for their database to reduce master downtime.
Imagine for a moment that you had stable automated master promotion. A lot of people are playing with is now (we think we have it solved as well) and hopefully it will become commonplace.
If your master fails you just promote a slave.
So why do you need RAID 10? It’s twice as expensive!
If you wanted to you could use software RAID 0. It’s just as fast and more than 1/2 the price! Probably 1/3rd the price if you factor in the price of the RAID card. Now you can buy another server!
Why stop there. Just ditch the RAID setup altogether. No software RAID.
But how do you utilize all those disks you ask? Good question.
Run parallel MySQL installs!
More and more MySQL implementations are rolling their own sharded database technologies.
Imagine you had an Opteron box with 16G of memory, 4 HDDs, and four cores.
Instead of running only ONE MySQL instance you could run four on this box (one for each HDD, core, and 4G of memory).
This has a lot of ideal properties.
Each instance is a member of a shard within a larger federation. You don’t have to worry about idiosyncrasies like RAID chunk size tuning. If a single disk dies you don’t lose the whole box. You only lose that member of a shard. So if one disk dies you only lose 1/4th of 1/Nth of the servers in your cluster.
The key win though is the fact that you can get significantly higher disk throughput. The binary log, write ahead log for INNODB, and data can utilize one disk. In our benchmarks taking two 100MBps disks and running them on RAID 0 only gives us about a 50% performance boost.
In this setup we scale linearly with the number of disks. We can now get 400MBps of IO with four disks. Not too shabby. If we were to run RAID 0 we wouldn’t see any where near 400MBps.
One difficulty lies in configuration. You’d have to run four MySQL processes. The easy solution here is to run one per port (3306-3309).
Another potential solution would be to run virtualization software like KVM or XEN. This would increase your complexity but you’d be able to avoid configuration difficulty. You’d also need to rely on the performance of your virtualization software.
One area where this does fall over though is with battery backed write caching controllers. It would be interesting to benchmark RAID 0 with a caching controller vs four independent MySQL instances across four disks.
Thoughts?
(Via Planet MySQL.)
Notes
1. This is not an “anti-MySQL” list, although it does contain critical comments;
2. MySQL is continually improving and some of the points listed here are no longer valid for recent versions;
3. This list is regularly updated, but may not always reflect the latest developments (unfortunately I’ve been busy the past couple of months and haven’t had time for the site at all; watch this space ;-). Note to Slashdot readers: this page deals with issues related to MySQL 4.1 and earlier, not 5.0
Why IS NULL doesn’t always work in MySQL at Xaprb
How can a query like “SELECT * FROM tbl WHERE col IS NULL” return a row where the column has a (non-NULL) value? Read on to find out.
How to write MySQL conditional comments at Xaprb
MySQL’s version-specific conditional comment syntax confused me for the longest time. Then I learned about printf formatting rules, and it all became clear. Read on if you don’t already know what I mean.
Technical Notes and Articles of Interest » Reading the MySQL Manual
The obvious answer was SELECT ROUTINE_NAME,ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES, given I like to use the INFORMATION_SCHEMA whenever possible. This lead me to think was is the corresponding SHOW command. A quick manual search got me to SHOW CREATE PROCEDURE.
What was interesting was not this, but the list of other SHOW commands I didn’t know. I did not know about SHOW MUTEX STATUS, SHOW OPEN TABLES and SHOW PROCEDURE CODE for example.
Sheeri Kritzer » Blog Archive » Data Warehousing Tips and Tricks – The MySQL She-BA
It’s not easy to do a DW in MySQL — but it’s not impossible either. Easier to go to Teradata than to write your own.
I need to learn more about MERGE TABLES and INSERT … ON DUPLICATE KEY UPDATE