Solaris Containers and MySQL
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).
March 17th, 2008 at 4:26 pm
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.
March 17th, 2008 at 5:44 pm
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. :)
March 17th, 2008 at 6:42 pm
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.
March 17th, 2008 at 8:14 pm
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.
March 18th, 2008 at 12:42 am
Hi Sheeri,
Which servers are these? V440? T1000? or X4100 series?
Frank
March 18th, 2008 at 2:51 am
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)
March 18th, 2008 at 8:16 am
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.
March 18th, 2008 at 12:22 pm
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.
March 18th, 2008 at 3:12 pm
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
March 19th, 2008 at 7:54 pm
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