QNAP - MySQL server SLOW response time / PHP

Question

We are going to use our QNAP as a Web Dev server.

We have the following:

TS-EC1279U-RP (Quad Core Intel Xeon E3-1225 3.1GHz Processor with 4GB ram & 12 HDDs in RAID 6)
PHP Version 5.3.9
MYSQL Server version: 5.1.36

We have installed Drupal and Joomla, however the response time seems to be very slows. We are waiting about 5 - 6 seconds before we get a response. We have edited the my.cnf for my sql (see below)

[client]
#password   = your_password
port = 3306
socket      = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket      = /tmp/mysql.sock
skip-locking
skip-networking
skip-name-resolve

max_connections=500
key_buffer_size=384M
max_allowed_packet=16M
table_cache=256
sort_buffer_size=2M
read_buffer_size=2M
join_buffer_size=2M

thread_cache=256

query_cache_type=1
query_cache_limit=1M
query_cache_size=32M

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[isamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M

[myisamchk]
key_buffer=128M
sort_buffer_size=128M
read_buffer=2M
write_buffer=2M

[mysqlhotcopy]
interactive-timeout

However even with the change above recommended by different sites, the performance got better but just by a little.

We had our test sites on the QNAP and a hosting company. The response on the hosting company's servers is much faster. I know they have better hardware however the CPU charts on the QNAP dont even go above 3%.

Thank you

Update:

I have added the log-slow-queries. I have used the site for about 30 minutes: The results so far:

/usr/local/mysql/libexec/mysqld, Version: 5.1.36-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
/usr/local/mysql/libexec/mysqld, Version: 5.1.36-log (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument

There is not indication that there is a SQL query longer than 1 second what so ever. So I am not sure to be honest if it is the PHP.ini or my.cnf setting.

Thanks

Take a look at dev.mysql.com/doc/refman/5.1/en/slow-query-log.html to identify your slow queries and try to optimize them. — Matt
@matt I do not think so. I have tried a FRESH install of joomla, drupal and word press. They all seem to be around 5-6 seconds and I also copied a production site. Same results. The live site is about 0.8 sec (real time) and the qnap is about 5-6 seconds. I will try the link you provided. Thank you — Arthor
In my experience, most of the time I get a slow response from the database is because a query isn't optimized. Make sure your tables are indexed properly after logging any slow queries and you'll see a HUGE performance increase (if indexing was the issue, of course). — Matt
Most GUIs have a point-click-type way of adding indexes to your columns. To figure out which columns need indexes, do an EXPLAIN on your queries (just type EXPLAIN before the SELECT and execute the query). Basically it will tell you how many rows were hit in each table when trying to execute the query. The high-hit tables require indexes on the comparison columns. — Matt

Arthor Arthor · Accepted Answer · 2012-08-11T14:44:31

After playing around with MYSQL and PHP. The problem was found.

Firstly I increased the PHP memory to 512M, overkill I know but that made a small difference.

The key was the website.

Application afterLoad: 0.000 seconds, 0.35 MB Application afterInitialise: 0.020 seconds, 1.17 MB Application afterRoute: 0.029 seconds, 1.36 MB Application afterDispatch: 0.064 seconds, 2.03 MB Application afterRender: 6.15 seconds, 2.72 MB

The issue was a RSS feed on the sites page. However after disabling it, the speed were fast as hell.

Application afterRender: 0.126 seconds, 2.83 MB

Thanks to matt for the ideas and support.

QNAP - MySQL server SLOW response time / PHP

1 Answers