MySql configuration for Production Environment

Mysql Configurations for High Volumn of Txns:

Developers knows how to tune Mysql during run time. However some require restart, some not.
The most used commands are (total around 500), samples are given below:

show variables;
show variables like '%tx_isolation%';

mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 151   |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)

mysql> set global max_connections=200;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 200   |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)


1) SET GLOBAL
2) innodb_buffer_pool_size: This is the first setting to look for InnoDB. The buffer pool is where data and indexes are cached.
    Making it large as much possible will ensure you use memory and not disks for most read operations. 
    Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM).
3) innodb_log_file_size: Size of the redo logs. The redo logs are used to make sure writes are fast and durable and also 
    during crash recovery. Make it 1 Gb for better use. These are two files. innodb_log_file_size = 512M (giving 1GB of redo logs).
4) max_connections: If you are often facing the ‘Too many connections’ error, max_connections is too low. Using a connection pool 
    at the application level or a thread pool at the MySQL level can help here.
    
5) innodb_file_per_table: This setting will tell InnoDB if it should store data and indexes in the shared tablespace 
    (innodb_file_per_table = OFF). Or store in a separate .ibd file for each table (innodb_file_per_table= ON). Having a file per table 
    allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as 
    compression. However it does not provide any performance benefit.    

6) innodb_flush_log_at_trx_commit: the default setting of 1 means that InnoDB is fully ACID compliant. It is the best value when your 
    primary concern is data safety.
7) query_cache_size: The query cache is a well known bottleneck that can be seen even when concurrency is moderate. The best option is 
    to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6).
8) For Enabling Logs in Mysql:

    set global general_log=1;
    show variables like '%SQL_LOG%';       //SQL_LOG_OFF should be ON
    set global general_log=ON;             //1 or ON both are same
    show variables like 'GENERAL_LOG%';    //GENERAL_LOG should be ON
    show variables like '%long_query_time%';  
    set @@GLOBAL.long_query_time=1;
    show global variables like '%long_query_time%';
    show session variables like '%long_query_time%';  //Will show the older value. 
                        //Exit Mysql and Re-login and fire the same query.
                        
9) Make sure the database tables are using InnoDB storage engine and READ-COMMITTED transaction isolation level.
   show variables like '%tx_isolation%';   //REPEATABLE-READ becomes slow for insertion, selection.

10) Increase the database server innodb_lock_wait_timeout variable to 500.