MySQL Administration and Performance Tuning

Important Variables 

=================== 

 

key_buffer_size	- used for index blocks  - aka. "key cache"
table_open_cache	- number of open tables for all threads

 

 

Common Performance tuning Tasks 

=============================== 

 

max performance  w/ moderate  number of  clients	mysqld_safe --key_buffer_size=384M \              --table_open_cache=4000 \              --sort_buffer_size=4M \              --read_buffer_size=1M &    # usually used if you have 1 - 2 GB of memory
few memory with  lot of sorting	mysqld_safe --key_buffer_size=64M --sort_buffer_size=1M    or    mysqld_safe --key_buffer_size=512K --sort_buffer_size=16K \             --table_open_cache=32 --read_buffer_size=8K \             --net_buffer_length=1K &
speeding up  operations	Scenario:  performing "GROUP BY" or "ORDER BY" on tables larger than  system memory    Solution:  increase read_rnd_buffer_size

 

 

Configuring the Server 

====================== 

 

Server  information

mysqld --verbose --help  mysqladmin variables  mysqladmin extended-status  # same as `SHOW STATUS;`    mysql> SHOW VARIABLES;  mysql> SHOW STATUS;  # statistical info

 

 

SQL Modes 

========= 

 

* Defined in `sql_mode` system variable 

* Can be set globally or per application basis 

* Things to avoid: 

    - change sql mode after inserting data into partition tables 

    - different sql modes between master and slave for replication 

* Important SQL Modes: 

    a. ANSI - closest to SQL 

    b. STRICT_TRANS_TABLE - abort statement if data cannot be inserted 

    c. TRADITIONA - behaves like traditional SQL system 

* Full list of SQL Modes: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html 

 

Setting	# runtime  SET GLOBAL sql_mode = 'modes';  SET SESSION sql_mode = 'modes';
Displaying	SELECT @@GLOBAL.sql_mode;  SELECT @@SESSION.sql_mode;