Comments - Poor performance on inserts MariaDB

7 years, 10 months ago Andy Ferretti

I enabled the performance schema and ran queries to find where the bottleneck it. See below. It appears to be System lock, Table lock, and update. I'll focus on these issues. Any comments on why we might reduce locking will be appreciated.

+------------+-----------------------------------------------------+-----------+---------------+-------------+-------------------+

userevent_nametotaltotal_latencyavg_latencytotal_latency

+------------+-----------------------------------------------------+-----------+---------------+-------------+-------------------+

pedadbastage/sql/System lock127553905.45 h1.54 ms19626537585557000
pedadbastage/sql/Table lock127553974.74 h1.34 ms17069504681410000
pedadbastage/sql/update127541244.39 h1.24 ms15808410965848000
pedadbastage/sql/Unlocking tables127553863.54 h999.80 us12752902800174000
backgroundstage/sql/Waiting for background binlog tasks956.19 m6.24 m3371540382932000
pedadbastage/sql/init3826108326.29 m41.23 us1577505387502000

+------------------------------------+--------------------------------------------------------+------------+---------------+-------------+-------------+-------------------+

hosteventtotaltotal_latencyavg_latencymax_latencytotal_latency

+------------------------------------+--------------------------------------------------------+------------+---------------+-------------+-------------+-------------------+

zlx70603.vci.att.comwait/lock/table/sql/handler59681254507.39 h4.46 us70.14 ms26590505970155343
zlx70603.vci.att.comwait/io/table/sql/handler213667743.08 h519.51 us762.54 ms11100210290586231
backgroundwait/io/file/innodb/innodb_data_file152668911.14 m437.99 us245.95 ms668673251037036
zlx70603.vci.att.comwait/io/file/innodb/innodb_data_file3847035.80 m904.07 us237.48 ms347796883650678
backgroundwait/io/file/innodb/innodb_log_file523592.70 m3.10 ms130.79 ms162268658433654
zlx70603.vci.att.comwait/io/file/innodb/innodb_log_file1253341.46 s3.31 ms138.57 ms41463102266223
zlx70603.vci.att.comwait/io/file/sql/binlog148849836.00 s24.18 us218.11 ms35997948198705
 
Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.