With your initial feedback (you are using innodb), I can tell you that your innodb_buffer_pool_size is too small (2M), so most of your queries may be using disk instead of memory. As a rule of the thumb, for a dedicated server, the usual recommendation is reserving between 60-85% of the available memory for the innodb buffer.
Increase it by setting it in the my.cnf file. I have also added extra changes (as it can be seen below), mainly making the transaction logs larger and setting a lower durability by default. Plese make sure to follow the following procedure if you change the log file size:
- Shutdown mysql
- Change the configuration file, usually found at /etc/my.cnf
- Delete the transaction log files, usually found at /var/lib/mysql/ib_logfile0 and /var/lib/mysql/ib_logfile1. Do not delete anything else.
- Start mysql again, it may take some extra time to recreate the log files
[mysqld]
# I am assuming you have 500M of memory spare for your data
innodb_buffer_pool_size=500M
# This option is ignored by modern OS, no need to set it
#innodb_additional_mem_pool_size=500K
innodb_log_buffer_size=500K
# let innodb handle concurrency, comment this or it may create a bottleneck
#innodb_thread_concurrency=2
# Add a bigger transaction log, assuming you have 100MB extra disk for it
innodb_log_file_size = 50MB
# The following assumes that durability is not important, but it may provide
# a huge boost in perfomarnace
innodb_flush_log_at_trx_commit = 2
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
query_cache_limit = 1M
query_cache_size = 16M
# the following lines are duplicated, remove them:
#innodb_buffer_pool_size=2M
#innodb_additional_mem_pool_size=500K
#innodb_log_buffer_size=500K
#innodb_thread_concurrency=2
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
[mysql]
#no-auto-rehash # faster start of mysql but no tab completition
[isamchk]
key_buffer = 16M
Additional comments:
- You may tune the query cache to better manage your load, but only if you have mostly reads
- Setting a lower durability may make you lose transactions in case of a crash or a loss of power
- You have less than 1MB of indexes, please make sure that your query performance problem is not due to the lack of proper indexing (all tables have primary keys and secondary keys as needed).
Edit: for more automatic examples of my.cnf configuration, have a look at Percona's configuration wizard. For a full review of your queries and configuration, please hire expert mysql consulting services.
select count(*), engine, round(sum(data_length)/1024/1024) as data_size_MB, round(sum(index_length)/1024/1024) as index_size_MB from information_schema.tables WHERE table_schema not in ('mysql', 'information_schema', 'performance_schema') GROUP BY engine;I am suspecting bad buffer sizes.([0] => 45 [count(*)] => 45 [1] => InnoDB [engine] => InnoDB [2] => 143 [data_size_MB] => 143 [3] => 0 [index_size_MB] => 0)buffer size is from my.cnf ?