Solaris Containers and MySQL

Mar 17, 2008 / By Sheeri Cabral

Tags:

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).

10 Responses to “Solaris Containers and MySQL”

  • Chen Shapira says:

    Do you actually have multiple systems on the machine with containers?
    If so, it is likely that you are not making full use of CPU cache, which can easily cause noticeable performance issues.

    I’d check with Sun support if they have a tool that will allow you to compare how long pages stay in cpu cache between the systems.

    I’m not sure which specific sun server you are using, but if it is NUMA architecture, there are different RAM speeds involved.

  • Jay Pipes says:

    Weird. Though, must be noted that doing a SELECT COUNT(*) on an InnoDB table with no WHERE clause on an indexed column is a death sentence when the table has more than just a few thousand rows…

    [[Sheeri's answer]] True, but the point is to get a baseline. We were seeing it for more complex queries and we just wanted a very simple query on commandline to test. :)

  • Justin Swanhart says:

    Even though the boxes may be identical hardware, that hardware might be configured differently.

    Check RAID block sizes, filesystem block sizes and make sure all the sysctl variables are the same for both machines.

    This could be a filesystem fragmentation issue. Try copying the table to a temporary (memory/shm based) filesystem on both machines and see if the performance characteristics change.

  • K says:

    Interesting. Let’s start with the basiccs here. I assume you looked at EXPLAIN? And truss -c? After that, I think running MySQL in a debugger will give the definitive answer to why this is happening.

  • Frank says:

    Hi Sheeri,

    Which servers are these? V440? T1000? or X4100 series?

    Frank

  • Nitek says:

    Did you try to dump the database and reimport it? A few weeks ago i heared about someone having a similar problem, when using databases from older MySQL versions with on a server with a newer version. Dumping fixed that (something about storage format, can’t remember the exact explanation)

  • John says:

    While I’m no expert on the internals of the optimizer, is it possible that the optimizer is taking a slightly different access path for the data? There’s also the issue of I/O to consider. I’ve not yet played with containers in any depth so I can’t comment on I/O configuration.

  • Sheeri Cabral says:

    Frank,

    The client says:

    “Our dedicated X2100 is indeed newer M2 architecture with 667MHz DDR2 RAM vs. 400MHz from X4800 1st generation” but goes on to wonder if that could make the 4-fold difference in even simple queries.

  • I agree with the earlier comment via truss, or even dTrace.

    Is MySQL doing the same thing every time, are you sure it’s always reading from memory. Reviewing SHOW STATUS difference before and after will just confirm, yes it’s all in memory, and MySQL is acting the same way. The easy part.

    Truss with truss -o out.txt -rall -wall -p [pid] to see what internally mysql is doing.

    Dtrace under Solaris is obviously another good reference, but I’ve got very little experience here.

    Have you been for example to run a direct memory test, outside of the MySQL Database that confirms if the memory access is indeed bad.

    Regards

    Ronald

  • Sheeri,

    Aha!

    Short answer: If all MySQL settings are similar, then your client’s response *is* the key to the difference in speed you’re seeing.

    Long answer: I really can’t go into much detail publicly at this time as the contents of my answer would spoil my upcoming presentation and paper. However, call me and we can discuss it in detail. I am not sure if you have my old 706 number or new 551 number so I will email the current number to you after this.

    Thanks!
    Frank

Leave a Reply

  • (will not be published)

XHTML: You can use these tags: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>