my.cnf optimization according to the configuration

Question

I am trying many problems with MySQL, with high memory usage and especially with high CPU usage.

I have a dedicated server with the following configuration:

8 CPU Intel(R) Xeon(R) CPU E3-1240 v6 @ 3.70GHz
16GB DDR3
SO Linux with cPanel/WHM + MySQL

Following is my.cnf file:

[mysqld]
max_connections=1000
wait_timeout=1000
interactive_timeout=1000
long_query_time=50
slow_query_log = 0
#slow_query_log_file = /var/log/mysql/slow-query.log

default-storage-engine=MyISAM
log-error=/var/lib/mysql/dc.leilaoweb.com.err
max_allowed_packet=268435456
local-infile=0
event_scheduler = on
tmp_table_size=300M
max_heap_table_size=128M
open_files_limit=65000
performance-schema=1

innodb_file_per_table=1
innodb_log_file_size=512M
innodb_buffer_pool_size=8G
key_buffer_size=512M
innodb_buffer_pool_instances=8

# config cache
query_cache_limit=8M
query_cache_size=256M
query_cache_type=1
table_open_cache=6000
table_cache=5000
thread_cache_size=96

#bind-address=127.0.0.1
#skip-networking
#performance_schema=ON

skip-name-resolve

How could I improve this setting to make queries faster and not raise server load?

Answer 1

It's a funny question that asks for help with query optimization, in which no specific query is mentioned.

Here are some tips on configuration options:

default-storage-engine=MyISAM

Change the default storage engine to InnoDB, and make sure all your existing tables are InnoDB. Don't use MyISAM.

query_cache_size=256M
query_cache_type=1

Set the query cache size and type to 0. The query cache is useful in such rare conditions that it has become deprecated, and removed in MySQL 8.0. It's better to cache query results in your application code, on a case-by-case basis.

innodb_buffer_pool_size=8G

If you have a lot more data than 8G, consider increasing the size of the buffer pool. The more of your data and indexes that resides in RAM, the better it will be for performance. But there's no further benefit to adding RAM once your data and indexes are 100% cached in the buffer pool.

And of course do not overallocate the buffer pool such that it causes the server to start swapping. That will kill performance (or else Linux's OOM killer will terminate mysqld if you have no swap).

key_buffer_size=512M

No need for extra memory allocated to the key buffer if you don't use MyISAM.

There may be other tuning parameters that can give benefit, but since you have said nothing about your queries or server activity, there's no way to guess what those would be.

You're better off focusing on indexes and query design.

In general, optimization naturally improves some queries at the expense of other queries. So you can make an optimization strategy only after you know which queries you need to optimize for.