CentOS 7.3, PHP Version 7.1.8, MariaDB 10.2.8

Transactional deadlocks and lock wait timeouts are quite often a part of the normal operation. MariaDB 10.2 has a higher default level of log_warnings, which makes it print into the error log messages which previous versions (MySQL 5.6, MariaDB 10.1) didn't print. If you really want to compare MySQL 5.6 and MariaDB 10.2 properly, make sure they both have the same log_warnings value in the config file – either both have 1, in which case they won't print these messages, or both have 2, in which case they will print them.

Everything was working fine prior to the change from MySQL to MariaDB, the issue would lie in the MariaDB configuration?I think we do mistake Upgrade to MariaDB. Lot of deadlocks and lock wait timeouts log happening. We have 10 CPUs with 50 GB RAM Memory.

This my current my.cnf the following configuration.

[mysqld]

performance_schema = OFF

innodb_lock_wait_timeout = 1000

default_storage_engine = InnoDB

max_allowed_packet=268435456

key_buffer_size = 128M

table_cache = 4096

table_open_cache = 600

read_buffer_size = 2M

read_rnd_buffer_size = 1M

thread_cache_size = 80

join_buffer_size = 2M

sort_buffer_size = 2M

max_connections = 1000

max_user_connections = 1000

lock_wait_timeout=31536000

wait_timeout = 2000

connect_timeout = 2000

interactive_timeout = 2000

tmp_table_size = 32M

max_heap_table_size = 32M

table_definition_cache = 8192

query_cache_size = 32M

innodb_buffer_pool_size = 3G

innodb_log_file_size = 128M

innodb_log_files_in_group = 2

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 2

innodb_thread_concurrency = 32

innodb_read_io_threads = 8

innodb_write_io_threads = 8

innodb_flush_method = O_DIRECT

innodb_file_per_table = 1

innodb_io_capacity = 2000

innodb_file_format = Barracuda

innodb_buffer_pool_dump_at_shutdown = ON

innodb_buffer_pool_load_at_startup = ON

innodb_checksum_algorithm = crc32

innodb_log_compressed_pages = 0

innodb_locks_unsafe_for_binlog = 1

myisam_sort_buffer_size = 64M

# SSD

innodb_flush_neighbors = 0

# Mutex contention

innodb_adaptive_hash_index_partitions = 16

innodb_buffer_pool_instances = 16

# Basic Settings

expire-logs-days = 7

log-error = /var/log/mysqld.log

#log-queries-not-using-indexes = 1

#long-query-time = 20

#max-binlog-size = 100M

#slow-query-log = 0

#slow-query-log-file = /var/log/mysqlslowqueries.log

Once again, if you are only judging that "everything was working and now it is not" by the messages in the error log, you cannot do that. MariaDB 10.2 by default produces more diagnostics messages than MySQL 5.6, which is why you are getting those records in the log. If you want to return to MySQL behavior, add log_warnings=1 to your my.cnf file and restart the server.

I have added log_warnings=1 to my my.cnf, What does with adding this? Does its stop deadlocks and lock wait timeouts issue?

To my understanding, your complaint was that 10.2 writes to the error log errors like

mysqld: Deadlock found when trying to get lock; try restarting transaction

which MySQL 5.6 does not write. The change in the variable value fixes that.

The presence of these errors in the log alone does not indicate any change in behavior between the servers, it's just a change in logging. (it's possible that there is an actual difference, because InnoDB is essentially different between the versions, but the error log is not a proof of it).

If after disabling the extra logging you still have reasons to believe that you are getting more deadlocks/timeouts on 10.2 than on 5.6, please present them, preferably with a use case which does not lock on 5.6 but locks on 10.2.