How to speed up Percona-server(mysql)?

Question

MySQL

I want to achieve maximum performance from Percona Server;
All database in InnoDB, 7 GB database. On regular mysql, with "avctonconfiguration" - it barely crawled.
How to achieve maximum performance from Percona?
Logs:
TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
[Warning] Using unique option prefix myisam_recover instead of myisam-recover-options is deprecated and will be removed in a future release. Please use the full name instead.
[Note] Plugin 'FEDERATED' is disabled.
[Note] InnoDB: Using atomics to ref count buffer pool pages
[Note] InnoDB: The InnoDB memory heap is disabled
[Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
[Note] InnoDB: Memory barrier is not used
[Note] InnoDB: Compressed tables use zlib 1.2.8
[Note] InnoDB: Using Linux native AIO
[Note] InnoDB: Using CPU crc32 instructions
[Note] InnoDB: Initializing buffer pool, size = 5.0G
[Note] InnoDB: Completed initialization of buffer pool
[Note] InnoDB: Highest supported file format is Barracuda.
[Note] InnoDB: 128 rollback segment(s) are active.
[Note] InnoDB: Waiting for purge to start
[Note] InnoDB: Percona XtraDB ( www.percona.com ) 5.6.23-72.1 started; log sequence number 1625987
[Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
[Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
[Note] Binlog end
[Note] InnoDB: FTS optimize thread exiting.
[Note] InnoDB: Starting shutdown...
[Note] InnoDB: Shutdown completed; log sequence number 1625997

Answer 1

There is no silver bullet!
To understand where the bottleneck is specifically in your installation, you need to monitor the server parameters - what is being forced out of memory to disk and starts to slow down. At one time, https://www.webyog.com/product/monyog helped a lot - in retrospect, you can see when buffers overflow under load and disk operations are in progress.
In general - to google and understand what parameters affect the performance of the server :) To begin with, answer the questions: how much memory is on the server and how much can be given to the muscle? The InnoDB engine means tweaking the parameters with INNODB_ https://dev.mysql.com/doc/refman/5.5/en/innodb-par...
UPD. And read about innodb_flush_log_at_trx_commit - select the value you need, the standard rake of optimizers ishabrahabr.ru/post/108418

Answer 2

It completely depends on what kind of data, what types of operations are performed.

Answer 3

put in my.cnf innodb_buffer_pool_size = 8G
it is also worth looking at load time what is happening mysqladmin pr
- which queries take a long time to execute and in what state they are.
There is also a tool https://raw.githubusercontent.com/major/MySQLTuner... but that's not a silver bullet either. Applying her advice, be prepared for things to get worse. make backups of configs.

Answer 4

It is necessary to look at the load and its type - created by MySQL.
After that, look at the state of the database and the current MySQL settings.
Here you need to at least access mysqltuner.pl and analyze its output + there are a couple more utilities for analyzing MySQL operation (I only have them in the form of a BASH script).
You can also manually monitor the performance of MySQL and turn the necessary knobs - this is usually the most effective method (but you need to know a lot for it).
After the analysis, you can already change the MySQL configuration and after a couple of busy days, look at the new statistics and so on until you get the desired result.

Answer 5

You here:
https://www.percona.com/blog/2013/09/20/innodb-per...
https://www.percona.com/blog/2007/11/03/choosing-i.. .