MySQL terribly slow

[Mr_Miyagi]

Hi all!
Having problems with mysql being terribly slow, at least I think it's mysql.
I noticed the problem when trying to use postfixadmin and mysql for virtual user database. Whenever I tried to add a new email or so, it took several minutes before anythning happened (got 'waiting for .....in the status bar of firefox). If I'd abort the operation the email (or whatever I added) would still have been created a few minutes later. I thought this problem was with postfixadmin or such...but now I'm experiencing the same thing having installed a project management software.
If I try to create a new project, or whatever, it takes about five minutes...but it works.
What on earth could cause this, been searching around but finding nothing.
I tried to enable log_slow_queries but when running mysqldumpslow I get the following:
Can't determine basedir from 'my_print_defaults mysqld' output: --datadir=/var/lib/mysql

Here's my my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
long_query_time = 1
log-slow-queries = /var/log/mysql/mysql-slow.log
#basedir=/var/lib/mysql

[mysql.server]
user=mysql
#basedir=/var/lib
basedir=/var/lib/mysql

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
As you can see I've tried around a bit, but mysqld won't start with basedir in the mysqld section.
In the mysql.server section I changed basedir=/var/lib to basedir=/var/lib/mysql

Any ideas on what could cause this slow behavior? Searching around on the mysqldumpslow issue I can't find much, one site indicates it's a bug...but I don't think it affects my problem.

Thanks!

[JediBob]

Ok, well aside from general system performance problems (which I guess you would have noticed elsewhere) one possibility would be a broken DNS configuration. IIRC When a client connects to mysql, mysql will perform a lookup on that client to try and resolve the clients details in DNS for various reasons (for example logging or access control) - It could be possible that for whatever reason DNS is breaking in such a way that this query is simply timing out. One way to test if this is the problem, is to start mysqld with the --skip-name-resolve option to stop it from doing this. If this resolves the issue, try looking at your DNS configuration etc.

Hope this helps.

Bob.

[Mr_Miyagi]

Thanks for you reply Bob.
Unfortunately that did not seem to fix the problem. I put skip-name-resolve in my my.cnf in the mysqld section and my log file indicates that it is using this option, but still the same problem...
I might add that I'm using my computers hostname (for the web-application) and I'm sitting locally at the computer in question.

[JediBob]

Hi again! Interesting, normally most connection timeout problems I've come across with mysql are DNS related, maybe not in your case... I wonder though, what happens if you try to connect using the mysql command line client (ie. mysql -h hostname -u user -p etc.) using the same credentials you have used to configure say your project management software? If you connect and then just try running a "show tables" command to list the mysql tables - does it respond in a timely fashion?

One other option would be to start mysql using the --log=<LOGFILE_NAME> option to produce a querylog, this should log to a text file all the querys Mysql handles in (more or less) real time, you could try using this and tail -f the logfile whilst trying to use the project management software to see if it is the query itself thats taking a long time to complete or if the delay is elsewhere..

[DamianS]

My money is on a DNS problem as well.
Are you sure it is MySQL timing out? Perhaps it is postfix?

[Mr_Miyagi]

Actually, I have to agree that this whole dns thing seems likely even though skip-name-resolve didn't help, maybe I did something wrong. When I try to add a project (or whatever) the browser says 'waiting for "hostname"...' and that continues for a couple of minutes, but when finally something happens it feels like it happens straight away. Even if I close down the window, the query will still happen in a few minutes, like it's just been stalled.
I'm very new (to say the least) to mysql so I'm not really comfortable using the command line...but I've given it a try, didn't work with 'show tables', however I gave the '\s' on the database in question and the last line seem interesting.

Threads: 1 Questions: 56 Slow queries: 0 Opens: 62 Flush tables: 1 Open tables: 56 Queries per second avg: 0.007
Note the "queries per second avg: 0.007", seems pretty quick to me :-)
I'll definitely look deeper into this dns thing. If you have any suggestions on how I would go about with this, please, I would really appreciate some input, have never worked with dns-servers before.
Cheers!
/Dave

[Mr_Miyagi]

Ok, I have now gone through my dns configuration thoroughly, at first I didn't see any difference, but I tried again now and it's working beautifully. Thanks for your replies, I really really appreciate your time and effort (if I could I would by you each a beer :) ).
Cheers!
/David