I have a table in my database that acts like a log to record accesses to some selected endpoints of my application.
That table contains:
- id (primary key)
- The http method (GET, POST, PUT, etc)
- The endpoint, some useful data that might be passed
- If the request was made by a logged in user, the user_id. Otherwise user_id is NULL (meaning that the request was made by a guest).
This table is starting to get big (already more than 6 million records) and sometimes the insert statements take more than 2 seconds. We don't perform "intensive" operations in this table: just
SELECT * FROM table WHERE user_id = xxxx.
Is it normal that sometimes this insert is done quickly and sometimes it takes more than 2 seconds? What can be done to reduce this time?
Thanks in advance!
CREATE TABLE `user_actions` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `http_method` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL, `endpoint` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `request_data` text COLLATE utf8mb4_unicode_ci NOT NULL, `user_id` int(10) unsigned DEFAULT NULL, `user_agent` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL, `created_at` timestamp NULL DEFAULT NULL, `updated_at` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `user_actions_user_id_foreign` (`user_id`), CONSTRAINT `user_actions_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION) ENGINE=InnoDB AUTO_INCREMENT=6312242 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
This is an example of one INSERT statement that took 5.3 seconds:
insert into `user_actions` (`http_method`, `route_name`, `query_string`, `ip_address`, `user_agent`, `request_data`, `user_id`, `updated_at`, `created_at`) values ('GET', '19-chars-string', '64-chars-string', '220.127.116.11', '139-chars-string', '', 96411, '2018-11-29 18:49:08', '2018-11-29 18:49:08');
Server RAM = 16GB innodb_buffer_pool_size = 12G innodb_buffer_pool_instances = 12 innodb_page_cleaners = 12