Racker Hacker

MySQL replication may sound complicated, but it can be done easily. Here’s a quick 7-step guide:

1) Create a replication user on the master:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'password';

2) On the master server, add the following to the [mysqld] section in my.cnf and restart MySQL:

server-id = 1
relay_log=mysqldrelay
log-bin
expire_logs_days = 7

3) On the slave server, add the following to the [mysqld] sesion in my.cnf and restart MySQL:

server-id = 2

4) Create a mysqldump file on the master server which includes a global lock:

# mysqldump -u user -ppassword -FQqx --opt --master-data=1 --databases db1,db2 > databases.sql

5) Configure the slave:

# mysql -u user -ppassword
mysql> CHANGE MASTER TO MASTER_HOST='master host name', MASTER_USER='repl', MASTER PASSWORD='repl';

6) Move the dump to the slave server and import it:

mysql -u user -ppassword < databases.sql

7) Start the slave:

mysql -u user -ppassword
mysql> START SLAVE;

I’m taking a little bit of time off for Christmas, but I’ll be adding new posts on December 31st. I wish all of you a happy holiday season, and if you celebrate Christmas, a Merry Christmas as well!

I had some time to do some testing of my blog’s performance today, and I discovered how much of a difference the WP-Cache plugin makes.

This blog runs on a server with dual Xeon Woodcrest CPU’s, 64-bit CentOS 4.5 and a 100mbit network connection. Here’s the first test with WP-Cache turned off:

$ http_load -parallel 10 -seconds 30 urltocheck.txt
346 fetches, 10 max parallel, 1.78616e+07 bytes, in 30 seconds
51623.2 mean bytes/connection
11.5333 fetches/sec, 595387 bytes/sec
msecs/connect: 15.1661 mean, 16.97 max, 14.922 min
msecs/first-response: 445.984 mean, 2328.82 max, 189.62 min
HTTP response codes:
code 200 — 346

346 fetches in 30 seconds is not a very exciting performance number for me. That’s just over 10 fetches per second, and on a busy day, I sometimes reach that number. Also, while this test ran, the server’s CPU usage was extremely high and over 80% of all four cores were in use. The iowait was about 20% across the board.

I decided to turn on WP-Cache and give it another go with the same test:

$ http_load -parallel 10 -seconds 30 urltocheck.txt
3482 fetches, 10 max parallel, 1.79671e+08 bytes, in 30 seconds
51600 mean bytes/connection
116.067 fetches/sec, 5.98904e+06 bytes/sec
msecs/connect: 15.2259 mean, 18.257 max, 14.891 min
msecs/first-response: 20.7297 mean, 69.39 max, 18.861 min
HTTP response codes:
code 200 — 3482

Wow, that’s a 10-fold improvement, and I can handle over 100 requests per second with 10 parallel requests. Also, the iowait dropped to 5%, and overall CPU usage remained under 8%.

I kicked it up to 20 parallel connections and tried again:

$ http_load -parallel 20 -seconds 30 urltocheck.txt
5817 fetches, 20 max parallel, 3.02176e+08 bytes, in 30 seconds
51947 mean bytes/connection
193.9 fetches/sec, 1.00725e+07 bytes/sec
msecs/connect: 17.9175 mean, 30.831 max, 14.911 min
msecs/first-response: 24.5352 mean, 97.475 max, 18.978 min
HTTP response codes:
code 200 — 5817

Almost 194 connections served per second! Also, the CPU usage was only at about 14% during the duration of the test.

I decided to tempt fate and see if I could blow the roof off the test with 50 parallel connections:

$ http_load -parallel 50 -seconds 30 urltocheck.txt
5794 fetches, 50 max parallel, 2.99718e+08 bytes, in 30 seconds
51729 mean bytes/connection
193.133 fetches/sec, 9.99059e+06 bytes/sec
msecs/connect: 43.286 mean, 63.878 max, 14.942 min
msecs/first-response: 68.967 mean, 202.854 max, 20.014 min
HTTP response codes:
code 200 — 5794

The performance suffered a bit, but the server was still pumping out almost 200 connections per second, and I’m okay with that. Well, unless anyone has a spare Cisco 11501 laying around that I could have. And, of course, one additional server.

Just as a sidenote, I installed Zend Optimizer v3.3 on the server and performance actually dropped by 1%-3% for each test. I found that a bit surprising.

I used http_load to perform the benchmarks after I found it on Caleb’s blog.

