Racker Hacker

On some operating systems, postgresql is not configured to listen on the network. To enable the TCP/IP connections, edit the /var/lib/pgsql/data/postgresql.conf and change the following:

tcpip_socket = true
port = 5432

Restart postgresql and you should be all set:

/etc/init.d/postgresql restart

If there’s one question I get a lot, it would be “Hey, how can I speed up MySQL?” There’s absolutely no end-all, be-all answer to this question. Instead a combination of many factors contribute to the overall performance of any SQL server. However, here’s a list of my recommendations for great MySQL performance. They’re arranged from the biggest gains to smallest gains:

Query Optimization
I know - you were hoping I’d talk about hardware to start this thing off, but optimizing queries is the #1 way to get a MySQL server in gear. MySQL gives you great tools, like the slow query log, multiple status variables, and the EXPLAIN statement. Put these three things together and your queries will be on their way to a more optimized state. I’ll go into great detail about query optimization in a later post.

Memory / System Architecture
We all know MySQL likes RAM, and the more you give it (to a point) the better the performance will be. If you consider the alternative to memory, which is swapping on disk, it’s obvious to see the gains.

So why did I add system architecture to this section? Well, if you have 32-bit Redhat, you can only allocate 2GB per process with the standard kernel. If you jump up to the SMP or hugemem kernel (in ES 2.1, you need the hugemem kernel for this to work), you can allocate 3GB per process. There is a caveat - MySQL can only use 2GB per buffer in 32-bit land. In a 64-bit OS with an appropriate Redhat kernel, you can allocate much larger buffers, and this can be tremendously helpful to tables which use the InnoDB engine. The memory allocation abilities are a great benefit, but also keep in mind that you will also get a boost in math performance within MySQL due to the 64-bit architecture. It’s a win-win!

Disk Performance
Running a critical database on IDE or SATA drives just doesn’t cut it any more. A SCSI or SAS drive is required for the best performance. Although you hope that MySQL doesn’t touch the disk much, it’s important to remember that you need to make backups often, and you may need to restore data. Also, if your site is write-intensive, the disk performance is much more important than you think. It will reduce the time that tables are locked, and it will also reduce the time for backups and restores.

CPU
Although CPU comes last, don’t forget how important it can be. If you run a high number of complex queries and perform a lot of mathematical operations, you’re going to need a CPU that can handle this load. Dual CPU’s or dual core CPU’s will help out even more, since MySQL can use multiple CPU cores to perform simultaneous operations. Keep in mind that 64-bit will outperform 32-bit in MySQL, and also allow for greater memory allocations (look in the Memory section above).

Final Note:
Keep in mind that these are general suggestions, and these suggestions may not apply to all users. For example, on sites that are heavily read-intensive, you may find that CPU speed is more important than disk speed. Also, if you’re not using all of the available memory on your server, but your performance is still sagging, adding more memory won’t help. Consult with a DBA and find out where your server’s slowdowns are, then make a change with your queries or with your hardware. Remember, throwing more hardware at the problem will not always solve it.

On some servers, you may notice that MySQL is consuming CPU and memory resources when it’s not processing any queries. During these times, running a mysqladmin processlist will show many processes in the ’sleep’ state for many minutes.

These issues occur because of code that uses a persistent connection to the database. In PHP, this is done with mysql_pconnect. This causes PHP to connect to the database, execute queries, remove the authentication for the connection, and then leave the connection open. Any per-thread buffers will be kept in memory until the thread dies (which is 28,800 seconds in MySQL by default). There’s three ways to handle this type of issue:

Fix the code
This is the #1 most effective way to correct the problem. Persistent connections are rarely needed. The only time when they would be even mildly useful is if your MySQL server has a huge latency. For example, if your web server takes > 250ms to make contact with your MySQL server, this setting might save you fractions of a second. Then again, if your web server and MySQL server are so far apart to where latency is even a consideration, you have more problems than I can help you with.

Restrict the connections
If push comes to shove, and you have users on a server who are abusing their MySQL privileges with mysql_pconnect, then you can pull the plug on their shenanigans with GRANT. You can reduce the maximum simultaneous connections for their database user, and they’ll find themselves wanting to make code changes pretty quickly. MySQL doesn’t queue extra connections for users who have passed their maximum, so they get a really nice error stating that they have exceeded their max connections. To set up this grant, just do something like the following:

GRANT ALL PRIVILEGES ON database.* TO 'someuser'@'localhost' WITH MAX_USER_CONNECTIONS = 20;

