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 …

So, you’ve emailed MySQL Support, they’re working on the problem you’re having. How are they working? What tools do they use? Well, here’s my list:

• IRC – All MySQL Support Engineers work on IRC, it’s our main communication medium. While you’re only getting emails from one Engineer, it’s quite likely they’re consulting with several others at the same time. Many pairs of eyes catch all of the details.
• MySQL Docs, Changelogs, Knowledge Base, Google, etc – There’s a lot of information out there, far too much for any one person to keep in their head at once. So, we have extensive documentation that everyone can access, plus the Knowledge Base available to customers. Also, given that MySQL is a very open project, we have plenty of community members who write about their experiences.
• MySQL Sandbox …

Sun Microsystems today announced that SPEECH DESIGN, a leading European provider of messaging and mobility solutions for businesses and network operators, has selected Sun's MySQL™ Cluster database to promote carrier grade availability, performance and scalability of an innovative new messaging offering with reduced costs and faster time-to-market.

OurDelta build for MySQL 5.0.77 with patchset d8 is now available, in source and packaged binaries for RHEL/CentOS 4 and 5, Debian 4.0 (Etch) and 5.0 (Lenny), Ubuntu 8.04 LTS (Hardy) and 8.10 (Intrepid).

If you already installed the OurDelta repository information, yum update or apt-get upgrade will install the updated packages for you. Please do review the notes below.

Fixes, Changes & Additions:

• With Percona moving the patches it maintains to using bzr/Launchpad, we now simply pull those in as a subdirectory percona_maintained. The old separate patch dirs are …