Mysql Database performance issue

MySQL & MariaDB Other MySQL® Questions
1

Hello,

A month ago (on September 28th), we migrated an application to AWS.
Our previous environment was run with a MySQL 5.6 database.

We have several database issues on which we would like your support:

  1. Much longer insertion time in MyISAM tables
    (see attachment saturn-prd-myisam-insertion-comparison.png)
    What optimization could we operate on the system variables? (buffer size, etc.)
    The documentation seems to suggest that disabling sync_binlog could improve this insertion time.
    What is the risk of passing sync_binlog to 0 on a system where we use both MyISAM and InnoDB?

  2. Increase in the number of deadlocks
    What settings would allow us to reduce the number of deadlocks?
    We know that there would be code improvements to be made but they are not easy / quick to do (they are located in the framework’s core that we use), but we would like to obtain support on parameter tuning on the server side, that would still allow us to improve the situation.

  3. Schema performance detailed stats
    We already have Performance Insights enabled.
    But in order to gain a better understanding of indexes usage on the various tables of our databases :

  • how to activate the detailed stats of the schema performance?
  • how to access usage statistics such as for example
    performance_schema.table_io_waits_summary_by_table
    performance_schema.table_io_waits_summary_by_index_usage ?

Thanks you in advance for your help.

1 Like
2

Much longer insertion time in MyISAM tables

Switch all MyISAM tables to InnoDB. MyISAM uses FULL TABLE LOCKING for all inserts/updates/deletes. There is no concurrency. Switch to InnoDB for row-level locking and better concurrency/parallelism.

What settings would allow us to reduce the number of deadlocks?

Change your transaction isolation level to READ-COMMITTED. The default, REPEATABLE READ, requires extra locks to be ACID compliant. Also, use of FOREIGN KEYS will drastically increase deadlocks. Remove FKs as much as possible.

Schema performance detailed stats

I highly recommend you spin up a PMM instance in AWS. We have an official AMI for this. You will get all of these statistics and 100s more by using PMM vs what AWS provides.

1 Like
3

Team ,

Thanks for your suggestion & response

1 Like
4

Diagnosis
Check if the MySQL slow query log is enabled.
Shut down the Atlassian applications which use the suspected MySQL server.
Enable the MySQL slow query log.
Restart MySQL after doing this.
Start your Atlassian application back up.
Wait till your Atlassian application has gone through a period of poor behavior.

Regards,
Rachel Gomez

5

Diagnosis

  1. Check if the MySQL slow query log is enabled.
  2. Shut down the Atlassian applications which use the suspected MySQL server.
  3. Enable the MySQL slow query log.
  4. Restart MySQL after doing this.
  5. Start your Atlassian application back up.
  6. Wait till your Atlassian application has gone through a period of poor behavior.

Regards,
Rachel Gomez