I spoke to a customer recently who was concerned about their Red Hat Enterprise Linux 2.1 server and its Red Hat support status. After some digging, I found these items on Red Hat’s security site:

Red Hat Enterprise Linux (version 5)
End of Full Support: Mar 31, 2010
End of Maintenance Support: Mar 31, 2014

Red Hat Enterprise Linux (version 4)
End of Full Support: May 15, 2008
End of Maintenance Phase: Feb 29, 2012

Red Hat Enterprise Linux (version 3)
Full Support (including hardware updates): April 30, 2006
Maintenance Support: Oct 31, 2010

Red Hat Enterprise Linux (version 2.1)
Full Support (including hardware updates): Nov 30, 2004
Maintenance Support: May 31, 2009

Here’s the difference between the levels of support:

Full Support
Start Date: General Availability
End Date: 3 Years from General Availability date
Description: During the Full Support phase, new hardware support will be provided at the discretion of Red Hat via Updates, Additionally, all available and qualified errata will be applied to the Enterprise products via Updates (or as required for Security level errata.) And finally, updated ISO images will only be provided during Phase 1: Full Support.

Maintenance
Start Date: 3.5 years from General Availability (end of Deployment)
End Date: 7 years from General Availability
Description: During the Maintenance phase, only Security errata and select mission critical bug fixes will be released for the Enterprise products.

Data was gathered from http://www.redhat.com/security/updates/errata/

I stumbled upon a server running Plesk 8.2.1 where a certain user could not receive e-mail. I sent an e-mail to the user from my mail client, and I never saw it enter the user’s mailbox. It didn’t even appear in the logs.

After checking the usual suspects, like MX records, mail account configuration, and firewalls, I was unable to find out why it was occurring. Even after a run of mchk, the emails would not be delivered.

I began testing with a telnet connection to the SMTP port:

$ telnet 11.22.33.44 25
Trying 11.22.33.44...
Connected to 11.22.33.44.
Escape character is '^]'.
220 www.yourserver.com ESMTP
HELO domain.com
250 www.yourserver.com
MAIL FROM: test@test.com
250 ok
RCPT TO: someuser@somedomain.com
421 temporary envelope failure (#4.3.0)
QUIT
221 www.yourserver.com
Connection closed by foreign host.

Temporary envelope failure? I was still confused. After reviewing the logs, I found the following line whenever I tried to telnet to port 25 and send an e-mail:

Dec 2 00:15:49 www relaylock: /var/qmail/bin/relaylock: mail from 44.33.22.11:17249 (yourdesktop.com)

It turns out that the customer was using greylisting in qmail with qmail-envelope-scanner. After a quick check of /tmp/greylist_dbg.txt, I found the entries from me (as well as a lot of other senders), and that ended up being the root of the problem.

MySQLTuner revision 22 is available today. Here’s some of the notable fixes and changes:

» Changed how indexes are calculated on MySQL 5

Thanks to Jon Hinds, I found that when running the tuning script against MySQL 5, the following SQL statement caused MySQL to open all of the tables on the server, which of course caused the table cache hit rate to plummet each time the script is run:

SELECT SUM(INDEX_LENGTH) from information_schema.TABLES where ENGINE='MyISAM'

The script now calculates index size using du operations for all MySQL versions.

» Added checks for innodb_log_file_size

I’m working in some InnoDB support, and the script now checks to see whether the innodb_log_file_size is 25% (+/- 5%) of your innodb_buffer_pool_size.

» Added checks for 32-bit and 64-bit architectures

The script now determines if you have a 32-bit system with less than 2GB of RAM. You’ll get a polite suggestion to move to a 64-bit OS so that MySQL can allocate more than 2GB of RAM safely. Also, if your maximum possible memory usage is over 2GB on a 32-bit system, you’ll get a warning about stability issues. (Allocating more than 2GB on a 32-bit system can cause thread thrashing and a system crash.)

» Fixed a bug in the recommendations for temporary tables

I had a pretty ugly math error, and it’s fixed now. You will see recommendations for increasing the size of the max_heap_table and tmp_table_size buffers as long as they are not at 256MB already.

» Fixed thread cache recommendations and warnings

If your thread cache is set to 0, you now receive a warning about a disabled thread cache. Also, a separate recommendation is made in that situation. If your thread cache is set too low, but still enabled, a separate recommendation will appear.

» Internal changes

Some of the commented lines are switched around a bit, and some of the arrays have been consolidated to speed up the script a bit more.

Ready to download the script? Go to http://mysqltuner.com/ and get it for free.