Web Programming, Linux System Administation, and Entrepreneurship in Athens Georgia

Author: Brandon (Page 14 of 29)

Poor Performance After Enabling Repliction Due to sync_binlog

I was pretty happy with myself with setting up some fairly complicated MySQL circular replication the other night.  I did it far after peak hours so as not to disturb any visitors if it caused any problems.   Everything appeared to be working great until I started watching things the next morning.

I started to notice that the main MySQL server seemed to be running really slow.   One process that we have usually completes in a couple hours, ended up taking well over 16 hours to complete.   I spent the whole day troubleshooting it, which got me familiar with all sorts of handy tools.   ‘mytop‘ is a handy version of ‘top’ for MySQL queries.  I got familiar with iostat for watching disk I/O performance.

In the end, after a whole day of troubleshooting it came down to the ‘sync_binlog‘ setting that I had enabled because I read some howto that mentioned it was useful for the replication master.  My understanding now of the setting is that it causes the operating system to tell the disk to sync the file to disk after each write to the binary log (every UPDATE, INSERT, or DELETE).   The idea is that when the data is sync’d to disk, the drive physically writes it to the drive, instead of keeping it in a cache.    My application does a ton, of inserts, so it was killing performance.

MySQL Multi-Master (Circular) Repliclation

With a little bit of work tonight, I now have a multi-master MySQL database configuration running. The setup is pretty slick, and surprisingly simple to set up. I have been playing with simple Master-Slave configurations for a while now, and recently came across this OnLamp article about Circular Replication

The basic concept is pretty straightforward – each server writes its own binary log as normal. You also enable log-slave-updates which writes updates received from the master server to its own binary log as well, so that those are passed on to the next server in the  circle.

I actually used a slightly modified version of the circle. I have several servers at one location. The ‘main’ master MySQL instance is on one of those, and the others all are slaves to it. We recently added a server at a remote location and made it a simple slave as well. All of the servers sent updates to the single master.

The problem I had was that the server at the remote location is about 70-80 ms away, which works fine for some applications, but I’m adding a feature that will potentially do 30 or so updates on each request. Multiply 80 ms times 30 updates, and there is some noticeable problems.

My new setup has updates being written locally on the remote server, and then using MySQL replication to get that back to the old master server, which then relays it to the rest of the servers in that location. The remote server still receives updates from the main master server.  I can now update either server, and updates propagate to all servers in very near real-time.  It is very slick.

Note that this might not be suitable for all sorts of data and all situations.  My data is an extremely simple key => value caching system.  I use REPLACE INTO statements so that it doesn’t matter if a given key already exists or not. Also, if my data becomes inconsistent, it also is not a big deal, as the caching system will just fail, and the application will proceed without using the cached results.

Previously, the major obstacle to this setup was when using automatically incrementing INSERT_ID’s and it was possible for each master server to use the same ID. MySQL 5 introduced the auto_increment_increment and auto_increment_offset settings which makes it possible to guarantee that will never happen. There still are some potential problems where statements not executed in the same order on all nodes may result in inconsistent data. So use with caution and make sure you understand the potential issues.

Sessions Don’t Work When Proxying Through Apache

This particular problem makes it look like your application’s sessions aren’t working at all. A common use for Apache is to serve as a reverse proxy for many applications. This is particularly common for serving dynamic Java content, and also for Ruby on Rails applications. A pretty typical configuration is to have Apache serve static content, but to have it redirect any requests for dynamic content to Tomcat. A sample Apache configuration might look like this:

RewriteEngine On
RewriteRule ^/(.+\.jsp)$ ajp://localhost:8009/myapp/\ [P]
ProxyPassReverse / ajp://localhost:8009/myapp/

When Apache serves as a reverse proxy, it just passes requests directly to the backend server, and returns the results directly as received. In the case of Java applications, they typically are installed in an application directory, and specify that directory in the SetCookie header. Here is a sample SetCookie header from an HTTP response:

Set-Cookie: JSESSIONID=E1576192767FB8D998137B52461C023D; Path=/myapp

