Re: Foreman query optimization

[Kyle Flavin]

The server has 64 GB of RAM. The buffer pool is set as follows:

innodb_buffer_pool_size = 45097156608

I tried adding an index on source_id on the logs side, but no luck:

Create Table: CREATE TABLE `logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `source_id` int(11) DEFAULT NULL,
  `message_id` int(11) DEFAULT NULL,
  `report_id` int(11) DEFAULT NULL,
  `level_id` int(11) DEFAULT NULL,
  `created_at` datetime DEFAULT NULL,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_logs_on_report_id` (`report_id`),
  KEY `index_logs_on_message_id` (`message_id`),
  KEY `index_logs_on_level_id` (`level_id`),
  KEY `idx_source_id` (`source_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8729466 DEFAULT CHARSET=latin1