MySql: September 2009 Archives

MySql Query Logging

Smart database usage is about more than optimizing slow queries. You also need to make sure you’re only running queries when necessary. A single N+1 query can really cause some serious problems, but luckily there’s a pretty easy way to find these hot spots: MySql query logging.

To enable query logging follow these steps (Tested with MySql 5.0):

  1. Add this to your my.cnf:
log=/var/log/mysql/query.log
  1. Make sure the directory exists and the user that is running mysql has write access to if.
sudo mkdir -p /var/log/mysql/
sudo chmod 750 /var/log/mysql/
sudo chown mysql:mysql /var/log/mysql/
  1. Restart your MySql server using whatever command is appropriate for your OS: /etc/init.d/mysql restart
  2. Generate some traffic
  3. Note the file size of the log increasing. It’s going to grow pretty fast so don’t leave it running overnight or your disk may fill up!

Now that you have some data you’ll need to analyze it. There are lots of tools available, but I’ve had good luck with the simple but awesome open source php script called myprofi

php ./parser.php query.log  > analysis.txt
By Author on September 4, 2009 3:04 PM 0 Votes

MySql ERROR 1153: Got a packet bigger than 'max_allowed_packet' bytes

You're probably here because you were trying to load a MySql database dump and you saw something like this: 
bar@foohost:~$  mysql -uroot -p some_database < some_big_dump.db
Enter password: 
ERROR 1153 (08S01) at line 42: Got a packet bigger than 'max_allowed_packet' bytes
Don't panic! This one is easy to fix. It's happening because somewhere on either the MySql client or the MySql server, the max_allowed_packet is being exceeded. It's an easy fix, but you will need to bounce your server.
  1. Edit your /etc/my.cnf and look for the parameter max_allowed_packet. In my case it was set to 1M, which is obviously not big enough. Set it to something larger (but not insanely large). I changed mine to 16M.
  2. Save the file and bounce MySql (/etc/init.d/mysql restart on redhat).
  3. Run your import again, but this time specify an explicit maximum packet size on your client as well. Try this command: 
    bar@foohost:~$  mysql -uroot -p some_database --max_allowed_packet=16M < some_big_dump.db
Enter password: 
bar@foohost:~$
I hope it worked that time. If not, try making your max_allowed_packet a bit bigger. Once you're done don't forget to set this parameter back to the original size!
By Author on September 2, 2009 5:09 PM 0 Votes
Create Entry
Blog Home | Archives

About this Archive

This page is an archive of entries in the MySql category from September 2009.

Find recent content on the main index or look in the archives to find all content.

Pages

MySql: Monthly Archives

Categories

Monthly Archives