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. -- Phil Stracchino Babylon Communications ph...@caerllewys.net p...@co.ordinate.org Landline: +1.603.293.8485 Mobile: +1.603.998.6958
