If you're running an operation on a large number of rows within a table that uses the InnoDB storage engine, you might see this error:
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
MySQL is trying to tell you that it doesn't have enough room to store all of the row locks that it would need to execute your query. The only way to fix it for sure is to adjust innodb_buffer_pool_size and restart MySQL. By default, this is set to only 8MB, which is too small for anyone who is using InnoDB to do anything.
If you need a temporary workaround, reduce the amount of rows you're manipulating in one query. For example, if you need to delete a million rows from a table, try to delete the records in chunks of 50,000 or 100,000 rows. If you're inserting many rows, try to insert portions of the data at a single time.
It's no secret that I'm a big fan of as a blog and CMS platform. While it does have its problems, it's relatively simple to set up, it's extensible, and — when properly configured — it has great performance. The plugin has been a staple on my WordPress blogs for quite some time and it has solved almost all of my performance problems.
However, when you load up quite a few plugins or a heavy theme, the performance will dip due to the increased number of stylesheets, javascript files, and images. You can compress and combine the stylesheets and javascript to decrease load times, but this may not get the performance to a level you like.
I was in this situation and I found a great solution: the plugin and the service.
To get started, and set up an account. Their current promotion gives you 1TB of CDN bandwidth for one year for $10 (regularly $99). Once you sign up, do the following:
Click Manage Zones
Click Create pull zone
At this point, you'll see a list of form fields to complete:
Enter an alias for the pull zone name
The origin server URL is the URL that's normally used to access your site (i.e. rackerhacker.com)
The custom CDN domain is the URL you want to use for your CDN (i.e. cdn.rackerhacker.com)
The label can be anything you'd like to use to remember which zone is which
Enabling compression is generally a good idea
Once you save the zone, MaxCDN will give you a new domain name. You'll want to create a CNAME record that points from your CDN URL (for me, that's cdn.rackerhacker.com) to the really long URL that MaxCDN provides.
STOP HERE: Ensure that all of your DNS servers are replying with the CNAME record before you continue with the W3 Total Cache installation and CDN setup. If you proceed without waiting for that, some of your blog's visitors will get errors when they try to load content via your CDN domain.
You're ready for W3 Total Cache now. Install the plugin within your WordPress installation and activate it. Hop into the settings for the plugin and make these adjustments:
Enable Page Caching and set it to Disk (enhanced)
Enable Minify and set it to Disk
Enable Database Caching and set it to Disk
Leave the CDN disabled for now, but flip the CDN Type to Origin Pull (Mirror)
Press Save changes
Click CDN Settings at the top of the page and configure the CDN:
Enter your CDN domain (for me, it's cdn.rackerhacker.com) in the top form field
Leave the other options as they are by default and click Save changes
W3 Total Cache should prompt you to clear out your page cache, and that would be recommended at this step. If you fully reload your blog's main page in your browser (may require you to hold SHIFT while you click reload/refresh) and check the page source, you should see your CDN URL appear for some of the javascript or CSS files.
You may discover that some CSS files, stylesheets, or images aren't being loaded via the CDN automatically. Luckily, that's an easy fix. Under the Minify Settings section of the W3 Total Cache plugin settings, scroll to the very bottom. Add in your javascript or CSS files via the form fields at the bottom and the plugin should handle the minifying (is that even a word?) and the CDN URL rewriting for you.
This problem has cropped up for me a few times, but I've always forgotten to make a post about it. If you're working with a large InnoDB table and you're updating, inserting, or deleting a large volume of rows, you may stumble upon this error:
ERROR 1206 (HY000): The total number of locks exceeds the lock table size
InnoDB stores its lock tables in the main buffer pool. This means that the number of locks you can have at the same time is limited by the innodb_buffer_pool_size variable that was set when MySQL was started. By default, MySQL leaves this at 8MB, which is pretty useless if you're doing anything with InnoDB on your server.
Luckily, the fix for this issue is very easy: adjust innodb_buffer_pool_size to a more reasonable value. However, that fix does require a restart of the MySQL daemon. There's simply no way to adjust this variable on the fly (with the current stable MySQL versions as of this post's writing).
Before you adjust the variable, make sure that your server can handle the additional memory usage. The innodb_buffer_pool_size variable is a server wide variable, not a per-thread variable, so it's shared between all of the connections to the MySQL server (like the query cache). If you set it to something like 1GB, MySQL won't use all of that up front. As MySQL finds more things to put in the buffer, the memory usage will gradually increase until it reaches 1GB. At that point, the oldest and least used data begins to get pruned when new data needs to be present.
So, you need a workaround without a MySQL restart?
If you're in a pinch, and you need a workaround, break up your statements into chunks. If you need to delete a million rows, try deleting 5-10% of those rows per transaction. This may allow you to sneak under the lock table size limitations and clear out some data without restarting MySQL.
To learn more about InnoDB's parameters, visit the .
About a year ago, I was introduced to the joys of using and to access irc servers. Before that time, I'd usually used graphical clients like , and I always enjoyed getting notifications when someone mentioned a word or string that I set up as a trigger.
Once I started using irssi in screen, I found that the visual bell in screen didn't get my attention quickly. Luckily, someone in the channel let me know about screen's audible bell. You can flip between the visual and audible bell with CTRL-A and then CTRL-G. If you keep repeating that key combination, you'll switch back and forth between the two (with a status update at the bottom left).
You can also set up your visual bell configuration in your .screenrc via some configuration parameters:
Thanks to a recommendation from and , I've been using with a lot of success for quite some time. In short, dsh is a small application which will allow you to run commands across many servers via ssh very quickly.
You may be wondering: «Why not just use ssh in a for loop?» Sure, you could do something like this in bash:
for i in`cat ~/myhosts.txt`; dossh$i'uptime'; done
But dsh allows you to do this:
dsh -g myhosts 'uptime'
In addition, dsh allows you to run the commands concurrently (-c) or one after the other (-w). You can tell it to prepend each line with the machine's name (-M) or it can omit the machine name from the output (-H). If you need to pass extra options, such as which ssh key to use, or an alternative port, you can do that as well (-o). All of these command line options can be tossed into a configuration file if you have a default set of options you prefer.
Another thing that makes dsh more powerful is the groups feature. Let's say you have three groups of servers — some are in California, others in Texas, and still others in New York. You could make three files for the groups:
~/.dsh/group/california
~/.dsh/group/texas
~/.dsh/group/newyork
Inside each file, you just need to list the hosts one after the other. Here's the ~/.dsh/group/texas group file:
As you can see, dsh handles comments in the hosts file. In the above example, the web3 server will be skipped since it's prepended with a comment. Let's say you want to check the uptime on all of the Texas servers as fast as possible:
dsh -c -g texas 'uptime'
That will run the uptime command on all of the servers in the Texas group concurrently. If you need to run it on two groups at once, just pass another group (eg. -g texas -g california) as an argument. You can also run the commands against all of your groups (-a).
The dsh command can really help you if you need to gather information or run simple commands on many remote servers. If you find yourself using it often for systems management, you may want to consider something like .
One of my favorite (and most used) applications on any Linux machine is . Once you fire up a screen session, you can start something and keep it running indefinitely. Even if your internet connection drops or you accidentally close your terminal window, the screen session will remain open on the remote server.
Detaching from a screen session is done by pressing CTRL-A and then d (for detach). However, when I'm on my Mac, CTRL-A and CTRL-E send my cursor to the beginning and end of lines, respectively. Once I launch screen, I lose the CTRL-A functionality because screen thinks I'm trying to send it a command.
Luckily, this can be changed in your ~/.screenrc:
escape ^Ww
With this change, you can press CTRL-W, then press d, and you'll detach from the screen session. For all of the screen options, run man screen on your local machine or review the .
Happy New Year! I certainly hope it's a great one for you, your family, and your business. As the new year begins, I figured it would be a good time to sit down and answer a question that I hear very often:
How do I become a better systems administrator?
The best way to become a better systems administrator is to fully understand the theory of what's happening in your server's environment.
What do I mean by that? Learn why things aren't happening as you expected and think about all of the factors that could possibly be involved. Instead of thinking purely about cause and effect, you'll find it much easier and rewarding to consider everything inside and outside your environment before you make any changes.
This still may be a little difficult to fully understand, so he's an example. Let's say you're handling an issue where a customer can't reach a website hosted on their server. When you ask them for more details, they might give you the dreaded reply: «It's not coming up.» Start by making a mental list of the problems that are easiest to check:
Is the web server daemon running?
If a database server is being used, is it running and accessible?
Is there a software/hardware firewall blocking port 80?
Is a script stuck on the server tying up resources?
Could there be a DNS resolution problem?
Is the server up?
Did a switch fail?
Is the server's hard disk out of space?
Can the customer reach other websites like Google or Yahoo?
If SELinux is involved, have the appropriate contexts been set?
Could the site be a target of a denial of service attack?
Has the server reached its connection tracking limit?
Of course, this is a relatively short list, but these are all easy to check. If you're thinking about cause and effect, you might only consider the web server daemon and some basic network issues. By considering all of the other factors that may be related, you've ensured that all of the basics are covered before you consider more complex problems.
Most systems administrators have taken an error message and tossed it in en masse into Google before. Occasionally, no results will appear for the search. If you find yourself in this situation, try to understand the individual parts of the error message. Work outward from what you know already. You should know which daemon said it, and you may have an idea of what the application was doing when the error occurred. Take time to consider what the daemon is trying to tell you within the context of what it was doing at the time.
One of the easiest ways to force yourself to be immersed into this way of thinking is to host applications for non-technical people. You'll find that many customers want things done differently, and they're all at different levels of technical aptitude. Some may find it a frustrating experience at first, but you'll think yourself later. It will force you to consider all aspects of how a server operates since you might not always know what's happening within a customer's application.
As always, if you find yourself stumbling, remember to ask your peers and colleagues. Even if they haven't seen the particular issue, they will probably be able to guide you closer to the solution you seek.
I upgraded a Fedora 11 instance to Fedora 12 and found the following error at the top of one of my WordPress blogs:
Parameter 1 to update_comment_type_cache() expected to be a reference,
value given in wp-includes/plugin.php on line 166
The problem wasn't in a plugin, actually. It was within my theme's () functions.php:
function update_comment_type_cache(&$queried_posts){
The temporary fix is to remove the & from that line so it looks like this:
function update_comment_type_cache($queried_posts){
After clearing out the WP Super Cache, the page was loading properly again. It turns out that the function actually calculates how many comments are available for a given post, so that functionality is working properly right now. A few theme authors are already releasing new versions to fix this bug, but my theme's author has not.