MySQL insert on an empty table takes 38.91ms



  • 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: https://www.percona.com/blog/2014/01/28/10-mysql-performance-tuning-settings-after-installation/

    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:

    enter image description here

    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

    enter image description here

    enter image description here

    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?



  • 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




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2