[BlueOnyx:22710] MariaDB / MySql Tuning

[Richard Barker]

How and where do we tune mysql it does not seem to wrok from the gui of 
the VPS now.

General recommendations:
     Control warning line(s) into /var/lib/mysql/blackbass.err file
     Control error line(s) into /var/lib/mysql/blackbass.err file
     Configure your accounts with ip or subnets only, then update your 
configuration with skip-name-resolve=1
     Adjust your join queries to always utilize indexes
     When making adjustments, make tmp_table_size/max_heap_table_size equal
     Reduce your SELECT DISTINCT queries which have no LIMIT clause
     Increase table_open_cache gradually to avoid file descriptor limits
     Read this before increasing table_open_cache over 64: 
http://bit.ly/1mi7c4C
     Read this before increasing for MariaDB 
https://mariadb.com/kb/en/library/optimizing-table_open_cache/
     This is MyISAM only table_cache scalability problem, InnoDB not 
affected.
     See more details here: https://bugs.mysql.com/bug.php?id=49177
     This bug already fixed in MySQL 5.7.9 and newer MySQL versions.
     Beware that open_files_limit (4162) variable
     should be greater than table_open_cache (2000)
     Performance schema should be activated for better diagnostics
     Consider installing Sys schema from 
https://github.com/mysql/mysql-sys for MySQL
     Consider installing Sys schema from 
https://github.com/good-dba/mariadb-sys for MariaDB
     Before changing innodb_log_file_size and/or 
innodb_log_files_in_group read this: https://bit.ly/2TcGgtU
Variables to adjust:
     query_cache_size (=0)
     query_cache_type (=0)
     query_cache_limit (> 1M, or use smaller result sets)
     join_buffer_size (> 256.0K, or always use indexes with JOINs)
     tmp_table_size (> 16M)
     max_heap_table_size (> 16M)
     table_open_cache (> 2000)
     performance_schema = ON enable PFS
     innodb_buffer_pool_size (>= 794.4M) if possible.
     innodb_log_file_size should be (=16M) if possible, so InnoDB total 
log files size equals to 25% of buffer pool size.

-- 

/*Richard C. Barker Sr.
CEO & President
1-813-873-8942
ProBass Networks Inc. */
www.probassnetworks.net <http://www.probassnetworks.net>
www.probass.net <http://www.probass.net>
***************************************
DISCLAIMER : -
This e-mail is confidential and intended only for the use
of the individual or entity named above and may contain
information that is privileged. If you are not the intended
recipient, you are notified that any dissemination, distribution
or copying of this e-mail is strictly prohibited. If you have
received this email in error, please notify us immediately
by return email or telephone and destroy the original message.

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.blueonyx.it/pipermail/blueonyx/attachments/20190224/a5adf864/attachment.html>