MySQL slowing down over time

I’ve got a mySQL server based in a Debian distro with updates etc, in a virtual (vmware) server.

Over time the slow queries log starts to get entries and it seems that the more it gets the slower mySQL is responding to all queries.

Shutdown and restart the server and it’s now 5 days without a single slow query entry. (of course, I could have buggered up the slow query logging, but probably not)

I have started trawling to find a possible solution, the now unmaintained blog Hack mySQL has this reporting process that I might want to come back to.

Yes, I came back to this and you may need to fiddle with the mysqld.sock location but otherwise this script works fine. Also use the prompt for your username and password if it is not root. The script appears to fall over with the following error:
[php]KEY BUFFER
ERROR 1317 (70100) at line 1: Query execution was interrupted
[/php]
but in fact it is just thinking. Wait 5 minutes or so (I didnot time it) and it will complete.

The information about your config and resources may just point you at some setting to improve the performance.

However for me I have not used the tuning script to makes changes as yet.

Earlier in the week I was checking the DNS settings as a result of a thread (that I have lost the link to) which led me to read both the basic notes at MySQL on DNS and this entry at HackMySQL

Now I am not copying the information from those sites verbatim, but suffice to say that your MySQL server and the application server(s) making requests must all see each other correctly by hostname and ip address. If your hosts file (local) or DNS server (local or remote) have incorrect details it may result in regular misdirection of the responses from the database.

In my case I found a small mistake in the hosts file on the mySQL server that was misdirecting responses to the wrong server. i.e the app server was requesting but being found with a different location on reverse lookup. I made that change last weekend and this week we have 0 entries in the slow log and no user issues. (At least none we were told about).

On that basis I think it might be case closed.

So if your MySQL is slowing for no apparent reason check and double-check the name resolution forward and backward.

Syncing iPhone / iPad with 2 computers

I use my own notebook for most of my work but recently took on a new contract role that included a work notebook. As I use my iPad for meeting notes and recently on an interstate trip found that I did not need my notebook until I got home (might be able to travel lighter!)

In any case I need to be able to sync with both notebooks so that I can manage the various documents I generate.

For me I have previously done something similar when moving from an older system to a new system and simply copied the iTunes folder from the My Music directory from the old to the new. This morning I ensured that I had both systems with the current iTunes and then sync’d my iPhone and iPad with my personal notebook. I then copied the iTunes folder to the new work notebook (having renamed the newly created iTunes folder to iTunes.original just in case). Once that was done I plugged in my iPhone to the new notebook. iTunes prompted to transfer Purchased Apps which was ok, and later I agreed to Authorise this computer using my regular account in the iTunes store. Once that was complete, I did the same process with my iPad and it all works. To confirm that I had no issues I then did a repeat sync with my personal notebook which also worked. Sync’ing with two notebooks works.

I also read with interest this entry from Andrew Grant’s ‘Shiny Things’ blog and I will want to investigate this further.

MySQL & PHP Upgrade CentOS server

I now appear to have a new Linux VPS running with CentOS and Plesk. I wont go into the stupid details of why this has taken 2 weeks to provision, or another 2 days for me to ‘fix’ the ip addresses etc, only to find that CentOS has an older version of PHP at 5.1 and MySQL needing to be upgraded to match with PHP 5.3 when I tried to upgrade.

I referenced Christian Montoya’s blog for the update for PHP but found that I had a couple of issues.

The first was that when I ran the yum command for the PHP upgrade it complained about the MySQL support / version.

yum update php

was changed to include MySQL

yum update php mysql

Which did what I needed.

Running the

php -v

Advised that the updated 5.3 version was installed but I then found that MySQL was not running.

A quick wade around discovered that /var/log/mysqld.log that there was another error preventing MySQL from starting. This was the message:

[ERROR] /usr/libexec/mysqld: unknown option '--skip-bdb'

Editing the /etc/my.cnf file to comment out this option in both the main section and the mysql_safe section of the file fixed this issue and allowed MySQL to start.

Next I had in the mysql log messages like:

[ERROR] Column count of mysql.proc is wrong. Expected 20, found 16. 
Created with MySQL 50077, now running 50156. 
Please use mysql_upgrade to fix this error.

So trying to use mysql_upgrade as a command by itself fails for user root@local host

]# mysql_upgrade
Looking for 'mysql' as: mysql
Looking for 'mysqlcheck' as: mysqlcheck
Running 'mysqlcheck with default connection arguments
mysqlcheck: Got error: 1045: Access denied for user 'root'@'localhost' (using password: NO) when 
trying to connect
FATAL ERROR: Upgrade failed

Using the correct username is as simple as 

#mysqlupgrade -u username -p  [Enter]

Which will prompt you for the password and then run the upgrade with the authenticated username.

And that, I hope, completes the backend upgrade so I can run SugarCRM on this server.