Lukas Zapletal wrote:
I don't have MySQL instance handy, but what SQL does it generate? It should be pretty fast as we limit the values via `host_id` so this should bring number of rows down pretty quick. MySQL optimizer could maybe do it in incorrect order, there are three where clauses.
The old version executed (fast transaction):
SELECT `fact_values`.`id` AS t0_r0,
`fact_values`.`value` AS t0_r1,
`fact_values`.`fact_name_id` AS t0_r2,
`fact_values`.`host_id` AS t0_r3,
`fact_values`.`updated_at` AS t0_r4,
`fact_values`.`created_at` AS t0_r5,
`fact_names`.`id` AS t1_r0,
`fact_names`.`name` AS t1_r1,
`fact_names`.`updated_at` AS t1_r2,
`fact_names`.`created_at` AS t1_r3,
`fact_names`.`compose` AS t1_r4,
`fact_names`. `short_name` AS t1_r5,
`fact_names`.`type` AS t1_r6,
`fact_names`.`ancestry` AS t1_r7
FROM `fact_values`
LEFT OUTER JOIN `fact_names`
ON `fact_names`.`id` = `fact_values`.`fact_name_id`
WHERE `fact_values`. `host_id` = 2309436
AND ( fact_names.type = 'PuppetFactName'
AND fact_names.name NOT IN (
'environment', 'processor4', 'operatingsystem',
'bmc_subnet','rvm_installed', 'interfaces',
'blockdevice_sdb_size', 'processor1',
'mounted _filesystems', 'os', 'ipmi_lan_channel', 'mtu_bond0',
'swapsize_mb', 'ipmi_ipaddress', 'package_provider', 'concat_basedir',
'bmc_mac', 'swapfree', 'ipmi1_lan_channel', 'macaddress_eth0',
'bios_release_date', 'staging_http_ge t', 'sshrsakey', 'clientnoop',
'system_uptime', 'memoryfree', 'processor37', 'main_interface',
'uuid', 'swapsize', 'postgres_default_version', 'mco_version',
'selinux_config_mode', 'swapfile_sizes', 'puppetversion', 'processor 34',
'processor28', 'bios_version', 'processor31', 'processor19',
'productname', 'processor25', 'facterversion', 'processor16',
'service_provider', 'processor22', 'uptime', 'blockdevice_sda_vendor',
'is_pe', 'megaraid_adapters ', 'processor13', 'filesystems',
'is_virtual', 'processor10', '_timestamp', 'processor8',
'macaddress', 'ipmi1_ipaddress', 'hostname', 'serialnumber',
'selinux', 'processor5', 'kernelmajversion', 'uptime_hours',
'uniqueid', 'mtu …...' ) )
DELETE FROM `fact_values` WHERE `fact_values`.`id` = 410225436
The new version executing (long transaction):
DELETE FROM `fact_values`
WHERE `fact_values`.`id` = (SELECT `fact_values`.`id` AS t0_r0,
`fact_values`.`value` AS t0_r1,
`fact_values`.`fact_name_id` AS t0_r2,
`fact_values`.`host_id` AS t0_r3,
`fact_values`.`updated_at` AS t0_r4,
`fact_values`.`created_at` AS t0_r5,
`fact_names`.`id` AS t1_r0,
`fact_names`.`name` AS t1_r1,
`fact_names`.`updated_at` AS t1_r2,
`fact_names`.`created_at` AS t1_r3,
`fact_names`.`compose` AS t1_r4,
`fact_names`. `short_name` AS t1_r5,
`fact_names`.`type` AS t1_r6,
`fact_names`.`ancestry` AS t1_r7
FROM `fact_values`
LEFT OUTER JOIN `fact_names`
ON `fact_names`.`id` =
`fact_values`.`fact_name_id`
WHERE
`fact_values`. `host_id` = 2309436
AND ( fact_names.type = 'PuppetFactName'
AND fact_names.name NOT IN (
'environment', 'processor4', 'operatingsystem', 'bmc_subnet',
'rvm_installed', 'interfaces', 'blockdevice_sdb_size', 'processor1',
'mounted _filesystems', 'os', 'ipmi_lan_channel', 'mtu_bond0',
'swapsize_mb', 'ipmi_ipaddress', 'package_provider', 'concat_basedir',
'bmc_mac', 'swapfree', 'ipmi1_lan_channel', 'macaddress_eth0',
'bios_release_date', 'staging_http_ge t', 'sshrsakey', 'clientnoop',
'system_uptime', 'memoryfree', 'processor37', 'main_interface',
'uuid', 'swapsize', 'postgres_default_version', 'mco_version',
'selinux_config_mode', 'swapfile_sizes', 'puppetversion', 'processor 34',
'processor28', 'bios_version', 'processor31', 'processor19',
'productname', 'processor25', 'facterversion', 'processor16',
'service_provider', 'processor22', 'uptime', 'blockdevice_sda_vendor',
'is_pe', 'megaraid_adapters ', 'processor13', 'filesystems',
'is_virtual', 'processor10', '_timestamp', 'processor8' ,
'macaddress', 'ipmi1_ipaddress', 'hostname', 'serialnumber',
'selinux', 'processor5', 'kernel…...' )))
I tried to collects stats and analyze all foreman schema and it not solved the problem.
I saw few ideas to improve the delete, instead nested subquery:
http://stackoverflow.com/a/17100006
http://stackoverflow.com/a/7361345
http://dba.stackexchange.com/a/71233