I have a single processor dedicated server with 4GB RAM and a 400MB Mysql database (Myisam) who has big performance problems. The database is used by an ecommerce. I already tryied to tune it using the mysqltuner script, but without good results. Because the variable settings have been modified several times, I would like to have a basic configuration to start from, thereafter try to tune it.
Asked
Active
Viewed 310 times
0
-
I assume you have tried looking into table indexes, and run EXPLAIN statements on key SQL queries to find out which queries have the biggest bottlenecks. Tools like what Nilesh is recommending will help you on this area. You might also want to look into how your applications executing your queries, for example if it can be cached in application level, cache it. – forestclown Jul 31 '15 at 02:17
2 Answers
0
For ecommerce, you need InnoDB. If you don't change, you will be burned badly when a crash occurs at just the wrong instant in a monetary transaction.
Make that change, then
key_buffer_size = 20M
innodb_buffer_pool_size = 1000M
read my blog on moving from MyISAM to InnoDB.
When you then find that things are not working fast enough, do
- long_query_time = 1
- turn on the slowlog
- wait a day
- run pt-query-digest to find the worst couple of queries
- present them to us for critique. The solution could be as simple as adding an composite index. Or maybe reformulating a SELECT.
I have redirected you toward slow queries because you cannot "tune" your way out of bad schema, bad queries, etc.
Rick James
- 135,179
- 13
- 127
- 222