With the default behavior, Apache passes that Set-Cookie header un-modified to the client. It receives the cookie, but will only send the cookie for requests in the /myapp directory. The solution is a new configuration parameter for ProxyPassReverseCookiePath introduced in Apache 2.2 which tells Apache to rewrite the Path parameter according to the rules that you define. To use it, simple add this line in your Apache config:

ProxyPassReverseCookiePath  /myapp  /

This tells apache to replace the ‘Path=/myapp’ in the Set-Cookie header with ‘Path=/’. That should tell your browser about the application’s path correctly, and let your sessions work correctly

A Case for Choosing Good Server Names

This morning, I had a client call me bright and early, frantic about some mail problems they were having.  All of their mail servers had stopped accepting incoming SMTP connections for some reason, and they couldn’t figure out why.

After a little bit of investigation, I found that they were using postfix with MySQL-based virtual domains.   The MySQL authentication was failing, which meant that postfix was unable to look up any valid recipient names.   That, in turn was causing tons of retried connections, until they hit the maximum number of connections where Postfix would refuse additional connections.

The problem is that these mail servers were initially set up with some dumb names for some reason.    A new administrator noticed the silly names in their Reverse DNS entries and changed them to some more sensible names.  The MySQL permissions were based off of the hostnames, so when the names in Reverse DNS changed, it broke the permissions, and the clients were unable to connect.

Solving the problem was simple enough – I just corrected the MySQL permissions, and then had to deal with some huge mail queues for a little while as all of the messages waiting to come in were finally allowed all at once.

The moral of the story is to use sensible names to start out with.   These names were chosen to be sortof funny I guess, but it didn’t end up being so amusing in the midst of all of the problems it caused.  As a side note, I usually do MySQL permissions based on IP Address as well, so that you further reduce this kind of problem.

Random Password Generator

There are times when I’ve been focusing on programming all day, and it is easier to write a program to do something trivial, then it is to just do it the simple way. Today was such a day. Instead of typing some random character to make up new user’s password, I wrote a script to do it for me:

#!/usr/bin/perl
#################################################################
## Quick Random Password Generator
## Author: Brandon Checketts
## https://www.brandonchecketts.com/
#################################################################

my $length = $ARGV[0] || 10;

my $charset = "abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890";

my $pw = "";
for (my $i=0; $i < $length; $i++) {
    my $pos = rand(length($charset));
    $pw .= substr($charset, $pos, 1);
}
print "\\nRandom Password: $pw\\n\\n";

Of course you can modify the default length and/or characters to make something more suitable for your use.

Sample Usage:

[root@dev ~]# ~/bin/pwgen

Random Password: mYTZrSpE8B

[root@dev ~]# ~/bin/pwgen 20

Random Password: EoSQpypmeK3SZCVPodaM

Postfix Vacation Message

The idea of a vacation message is kindof odd to me, but I had one client request it today, so took a look at configuring it. On RHEL/CentOS distros, the ‘vacation’ binary is distributed with sendmail, and is not available with postfix, so you have to build it yourself. Fortunately, it is about the easiest thing I have ever compiled.

[root@host ~]# yum install gdbm-devel
[root@host ~]# cd /usr/local/src/
[root@host ~]# wget https://internap.dl.sourceforge.net/sourceforge/vacation/vacation-1.2.7.0-rc2.tar.gz
[root@host ~]# tar -xvzf vacation*
[root@host ~]# cd vacation-1.2.7.0-rc2
[root@host ~]# make
[root@host ~]# make install

That’s it. Not even a configure script. That should install the vacation binary in /usr/bin/vacation.

Now just create a vacation message by putting a ‘.vacation.msg’ in the user’s home directory with the auto-reply content:

Subject: On vacation message.

I'm on vacation and will not be reading my mail for a while.
Your mail will be dealt with when I return. 

And finally, create a .forward file that tells your mail program to deliver to the vacation program:

\myuser, "|/usr/bin/vacation  myuser"

That should be it. I tested and verified that it works. Note that you have to provide a to: header with the recipient’s address.

Next, I might try some experiments to see if I can get it to work with virtual users.

Checking MySQL Replication

MySQL replication is pretty easy to set up, but needs a few extra things to make it more reliable. I wrote this quick PHP script to alert me when replication has failed and is more than 5 minutes behind the master.

