Mariadb – Log to disk all queries that waited for a lock over a period of time

A quick look shows 2 main problems:

• Many queries on the stage Waiting for table level lock indicates a concurrency problem due to the usage of a table-level-only lock engine, such as MyISAM. Moving, as you did, to InnoDB will mostly solve the problem, specially for the queries you are doing (counters):

  UPDATE topics SET views=views+2 WHERE tid=64896
  

  Before, only one topic could be updated at the same time. With Innodb, different topics can be updated at the same time. However, you can still suffer from locks due to updates to the same row (same tid), and they will not be show as Waiting for table level lock but as stalls in the state Updating.

• You still have probably a high number of temporary tables, and setting your config to:

  tmp-table-size                  =       384M
  max-heap-table-size             =       256M
  

  may have the opposite effect, making the queries slower, not faster, if the temporary tables are larger than that. You query:

  SELECT p.pid, p.post_date, p.post, p.author_id,t.tid, t.title_seo, t.title 
  FROM posts p 
  LEFT JOIN topics t 
  ON ( t.tid=p.topic_id ) 
  WHERE t.approved=1 AND 
        t.forum_id IN (lots, of, in, values) AND 
        queued=0 
  ORDER BY p.post_date DESC 
  LIMIT 0,5
  

  is not that complex to require a temporary table -I may be wrong, that is why cannot be said reliably without looking at your table structure and indexes- optimizing its execution by adding the appropriate indexes may solve your main problems. In order to do that, a full check of all your queries and data structure should be performed.

Can you try connecting to a single machine on your cluster? Your client will only need to connect to a single node on the cluster, and Galera will handle all the replication between the cluster nodes.

So your URL might look something like: "jdbc:mysql://10.32.18.90/replicationTest"