The mysql slow query log

The first step to tuning your database, is to find out what's running slow.

I recommend doing tuning your queries before changing your MySql memory allocation, sessions etc.

Its always better to treat the problem ground up (by finding rogue queries) rather than put a quick fix and forget about it (e.g. allocating more memory or setting sessions). Over time, the quick fixes will lead you to buy more hardware and cost more.

1. Setup the MySQL Slow query log:

To setup the log, first uncomment the log_slow_queries and long_query_time parameters:

# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 5
#log-queries-not-using-indexes

This will set the slow query log to: /var/log/mysql/mysql-slow.log and will log any queries that take more than 5 seconds.

To start with, you should set the long_query_time to a higher value, so you don't get overwhelmed with too many slow queries. Later you can bring this down to 2 seconds or so.

And later you should also set the 'log-queries-not-using-indexes, to identify queries that will slow significantly as data load increases.

2. Restart mysql:

In ubuntu, simply run the command:

sudo /etc/init.d/mysql restart

This will restart the mysql server and start logging your slow queries.

3. Use your database, and keep checking for slow queries!

I hope this helps you in tuning your MySql database from the ground up.

LAMP: Why does a page take too long to open?

If you're wondering why some pages take ages to open on your LAMP site, consider checking all the queries that hit your mysql database when you open that page.
Sometimes it is not slow queries, but too many reasonably fast queries, that are slowing you down.

Note: I suggest you try using your slow query log first! Here is a post that will help you.

For checking the database for all queries, you should be the only user on the database.

1. To know *all* that's hitting your mysql database, simply uncomment this command in your my.cnf file (in Ubuntu, it is located at: /etc/mysql; you need to be root or run 'sudo /etc/mysql/my.cnf')

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.

log = /var/log/mysql/mysql.log

2. Once you've uncommented this line, restart mysql server:

In ubuntu: sudo /etc/init.d/mysql restart

3. Clear startup logs:

Most likely you don't want anything to do with the startup queries, so simply clear the log:

sudo echo " " > /var/log/mysql/mysql.log

4. Run your test: Go ahead and open your troublesome page in your browser.

5. As soon as the page is opened fully, copy out the log (here it copies to your home directory):

sudo cp /var/log/mysql/mysql.log ~/slowpage.log

If you have more troublesome pages, clear the log again and copy it to another location, for analysis.

6. Once you're done with getting your logs, comment the log setup in my.cnf once more, and restart mysql again (steps 1 and 2). This will keep your mysql running fast.

7. Now go ahead and analyze what queries are run when you go to a particular page.

All the best with your tuning efforts!

MySql Session

To find out what is going on in your MySql database, you can check the sessions that are presently running, with the command:

show processlist;

Apache MySQL Performance dip

Separating the MySQL and Apache servers has slowed my site down, instead of speeding it up! Any suggestions?

I have hosted a LAMP website on a 2GHz AMD Opteron server running with 2GB of RAM.
As the data needs grew, the MySQL database seemed to need its own area, and could not serve the apache server fast enough.

So I split the MySQL database to run on its own 2GHz AMD Opteron/2GB RAM server - with a single-hop connection between the two of them at the data center.

But performance has dipped - any clicks take a much longer time to show a result from the database.
Any ideas how to get better performance from the separate Apache/MySQL server setup?

Thanks!

Password Management Using htpasswd

In Linux, several components use non-system standard passwords.

Some of these are svn, and ftp (e.g. vsftpd).

Here are the basics to get you through setting and resetting these passwords:

For svn, refer this guide.

For vsftpd, refer this guide.

Basically, htpasswd can be used to create encrypted passwords for such modules. Here are some basics to htpasswd:

1. htpasswd uses a password encrypted file, using an SSL certificate.
You can create your own ssl certificate too. But for public/production environments, you'd want to get a certificate from a third party so users don't get a security alert.

2. The first time, use the -c flag to set a password, as follows. You'll need to do this as root (or prefix sudo in ubuntu)

htpasswd -c /etc/apache2/my_passwd.passwd username

3. Subsequently, use the -m flag to modify this file for adding/editing users:

htpasswd -m /etc/apache2/my_passwd.passwd username

LAMP Server Crash

If you did some optimizations by adding memory or RAM to processes, and the next morning, you find your server is no longer online: chances are it ran out of space and triggered the oom-killer.

oom-killer is a process that goes randomly killing processes to help the system survive.
After this occurs you should always restart as soon as possible.

Here is where you can find the logs in ubuntu, and how to identify if your system shutdown because of memory:

location of log: /var/log/messages (you will have to have super user permissions to view)
What you'll see: Just before the server shutdown or went erratic, this message:

apache2 invoked oom-killer

To get out of this situation, try to put more conservative memory settings on your applications, or add more RAM!

All the best!

5 features Ubuntu needs to compete

Though I dearly love Ubuntu, and many of its features make windows seem obsolete...
But no matter how much I'd like to bid Windows adieu... there are reasons I need to still hang on to Windows - and no, its not just because of Word.

1. IE Compatibility
It's tiresome to go online and find a site (turbotax, netflix, web outlook...) which doesn't work well with firefox - especially linux based firefox. Sure there are workarounds and spoofs, but that's not a system working out of the box.

2. Multimedia experience
Ubuntu has some really neat multimedia features. Compiz Fusion adds mouthwatering features to your desktop - allowing you to rotate it like a cube for example.
But when I plug in an HDMI cable to connect a big screen tv, windows still supports the extension much better. My nVidia ubuntu driver asks for a restart, but doesn't do anything even after that.
DRM support is not present, which means the music industry leaves Linux users with no form of entertainment. No rhapsody to go.

3. Application Setup
It takes a whole lot of effort to get some apps to work. Skype, for example, needs a whole lot of tweaking and driver upgrades before it can work. For an app that needs speakers and a mic for basic functionality, setup should not be so difficult.
The soundcard drivers and programs, though feature rich, are not seamlessly integrated yet.

4. Much needed Applications
Some applications are altogether missing linux support. For example, no iTunes, no Microsoft Office are completely missing (sure the MS Office issue has been beaten to death - but really, at least give us an application that formats word documents accurately!) - and I'm not even talking hit games like GTA.
And yes, Wine can help get some things working. But any serious OS can't rely on workarounds.

5. Teething issues
Linux is lightweight, and efficient. Not!
That's what I always believed, until I wondered why my computer was so sluggish, when all I was doing was browsing the internet. Turns out, the Adobe Flash plugin on linux is a resource hog. Even if its just pandora playing, or Rhapsody online, the cpu usage peaks, and everything slows to a crawl.
Its a known issue. But is anyone fixing it?

Most of these are minor issues, and most of them have workarounds. But the question is, if Ubuntu linux is really going to compete mainstream, these need to be addressed. Linux has come a long way since its inception and text based output. But it still needs some polishing before it can kill the competition.