maria-discuss team mailing list archive

[Dragos Pacher <dragosrp@xxxxxxxxx>]

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.bobic@xxxxxxxxx> 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 dragosrp@xxxxxxxxx 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 jmrenouard@xxxxxxxxx
> > > > * Major Hayden major@xxxxxxxx
> > > > 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@xxxxxxxxxxxxxxxxxxx
> > Unsubscribe : https://launchpad.net/~maria-discuss
> > More help : https://help.launchpad.net/ListHelp