Tell me more ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.
up vote 0 down vote favorite

I just installed AMP stack on a VPS. I logged in to mysql console and found out that queries were incredibly slow. A single update query, targeting 1 row in a table with ~200 rows took 0.5 seconds to complete (same query takes 0.001s to complete on my windows laptop).

I thought it's related to VPS not having enough resources, but after doing some benchmarks, namely I/O:

1073741824 bytes (1.1 GB) copied, 14.207 s, 75.6 MB/s

avg-cpu:  %user   %nice %system %iowait  %steal   %idle  
           0.01    0.00    0.00    0.03    0.00   99.96

and a PHP benchark script, it seems there are no problems with resources. PHP benchmark script completed in 5 seconds, whereas it took my i7 laptop with SSD 17 seconds to complete.

Any ideas what might be wrong?

share|improve this question

2 Answers

up vote 0 down vote

Some tests and questions that got too long for a comment:

What engine are you using for this table (SHOW CREATE TABLE tablename\G, look for ENGINE at the bottom)?

Does the performance improve after cache warms (that is, if you run the query twice with slightly different update data, is the second run faster)?

Can you list the contents of your my.cnf?

What do you get when running strace -cp $(pidof mysqld) while running the UPDATE? To do this, run the strace, then run the query, then hit CTRL-C on the strace. It'll spit out some very handy values.

If I were only given one guess, I'd guess that you don't have skip-name-resolve, and you're connecting from an address that doesn't respond to either reverse or forward DNS (or your resolv.conf is misconfigured). But that is purely a shot in the dark without the information requested above. Easy test: connect first (that is, run the mysql CLI with appropriate host and port parameters), then run your UPDATE from within that interactive session and see if the overall speed is faster versus running the query in batch mode with -e.

share|improve this answer
1. InnoDB - problem is not related to table. Importing sql source file also took forever. 2. no. it fluctuates a lot though, from 0.1 to 1.0 seconds 3. comments stripped: ` [mysqld] user=mysql datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock symbolic-links=0 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ` – Netismine Jan 3 at 14:51
3. this is the output after refreshing website homepage: i46.tinypic.com/ilvqxs.png – Netismine Jan 3 at 14:53
I have three mysql processes running - my shell process, mysql and mysql_safe. which one would you like me to strace? – Netismine Jan 3 at 14:56
I now attached it to mysql shell process (me logging in to mysql command line) and ran a few queries. output : i47.tinypic.com/2ho8bqq.png – Netismine Jan 3 at 15:00
@Netismine: You want to attach it to the "mysqld" process. Can you post your my.cnf in the question section as an edit, so it's easier to read? – BMDan Feb 4 at 16:56
up vote 0 down vote accepted

Issue was not solved. I moved hosting providers and got expected results. I coldn't find out what originally caused the issue.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.