Auto increment gap in master master setup

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hello,
I was using mariadb 5.5 before but now I have upgraded it and
using percona xtradb cluster 5.6
I have 3 nodes cluster. I am only using single node for read and write.

I saw some increment gaps in ids so i had to turn off wsrep auto increment control.

I am still confused about innodb_autoinc_lock_mode which is 2 and bin log format is Row.

Is it safe to make it 1 to avoid gaps issue since we are only using single node for write and read?

Thanks

2

Auto increment gaps are used to avoid conflicts when you write to more then one node at a time. So, yes, it is safe to disable wsrep auto increment control if you have single writer.

About innodb_autoinc_lock_mode -
[url]https://www.percona.com/blog/2017/07/26/what-is-innodb_autoinc_lock_mode-and-why-should-i-care/[/url]
it is needed for proper parallel applying: [url]http://galeracluster.com/documentation-webpages/performance.html#setting-parallel-slave-threads[/url]

3

przemek Thank you for your reply.

I read the blog and it says:

"Another consideration – which you shouldn’t rely on anyway – is that IDs might not be consecutive with a lock mode of 2. That means you could do three inserts and expect IDs 100,101 and 103, but end up with 100, 102 and 104. For most people, this isn’t a huge deal."

Actually, our application does not work properly if ids are not incremented one by one. What do you suggest besides turning off wsrep auto increment control? Because bulk inserts may become problem for us in future.