<?php

$user = 'username';
$pass = 'password';
$host = 'localhost';
// Grant this user permission to check the status with this mysql statement
// GRANT REPLICATION CLIENT on *.* TO 'user'@'host' IDENTIFIED BY 'password';

$threshold = 300;

$db = mysql_connect($host, $user, $pass);

$result = mysql_query('SHOW SLAVE STATUS');
if (!$result) {
    // Make sure that your user has the 'REPLICATION CLIENT' privlege
    echo "Error 'SHOW SLAVE STATUS' command failed\n";
    echo mysql_error()."\n";
    exit(1);
}

$status = mysql_fetch_array($result);

if (!isset($status['Seconds_Behind_Master'])) {
    echo "Error: Seconds_Behind_Master column not found in result\n";
    print_r($status);
    exit(2);
}

if ($status['Seconds_Behind_Master'] > $threshold) {
    $minutes = floor($status['Seconds_Behind_Master'] / 60);
    echo "Error: Slave is $minutes minutes behind the master server\n";
    exit(3);
}

exit(0);
?>

This script is intended to be run periodically from cron. It doesn’t generate any output unless something is wrong. The behavior of cron is that when a script generates output, it will email the output to the user, so make sure that you have mail on your system configured to send you the cron output correctly. The script also exits with a non-zero status on each error, so you might include this in a more complicated script that attempts to do something else based on the status.

I use something like this in a non-privileged user’s crontab:

*/15 * * * /usr/bin/php /path/to/check_replication.php

Getting Dkimproxy Installed and Configured

Dkimproxy is a great program for getting Postfix to both sign and validate DomainKeys and DKIM messages. Prior to dkimproxy, one would have used dk-filter and dkim-filter which did DomainKeys and DKIM signing separately. dkimproxy is a newer version that combines the functionality into one program. Installing it can be a bit complicated because it isn’t available in most distro repositories, and requires several Perl modules that need to be installed. Configuring it can be difficult as well, because it involves making changes DNS and postfix, in addition to its own configuration.

I wrote these steps below as I went through a recent installation for a customer

You can install the required Perl modules through the RPM Forge Repository if you have it enabled with the command (Thanks JohnB for mentioning that):

yum install perl-Net-Server perl-Error perl-Mail-DKIM

Otherwise, you can install them manually with CPAN. First install the openssl-devel package (You’ll need it for CPAN to install Mail::DKIM)

yum install openssl-devel

Now install the required Perl modules

# perl -MCPAN -e shell
> install Net::Server
> install Error
> install Mail::DKIM

Download and install the actual dkimproxy code:

cd /usr/local/src
wget https://internap.dl.sourceforge.net/sourceforge/dkimproxy/dkimproxy-1.0.1.tar.gz
tar -xvzf dkimproxy-1.0.1.tar.gz
cd dkimproxy-1.0.1
./configure --prefix=/usr/local/dkimproxy
make
make install

You should now have the program installed in /usr/local/dkimproxy. A sample init file is included, so we can copy it into place also:

cp /usr/local/src/dkimproxy-1.0.1/sample-dkim-init-script.sh /etc/init.d/dkimproxy

Create a ‘dkim’ user and group, but lock the password:

useradd -d /usr/local/dkimproxy dkim
passwd -l dkim

That should be enough to get dkimproxy running, but it isn’t configured yet.

Create a key file for your domain

cd /usr/local/dkimproxy/etc/
openssl genrsa -out domain.tld.key 1024
openssl rsa -in domain.tld.key -pubout -out domain.tld.pub

Now create a DNS TXT record for mail._domainkey.domain.tld with the contents of domain.tld.pub. Your public key will span at least two lines, so combine all of the lines of the key together when putting it in your DNS record. The whole DNS record will look something like this:

k=rsa; t=s; p=MFwwDQYJ......0JMCAwEAAQ==

(Note that the key is pretty long and I’ve shortened it here)
You could now confirm the key is correct in your DNS:

[root@host etc]# host -ttxt mail._domainkey.domain.tls
mail._domainkey.domain.tld descriptive text "k=rsa\; t=s\; p=MFwwDQYJ......0JMCAwEAAQ=="

