How to improve MyXQL performance to not have so many errors

Hi everyone,

More than 1 year ago we have migrated from Mariaex to MyXQL library. Since then, the performance of the connection has decreased. I mean, our program has this type of error more often:

MyXQL.Error (1205) Lock wait timeout exceeded; try restarting transaction

And this other type:

DBConnection.ConnectionError connection not available and request was dropped from queue after 2401ms

This didn’t happen on Mariaex.

A while ago we have already configured our service to have a better configuration:

  pool_size: 20,
  timeout: :infinity,
  after_connect_timeout: :infinity,
  queue_target: 1000,
  queue_interval: 5000

This seems to have stopped those errors for a while. However we have a couple of servers with large databases where this error happens really often. We already have indexes and all of the possible optimizations we can have. Sadly, the servers are running on Ubuntu 14.04 with mysql 5.5.61 and it’s something that we can’t change right now. We are using MyXQL 0.6.1.

In our queries we have one specific transaction that does 3 inserts. This happens quite often.

Is there anything else that we can do to improve the performance of MyXQL to avoid having those errors?

Thank you in advance.