Increment query takes long time to execute in MariaDB

Question

The query below takes 0.005secs to 7secs to execute on the production server database but max 0.010 secs on the local database. Don't know what's wrong with the query.

Query:

UPDATE tbl_entrance_exam_report_info SET 
total_marks_obtained=total_marks_obtained+0.0, 
marks_obtained_from_overall_subjects=marks_obtained_from_overall_subjects+0.0, 
end_time=NOW(), 
total_solved=total_solved+1, 
overall_skipped_question_ids='', 
skipped_question_ids='', 
total_incorrect_answers=total_incorrect_answers+1 
WHERE id=4737

Note: Columns with +x increment are either int or float. Also, the production server database has been dumped and imported on the localhost and has 4k data.

SHOW CREATE TABLE tbl_entrance_exam_report_info:

CREATE TABLE `tbl_entrance_exam_report_info` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `created_date` datetime NOT NULL,
  `end_time` datetime DEFAULT NULL,
  `start_time` datetime NOT NULL,
  `total_correct_answers` int(11) NOT NULL DEFAULT 0,
  `total_incorrect_answers` int(11) NOT NULL DEFAULT 0,
  `total_marks_obtained` float NOT NULL,
  `total_skipped` int(11) NOT NULL DEFAULT 0,
  `total_solved` int(11) NOT NULL DEFAULT 0,
  `admission_student_information_id` bigint(20) DEFAULT NULL,
  `online_exam_set_id` bigint(20) NOT NULL,
  `team_id` int(11) DEFAULT NULL,
  `exited` tinyint(1) DEFAULT 0,
  `overall_skipped_question_ids` longtext DEFAULT NULL,
  `skipped_question_ids` longtext DEFAULT NULL,
  `student_id` int(11) DEFAULT NULL,
  `marks_obtained_from_overall_subjects` float NOT NULL DEFAULT 0,
  `admission_application_id` bigint(20) DEFAULT NULL,
  `college_admission_application_id` bigint(20) DEFAULT NULL,
  `public_student_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `FK1B3AE9E19768BB6B` (`online_exam_set_id`),
  KEY `FK1B3AE9E1F94118FD` (`admission_student_information_id`),
  KEY `FK1B3AE9E17E2DE65F` (`team_id`),
  KEY `FK1B3AE9E18CFA03F5` (`student_id`),
  KEY `FKoq6i5nhgrsdcin3vxrwl6qout` (`admission_application_id`),
  KEY `FKfskks7r4xmjma8eckjdfmg9st` (`college_admission_application_id`),
  KEY `FKsmin66q8dcsuvm7rexdujrolm` (`public_student_id`),
  CONSTRAINT `FK1B3AE9E17E2DE65F` FOREIGN KEY (`team_id`) REFERENCES `tbl_team` (`id`),
  CONSTRAINT `FK1B3AE9E18CFA03F5` FOREIGN KEY (`student_id`) REFERENCES `tbl_student` (`id`),
  CONSTRAINT `FK1B3AE9E19768BB6B` FOREIGN KEY (`online_exam_set_id`) REFERENCES `tbl_online_exam_set_school` (`id`),
  CONSTRAINT `FK1B3AE9E1F94118FD` FOREIGN KEY (`admission_student_information_id`) REFERENCES `tbl_admission_student_application` (`id`),
  CONSTRAINT `FKfskks7r4xmjma8eckjdfmg9st` FOREIGN KEY (`college_admission_application_id`) REFERENCES `tbl_college_admission_application` (`id`),
  CONSTRAINT `FKoq6i5nhgrsdcin3vxrwl6qout` FOREIGN KEY (`admission_application_id`) REFERENCES `tbl_admission_application` (`id`),
  CONSTRAINT `FKsmin66q8dcsuvm7rexdujrolm` FOREIGN KEY (`public_student_id`) REFERENCES `tbl_public_student` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4744 DEFAULT CHARSET=utf8

SHOW GLOBAL STATUS

Production server MariaDB details (slower one):

Local server MariaDB details:

Update: my.cnf with tuning changes:

[mysqld]
max_allowed_packet = 1000M
max_connections = 9999
innodb_buffer_pool_instances    = 6     # Use 1 instance per 1GB of InnoDB pool size
innodb_buffer_pool_size         = 6G    # Use up to 70-80% of RAM
innodb_log_file_size = 256M

#cache
query_cache_type = 1
query_cache_limit = 256k
query_cache_min_res_unit = 2k
query_cache_size = 80M

Server Info:

Architecture:          x86_64
CPU op-mode(s):        32-bit, 64-bit
Byte Order:            Little Endian
CPU(s):                20
On-line CPU(s) list:   0-19
Thread(s) per core:    1
Core(s) per socket:    1
Socket(s):             20
NUMA node(s):          1
Vendor ID:             GenuineIntel
CPU family:            6
Model:                 79
Model name:            Intel(R) Xeon(R) CPU E5-2620 v4 @ 2.10GHz
Stepping:              1
CPU MHz:               2097.063
BogoMIPS:              4195.17
Hypervisor vendor:     Xen
Virtualization type:   full
L1d cache:             32K
L1i cache:             32K
L2 cache:              256K
L3 cache:              20480K
NUMA node0 CPU(s):     0-19
RAM:                   40GB

Answer 1

Disable query cache;

query_cache_type=0
query_cache_size=0

in configuration file for test run with set global query_cache_type=0,query_cache_size=0.

To see if this is still happening enable your query log:

slow_query_log=1
slow_query_log_file=/var/log/mysql/mariadb-slow.log
log_slow_verbosity=explain
long_query_time=1

(these also can be set at runtime).

Try to solve your slow queries on dba stack exchange.

Look up mechanisms for monitoring the io capacity and latency of your storage.