On 8/23/2018 9:28 PM, Phil Stracchino wrote:
On 8/23/18 7:43 PM, deoren wrote:
Here is a direct link to the config file with comments:
https://github.com/deoren/postfix-examples/blob/master/database-server/mysql/my.cnf
OK. Pretty much everything up to skip-name-resolve is compiled-in
default values. (And so are most of the ones after.) You don't need
16MB of key buffer unless you're using a lot of MyISAM tables, and you
shouldn't be; in fact with all InnoDB tables (which you should be doing)
you should be able to get away with 8K of key buffer with room to spare.
You almost certainly don't need max_allowed_packet = 16M either.
(In fact, futzing with max_allowed_packet without knowing what you're
doing can be enough by itself to cause database connection problems,
because if either the server *OR* the client tries to send a packet
larger than the other end of the connection is prepared to receive, the
connection will drop.)
max_connections = 151 is another compiled-in default. It may be too
low. So is table_open_cache = 400. As a rule, table_definition_cache
should be at least equal to your TOTAL number of tables plus a safety
margin of 20% or so, and table_open_cache should be at minimum the
largest number of tables used by any commonly running query, times your
typical daily peak connections, times two. Another way to tune it is to
execute SHOW GLOBAL STATUS LIKE 'open%' and increase table_open_cache in
steps until opened_tables stops increasing. Don't be afraid to set it
to 4000 or 8000 or even higher; all it's caching is file handles, it
doesn't use much memory.
Consider turning off the query cache unless you have a high rate of
EXACT duplicate queries. To tell if it's doing you any good, execute
the command SHOW GLOBAL STATUS LIKE 'qcache%' in MariaDB; if Qcache_hits
is not at least four or five times Qcache_inserts, you're probably
better off turning it off. (Let's just say that cache invalidation is
one of the hard things in computer science. Also, the solitary query
cache mutex is a bottleneck that will kill you at high query rates.)
Aaaaand it looks like your InnoDB configuration is also probably at
compiled-in defaults, which is to say, about big enough to keep your
pinochle score sheet in.
Some first steps:
- Use all InnoDB tables. It's not hard to do, it's the default storage
engine since MySQL 5.5. Do not increase any MyISAM-specific buffers
above compiled-in defaults unless you have a good reason.
- Don't touch, or even set, join_buffer_size. It does not do what you
think it does. Unless you know what you're doing and have a good
reason, don't mess with it.
- Ideally, set innodb_buffer_pool_size at least sufficient to hold your
entire DB data volume plus about 30%, but in any case at least 1GB.
- Ideally, set innodb_buffer_pool_instances to 1 per core you allow
MySQL to use, BUT NOT if this would bring individual InnoDB partitions
below 1GB.
- For performance reasons, you probably want to set
innodb_flush_log_at_trx_commit = 2 and innodb_autoinc_lock_mode = 2.
(The first makes InnoDB flush its write-ahead logs to disk once per
second instead of after every commit. The second allows mysqld to
interleave rows inserted by different transactions, which means multiple
transactions can append to the same table simultaneously.)
Install a recent version of mysqltuner, run it, then go to dev.mysql.com
and look up everything it tells you so that you understand what it's
telling you. If you have specific questions you can't find answers to
after you've done that, you can contact me offline and I'll try to help.
Remember that almost all SQL database performance problems come down to
one of three things - bad queries, poor indexing, not enough RAM
allocated to the database - and of those, if you're using anything close
to the out-of-the-box configuration, you ALMOST CERTAINLY aren't
allocating enough RAM to the database unless you're barely using it.
This is immensely helpful, thank you very much for taking the time to
respond in so much detail. I will spend time going over our
configuration, making the changes you indicate and reading over the
settings on dev.mysql.com.
Again, many thanks!
