The mailing list for listmasters using Sympa

[Phil Stracchino <address@concealed>]

On 11/30/22 16:51, Ayhan Turgut (via en Mailing List) wrote:
>   Our 
> my.conf file doesn’t have any customizations and am wondering if this is 
> causing the slow down.

If I read this right, what you're saying is, you have never tuned MySQL 
and it is running on pure unmodified out-of-the-box configuration.

For many years, Red Hat's (for one) default out-of-the-box MySQL 
configuration file contained only one directive that actually *did* 
anything (i.e. didn't simply repeat a compiled-in default), and *that* 
directive was *actively harmful*.

The first thing one should always do with a brand new MySQL installation 
is properly secure the installation.  (There is a script provided to do 
this, mysql_secure_installation.  It used to be dead simple, but in 
MySQL 8 it was completely rewritten and now takes a lot more care to 
understand what it is actually asking for.)

The SECOND thing one should do is tune it.  The out-of-the-box 
configuration is worthless.

I would recommend getting yourself a copy of the O'Reilly book "High 
Performance MySQL" and reading the sections on MySQL performance tuning.

If you want to show me what your mysql configuration is *now* and tell 
me a little about your system configuration, I can try to help you tune 
it, but you're much better off getting the book and learning *how and 
why* to tune it yourself.

A few quick rules of thumb:
* Don't use MyISAM tables.  Seriously.  Just don't use them.  Ever.
* Use mysqltuner, but as a guide, not as a bible.
* Remember than after a restart, it can take anywhere from an hour or so 
to perhaps over 24 hours (depending on the size of the database) to 
"warm" MySQL's caches and buffers.  Never make tuning decisions based 
upon the performance of a freshly restarted mysqld.
* On a SHARED server, mysqld should PROBABLY be tuned to use 
approximately 50% of physical installed RAM.  mysqltuner can tell you 
how much mysqld's current, and maximum possible, RAM usage is for a 
given configuration.
* Partition your InnoDB buffer pool into chunks (optimally) between 1GB 
and 4GB in size, but not more than one partition per available processor 
core.
* Use 'Barracuda' file format or newer.
* For best performance, set innodb_autoinc_lock_mode and 
innodb_flush_log_at_trx_commit to 2.
* Set table_definition_cache to the total number of tables in your 
database plus about a 20% safety margin.  Monitor table count as your 
database grows inc ase you need to increase it.
* Set table_open_cache INITIALLY to the maximum number of simultaneous 
active connections you expect, times the largest number of tables used 
by any common query, times two.  Monitor GLOBAL STATUS and increase 
table_open_cache until 'Opened_tables' STOPS INCREASING.


Also consider:
- Make sure you have a good backup strategy and understand how the 
available backup tools work.  (I personally recommend using a 
third-party tool called 'mydumper'.  DO NOT try to use mysqldump to back 
up a DB containing both MyISAM and InnoDB tables.  It *CANNOT* correctly 
handle both in the same run.  However, you should never use MyISAM in 
production anyway.)
- Don't use mysqlautobackup.  It's nearly worthless.



--
  Phil Stracchino
  Babylon Communications
  address@concealed
  address@concealed
  Landline: +1.603.293.8485
  Mobile:   +1.603.998.6958