At the 2008 MySQL Conference and Expo, The Pythian Group gave away EXPLAIN cheatsheets. They were very nice, printed in full color and laminated to ensure you can spill your coffee* on it and it will survive.

For those not at the conference, or those that want to make more, the file is downloadable as a 136Kb PDF at explain-diagram.pdf

* or tea, for those of us in the civilized world.

10,000 Tables Can’t Be Wrong: Designing a Highly Scalable MySQL Architecture for Write-intensive Applications by Richard Chart

Chose MySQL for performance and stability, and less important but still there, experience and support. Support is becoming increasingly more and more important.

Starting point: 1 appliance supporting 200 devices
Problem/Goal: Extensible architecture with deep host and app monitoring, over 1000 devices with 100 mgmt points each
Distributed collection over a WAN, with latency and security concerns
Current reality: several times the scale of the original goal
Commercial embedded product, so they actually pay for the embedded MySQL server

Future: The fundamentals are sound: next generation of the product moves up another order of magnitude

Data Characteristics
>90% writes
ACID not important
Resilient to loss, because gaps in data do not invalidate the rest of the data
Data elements by themselves are valuable, but much more so when relationships are added.

Chose MyISAM because: (more…)

Jeff Rothschild of Facebook’s “A Match Made in Heaven? The Social Graph and the Database”

Taking a look at the social graph and what it means for the database.

The social graph:

• At it’s heart it’s about people and their connections.
  
• Learning about people who are in your world.
  
• Can be a powerful tool for accelerating the use of an application.
  

“The social graph has transformed a seemingly simple application such as photos into something tremendously more powerful.” We’re interested about what people are saying about us, and about our friends. Social applications are compelling.

Facebook users blew through the estimate for 6 months of storage in 6 weeks. It is serving 250,000 photos per second at peak time, not including profiles. Facebook serves more photos than even the photo sites out there, and serves more event invitations than any other website out there.

E-mail invitations are an example of the power of the social graph. If you get a newsfeed or an invitation that tells you 12 friends are attending an event, you have more information, and then can have a better decision on whether or not you want to go. (more…)

If you’ve read many of my blog posts, you know that I consider lack of procedure cache control to be a major SQL Server pain point. Badly written apps that use non-parameterized ad hoc queries can quickly flood SQL Server’s memory pools and bring the server to its knees.

SQL Server 2005 brought some relief in the form of the Forced Parameterization database option, and SP2 took things one step further with better throttling of the cache… but it’s still not enough. We want a knob!

The bad news: We’re not getting quite the knob I was hoping for.

The good news: SQL Server 2008 will include an sp_configure option called ”optimize for ad hoc workloads“.  This option will cause the procedure cache to only cache the parameterized stubs for ad hoc queries, rather than the full query with parameters.  This means that applications passing a large number of non-parameterized batches should see much lower procedure cache memory utilization and, therefore, better overall throughput.  I’m really looking forward to seeing this in action; this feature should be added with the next pre-release drop.

Remember, there is simply no substitute for properly designing your application’s data access layer, but hopefully this will help for those applications that simply can’t be changed…

How creative are you with manipulating your queries to produce more efficient plans? Try the following puzzle and e-mail your solution to me at [<my last name> @ pythian.com]. Make sure to include an explanation of why it works, as well as your mailing address. The best two solutions/explanations win a free copy of Expert SQL Server 2005 Development, a wonderful feeling of accomplishment, plus eternal fame and glory when I reveal your solutions here on the blog.

Run the following T-SQL to create two tables in TempDB:

USE TempDB
GO

CREATE TABLE b1 (blat1 CHAR(5) NOT NULL)
CREATE TABLE b2 (blat2 VARCHAR(200) NOT NULL)
GO

INSERT b1
SELECT LEFT(AddressLine1, 5) AS blat1
FROM AdventureWorks.Person.Address

INSERT b2
SELECT AddressLine1 AS blat2
FROM AdventureWorks.Person.Address
GO

Now consider the following query:

SELECT *
FROM b1
JOIN b2 ON
    b2.blat2 LIKE b1.blat1 + '%'

This query takes around three minutes to run on my notebook, and does over 1.8 million logical reads. Can you figure out a way to re-write it so that it performs better? No modification of the base tables or addition of any other objects is allowed (sorry, no indexed views!) — the challenge is to tune this by doing nothing more than re-writing the query.

Good luck! I’ll leave the contest open for submissions until May 1.

For the past few months I’ve been using a query that I refer to as “ash report - recent spike”. That’s the second thing I do when I get a call of the “the system is slow” type. The first thing I do is run “top” (or whichever alternative for the OS) and check the overall CPU usage.

The script is fully RAC-aware, and although it’s not 100% perfect, I use this imperfection to see if any particular node is doing something stupid. Although it is primarily targeted for OLTP systems, it can be useful for data warehouses as well, especially if they use the parallel option.

