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!