Racker Hacker

If you’ve used newer versions of Horde with Plesk, you have probably noticed the news feed that runs down the left side of the screen. Depending on the types of e-mails you receive, you may get some pretty odd news popping up on the screen.

Luckily, you can remove the news feeds pretty easily. Open the following file in your favorite text editor:

/usr/share/psa-horde/templates/portal/sidebar.inc

Once the file is open, drop down to line 102 and comment out the entire if() statement (lines 102-117).

NOTE: If you upgrade Plesk, this change will most likely be reversed.

If you’ve ever worked on a server that was being overworked (I know I have), you might see sendmail throw out a message like this one:

Jan 9 14:15:49 mail sendmail[18184]: rejecting connections on daemon MTA: load average: 70

This is sendmail’s way of trying not to contribute to the already high load placed on the server. Since busy mail servers generally use a lot of disk I/O for the queue, and they may pass mail to other filters (which uses CPU time), the sendmail daemon will refuse to handle mail to help reduce the load.

If you don’t like the values that are currently set, you can change them with the following configuration directives in your sendmail.mc:

define(`confQueueLA', `100')dnl
define(`confRefuseLA', `100')dnl

The confQueueLA defaults to the number of processors in your system multiplied by eight, and the confRefuseLA defaults to the number of processors in your system multiplied by 12. However, it is noted that this default differs between versions as well as distributions.

The two configuration items control when sendmail will process items in the queue only (QueueLA) and when sendmail will stop accepting incoming SMTP connections all together (RefuseLA).

A new version of MySQLTuner was released tonight to correct some bugs found within revision 26. As usual, you can get a new copy from the MySQLTuner site.

Here are the new features:

» Storage engine counts are shown

In addition to the actual size of the tables stored under each storage engine, there is a table count as well. The table count reflects the number of tables that exist on the server which use the specified storage engine.

» Minor changes

• Reduced overall code size
• Optimized subroutines to use fewer system calls and math computations
• Added storage engine disabling recommendations to the bottom of the output

» Bugs fixed

• Fixed an issue that caused incorrect statistics for storage engines with MySQL 3.23
• Corrected a logic bug that displayed odd storage engine statistics calculations

After a couple of weeks, my MySQL replication series has come to a close. Here’s links to all of the topics that I covered:

Performance
Redundancy
Backups and Data Integrity
Horizontal Data Partitioning
Delayed Slaves
Breakdown
Replication Across an External Network
Upgrading the MySQL Server
Slave Performance

If there’s any other questions that you have, please let me know and I’ll be happy to add some extra posts on your topic!

As some subversion users may have noticed, revision 23 of MySQLTuner was released quietly on Sunday. Thanks to Mike Jackson, a few bugs from revision 23 were smashed today and revision 26 was released this morning.

To pick up the new script, visit the MySQLTuner site.

Here’s some of the new features:

» Shows banner of enabled and disabled storage engines

Near the top of the MySQLTuner output, you’ll find a line like this:

[**] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster

Of course, this is shown in color within the terminal, but all enabled storage engines are shown in green with a plus sign at the front. Disabled storage engines are shown in red and are prepended with a minus sign.

» Recommends disabling unused storage engines

If MySQLTuner finds storage engines enabled that are not in use, it will recommend that they are disabled to save resources:

[!!] InnoDB is enabled but isn't being used

» Calculates total data corresponding to storage engines

Any storage engines in use are shown with the total amount of data stored using those engines:

[**] Data in MyISAM tables: 9G
[**] Data in InnoDB tables: 189M
[**] Data in MEMORY tables: 0B

» Displays exact counts in addition to percentages

For some results where percents are shown, exact counts are being displayed as well:

[OK] Slow queries: 0% (1/4M)
[OK] Highest usage of available connections: 56% (17/30)

» Initial InnoDB support

As an initial step towards InnoDB support, the innodb_buffer_pool_size is compared to the total amount of InnoDB data stored on the server:

[OK] InnoDB data size / buffer pool: 189.7M/384.0M

» Other minor changes

• Added additional section headers to further organize the output
• Merged the total buffers lines into one for more compact output
• Added MySQL 5.1 to the supported list to prepare for upcoming GA release
• For filesize amounts less than 1024 bytes, the “B” letter is shown to represent bytes
• InnoDB log file recommendation removed due to bad implementations based on recommendations
• Switches from spaced indents to tabbed indents

There’s a few final configuration options that may help the performance of your slave MySQL servers. If you’re not using certain storage engines, like InnoDB or Berkeley, then by all means, remove them from your configuration. For those two specifically, just add the following to your my.cnf on the slave server:

skip-innodb
skip-bdb

To reduce disk I/O on big MyISAM write operations, you can delay the flushing of indexes to the disk:

delay_key_write = ALL

You can also make all of your write queries take a backseat to any reads:

low-priority-updates

Keep in mind, however, that the last two options will increase slave performance, but it may cause them to lag behind the master. Depending on your application, this may not be acceptable.

If you want to make a DBA nervous, just let them know that they need to upgrade MySQL servers that are replicating in a production environment. There’s multiple ways to get the job done, but here is the safest route:

First, make sure you have dumped all of your databases properly. Verify that your backups are correct and intact, and that you have multiple copies of them.

Next, upgrade the slave servers individually to the newest version. After upgrading the first one, make sure the slave server is operating properly. If it is working properly, then you can continue to upgrade the other slaves.

Once all of the slaves have been upgraded, then you can upgrade the master. If a busy web application is sending write queries to the master, you may want to put up a temporary page that tells visitors that maintenance is being performed. Once all of the writes clear out, stop the master and upgrade it.

After the master starts up, be sure that the slaves reconnect, and you might want to perform a test write query. Verify that the write is performed on the slaves as it was done on the master.

While many people might find replicating over an external network to be an odd concept, it does have some uses. For example, if you need to replicate data for local access at certain locations, it may be helpful. Also, if you have a dedicated server, you can replicate to your home to run backups.

First off, you’re going to need security for the connection. This is easily done with SSL. On the master, simply add the following lines to the [mysqld] section and restart the master:

ssl-ca=cacert.pem
ssl-cert=server-cert.pem
ssl-key=server-key.pem

To have the slaves use SSL connections to the master server, simply add on MASTER_SSL=1 to the CHANGE MASTER statement on the slave.

Another aspect to consider is bandwidth usage. This may be a priority if your remote areas have slow downlinks, or if you are charged for your bandwidth usage. You can compress the MySQL traffic very easily. Simply add the following to the MySQL configuration file in the [mysqld] section:

slave_compressed_protocol = 1

With both of these changes, keep in mind that there is a significant CPU overhead required to compress and/or encrypt data. Determine carefully what your application requires and test your configuration thoroughly.

On some occasions, MySQL replication can break down if an statement comes from the master that makes no sense to the slave. For example, if an UPDATE statement arrives from the master server, but the table referenced by the UPDATE no longer exists, then the slave will halt replication and throw an error when SHOW SLAVE STATUS; is run.

The obvious question here is: how can the master and the slave have different data after replication has started? After all, you make a dump file prior to starting replication, so both servers contain the same information. Stray updates can be thrown into the mix from application errors or plain user errors. These kinds of things happen, even though we all try to avoid it.

Don’t worry - this is almost always an easy fix. You have two main options:

Fix the problem yourself. If the master sent a query that the slave can’t run, fix it manually. For example, if the master wants to run an INSERT on a table that doesn’t exist, run a quick SHOW CREATE TABLE; on the master and create the table manually on the slave. When the table is there, run a START SLAVE; on the slave and you should be all set.

Skip an unnecessary query. Let’s say that the master sent over a DROP TABLE query but the table doesn’t exist on the master. It’s safe to say that the master won’t be sending any write queries to that table in the future, so the query can be skipped. To skip it, run the following statement:

mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql> START SLAVE;

In short, you’re telling MySQL to skip that unnecessary query and keep going with the ones after that. Of course, if you need to skip multiple queries, change the 1 to whatever number you need and then run START SLAVE;.

In a perfect world, slaves will contain the same data as the master at all times. The events should be picked up and executed by the slaves in milliseconds. However, in real world scenarios, replication will be held up for different reasons. Whether it’s table locks, disk I/O, network saturation, or CPU usage, slaves might become several seconds, minutes or even hours behind the master.

In some situations, delays of less than 30 seconds may not be a big issue. Some applications, like social networking applications, would need to have the data match at all times. Lags would not be acceptable.

For example, review this scenario. Let’s say you go to a site and create an account. That would send a write query to the master. Once you’ve finished the account creation, the page will depend on a read query. If the slave is behind the master, it won’t have any data about your new account, and the application will probably tell you that you don’t have an account. That would be pretty annoying for your application’s users.

To check your current lag, simply run SHOW SLAVE STATUS; in MySQL, and review the number following Seconds_Behind_Master. If everything is running well, it should be followed by 0. If NULL is shown, then there is most likely an issue with replication, and you might want to check Last_Error.

So, how can replication lags be corrected? Try these methods:

Review your queries. When queries keep running in MySQL, the slave may be unable to keep up. Make sure that your read queries are as optimized as possible so they complete quickly.

Optimize your MySQL server variables. Be sure to thoroughly review your MySQL configuration for any bottlenecks.

Choose the right storage engines. If you’re making a lot of updates to a table, consider using InnoDB. If your tables are not updated often, consider using MyISAM tables (or even compressed MyISAM tables).

Upgrade your hardware. Find your hardware bottleneck. If it’s the CPU, consider upgrading to a multi-core CPU, or a CPU with a higher clock speed. For I/O bottlenecks, consider a RAID solution with SAS drives. If you’re lucky enough to have a network bottleneck (lucky since it means you’re doing well with CPU and I/O), use a dedicated switch or upgrade to gigabit (or faster) hardware.