I recently migrated DB servers from MySQL 5.6.35 to MariaDB 10.6.3
I have found that few queries which used to run quick on MySQL is now very slow or not working in MariaDB. It gives:
Error Code: 2013. Lost connection to MySQL server during query.
I have even increased the DBMS connection read timeout interval from Edit -> Preferences. Still the query gives the same error. The query contains high number of outer joins and I think that could be an issue.
Overall DB size is less than 5GB and the number of rows per table are less than 10k.
I have run MySQL
tuner script for MariaDB
server and these are the recommendations reported by the tool.
**
>
>
> perl mysqltuner.pl
> >> MySQLTuner 1.8.1 - Major Hayden <[email protected]>
> >> 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
> [OK] Currently running supported MySQL version 10.6.3-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(10M) [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 90 warning(s). [!!]
> /var/log/mariadb/mariadb.log contains 12 error(s). [--] 0 start(s)
> detected in /var/log/mariadb/mariadb.log [--] 0 shutdown(s) detected
> in /var/log/mariadb/mariadb.log
>
> -------- Storage Engine Statistics --------------------------------------------- -------------------- [--] Status: +Aria +CSV +InnoDB +MEMORY +MRG_MyISAM +MyISAM+PERFORMANCE_SCHEMA+SEQUENCE [--] Data in Aria tables: 32.0K (Tables: 1) [--] Data in MyISAM tables: 287.0K (Tables:
> 8) [--] Data in InnoDB tables: 4.5G (Tables: 900) [OK] Total
> fragmented tables: 0
>
> -------- Analysis Performance Metrics ------------------------------------------ -------------------- [--] innodb_stats_on_metadata: OFF [OK] No stat updates during querying INFORMATION_SCHEMA.
>
> -------- Security Recommendations ---------------------------------------------- --------------------
> [OK] There are no anonymous accounts for any database users [OK] All database users have passwords assigned [!!] User 'user'@%
> does not specify hostname restrictions. [!!] User 'root'@% does not
> specify hostname restrictions. [!!] User 'support'@% does not specify
> hostname restrictions. [!!] There is no basic password file list!
>
> -------- CVE Security Recommendations ------------------------------------------ --------------------
> [--] Skipped due to --cvefile option undefined
>
> -------- Performance Metrics --------------------------------------------------- --------------------
> [--] Up for: 44d 22h 1m 57s (38M q [9.997 qps], 107K conn, TX: 212G, RX: 36G) [--] Reads / Writes: 99% / 1% [--] Binary logging is
> disabled [--] Physical Memory : 7.6G [--] Max MySQL memory :
> 18.9G [--] Other process memory: 0B [--] Total buffers: 417.0M global + 18.9M per thread (1000 max threads) [--] P_S Max memory
> usage: 0B [--] Galera GCache Max memory usage: 0B [OK] Maximum
> reached memory usage: 4.4G (58.64% of installed RAM) [!!] Maximum
> possible memory usage: 18.9G (249.66% of installed RAM) [!!] Overall
> possible memory usage with other process exceeded memory [OK] Slow
> queries: 0% (841/38M) [OK] Highest usage of available connections:
> 21% (218/1000) [OK] Aborted connections: 0.01% (15/107805) [!!]
> name resolution is active : a reverse name resolution is made for each
> new connection and can reduce performance [OK] Query cache is
> disabled by default due to mutex contention on multiprocessor
> machines. [OK] Sorts requiring temporary tables: 0% (33 temp sorts /
> 1M sorts) [!!] Joins performed without indexes: 383010 [OK]
> Temporary tables created on disk: 9% (1M on disk / 19M total) [OK]
> Thread cache hit rate: 99% (935 created / 107K connections) [OK] Table
> cache hit rate: 99% (181M hits / 181M requests) [!!]
> table_definition_cache(400) is lower than number of tables(1224) [OK]
> Open file limit used: 0% (33/32K) [OK] Table locks acquired
> immediately: 100% (25K immediate / 25K locks)
>
> -------- Performance schema ---------------------------------------------------- --------------------
> [--] Performance schema is disabled. [--] Memory used by P_S: 0B [--] Sys schema is installed.
>
> -------- ThreadPool Metrics ---------------------------------------------------- --------------------
> [--] ThreadPool stat is enabled. [--] Thread Pool Size: 4 thread(s). [--] Using default value is good enough for your version
> (10.6.3-MariaDB)
>
> -------- MyISAM Metrics -------------------------------------------------------- --------------------
> [!!] Key buffer used: 18.2% (24M used / 134M cache) [OK] Key buffer size / total MyISAM indexes: 128.0M/58.0K [OK] Read Key buffer
> hit rate: 100.0% (17K cached / 0 reads) [!!] Write Key buffer hit
> rate: 1.0% (8K cached / 92 writes)
>
> -------- InnoDB Metrics -------------------------------------------------------- --------------------
> [--] InnoDB is enabled. [OK] InnoDB File per table is activated [!!] InnoDB buffer pool / data size: 128.0M/4.5G [!!]
> Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 96.0M *
> 1/128. 0M should be equal to 25% [--] 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.89% (127064097429 hits/ 127199499037 total) [OK] InnoDB Write log efficiency: 98.65% (6576119 hits/ 6666329 total) [OK] InnoDB log
> waits: 0.00% (0 waits / 90210 writes)
>
> -------- Aria Metrics ---------------------------------------------------------- --------------------
> [--] Aria Storage Engine is enabled. [OK] Aria pagecache size / total Aria indexes: 128.0M/352.0K [OK] Aria pagecache hit rate:
> 97.3% (9M cached / 255K 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
> Restrict Host for 'user'@'%' to 'user'@LimitedIPRangeOrLocalhost
> RENAME USER 'user'@'%' TO 'user'@LimitedIPRangeOrLocalhost;
> Restrict Host for 'root'@'%' to 'root'@LimitedIPRangeOrLocalhost
> RENAME USER 'root'@'%' TO 'root'@LimitedIPRangeOrLocalhost;
> Restrict Host for 'support'@'%' to 'support'@LimitedIPRangeOrLocalhost
> RENAME USER 'support'@'%' TO 'support'@LimitedIPRangeOrLocalhost;
> Reduce your overall MySQL memory footprint for system stability
> Dedicate this server to your database for highest performance.
> 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).
> Performance schema should be activated for better diagnostics
> Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: Variables to adjust: ***
> MySQL's maximum memory usage is dangerously high ***
> *** Add RAM before increasing MySQL buffer variables ***
> join_buffer_size (> 256.0K, or always use indexes with JOINs)
> table_definition_cache(400) > 1224 or -1 (autosizing if supported)
> performance_schema = ON enable PFS
> innodb_buffer_pool_size (>= 4.5G) if possible.
> innodb_log_file_size should be (=32M) if possible, so InnoDB total log files
**
Is the slow queries a possibility due to low JOIN Buffer size ? Also will Thread Pooling improve the performance of the DB? Can anyone help me with this?
Sim Susee, Welcome to SO. Suggestions to consider for your my.cnf [mysqld] section
You had 383,000+ joins not using indexes in 44 days. Appropriate indexes are needed, likely multi-column indexes will be required. View profile for additional assistance, please.