I’ve been battling with a mysterious problem for about a month or so where running xtrabackup from a cron job to do a full database backup would mysteriously fail when running on Sunday night, but would run fine when run by hand or when run in the cron at some other time. It would always fail at the same place in the database backup, but only leave the mysterious error [11592490.
I’m really not sure how I missed out on some of this stuff for so long, but I just came across some MySQL tools from the fine folks at Percona that I’ve been digging into over the last couple of days. I’ve already gotten a lot of use out of the Percona toolkit stuff, particularly pt-query-advisor and pt-variable-advisor. I’m also looking very closely at their monitoring plugins; they already have plugins for nagios and cacti, and I’m curious if these plugins can be adapted for using with newrelic.
I've been doing some experiments with some relatively large MySQL tables with trying to get around using a filesort when selecting rows ordered by a datetime in descending order. I'm not sure how much real world benefit this has, but some of the initial results are encouraging. We'll see.
Since MySQL doesn't allow you to define a reverse index (which would be useful for a field that you're constantly sorting by descending order), I'm experimenting with an indexed column that's set with 2^32 - UNIX_TIMESTAMP(time), where time's a normal MySQL timestamp. This way, you can ORDER BY countdown without the DESC keyword. I'm still experimenting with different indexes and queries, but there seem to be at least some areas where it leads to improved performance by avoiding the file sort. If more results seem encouraging enough, I'll write up the comparisons.
Using "large pages" (aka HugeTLB or "huge pages") for MySQL under Linux is pretty sweet, since it lets you use larger memory pages. Unfortunately, if you're like me, you can set up huge pages according to the documentation, but still get errors like this when you start up MySQL:
080324 01:38:04 mysqld started
InnoDB: HugeTLB: Warning: Failed to allocate 12884918272 bytes. errno 22
InnoDB HugeTLB: Warning: Using conventional memory pool
080324 1:38:04 InnoDB: Error: cannot allocate 12884918272 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 153659216 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.
InnoDB: On FreeBSD check you have compiled the OS with
InnoDB: a big enough maximum process size.
InnoDB: Note that in most 32-bit computers the process
InnoDB: memory space is limited to 2 GB or 4 GB.
InnoDB: We keep retrying the allocation for 60 seconds...
InnoDB: Fatal error: cannot allocate the memory for the buffer pool
Warning: Failed to allocate 220200960 bytes from HugeTLB memory. errno 22
The fix, it turns, out, is simple. While it's not actually spelled out anywhere in the documentation, you need to adjust the kernel.shmall and kernel.shmmax syctls (also found at /proc/sys/kernel/shmall and /proc/sys/kernel/shmmax) to allow enough memory to be allocated to those big pages. What you set those values to will depend on how much RAM you have, how much RAM you've allocated to InnoDB, and how many large pages you've reserved. These are the values I used on the DKos db servers:
vm.nr_hugepages = 6350
kernel.shmmax = 17179869184
kernel.shmall = 4194304
Or, a look at what I've been working on for the site.
It's been three months or so in the making, but today I've finally finished moving the site to its new hardware. Many of the major and minor things that could go wrong, went wrong, but at last everything's squared away and we're up and running on the new machines. Now that that's finished, I can even start rolling out some new features that I've been waiting on until this was all finished.
WARNING: There will be honest, frank discussion of computers and technology issues in this diary. Please, for the love of God, do not post a comment saying that you don't understand what I'm talking about. It will only antagonize the violent beast within.
Let's get started.