title: Migration from MySQL 5.6.35 to MariaDB 10.6.3 - Queries not working
body: <p>I recently migrated DB servers from MySQL 5.6.35 to MariaDB 10.6.3</p>
  <p>I have found that few queries which used to run quick on MySQL is now very slow or not working in MariaDB.
  It gives:</p>
  <blockquote>
  <p>Error Code: 2013. Lost connection to MySQL server during query.</p>
  </blockquote>
  <p>I have even increased the DBMS connection read timeout interval from
  Edit -&gt; Preferences. Still the query gives the same error. The query contains high number of outer joins and I think that could be an issue.</p>
  <p>Overall DB size is less than 5GB and the number of rows per table are less than 10k.</p>
  <p>I have run <code>MySQL</code> tuner script for <code>MariaDB</code> server and these are the recommendations reported by the tool.</p>
  <p>**</p>
  <pre><code>&gt;       
  &gt;     
  &gt;               perl mysqltuner.pl
  &gt;                      &gt;&gt;  MySQLTuner 1.8.1 - Major Hayden &lt;major@mhtx.net&gt;
  &gt;                      &gt;&gt;  Bug reports, feature requests, and downloads at http://mysqltuner.pl/
  &gt;                      &gt;&gt;  Run with '--help' for additional options and output filtering
  &gt;                     
  &gt;                     [--] Skipped version check for MySQLTuner script 
  &gt;         [OK] Currently running supported MySQL version 10.6.3-MariaDB 
  &gt;         [OK] Operating on 64-bit architecture
  &gt;                     
  &gt;                     -------- Log file Recommendations ----------------------------------------------                                                                                        --------------------   [OK] Log file /var/log/mariadb/mariadb.log exists  [--] Log file: /var/log/mariadb/mariadb.log(10M)  [OK] Log
  &gt; file /var/log/mariadb/mariadb.log is not empty  [OK] Log file
  &gt; /var/log/mariadb/mariadb.log is smaller than 32 Mb  [OK] Log file
  &gt; /var/log/mariadb/mariadb.log is readable.  [!!]
  &gt; /var/log/mariadb/mariadb.log contains 90 warning(s).  [!!]
  &gt; /var/log/mariadb/mariadb.log contains 12 error(s).  [--] 0 start(s)
  &gt; detected in /var/log/mariadb/mariadb.log  [--] 0 shutdown(s) detected
  &gt; in /var/log/mariadb/mariadb.log
  &gt;                     
  &gt;                     -------- 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:
  &gt; 8)  [--] Data in InnoDB tables: 4.5G (Tables: 900)  [OK] Total
  &gt; fragmented tables: 0
  &gt;                     
  &gt;                     -------- Analysis Performance Metrics ------------------------------------------                                                                                        --------------------  [--] innodb_stats_on_metadata: OFF  [OK] No stat updates during querying INFORMATION_SCHEMA.
  &gt;                     
  &gt;                     -------- Security Recommendations ----------------------------------------------                                                                                        -------------------- 
  &gt;         [OK] There are no anonymous accounts for any database users  [OK] All database users have passwords assigned  [!!] User 'user'@%
  &gt; does not specify hostname restrictions.  [!!] User 'root'@% does not
  &gt; specify hostname restrictions.  [!!] User 'support'@% does not specify
  &gt; hostname restrictions.  [!!] There is no basic password file list!
  &gt;                     
  &gt;                     -------- CVE Security Recommendations ------------------------------------------                                                                                        --------------------
  &gt;          [--] Skipped due to --cvefile option undefined
  &gt;                     
  &gt;                     -------- Performance Metrics ---------------------------------------------------                                                                                        -------------------- 
  &gt;         [--] Up for: 44d 22h 1m 57s (38M q [9.997 qps], 107K conn, TX: 212G, RX: 36G)  [--] Reads / Writes: 99% / 1%  [--] Binary logging is
  &gt; disabled  [--] Physical Memory     : 7.6G  [--] Max MySQL memory    :
  &gt; 18.9G  [--] Other process memory: 0B  [--] Total buffers: 417.0M global + 18.9M per thread (1000 max threads)  [--] P_S Max memory
  &gt; usage: 0B  [--] Galera GCache Max memory usage: 0B  [OK] Maximum
  &gt; reached memory usage: 4.4G (58.64% of installed RAM)  [!!] Maximum
  &gt; possible memory usage: 18.9G (249.66% of installed RAM)  [!!] Overall
  &gt; possible memory usage with other process exceeded memory  [OK] Slow
  &gt; queries: 0% (841/38M)  [OK] Highest usage of available connections:
  &gt; 21% (218/1000)  [OK] Aborted connections: 0.01%  (15/107805)  [!!]
  &gt; name resolution is active : a reverse name resolution is made for each
  &gt; new connection and can reduce performance  [OK] Query cache is
  &gt; disabled by default due to mutex contention on multiprocessor
  &gt; machines.  [OK] Sorts requiring temporary tables: 0% (33 temp sorts /
  &gt; 1M sorts)  [!!] Joins performed without indexes: 383010  [OK]
  &gt; Temporary tables created on disk: 9% (1M on disk / 19M total)  [OK]
  &gt; Thread cache hit rate: 99% (935 created / 107K connections) [OK] Table
  &gt; cache hit rate: 99% (181M hits / 181M requests)  [!!]
  &gt; table_definition_cache(400) is lower than number of tables(1224)  [OK]
  &gt; Open file limit used: 0% (33/32K)  [OK] Table locks acquired
  &gt; immediately: 100% (25K immediate / 25K locks)
  &gt;                     
  &gt;                     -------- Performance schema ----------------------------------------------------                                                                                        -------------------- 
  &gt;         [--] Performance schema is disabled.  [--] Memory used by P_S: 0B  [--] Sys schema is installed.
  &gt;                     
  &gt;                     -------- ThreadPool Metrics ----------------------------------------------------                                                                                        -------------------- 
  &gt;         [--] ThreadPool stat is enabled.  [--] Thread Pool Size: 4 thread(s).  [--] Using default value is good enough for your version
  &gt; (10.6.3-MariaDB)
  &gt;                     
  &gt;                     -------- MyISAM Metrics --------------------------------------------------------                                                                                        -------------------- 
  &gt;         [!!] Key buffer used: 18.2% (24M used / 134M cache)  [OK] Key buffer size / total MyISAM indexes: 128.0M/58.0K  [OK] Read Key buffer
  &gt; hit rate: 100.0% (17K cached / 0 reads)  [!!] Write Key buffer hit
  &gt; rate: 1.0% (8K cached / 92 writes)
  &gt;                     
  &gt;                     -------- InnoDB Metrics --------------------------------------------------------                                                                                        -------------------- 
  &gt;         [--] InnoDB is enabled.  [OK] InnoDB File per table is activated  [!!] InnoDB buffer pool / data size: 128.0M/4.5G  [!!]
  &gt; Ratio InnoDB log file size / InnoDB Buffer pool size (75 %): 96.0M *
  &gt; 1/128. 0M should be equal to 25%  [--] Number of InnoDB Buffer Pool
  &gt; Chunk : 1 for 1 Buffer Pool Instance(s) [OK] Innodb_buffer_pool_size
  &gt; aligned with Innodb_buffer_pool_chunk_size &amp;
  &gt; Innodb_buffer_pool_instances  [OK] InnoDB Read buffer efficiency:
  &gt; 99.89% (127064097429 hits/ 127199499037 total)  [OK] InnoDB Write log efficiency: 98.65% (6576119 hits/ 6666329 total)  [OK] InnoDB log
  &gt; waits: 0.00% (0 waits / 90210 writes)
  &gt;                     
  &gt;                     -------- Aria Metrics ----------------------------------------------------------                                                                                        -------------------- 
  &gt;         [--] Aria Storage Engine is enabled.  [OK] Aria pagecache size / total Aria indexes: 128.0M/352.0K  [OK] Aria pagecache hit rate:
  &gt; 97.3% (9M cached / 255K reads)
  &gt;                     
  &gt;                     -------- TokuDB Metrics --------------------------------------------------------                                                                                        -------------------- 
  &gt;         [--] TokuDB is disabled.
  &gt;                     
  &gt;                     -------- XtraDB Metrics --------------------------------------------------------                                                                                        -------------------- 
  &gt;         [--] XtraDB is disabled.
  &gt;                     
  &gt;                     -------- Galera Metrics --------------------------------------------------------                                                                                        --------------------
  &gt;          [--] Galera is disabled.
  &gt;                     
  &gt;                     -------- Replication Metrics ---------------------------------------------------                                                                                        --------------------
  &gt;          [--] Galera Synchronous replication: NO  [--] No replication slave(s) for this server.  [--] Binlog format: MIXED  [--] XA support
  &gt; enabled: ON  [--] Semi synchronous replication Master: OFF  [--] Semi
  &gt; synchronous replication Slave: OFF  [--] This is a standalone server
  &gt;                     
  &gt;                     -------- Recommendations -------------------------------------------------------                                                                                        -------------------- 
  &gt;         General recommendations:
  &gt;         Check warning line(s) in /var/log/mariadb/mariadb.log file
  &gt;         Check error line(s) in /var/log/mariadb/mariadb.log file
  &gt;         Restrict Host for 'user'@'%' to 'user'@LimitedIPRangeOrLocalhost
  &gt;         RENAME USER 'user'@'%' TO 'user'@LimitedIPRangeOrLocalhost;
  &gt;         Restrict Host for 'root'@'%' to 'root'@LimitedIPRangeOrLocalhost
  &gt;         RENAME USER 'root'@'%' TO 'root'@LimitedIPRangeOrLocalhost;
  &gt;         Restrict Host for 'support'@'%' to 'support'@LimitedIPRangeOrLocalhost
  &gt;         RENAME USER 'support'@'%' TO 'support'@LimitedIPRangeOrLocalhost;
  &gt;         Reduce your overall MySQL memory footprint for system stability
  &gt;         Dedicate this server to your database for highest performance.
  &gt;         Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
  &gt;         We will suggest raising the 'join_buffer_size' until JOINs not using indexes are found.
  &gt;      See https://dev.mysql.com/doc/internals/en/join-buffer-size.html
  &gt;      (specially the conclusions at the bottom of the page).
  &gt;      Performance schema should be activated for better diagnostics
  &gt;      Before changing innodb_log_file_size and/or innodb_log_files_in_group read this: Variables to adjust:   ***
  &gt; MySQL's maximum memory usage is dangerously high ***   
  &gt;     *** Add RAM before increasing MySQL buffer variables ***
  &gt;       join_buffer_size (&gt; 256.0K, or always use indexes with JOINs)
  &gt;      table_definition_cache(400) &gt; 1224 or -1 (autosizing if supported)
  &gt;      performance_schema = ON enable PFS
  &gt;      innodb_buffer_pool_size (&gt;= 4.5G) if possible.
  &gt;      innodb_log_file_size should be (=32M) if possible, so InnoDB total log files
  </code></pre>
  <p>**</p>
  <p>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?</p>