MySQL insert on an empty table takes 38.91ms

Question

This DB is running in a cluster.

Insert/Update queries are taking more than 30ms to execute. Select queries are quick with less than 5ms.

We are already following this guide: here

From Laravel Query Log time is in (ms):

array (
    'query' => 'insert into legacy_item_requests (request) values (?)',
    'bindings' => 
    array (
      0 => 1,
    ),
    'time' => 38.91,
  ),

Direct Insert in MySQL:

Show create table:

CREATE TABLE `legacy_item_requests` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  `request` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

The insert is happening on an empty table which is puzzling.

We are using: Server version: 5.7.28-31-57 Percona XtraDB Cluster (GPL), Release rel31, Revision ef2fa88, WSREP version 31.41, wsrep_31.41

Laravel 9.9.0
PHP 8.0.18

My question here is how do I diagnose this and figure out what's causing the slow insert/update?

Note that this insert is part of a big transaction and I can't do BEGIN and COMMIT because it causes a deadlock due to the sheer size of the transaction.

I can provide config details if needed

Show Profile Result

On a different MySQL server the insert took 0ms so we're wondering what's the difference considering the one with the 39ms is a bigger server. What other factors will affect the insert query?

Answer 1

Might be that you have too many indexes, or redundant indexes. You can use tools like EverSQL.com to find these indexes.

You can also run select 1; to see if you have any network latency.

Disclosure, I am the co-founder of EverSQL