Racker Hacker

When Urchin’s task scheduler fails, you’ll notice big gaps in your data within Urchin. If your logs rotate out before someone catches the problem, then your data is gone, and unless you have it backed up, you’re out of luck. I’ve scoured the internet (and Urchin gurus) and I’ve yet to find a complete explanation for the occasional death of Urchin’s task scheduler.

You’ll see the “Warning! Task scheduler disabled.” error in bright red print in Urchin’s configuration menu when you click the “Run/Schedule” tab. It appears right below the gleaming “Run Now” button. If you click “Run Now”, Urchin will tell you again that the task scheduler is disabled.

To correct the problem, completely stop Urchin as root:
# /etc/init.d/urchin stop
-- OR --
# /usr/local/urchin/bin/urchinctl stop

Now, change to the /usr/local/urchin/bin directory and run:
# ./urchinctl status

If the Urchin webserver is running, but the task scheduler isn’t (which is the most likely situation), run:
# ./urchinctl -s start
# ./urchinctl status
Urchin webserver is running
Urchin scheduler is running

You should be all set. Credit for this fix goes to Urchin’s site.

One question I hear quite often is “how do I add IP aliases in FreeBSD?” It’s not terribly intuitive, but you can follow these steps:

Example:
Server’s primary IP: 192.168.1.5
Additional IP’s to add: 192.168.1.10, 192.168.1.15, and 192.168.1.20

Boot-time configuration:
Add it to /etc/rc.conf first (so you don’t forget). In this example, we have a Realtek card called rl0:
ifconfig_rl0="inet 192.168.1.5 netmask 255.255.255.0"
ifconfig_rl0_alias0="inet 192.168.1.10 netmask 255.255.255.0"
ifconfig_rl0_alias1="inet 192.168.1.15 netmask 255.255.255.0"
ifconfig_rl0_alias2="inet 192.168.1.20 netmask 255.255.255.0"

UBER-IMPORTANT NOTE: Start with the number 0 (zero) any time that you make IP alias configurations in /etc/rc.conf.
This is BAD form:

ifconfig_rl0="inet 192.168.1.5 netmask 255.255.255.0"
ifconfig_rl0_alias1="inet 192.168.1.10 netmask 255.255.255.0"
ifconfig_rl0_alias2="inet 192.168.1.15 netmask 255.255.255.0"
ifconfig_rl0_alias3="inet 192.168.1.20 netmask 255.255.255.0"

If you do it the wrong way (which means starting alias with anything but alias0), only the primary comes up. Keep that in mind.

Bringing up the new IP’s:
You can do things the extraordinarily dangerous way:
# /etc/rc.network restart

Or, you can follow the recommended steps:
# ifconfig rl0 alias 192.168.1.10 netmask 255.255.255.0
# ifconfig rl0 alias 192.168.1.15 netmask 255.255.255.0
# ifconfig rl0 alias 192.168.1.20 netmask 255.255.255.0

Test your work:
Any good system administrator knows to test things once their configured. Make sure to ping your new IP’s from a source on your network and outside your network (if possible/applicable).

Using the InnoDB engine can be tricky due to the ibdata files’ rather untraditional behavior. Instead of storing data in MYI and MYD files for each table, InnoDB stores everything in one (or several) large files starting with ibdata1. Of course, MySQL nerds know that you can adjust this behavior slightly with innodb_file_per_table, but you can read up on this at your leisure.

If you’ve restored the ibdata files from a previous backup, or if you just toss the .frm files into a database directory, you might find this when you start MySQL:

ERROR 1016 (HY000): Can't open file: 'files.ibd' (errno: 1)

Any good MySQL DBA will find out what error #1 means:

# perror 1
OS error code 1: Operation not permitted

This error sure sounds like a permission error. Go ahead and check your permissions in /var/lib/mysql, but you’ll probably find that they’re properly set.

So, why is the operation not permitted?

MySQL is actually hiding the actual problem behind an incorrect error. The actual issue is that the tables described in your .frm files are not present in the InnoDB tablespace (the ibdata files). This may occur if you restore the .frm files, but you don’t restore the correct ibdata files.

What’s the solution?

The easiest fix is to obtain a mysqldump backup of your original data. When you import it, MySQL will create your .frm files and populate the ibdata files for you without any fuss. You’ll be up and running in no time.

If you don’t have mysqldump backups, then you’ve just realized how important it is to have a flatfile backup of your databases. If you can restore your original ibdata file that you backed up with your .frm’s, you should be able to stop MySQL, put the old ibdata file and transaction logs back, and start MySQL. However, if multiple databases have InnoDB tables, you’re going to be reverting them to their previous state. This could cause BIG problems if you’re not careful. You will want to begin running this on a regular basis:

mysqldump -Q --opt -A --single-transaction -u username -p > mysqldump.sql

As a sidenote, this error utterly stumped this DBA. I’ve never run into this issue before, and I assumed that the server was supposed to have tablespaces per table, but I couldn’t find any mention in the /etc/my.cnf file. I found the solution on MySQL’s site after some intense Google action.

Rackerhacker has just been upgraded to Wordpress 2.2.2. If you haven’t updated it your own blog yet, you will want to download the new version and upgrade it soon.

If you want to know why, you can review the fixes and code changes.

MySQL documentation can be awfully flaky - extremely verbose on issues that don’t require such verbosity, and then extremely terse on issues that need a lot of explanation. The documentation for max_seeks_for_key matches the latter.

