0

I have feeling that mine mysql server is slower than normal while processing queries from the PHP website.

Is there anything like i can allocate more resources to Mysql server to make it speedy?

I don't know much about this , So need your help .

I am on Ubuntu 10 server.

Note: If the above question silly and not practical looking , then NVM . :) .

user44520
  • 205
  • 1
  • 5
  • 8

4 Answers4

2

Try enable all mysql buffers and cache. Also, be sure that the server has enough RAM for this (I recommend at least 6GB of memory for a small amount of queries). Below you can find some of the buffers that I added to my my.cnf:

key_buffer = 128M

join_buffer_size = 5120K

innodb_buffer_pool_size = 8129M

key_buffer = 16M

max_allowed_packet = 64M

table_cache = 2048

query_cache_limit = 128M

query_cache_size = 512M

tmp_table_size = 128M

max_heap_table_size = 128M

Also, another thing that you should do is to disable the reverse lookup. Here is the var for the config:

skip-name-resolve

Also, modify your wait_timeout (how much time a child should be alive if no query is made):

wait_timeout = 300

All of the above settings need fine tuning for your configuration, so please don't just copy/paste because there is the possibility to have worse results. After you done all these settings, monitor everything using mysqlreport (mysqlreport -u username --password=password)

MihaiM
  • 728
  • 1
  • 9
  • 17
1

With the info you gave as I understand that you have installed everything from apt, so no special configuration is running.

This makes me consider on which can be the point with more chances to be the bottleneck. And no offense, but probably is the php code, if the apt repos had a bad config there will be more people with the same issue.

So , what do you need is to debugg a bit your code, and that is not fun.

You may try 'mtop', it's a 'top' or 'htop' tool to look inside the mysql server, with this you'll be able to know which queries are taking more time.

Also, if mysql its in a different server and you don't use dns it usually adds 20 seconds to your queries, sounds weird but it happend to me a month ago.

If nothing of what i've said helps, please explain yourself a bit longer. thanks.

Marc Riera
  • 1,637
  • 4
  • 23
  • 38
0

You could activate the slow query log and start with the long-running queries. I bookmarked the following page, even when it is in German: http://techblog.tilllate.com/2006/12/23/optimierung-von-mysql-abfragen-der-slow-query-log/

mysqldumpslow slow.log -t 10 -s t

Maybe you run theses queries too often? You can use EXPLAIN and see, if you should create another index for a better performance.

There is a blog about the mysql performance: http://www.mysqlperformanceblog.com/

The mysql manual has a chapter for performace questions: http://dev.mysql.com/doc/refman/5.5/en/innodb-performance.html

da_didi
  • 255
  • 4
  • 11
0

First, make sure you are running on 64bits instead of 32bits. Second, verify how your storage is doing (I/O waits). If you see a lot of I/O wait % , probably you will require to improve your storage subsystem. (Better controller, Faster disks, RAID, and so on) Third, make sure you have logs written to a different partition (DISK), so you can isolate writes.

cargom98
  • 76
  • 1