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

On server version is 10.2.31 and the other is 10.4.13? Which is the slower production server? Are you testing them with the same size dataset? Are you running out of memory on your production server? What else is happening on the production server? Is `id` the primary key of `tbl_entrance_exam_report_info`? — danblack, Aug 29 '20 at 02:58
btw, the query is fine. There's something else in the environment going. Your production server mysql configuration, RAM, `show global status` and `show create table tbl_entrance_exam_report_info` information would be useful. Edit the question to include this. — danblack, Aug 29 '20 at 03:00
Hi @danblack, thanks for your response. I have updated the question as per your last comments. Please have a look. — Kapil, Aug 29 '20 at 04:38
Disable your query cache for a start this could be the cause (its rarely useful). Include the my.cnf files you have on the server that contain tuning changes. How much RAM on the production server? You are creating a temporary table on disk every 2 seconds. Is your IO overloaded. You have problems with your `select` statements too to generate this many tmp tables on disk. — danblack, Aug 29 '20 at 05:08
Thanks again. I have updated the question as per your last comment. — Kapil, Aug 29 '20 at 05:55
About how long were those two `longtext` columns before you cleared them? `STATUS` is not very useful without `SHOW GLOBAL VARIABLES`. Please provide `SHOW TABLE STATUS LIKE 'tbl_entrance_exam_report_info';` — Rick James, Aug 30 '20 at 01:42

score 1 · Accepted Answer · answered Aug 29 '20 at 06:43

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.

answered Aug 29 '20 at 06:43

danblack

12,130
2
22
41

Thanks a lot. It's faster than the previous one but still takes up to 2 seconds. I have applied all changes mentioned above. I will monitor it more and provide you the update. – Kapil Aug 29 '20 at 17:12
Everything is fine till date. Thanks :) – Kapil Sep 02 '20 at 04:47

Increment query takes long time to execute in MariaDB

1 Answers1