How to Improve Query Performance of UPDATE and INSERT?

MySQL & MariaDB Percona Server for MySQL 5.6
1

I’m running a Percona 5.6 cluster (3 nodes) on lxc containers. Each nodes hardware is as follows:

4 x Intel(R) Xeon(R) CPU E5-2630 v3 @ 2.40GHz (2 Sockets)
16GB RAM
130GB HD

My database is just under 4GB in size. I’ve managed to tweak the config settings so selects are handled swiftly. However, I’m seeing some UPDATE and INSERT performing quite slow (5 seconds+). So there must be some sort of bottleneck which i’m wondering if I can resolve via config tweaking.

I’m aware that a couple of the tables may require some unused indexes to be dropped, but I was wondering what else to look into to improve the performance of the queries. Load isn’t really that big, it’s about 100 users as it’s an internal system.

innodb_buffer_pool_size: 8G
innodb_log_file_size: 256M
innodb_log_buffer_size: 128M
innodb_flush_log_at_trx_commit: 2
innodb_thread_concurrency: 0
innodb_flush_method: O_DIRECT
innodb_file_per_table: ON

Here is my SHOW ENGINE INNODB STATUS when things are normal:

[url]https://pastebin.com/raw/jDzfm99e[/url]

Here are some details about the table which has the problem most often:

Engine: Innodb
Row format: Compact
Column Count: 124
Table Row: 581705 (I know, not alot!)
AVG row length: 560
Data lenght: 310.9MB
Index lenght: 411.4MB
Max data length: 0.0bytes
Data free: 4.0mb
Table size (estimate): 722.3MB
file format: antelope
Auto Increment: 745745
Table collation: utf8_unicode_ci

Number of Indices: 34 (most are BTREE, single column, non-unique Indices)

I think my biggest problem with this table is 1) the number of indecies and 2) the data types used. There is quite alot of usage of ‘bigint(20) unsigned’ which I can’t only imagine to be totally unnecessary. Along with ‘mediumtext’ fields too.

I suspect the inserts are slow due to the updates being slow. And I suspect that is do to the number of indexes and the large data types used.

However, I’ve got plenty of available CPU/Ram that I can throw at it if needed and would like to know if there is any further configurations I might want to look at.

I’ve enabled userstat to determine some unused indexes since the last time mysql was restarted ([url]https://www.percona.com/blog/2012/06/30/find-unused-indexes/[/url]). It turns out that i’ve only used 15 out of 34 indexes.

Here is a status output during a thread backup:
[url]https://pastebin.com/raw/QhkHGH5s[/url]

2

@dburleson

Reducing or eliminating the unwanted/unused/redundant  indexes always helps not only to reduce the disk space but also helps also performance.
As far as update query slowness, you can analyse the explain plan or in other word ensure to use of proper efficient index to help query to respond faster.

If you have frequent updates and deletes on the table it is recommended to optimise the table to get better performance.

Hope this helps…