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 <major@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
> [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?
CodePudding user response：
This is more like a suggestion rather than an answer.
I think in your case you have to check all the queries because there are very differences between MySQL 5.6 and MariaDB, like the introduction of window function:
https://mariadb.com/kb/en/window-functions/
You have to check and adapt the queries.
Is the slow queries a possibility due to low JOIN Buffer size ?
It is used for queries that cannot use an index, and instead perform a full table scan. Increase to get faster full joins when adding indexes is not possible , although be aware of memory issues, since joins will always allocate the minimum size.
join_buffer_size: https://mariadb.com/kb/en/server-system-variables/#join_buffer_size
My suggestion is: add indexes when it is possible.
As per thread pool read more on, because I do not know which is the scenario in your case : https://mariadb.com/kb/en/thread-pool-in-mariadb/.
If you are having difficulties with specific queries ask another question, as far as I know there are no shortcut to your problem.
Error Code: 2013. Lost connection to MySQL server during query.
Why this happens? This error appears when the connection between your MySQL client and database server times out. Essentially, it took too long for the query to return data so the connection gets dropped.
Avoid the problem by refining your queries In many cases, you can avoid the problem entirely by refining your SQL queries. For example, instead of joining all the contents of two very large tables, try filtering out the records you don’t need. Where possible, try reducing the number of joins in a single query. This should have the added benefit of making your query easier to read. For my purposes, I’ve found that denormalizing content into working tables can improve the read performance. This avoids time-outs.
Re-writing the queries isn’t always option so you can try the following server-side and client-side workarounds.
Server-side solution If you’re an administrator for your MySQL server, try changing some values. The MySQL documentation suggests increasing the net_read_timeout or connect_timeout values on the server.
https://dev.mysql.com/doc/refman/8.0/en/error-lost-connection.html
CodePudding user response：
Sim Susee, Welcome to SO. Suggestions to consider for your my.cnf [mysqld] section
innodb_buffer_pool_size=4G # from 128M to accomodate more of your 5 G of data in RAM.
max_connections=500 # from 1000 because you have only used 218 concurrent connections in 44 days.
innodb_buffer_pool_instances=4 # from default of 1 to reduce mutex contention
innodb_lru_scan_depth=100 # from 1024 to conserve 90% of CPU cycles used for function, every SECOND.
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.