Encouraged by Baron Schwartz tip on result set comparison, here are a few more, on the same vein.
First, you can send a result set to a file. Probably you will say "yeah, I know, using SELECT INTO OUTFILE". Correct. Except that you can't rewrite to an existing file, if you want to, and you will get a raw output, not the well formatted one that you usually see on the command line. For example:

mysql > select 1 into outfile '/tmp/f1.txt';
mysql > \! cat /tmp/f1.txt
1

mysql > select 1 into outfile '/tmp/f1.txt';
ERROR 1086 (HY000): File '/tmp/f1.txt' already exists

BTW, \! command is a handy shortcut for executing a shell command.
Let's see what happens with the alternative …

At the March Boston MySQL User Group meeting, Jacob Nikom of MIT’s Lincoln Laboratory presented “Optimizing Concurrent Storage and Retrieval Operations for Real-Time Surveillance Applications.” In the middle of the talk, Jacob said he sometimes calls what he did in this application as “real-time data warehousing”, which was so accurate I decided to give that title to this blog post.

The slides can be downloaded in PDF format (1.3 Mb) at http://www.technocation.org/files/doc/Concurrent_database_performance_02.pdf. The 54 minute video can be downloaded (644Mb) at http://technocation.org/node/693/download or streamed directly in your browser at http://technocation.org/node/693/play. …

Hi again!

As a sidekick for my previous post, I came up with a snippet of code that generates the Google Chart URL to visualize table size for the current database. For example, for the sakila sample database, we get URL's like this:

 …

At the March Boston MySQL User Group meeting, Jacob Nikom of MIT's Lincoln Laboratory presented "Optimizing Concurrent Storage and Retrieval Operations for Real-Time Surveillance Applications." In the middle of the talk, Jacob said he sometimes calls what he did in this application as "real-time data warehousing", which was so accurate I decided to give that title to this blog post.

The slides can be downloaded in PDF format (1.3 Mb) at http://www.technocation.org/files/doc/Concurrent_database_performance_02.pdf. The 54 minute video can be downloaded (644Mb) at http://technocation.org/node/693/download or streamed directly in your browser at http://technocation.org/node/693/play.

…

Abstract - As described by Walter Heck, MySQL database size can be visualized using Google Charts. With a minor code improvement the URL for the chart can be obtained twice as fast. With some more modification, the number of lines can be cut down resulting in a function that is half as long.

Hi!It's been a while since I posted - I admit I'm struggling for a bit to balance time and attention to the day job, writing a book, preparing my talks for the MySQL user's conference and of course family life.

A month ago or so I …

I mentioned in my previous post that recently I have been working with web performance. Often when working with clients they want fast performing web sites but then proceed to give you all manner of other requirements which slow the site down to a crawl. Convincing a client to give you the time to look at performance isn't easy, while the gains in speed are there for all to see attributing some monetary value to that is close to impossible. In the longer term you might see an up turn in the traffic levels or a higher percentage of people taking a longer user journey on your site but whether that was a result of higher performance is very difficult to prove.

Thankfully one of our larger clients could see the benefit of having a dedicated resource looking at performance issues, all be it as a result of such poor performance that the sites became difficult to use during peek times. When a client keeps throwing stuff at you it's easy to …

Wow, is it really that long since I updated this thing. I can't believe that it's been close to 3 years since I updated it regularly, but I suppose it is as I have been in my current job for that long and it was a transition from MySQL back to Oracle (and a lot more work) that meant I stopped posting in the first place.

The good news is I'm moving back to working with MySQL after 3 years away and that should give me some more opportunities to update the blog. While there have been some highlights over the 3 years much of the work I have been doing has been maintenance and the normal daily grind of work so it didn't offer that much that was interesting. Of late I have been doing some really cool stuff with web performance but I've been out of the blogging for so long I never really thought about sharing it.

Of course any regular readers will be long gone but the blog still ranks pretty high in Google so maybe some folks …

The best way to see if your server is doing well and check that you have optimized its power it so have server statistics!

We finally have some nice graphs that let you see your server's CPU usage, network traffic, and your disk's read-write usage. These graphs will give you precious information on how your server is used, and when various usage peaks or low-points occur, so that you can optimize your services.

Below is an example of a server that is rather "active". You can see that the processor is only using on average 16% to 18% of its power (over a 24-hour period), with a peak at 40%. The server is running on 2 shares, and is therefore clearly too powerful for its current needs.

The following example shows the contrary. This server is in serious need of additional shares:

In the image below, you can see that the data disk /xvdb/ performs far more writing (light orange) than reading …

Stopping All Servers
All 221 tests were successful.
The servers were restarted 14 times
Spent 1424.921 of 1521 seconds executing testcases

(All tests have passed on OpenSolaris on x86 for a while now).

As larger and larger amount of memory become common (512GB is something you can fit into relatively commodity server this day) many customers select to build their application so all or most of their database (frequently Innodb) fits into memory.

If all tables fit in Innodb buffer pool the performance for reads will be quite good however writes will still suffer because Innodb will do a lot of random IO during fuzzy checkpoint operation which often will become bottleneck. This problem makes some customers not concerned with persistence run Innodb of ram drive

In fact with relatively simple changes Innodb could be made to perform much better for memory resident workloads and we should consider fixing these issues for XTRADB.

Preload It is possible to preload all innodb tables (ibdata, .ibd files) on the system start - this would avoid warmup problem and also make crash recovery fast even with very large …