(Note that the key is pretty long and I’ve shortened it here)

Now tell dkimproxy about the key files, and configuration parameters. Create /usr/local/dkimproxy/etc/dkimproxy_out.conf with this content

# specify what address/port DKIMproxy should listen on
listen    127.0.0.1:10027

# specify what address/port DKIMproxy forwards mail to
relay     127.0.0.1:10028

# specify what domains DKIMproxy can sign for (comma-separated, no spaces)
domain    domain.tld

# specify what signatures to add
signature dkim(c=relaxed)
signature domainkeys(c=nofws)

# specify location of the private key
keyfile   /usr/local/dkimproxy/etc/domain.tld.key

# specify the selector (i.e. the name of the key record put in DNS)
selector  mail

And copy the sample inbound config to the real inbound config

cd /usr/local/dkimproxy/etc
cp dkimproxy_in.conf.example dkimproxy_in.conf

Now you should be able to start up dkimproxy, and configure it to start at boot:

/etc/init.d/dkimproxy start
chkconfig dkimproxy on

And the last step is just to modify the postfix configuration to tell it to forward messages sent to port 587 through dkimproxy for signing. I added these three sections to /etc/postfix/master.cf

### dkimproxy filter - see https://dkimproxy.sourceforge.net/postfix-outbound-howto.html
#
# modify the default submission service to specify a content filter
# and restrict it to local clients and SASL authenticated clients only
#
submission  inet  n     -       n       -       -       smtpd
    -o smtpd_etrn_restrictions=reject
    -o smtpd_sasl_auth_enable=yes
    -o content_filter=dksign:[127.0.0.1]:10027
    -o receive_override_options=no_address_mappings
    -o smtpd_recipient_restrictions=permit_mynetworks,permit_sasl_authenticated,reject

# specify the location of the DKIM signing proxy
# Note: the smtp_discard_ehlo_keywords option requires a recent version of
# Postfix. Leave it off if your version does not support it.
dksign    unix  -       -       n       -       10      smtp
    -o smtp_send_xforward_command=yes
    -o smtp_discard_ehlo_keywords=8bitmime,starttls

# service for accepting messages FROM the DKIM signing proxy
127.0.0.1:10028 inet  n  -      n       -       10      smtpd
    -o content_filter=
    -o receive_override_options=no_unknown_recipient_checks,no_header_body_checks
    -o smtpd_helo_restrictions=
    -o smtpd_client_restrictions=
    -o smtpd_sender_restrictions=
    -o smtpd_recipient_restrictions=permit_mynetworks,reject
    -o mynetworks=127.0.0.0/8
    -o smtpd_authorized_xforward_hosts=127.0.0.0/8

If you want it to sign messages sent from the command line sendmail program, modify the pickup service to use the content_filter like this:

pickup    fifo  n       -       n       60      1       pickup
    -o content_filter=dksign:[127.0.0.1]:10027

Finally restart postfix with ‘postfix reload’, and you *should* have a working installation. You can now use my Domainkeys/Dkim validator to test and ensure that it is working.

CentOS Mail Toaster Guide Updated

I’m getting a chance to go through my own guide with several installations that I’m doing.  I noticed that PostfixAdmin has a new version 2.2.0 release available which changes the installation process significantly.   It is much simpler now and my howto guide has been updated to reflect the new changes.   Kudos to the developers who updated the installation process making it much simpler.

Getting Un-locked out of Webmin

Webmin has come a long way in the last year or two. I still (and always will) prefer the command line, but many customers are certainly much more comfortable using a web-interface to configure their server. I have to reset a password every once in a while and have to look up the steps every time. It seems I can never find them quickly, so I’ll put them here so I can find them easily next time.

Blocked IP addresses are stored a text file named /var/webmin/blocked. IP Address should be cleared out after some period of time, but you can hasten the process by clearing it manually:

cp /dev/null /var/webmin/blocked

Webmin passwords are stored in /etc/webmin/miniserv.users. A script for changing a user password is provided with webmin. Just run it like this:

/usr/libexec/webmin/changepass.pl /etc/webmin root
« Older posts Newer posts »

© 2025 Brandon Checketts

Theme by Anders NorenUp ↑