Hello Gordan, Thank you for your advice. Your answer surprised me from 2 perspectives:
1. The following is coming from Plesk people in the default production install(!), I just changed it a bit on tuners advice. By default the setting is present in my.cnf: join_buffer_size = 3M 2. I see mysqltuner gaining more adoption - did not knew its that bad.. I will take your advice and leave the options as defaults - in the way Plesk meant it with the only change being to delete the join_buffer_size. What do you think about point nr 1? Regards, Dragos ------- Original Message ------- On Wednesday, January 18th, 2023 at 3:08 PM, Gordan Bobic <gordan.bo...@gmail.com> wrote: > A lot of MySQL Tuner's advice is based on extensive misconceptions and > questionable understanding of MySQL internals. Realistically - the > best option is to just pretend it doesn't exist. > But since you asked: > > join_buffer_size (> 5.0M, or always use indexes with JOINs) > > > It is spectacularly bad advice to suggest off hand that increasing the > join buffer size is a good idea. > If you genuinely do find that you need to increase it, you almost > certainly have bigger problems that would be better addressed by > improving your queries and indexes. > > innodb_log_file_size should be (=16M) if possible, so InnoDB total > log files size equals 25% of buffer pool size. > > I have no idea where this notion that innodb_log_file_size is in any > way related to the buffer pool size, but it is completely > non-sensical. The two are not in any way related, and each needs to be > configured independently in a way that is appropriate to the server's > workload. > > Bottom line: uninstall MySQL Tuner and forget it ever existed. At best > it will mislead you. At worst it will completely cripple the server. > > > On Wed, Jan 18, 2023 at 2:27 PM Dragos Pacher drago...@proton.me wrote: > > > Hello, > > > > I am looking for an expert opinion on MySQLTuner-perl recommendations and > > usefulness in my specific case of MariaDB 10.3.35 > > with Plesk Web Host Edition 18.0.49 Update #2 - with a somehow default > > installation: one demo site added + some popular > > components in default state idling - on Rocky Linux 8.7 kernel > > 4.18.0-425.10.1.el8_7.x86_64. > > > > What are your opinions on the recommendations below? My machine is a > > VirtualBox 7.0.4 one(on Windows 11 22H2 build 22621.1105) > > with slower Seagate Barracuda PRO disks (non RAID), 8 GB RAM, processor > > Ryzen 7 3700x(all cores assigned). This was meant > > just for testing but I am trying to extrapolate some of the results here > > for a production environment with generous > > hardware(over 48 GB RAM and newer generation CPUs, NVME/RAIDs). The only > > way (as of now) for Plesk to work is by coexisting on the > > same machine with the database server - please take this into consideration. > > > > Please see the results below after ~15-24h of Plesk idling on the machine. > > Also please let me know if you need more data > > from my side or if I should enable something and rerun a specific test, I > > can provide detailed statistics if needed. I am > > really looking forward on how to make this database a bit faster if > > possible(without enabling really dangerous options). > > Thank you. > > > > [root@pensive-aryabhata ~]# cat /etc/my.cnf > > # > > # This group is read both both by the client and the server > > # use it for options that affect everything > > # > > [client-server] > > > > # > > # include all files from the config directory > > # > > !includedir /etc/my.cnf.d > > > > [mysqld] > > sql_mode=ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION > > bind-address = ::ffff:127.0.0.1 > > local-infile=0 > > > > join_buffer_size = 5M # I changed this from 3 to 5 24h before running the > > MySQLTuner > > > > [root@pensive-aryabhata ~]# sysctl -a | grep swapp > > vm.force_cgroup_v2_swappiness = 0 > > vm.swappiness = 5 > > > > [root@pensive-aryabhata ~]# perl mysqltuner.pl --host 127.0.0.1 > > > > > > MySQLTuner 2.0.10 > > > > * Jean-Marie Renouard jmrenou...@gmail.com > > > > * Major Hayden ma...@mhtx.net > > > > Bug reports, feature requests, and downloads at http://mysqltuner.pl/ > > > > Run with '--help' for additional options and output filtering > > > > [--] Skipped version check for MySQLTuner script > > [--] Performing tests on 127.0.0.1:3306 > > [OK] Currently running supported MySQL version 10.3.35-MariaDB > > [OK] Operating on 64-bit architecture > > > > -------- Log file Recommendations > > ------------------------------------------------------------------ > > [OK] Log file /var/log/mariadb/mariadb.log exists > > [--] Log file: /var/log/mariadb/mariadb.log (13K) > > [OK] Log file /var/log/mariadb/mariadb.log is not empty > > [OK] Log file /var/log/mariadb/mariadb.log is smaller than 32 Mb > > [OK] Log file /var/log/mariadb/mariadb.log is readable. > > [!!] /var/log/mariadb/mariadb.log contains 15 warning(s). > > [!!] /var/log/mariadb/mariadb.log contains 2 error(s). > > [--] 5 start(s) detected in /var/log/mariadb/mariadb.log > > [--] 1) 2023-01-17 23:36:33 0 [Note] /usr/libexec/mysqld: ready for > > connections. > > [--] 2) 2023-01-17 23:36:20 0 [Note] /usr/libexec/mysqld: ready for > > connections. > > [--] 3) 2023-01-17 23:23:33 0 [Note] /usr/libexec/mysqld: ready for > > connections. > > [--] 4) 2023-01-17 18:18:58 0 [Note] /usr/libexec/mysqld: ready for > > connections. > > [--] 5) 2023-01-17 18:18:24 0 [Note] /usr/libexec/mysqld: ready for > > connections. > > [--] 4 shutdown(s) detected in /var/log/mariadb/mariadb.log > > [--] 1) 2023-01-17 23:36:29 0 [Note] /usr/libexec/mysqld: Shutdown complete > > [--] 2) 2023-01-17 23:36:16 0 [Note] /usr/libexec/mysqld: Shutdown complete > > [--] 3) 2023-01-17 23:20:59 0 [Note] /usr/libexec/mysqld: Shutdown complete > > [--] 4) 2023-01-17 18:18:50 0 [Note] /usr/libexec/mysqld: Shutdown complete > > > > -------- Storage Engine Statistics > > ----------------------------------------------------------------- > > [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MEMORY > > +MRG_MyISAM +MyISAM +PERFORMANCE_SCHEMA +SEQUENCE > > [--] Data in InnoDB tables: 8.2M (Tables: 267) > > [OK] Total fragmented tables: 0 > > > > -------- Analysis Performance Metrics > > -------------------------------------------------------------- > > [--] innodb_stats_on_metadata: OFF > > [OK] No stat updates during querying INFORMATION_SCHEMA. > > > > -------- Views Metrics > > ----------------------------------------------------------------------------- > > > > -------- Triggers Metrics > > -------------------------------------------------------------------------- > > > > -------- Routines Metrics > > -------------------------------------------------------------------------- > > > > -------- Security Recommendations > > ------------------------------------------------------------------ > > [OK] There are no anonymous accounts for any database users > > [OK] All database users have passwords assigned > > [!!] There is no basic password file list! > > > > -------- CVE Security Recommendations > > -------------------------------------------------------------- > > [--] Skipped due to --cvefile option undefined > > > > -------- Performance Metrics > > ----------------------------------------------------------------------- > > [--] Up for: 13h 39m 22s (45K q [0.920 qps], 1K conn, TX: 38M, RX: 3M) > > [--] Reads / Writes: 86% / 14% > > [--] Binary logging is disabled > > [--] Physical Memory : 7.6G > > [--] Max MySQL memory : 3.9G > > [--] Other process memory: 0B > > [--] Total buffers: 417.0M global + 23.7M per thread (151 max threads) > > [--] Performance_schema Max memory usage: 0B > > [--] Galera GCache Max memory usage: 0B > > [OK] Maximum reached memory usage: 724.6M (9.33% of installed RAM) > > [OK] Maximum possible memory usage: 3.9G (51.40% of installed RAM) > > [OK] Overall possible memory usage with other process is compatible with > > memory available > > [OK] Slow queries: 0% (0/45K) > > [OK] Highest usage of available connections: 8% (13/151) > > [OK] Aborted connections: 0.30% (3/1012) > > [!!] Name resolution is active: a reverse name resolution is made for each > > new connection which can reduce performance > > [OK] Query cache is disabled by default due to mutex contention on > > multiprocessor machines. > > [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 306 sorts) > > [!!] Joins performed without indexes: 228 > > [!!] Temporary tables created on disk: 90% (8K on disk / 9K total) > > [OK] Thread cache hit rate: 86% (140 created / 1K connections) > > [OK] Table cache hit rate: 98% (42K hits / 43K requests) > > [!!] table_definition_cache (400) is less than number of tables (428) > > [OK] Open file limit used: 0% (84/32K) > > [OK] Table locks acquired immediately: 100% (48 immediate / 48 locks) > > > > -------- Performance schema > > ------------------------------------------------------------------------ > > [!!] Performance_schema should be activated. > > [--] Sys schema is not installed. > > > > -------- ThreadPool Metrics > > ------------------------------------------------------------------------ > > [--] ThreadPool stat is disabled. > > > > -------- MyISAM Metrics > > ---------------------------------------------------------------------------- > > [!!] Key buffer used: 18.3% (23.4M used / 128.0M cache) > > [OK] Key buffer size / total MyISAM indexes: 128.0M/123.0K > > [!!] Read Key buffer hit rate: 73.3% (75 cached / 20 reads) > > > > -------- InnoDB Metrics > > ---------------------------------------------------------------------------- > > [--] InnoDB is enabled. > > [--] InnoDB Thread Concurrency: 0 > > [OK] InnoDB File per table is activated > > [OK] InnoDB buffer pool / data size: 128.0M / 8.2M > > [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 48.0M * 2 > > / 128.0M should be equal to 25% > > [OK] InnoDB buffer pool instances: 1 > > [--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s) > > [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & > > Innodb_buffer_pool_instances > > [OK] InnoDB Read buffer efficiency: 99.08% (182560 hits / 184256 total) > > [!!] InnoDB Write Log efficiency: 11.48% (114 hits / 993 total) > > [OK] InnoDB log waits: 0.00% (0 waits / 1107 writes) > > > > -------- Aria Metrics > > ------------------------------------------------------------------------------ > > [--] Aria Storage Engine is enabled. > > [OK] Aria pagecache size / total Aria indexes: 128.0M/0B > > [!!] Aria pagecache hit rate: 91.0% (95K cached / 8K reads) > > > > -------- TokuDB Metrics > > ---------------------------------------------------------------------------- > > [--] TokuDB is disabled. > > > > -------- XtraDB Metrics > > ---------------------------------------------------------------------------- > > [--] XtraDB is disabled. > > > > -------- Galera Metrics > > ---------------------------------------------------------------------------- > > [--] Galera is disabled. > > > > -------- Replication Metrics > > ----------------------------------------------------------------------- > > [--] Galera Synchronous replication: NO > > [--] No replication slave(s) for this server. > > [--] Binlog format: MIXED > > [--] XA support enabled: ON > > [--] Semi synchronous replication Master: OFF > > [--] Semi synchronous replication Slave: OFF > > [--] This is a standalone server > > > > -------- Recommendations > > --------------------------------------------------------------------------- > > General recommendations: > > Check warning line(s) in /var/log/mariadb/mariadb.log file > > Check error line(s) in /var/log/mariadb/mariadb.log file > > MySQL was started within the last 24 hours: recommendations may be > > inaccurate > > Configure your accounts with ip or subnets only, then update your > > configuration with skip-name-resolve=1 > > We will suggest raising the 'join_buffer_size' until JOINs not using > > indexes are found. > > See https://dev.mysql.com/doc/internals/en/join-buffer-size.html > > (specially the conclusions at the bottom of the page). > > When making adjustments, make tmp_table_size/max_heap_table_size equal > > Reduce your SELECT DISTINCT queries which have no LIMIT clause > > Performance schema should be activated for better diagnostics > > Consider installing Sys schema from https://github.com/mysql/mysql-sys for > > MySQL > > Before changing innodb_log_file_size and/or innodb_log_files_in_group read > > this: https://bit.ly/2TcGgtU > > Variables to adjust: > > skip-name-resolve=1 > > join_buffer_size (> 5.0M, or always use indexes with JOINs) > > tmp_table_size (> 16M) > > max_heap_table_size (> 16M) > > table_definition_cache (400) > 428 or -1 (autosizing if supported) > > performance_schema=ON > > key_buffer_size (~ 24M) > > innodb_log_file_size should be (=16M) if possible, so InnoDB total log > > files size equals 25% of buffer pool size. > > > > # end of MySQL Tuner-perl script #1 > > > > # more details > > [root@pensive-aryabhata ~]# perl mysqltuner.pl --buffers --dbstat --idxstat > > --sysstat --pfstat --tbstat > > # > > # skipped part as the output was too big > > # > > -------- Views Metrics > > ----------------------------------------------------------------------------- > > > > -------- Triggers Metrics > > -------------------------------------------------------------------------- > > > > -------- Routines Metrics > > -------------------------------------------------------------------------- > > > > -------- Security Recommendations > > ------------------------------------------------------------------ > > [OK] There are no anonymous accounts for any database users > > [OK] All database users have passwords assigned > > [!!] There is no basic password file list! > > > > -------- CVE Security Recommendations > > -------------------------------------------------------------- > > [--] Skipped due to --cvefile option undefined > > > > -------- Performance Metrics > > ----------------------------------------------------------------------- > > [--] Up for: 13m 5s (3K q [4.741 qps], 1K conn, TX: 1M, RX: 549K) > > [--] Reads / Writes: 97% / 3% > > [--] Binary logging is disabled > > [--] Physical Memory : 7.6G > > [--] Max MySQL memory : 3.9G > > [--] Other process memory: 1.1G > > [--] Total buffers: 417.0M global + 23.7M per thread (151 max threads) > > [--] Performance_schema Max memory usage: 0B > > [--] Galera GCache Max memory usage: 0B > > [--] Global Buffers > > [--] +-- Key Buffer: 128.0M > > [--] +-- Max Tmp Table: 16.0M > > [--] Query Cache Buffers > > [--] +-- Query Cache: OFF - DISABLED > > [--] +-- Query Cache Size: 1.0M > > [--] Per Thread Buffers > > [--] +-- Read Buffer: 128.0K > > [--] +-- Read RND Buffer: 256.0K > > [--] +-- Sort Buffer: 2.0M > > [--] +-- Thread stack: 292.0K > > [--] +-- Join Buffer: 5.0M > > [OK] Maximum reached memory usage: 535.3M (6.90% of installed RAM) > > [OK] Maximum possible memory usage: 3.9G (51.40% of installed RAM) > > [OK] Overall possible memory usage with other process is compatible with > > memory available > > [OK] Slow queries: 0% (0/3K) > > [OK] Highest usage of available connections: 3% (5/151) > > [OK] Aborted connections: 0.00% (0/1594) > > [!!] Name resolution is active: a reverse name resolution is made for each > > new connection which can reduce performance > > [OK] Query cache is disabled by default due to mutex contention on > > multiprocessor machines. > > [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 93 sorts) > > [!!] Joins performed without indexes: 4 > > [!!] Temporary tables created on disk: 73% (1K on disk / 1K total) > > [OK] Thread cache hit rate: 99% (5 created / 1K connections) > > [OK] Table cache hit rate: 89% (3K hits / 3K requests) > > [!!] table_definition_cache (400) is less than number of tables (428) > > [OK] Open file limit used: 0% (59/32K) > > [OK] Table locks acquired immediately: 100% (18 immediate / 18 locks) > > > > -------- Performance schema > > ------------------------------------------------------------------------ > > [!!] Performance_schema should be activated. > > [--] Sys schema is not installed. > > > > -------- ThreadPool Metrics > > ------------------------------------------------------------------------ > > [--] ThreadPool stat is disabled. > > > > -------- MyISAM Metrics > > ---------------------------------------------------------------------------- > > [!!] Key buffer used: 18.2% (23.3M used / 128.0M cache) > > [OK] Key buffer size / total MyISAM indexes: 128.0M/123.0K > > > > -------- InnoDB Metrics > > ---------------------------------------------------------------------------- > > [--] InnoDB is enabled. > > [--] InnoDB Buffers > > [--] +-- InnoDB Buffer Pool: 128.0M > > [--] +-- InnoDB Buffer Pool Instances: 1 > > [--] +-- InnoDB Buffer Pool Chunk Size: 128.0M > > [--] +-- InnoDB Log File Size: 48.0M > > [--] +-- InnoDB Log File In Group: 2 > > [--] +-- InnoDB Total Log File Size: 96.0M(75 % of buffer pool) > > [--] +-- InnoDB Log Buffer: 16.0M > > [--] +-- InnoDB Log Buffer Free: 6.2K > > [--] +-- InnoDB Log Buffer Used: 8.0K > > [--] InnoDB Thread Concurrency: 0 > > [OK] InnoDB File per table is activated > > [OK] InnoDB buffer pool / data size: 128.0M / 8.2M > > [!!] Ratio InnoDB log file size / InnoDB Buffer pool size (75%): 48.0M * 2 > > / 128.0M should be equal to 25% > > [OK] InnoDB buffer pool instances: 1 > > [--] Number of InnoDB Buffer Pool Chunk: 1 for 1 Buffer Pool Instance(s) > > [OK] Innodb_buffer_pool_size aligned with Innodb_buffer_pool_chunk_size & > > Innodb_buffer_pool_instances > > [!!] InnoDB Read buffer efficiency: 83.63% (8790 hits / 10511 total) > > [!!] InnoDB Write Log efficiency: 154.55% (17 hits / 11 total) > > [OK] InnoDB log waits: 0.00% (0 waits / 28 writes) > > > > -------- Aria Metrics > > ------------------------------------------------------------------------------ > > [--] Aria Storage Engine is enabled. > > [OK] Aria pagecache size / total Aria indexes: 128.0M/0B > > [OK] Aria pagecache hit rate: 95.4% (29K cached / 1K reads) > > > > -------- TokuDB Metrics > > ---------------------------------------------------------------------------- > > [--] TokuDB is disabled. > > > > -------- XtraDB Metrics > > ---------------------------------------------------------------------------- > > [--] XtraDB is disabled. > > > > -------- Galera Metrics > > ---------------------------------------------------------------------------- > > [--] Galera is disabled. > > > > -------- Replication Metrics > > ----------------------------------------------------------------------- > > [--] Galera Synchronous replication: NO > > [--] No replication slave(s) for this server. > > [--] Binlog format: MIXED > > [--] XA support enabled: ON > > [--] Semi synchronous replication Master: OFF > > [--] Semi synchronous replication Slave: OFF > > [--] This is a standalone server > > > > -------- Recommendations > > --------------------------------------------------------------------------- > > General recommendations: > > Consider stopping or dedicate server for additional process other than > > mysqld. > > Check warning line(s) in /var/log/mariadb/mariadb.log file > > Check error line(s) in /var/log/mariadb/mariadb.log file > > Limit charset for column to one charset if possible for psa database. > > Limit collations for column to one collation if possible for psa database. > > Limit collations for column to one collation if possible for phpmyadmin > > database. > > Check all table collations are identical for all tables in roundcubemail > > database. > > Limit collations for column to one collation if possible for roundcubemail > > database. > > Limit charset for column to one charset if possible for apsc database. > > Limit collations for column to one collation if possible for apsc database. > > Add at least a primary key on table psa.SchemaVersions > > Add at least a primary key on table psa.suspend_handler_history > > Add at least a primary key on table psa.WebsitesDiagnosticDomains > > MySQL was started within the last 24 hours: recommendations may be > > inaccurate > > Configure your accounts with ip or subnets only, then update your > > configuration with skip-name-resolve=1 > > We will suggest raising the 'join_buffer_size' until JOINs not using > > indexes are found. > > See https://dev.mysql.com/doc/internals/en/join-buffer-size.html > > (specially the conclusions at the bottom of the page). > > When making adjustments, make tmp_table_size/max_heap_table_size equal > > Reduce your SELECT DISTINCT queries which have no LIMIT clause > > Performance schema should be activated for better diagnostics > > Consider installing Sys schema from https://github.com/mysql/mysql-sys for > > MySQL > > Before changing innodb_log_file_size and/or innodb_log_files_in_group read > > this: https://bit.ly/2TcGgtU > > Variables to adjust: > > DON'T APPLY SETTINGS BECAUSE THERE ARE TOO MANY PROCESSES RUNNING ON THIS > > SERVER. OOM KILL CAN OCCUR! > > skip-name-resolve=1 > > join_buffer_size (> 5.0M, or always use indexes with JOINs) > > tmp_table_size (> 16M) > > max_heap_table_size (> 16M) > > table_definition_cache (400) > 428 or -1 (autosizing if supported) > > performance_schema=ON > > key_buffer_size (~ 24M) > > innodb_log_file_size should be (=16M) if possible, so InnoDB total log > > files size equals 25% of buffer pool size. > > > > # end of MySQLTuner command #2 > > > > ################################################### > > # here we can see the databases and count number of tables if this matters > > > > MariaDB [psa]> show databases; > > +--------------------+ > > | Database | > > +--------------------+ > > | apsc | > > | information_schema | > > | mysql | > > | performance_schema | > > | phpmyadmin | > > | psa | > > | roundcubemail | > > +--------------------+ > > 7 rows in set (0.001 sec) > > > > MariaDB [psa]> SELECT COUNT() FROM information_schema.tables WHERE > > table_schema = 'apsc'; > > +----------+ > > | COUNT() | > > +----------+ > > | 23 | > > +----------+ > > 1 row in set (0.003 sec) > > > > MariaDB [psa]> SELECT COUNT() FROM information_schema.tables WHERE > > table_schema = 'mysql'; > > +----------+ > > | COUNT() | > > +----------+ > > | 31 | > > +----------+ > > 1 row in set (0.001 sec) > > > > MariaDB [psa]> SELECT COUNT() FROM information_schema.tables WHERE > > table_schema = 'phpmyadmin'; > > +----------+ > > | COUNT() | > > +----------+ > > | 19 | > > +----------+ > > 1 row in set (0.001 sec) > > > > MariaDB [psa]> SELECT COUNT() FROM information_schema.tables WHERE > > table_schema = 'psa'; > > +----------+ > > | COUNT() | > > +----------+ > > | 208 | > > +----------+ > > 1 row in set (0.001 sec) > > > > MariaDB [psa]> SELECT COUNT() FROM information_schema.tables WHERE > > table_schema = 'roundcubemail'; > > +----------+ > > | COUNT() | > > +----------+ > > | 17 | > > +----------+ > > 1 row in set (0.001 sec) > > > > # Thank you, > > > > Dragos > > > > _______________________________________________ > > Mailing list: https://launchpad.net/~maria-discuss > > Post to : maria-discuss@lists.launchpad.net > > Unsubscribe : https://launchpad.net/~maria-discuss > > More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~maria-discuss Post to : maria-discuss@lists.launchpad.net Unsubscribe : https://launchpad.net/~maria-discuss More help : https://help.launchpad.net/ListHelp
