How to configure Mysql Server to maximize performance

Question

I hold a server with 12 cores CPU, 16GB RAM and 128GB SSD, using Centos as operating system, mainly for MySQL server. But I found that server always process queries with bad performance. I am not sure whether I configure my server correctly or not, who can give me a config file adapt to the current performance of my server(temporarily excludes problems caused by tables design).

More information about my use case and problems I met:

1. more than 80 schemas in my database, each schema includes 30 tables and used by different server applications.
2. querys becomes slow when some tables contains more than 10000 rows, and it will influence performance of all queries. What I found in process monitor is usage of CPU and RAM up to almost 100%.

Answer 1

First and foremost, among all system variables, the innodb_buffer_pool_size is regarded as the most important variable for the entire MySQL server. Unfortunately, a lot of users don't have the faintest idea of its existence, making their MySQL DB reduced to running at the default 128MB buffer pool. I would absolutely recommend you setting a high value (consider half of your ram if the server is dedicated for MySQL) for the innodb_buffer_pool_size variable in the options file. Below is a typical template of options file suggested by the gurus from Percona in their book High Performance MySQL 3rd edition. Fine-tuning is by no means an easy task. By reading from the professionals' experience, you are making steady progress though.

[mysqld]
# GENERAL
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
pid_file = /var/lib/mysql/mysql.pid
user = mysql
port = 3306
storage_engine = InnoDB
# INNODB
innodb_buffer_pool_size = <value>
innodb_log_file_size = <value>
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
# MyISAM
key_buffer_size = <value>
# LOGGING
log_error = /var/lib/mysql/mysql-error.log
log_slow_queries = /var/lib/mysql/mysql-slow.log
# OTHER
tmp_table_size = 32M
max_heap_table_size = 32M
query_cache_type = 0
query_cache_size = 0
max_connections = <value>
thread_cache_size = <value>
table_cache_size = <value>
open_files_limit = 65535
[client]
socket = /var/lib/mysql/mysql.sock
port = 3306