The query is to the database what “load” (uptime) for a Linux/Unix machine is, except it has much more detail. It is basically a summarization query of the v$active_session_history table. NOTE: you need to have the performance pack license to use it. It is not designed to be aligned, or read. The best is to leave it on just a few lines and concentrate on the results.

It has two “variables” that you can adjust: how far back to look (I use two hours), and how aggressively to look for problems (having count(*) >= 2).

An explanation of how to make sense of the results follows the query.

(more…)

There’s no video for Jacob Nikom’s December 2007 Boston MySQL User Group meeting, but the slides for “Measuring MySQL Server Performance” can be downloaded (2.33 MB) at http://technocation.org/files/doc/Measuring_MySQL_server_performance_03.ppt

And with that, this is (I believe) post #10,000 at Planet MySQL!

SQL Server has a variety of features that let developers and DBAs take advantage of minimally logged inserts and leverage the Bulk Copy API. This is an extremely important area to understand when doing large data loads or moving data between tables, databases, or servers, but I have found that many DBAs don’t understand what’s actually happening under the covers, and/or don’t realize that taking full advantage of these interfaces requires playing by a couple of special rules.

In short:

1. There is no such thing as a “non-logged” transaction in SQL Server (a very common myth)
2. Minimal logging means that only extent allocations will be logged, rather than the actual data copied to the destination. This is a huge plus when moving enough data to fill many new pages… but again, it’s not a lack of logging, just less logging
3. In order to take advantage of minimal logging (as of SQL Server 2005), the bulk copy operation must use a table lock, and the table must have either no rows or no indexes

For more detail on all of this, please see the following two recent posts from the SQL Server Storage Engine blog:

• What are the Bulk Import Optimizations?
• Bulk Import Optimizations (Minimal Logging)

And all of that is great, but it’s not what prompted me to post today.

Background: SQL Server has long had a feature called SELECT INTO that lets you do a minimally logged insert via a SELECT, but it requires that the target table does not exist (it will be created by the SELECT). This feature is a bit limiting; you can’t target a specific filegroup, you need to use somewhat awkward CASTs or CONVERTs to make sure the target columns have the right scale/precision (if you’re working with variable-length types), and you can’t pre-create indexes or constraints.

After spending a long time on a project for a client that does a huge amount of data movement using SELECT INTO, I posted a Connect item asking for an enhancement where a minimally-logged insert would be available from a SELECT doing an INSERT into an existing table. And I waited. And I waited some more. And then one day the item was updated with a comment: “This is targetted to be available in SQL Server 2008.”

I wasn’t sure exactly what this meant until the last few days, when Sunil Agarwal posted a series of three posts (one, two, three) describing the enhancements to minimal logging in SQL Server 2008 — including the ability to do a minimally logged insert via a SELECT, to an existing table!

This is a fantastic enhancement, and one that I am certain some of my customers will be able to make great use of. I have been kind of lukewarm on many of the SQL Server 2008 enhancements, but this one goes immediately into my top 5 and is one of those things I will bring out when certain customers ask me whether it’s worth their time to upgrade. Cheers to the Storage Engine team for making this happen!

We’ve been running into a problem with one client:

SELECT COUNT(*) FROM tbl;

takes 0.25 seconds on one db, and 0.06 seconds on another.

Consistently. That’s a fourfold difference.

There aren’t any significant configuration differences (like query cache, etc.), the software versions are the same, and the table fits into memory. This has been looked at by at least 3 in-house MySQL experts, and the only thing we can determine is that it’s a hardware difference.

The table fits into memory so it’s not a disk issue, and the only other difference among the hardware is that the slower machine has Solaris virtualization in place in the form of “containers” (cpu is the same, etc). Is this something that’s known to cause issues with speed? The “tbl” in question is an InnoDB table, if that means anything. Is there something like the “speed” of RAM?

Note that Sun has already been called in, and scripts have been run and analyzed on their side and they say “must be a db config issue” even though the configs have no significant differences (and neither does the hardware and OS spec).

Today is Hotsos Symposium 2008 Training Day — one full day with Tom Kyte. I haven’t registered for it so I took the chance to sleep until 10 this morning which was excellent idea considering that last night we were quite late going to bed thanks in parts to the joined demo that James Morle and Mike Erwin organized at the last presentation yesterday. I was in the James’ session and he was demonstrating how to hide latency problem with batching. I suspect that Mike, in the next hall, was showing the impact of MTU settings on cluster interconnect. The end result is that beer bottles travelled between the presentation halls and James ended up with about 3 packs of Guinness and Shiner Bock. That what kept us up longer last night.

James’ presentation itself was excellent — he explained that all performance problems can be caused by either skew or latency. You can’t normally fix skew issue so you just need to be aware and account for it. Latency can sometimes be shortened but usually insignificantly or it’s impractical (i. e. very expensive). It’s also very important to distinguish bandwidth and latency. I like his idea that the efficient way to solve latency is hiding it and there are generally two ways to do that — batching and threading. Improving bandwidth often doesn’t cause any performance improvement without taking latency into account. Very insightful talk. Thanks James.
(more…)