Reduce the timeouts
If changing the code isn’t an option, and you don’t feel mean enough to restrict your users (however, if they were causing a denial of service on my MySQL server, I’d have no problem restricting them), you can reduce the wait_timeout and interactive_timeout variables. The wait_timeout affects non-interactive connections (like TCP/IP and Unix socket) and interactive_timeout affects interactive connections (if you don’t know what these are, you’re not alone). The defaults of these are fairly high (usually 480 minutes) and you can drop them to something more reasonable, like 30-60 seconds. Web visitors shouldn’t notice the difference - it will just cause the next page load to start a new connection to the database server.

Thanks to a highly awesome technician on my team, we’ve discovered the perfect permissions setup for Joomla and Plesk:

Change the umask in ‘/etc/proftpd.conf’ to ‘002′ and add the ‘apache’ user to the ‘psacln’ group. Then, update the directory permissions:

cd /home/httpd/vhosts/[domain.com]
chown -R [username]:psacln httpdocs
chmod -R g+w httpdocs
find httpdocs -type d -exec chmod g+s {} \;

Joomla also complains about some PHP settings, sometimes including not being able to write to ‘/var/lib/php/session’. To fix the issues, make some adjustments to the vhost.conf for the domain:

<Directory /home/httpd/vhosts/[domain]/httpdocs>
php_admin_flag magic_quotes_gpc on
php_admin_flag display_errors on
php_admin_value session.save_path /tmp
</Directory>

If the vhost.conf is brand new, then run:

/usr/local/psa/admin/bin/websrvmng -av

Make sure Apache runs with your new configuration:

# httpd -t (check your work)
# /etc/init.d/httpd reload

Credit for this goes to Bryan T.

If you need to strip query strings from a URL with mod_rewrite, you can use a rewrite syntax such as the following:

RewriteEngine on
RewriteCond %{QUERY_STRING} "action=register" [NC]
RewriteRule ^/. http://www.domain.com/registerpage.html? [R,L]

If you’re checking through your mail logs, or you catch a bounced e-mail with “554 relay access denied” in the bounce, the issue can be related to a few different things:

If your server bounces with this message when people send e-mail to you:

• Check to make sure that your mail server is configured to receive mail for your domain
  • Postfix: /etc/postfix/mydomains (on some systems)
  • Sendmail: /etc/mail/local-host-names
  • Qmail: /var/qmail/control/rcpthosts
• Verify that your MX records are pointing to your server, and not someone else’s (very important during server migrations)
• If you recently made changes in Postfix, make sure to run postmap on your domains file and run postfix reload

If you get this message when you try to send e-mail to other people through your server:

• Enable SMTP authentication in your e-mail client
• If SMTP authentication is on in your client, check your server’s authentication daemons to be sure they’re operating properly

If you can’t see hidden files in proftpd (the files beginning with a dot, like .htaccess), you can enable the option in your client. However, you can force the files to be displayed in almost all clients with a server wide variable in your proftpd.conf:

ListOptions -a

Make sure to restart proftpd afterwards and re-connect to the FTP server to see the changes.

To enable SSL/TLS support in proftpd, add the following to the proftpd.conf file:

<IfModule mod_tls.c>
    TLSEngine on
    TLSLog /var/ftpd/tls.log
    TLSProtocol TLSv1
    TLSRequired off
    TLSRSACertificateFile /usr/share/ssl/certs/server.crt
    TLSRSACertificateKeyFile /usr/share/ssl/private/server.key
    TLSCACertificateFile /usr/share/ssl/certs/cacert.crt
    TLSVerifyClient off
    TLSRenegotiate required off
</IfModule>

To require SSL/TLS on all connections, change TLSRequired to on. Of course, replace the certificate, key, and CA certificate (if applicable) to the correct files on your system.

Once you’re all done, make sure to restart proftpd to activate the changes.

If you can’t send mail via port 25 due to blocks imposed by your ISP, you can enable the submission port within Plesk pretty easily. There’s two methods:

The iptables way:
iptables -t nat -A PREROUTING -p tcp --dport 587 -i eth0 -j REDIRECT --to-ports 25

The xinetd way (recommended):
# cd /etc/xinetd.d
# cp smtp_psa smtp_additional
# vi smtp_additional

Make the first line say “service submission” and save the file. Then restart xinetd:

/etc/rc.d/init.d/xinetd restart

This is no longer needed in Plesk 8.4. To enable the submission port in Plesk 8.4, log into the Plesk interface as the Administrator, click Server and click Mail.

If you find that Horde (with Plesk) keeps refreshing when you attempt to log in, and there are no errors logged on the screen or in Apache’s logs, check the session.auto_start variable in /etc/php.ini.

If session.auto_start is set to 1, set it to 0 and Horde will miraculously start working again.