This is MySQL’s own documentation:

7.2.16. How to Avoid Table Scans
Start mysqld with the –max-seeks-for-key=1000 option or use SET max_seeks_for_key=1000 to tell the optimizer to assume that no key scan causes more than 1,000 key seeks. See Section 5.2.3, “System Variables”.

5.2.3. System Variables
Limit the assumed maximum number of seeks when looking up rows based on a key. The MySQL optimizer assumes that no more than this number of key seeks are required when searching for matching rows in a table by scanning an index, regardless of the actual cardinality of the index (see Section 13.5.4.13, “SHOW INDEX Syntax”). By setting this to a low value (say, 100), you can force MySQL to prefer indexes instead of table scans.

Just in case you need a quick refresher on cardinality, here you go:

13.5.4.13. SHOW INDEX Syntax
Cardinality
An estimate of the number of unique values in the index. This is updated by running ANALYZE TABLE or myisamchk -a. Cardinality is counted based on statistics stored as integers, so the value is not necessarily exact even for small tables. The higher the cardinality, the greater the chance that MySQL uses the index when doing joins.

Are you confused yet? If you’re not confused, you are a tremedously awesome DBA (or you’re a MySQL developer). Here’s the break down:

Cardinality is the count of how many items in the index are unique. So, if you have 10 values in an indexed column, and the same two values are reused throughout, then the cardinality would be relatively low. A good example of this would be if you have country or state names in a database table. You’re going to have repeats, so this means that your cardinality is low. A good example of high cardinality is when you have a column that is a primary key (or unique). In this case, every single row has a unique key in the column, and the cardinality should equal the number of rows.

How does this come into play with max_seeks_for_key? It’s higly confusing based on the documentation, but lowering this variable actually makes MySQL prefer to use indexes - even if your cardinality is low - rather than using table scans. This can reduce total query time, iowait, and CPU usage. I’m not completely sure why MySQL doesn’t default to this behavior since it’s easy to see the performance gains.

By default, this variable is set to the largest number your system can handle. On 32-bit systems, this is 4,294,967,296. On 64-bit systems, this is 18,446,744,073,709,551,616. Some linux variants, like Gentoo Linux, are setting this value to 1,000 in the default configuration files. Reducing max_seeks_for_key to 1,000 is like telling MySQL that you want it to use indexes when the cardinality of the index is over 1,000. I’ve seen this variable reduced to as low as 1 on some servers without any issues.

I’m still utterly confused at why this variable is set so high by default. If anyone has any ideas, please send them my way!

Plesk has a (somewhat annoying) default firewall configuration that you can adjust from within the Plesk interface. However, if you want to add additional rules, you may find that you can’t add the rules you want from the interface. If you add them from the command line, Plesk will overwrite them when it feels the urge, even if you run service iptables save as you’re supposed to.

You can override this by making /etc/sysconfig/iptables immutable with chattr. Just run the following:

# chattr +i /etc/sysconfig/iptables

Now, Plesk can’t adjust your iptables rules without your intervention. Well, that is until SWSoft figures out how to run chattr when Plesk can’t edit certain configuration files.

If you need a quick self-signed certificate, you can generate the key/certificate pair, then sign it, all with one openssl line:

openssl req -new -newkey rsa:1024 -days 365 -nodes -x509 -keyout server.key -out server.crt

This error means that Plesk attempted to make a DNS change and reload named, but it failed. The problem generally lies within some seemingly innocent RPM’s that are causing problems with Plesk’s installation of bind.

Check your /var/log/messages for lines like these:

named[xxx]: could not configure root hints from ‘named.root’: file not found
named[xxx]: loading configuration: file not found
named[xxx]: exiting (due to fatal error)
named: named startup failed

In this case, do a quick check for these RPM’s and remove them if they are on the system:

• bind-chroot
• caching-nameserver

# rpm -ev bind-chroot
# rpm -ev caching-nameserver

If you’ve used Plesk with a large amount of domains, you know what a pain running out of file descriptors can be. Web pages begin acting oddly, Horde throws wild errors, and even squirrelmail rolls over onto itself. Luckily, Plesk introduced piped Apache logs (along with lots of bugs!) in Plesk 8.2, and you can enable piped logs with the following commands:

# mysql -uadmin -p`cat /etc/psa/.psa.shadow` psa -e "replace into misc (param,val) values ('apache_pipelog', 'true');"
# /usr/local/psa/admin/sbin/websrvmng -v -a

Technically, these changes will allow Plesk to host about 900 sites, but this is still a little extreme in my opinion, even on the best hardware money can buy. If you find yourself passing the 900 mark, then you should probably follow this SWSoft KB article, adjust your FD_SETSIZE and recompile.

More information about configuring piped logs can be found on SWSoft’s site. Thanks, Jon!

If you haven’t checked out my automated mysqltuner.pl script, head on over to mysqltuner.com and give it a try. The script is written in Perl, and it’s an automated way to optimize your MySQL variables for the best performance. It’s like being able to ask a DBA to fix your variables, except the script is free, it doesn’t require coffee to function (only Perl), and it gives immediate results.

Changes for tonight:

• Fixed some of the logic for key buffer calculations
• Added a warning if MySQL hasn’t been running very long
• Additional detailed explanations
• Checks for max_seeks_for_key variable