Problem:
In previous versions of Foreman, I could go to any search box, and type the first few characters of a server name (or hostgroup, or etc) and have the reasonable expectation that the search would complete in <1 second. Recently (1.16 or 1.17) this behavior changed. If i search for a hostname “unscoped” (meaning no name ~ or name = prefix) by just typing it into the search bar, it takes minutes to return (if ever). It still completes (albiet very slowly) in my staging environment with 3-400 hosts. But in my main environment (14000 hosts) - the query is either extremely slow, or never completes at all. I can see the query taking “seconds to minutes” to return anything via a "show full processlist;.
Stick a name ~ or hostgroup_title ~ in front of the search and it immediately completes with no issue(s).
Expected outcome:
Unscoped Searches complete “quickly” as they used to in previous versions of foreman
Scoped Searches complete equally “quickly”
Foreman and Proxy versions:
Foreman 1.18.2 RHEL 7.5 MariaDB 10.2.16
Foreman and Proxy plugin versions:
| Name | Description | Author | Version |
|---|---|---|---|
| foreman-tasks | The goal of this plugin is to unify the way of showing task statuses across the Foreman instance. It defines Task model for keeping the information about the tasks and Lock for assigning the tasks to resources. The locking allows dealing with preventing multiple colliding tasks to be run on the same resource. It also optionally provides Dynflow infrastructure for using it for managing the tasks. | Ivan Nečas | 0.13.4 |
| foreman_ansible | Ansible integration with Foreman | Daniel Lobato Garcia | 2.2.7 |
| foreman_bootdisk | Plugin for Foreman that creates iPXE-based boot disks to provision hosts without the need for PXE infrastructure. | Dominic Cleal | 12.0.0 |
| foreman_column_view | Displays an additional column in the Foreman Hosts view and/or additional entries in the Host show page | Greg Sutcliffe | 0.4.0 |
| foreman_dhcp_browser | Plugin for Foreman to browse and add/edit/delete DHCP leases independent of Foreman’s host creation | Ohad Levy | 0.0.8 |
| foreman_hooks | Plugin engine for Foreman that enables running custom hook scripts on Foreman events | Dominic Cleal | 0.3.14 |
| foreman_memcache | Adds memcache support to foreman | Ohad Levy | 0.1.1 |
| foreman_remote_execution | A plugin bringing remote execution to the Foreman, completing the config management functionality with remote management functionality. | Foreman Remote Execution team | 1.5.6 |
| foreman_templates | Engine to synchronise provisioning templates from GitHub | Greg Sutcliffe | 6.0.3 |
Other relevant data:
Example log(s) and SQL Query
Foreman Log(s)
2018-10-17T10:24:14 [I|app|] Started GET "/widgets/14851?search=genappcv6" for 1 0.2.216.7 at 2018-10-17 10:24:14 -0400
2018-10-17T10:24:14 [I|app|] Started GET "/widgets/14852?search=genappcv6" for 1 0.2.216.7 at 2018-10-17 10:24:14 -0400
2018-10-17T10:24:14 [I|app|1688e] Parameters: {"search"=>"genappcv6", "id"=>"1 4851"}
2018-10-17T10:24:14 [I|app|bc77a] Parameters: {"search"=>"genappcv6", "id"=>"1 4852"}
2018-10-17T10:24:14 [I|app|] Started GET "/widgets/14853?search=genappcv6" for 1 0.2.216.7 at 2018-10-17 10:24:14 -0400
2018-10-17T10:24:14 [I|app|ab665] Parameters: {"search"=>"genappcv6", "id"=>"1 4853"}
2018-10-17T10:31:58 [I|app|] Started GET "/widgets/14850?search=genappcv6" for 1 0.2.216.7 at 2018-10-17 10:31:58 -0400
2018-10-17T10:31:58 [I|app|fcd2f] Parameters: {"search"=>"genappcv6", "id"=>"1 4850"}
| ActionView::Template::Error: Mysql2::Error: Not unique table/alias: 'reports' : SELECT COUNT(DISTINCT `hosts`.`id`) FROM `hosts` LEFT OUTER JOIN `reports` ON `reports`.`host_id` = `hosts`.`id` AND `reports`.`type` IN ('ConfigReport') LEFT OUTER JOIN `models` ON `models`.`id` = `hosts`.`model_id` LEFT OUTER JOIN `host groups` ON `hostgroups`.`id` = `hosts`.`hostgroup_id` AND (hostgroups.id IN (41, 51,81,91,101,111,121,151,171,181,201,211,212,222,231,241,242,252,262,272,281,282 ,291,321,331,431,441,451,461,462,501,511,581,582,591,592,602,611,621,631,641,651 ,661,671,681,691,701,711,721,731,741,751,781,791,801,811,821,831,841,851,861,871 ,881,891,901,911,921,931,941,942,951,961,971,981,991,992,1001,1011,1031,1041,105 1,1061,1071,1081,1091,1101,1111,1121,1131,1141,1142,1151,1161,1171,1181,1191,120 1,1211,1221,1231,1241,1261,1281,1291,1301,1311,1321,1331,1341,1361,1371,1381,139 1,1401,1411,1421,1431,1441,1451,1461,1471,1481,1491,1501,1511,1551,1561,1571,158 1,1591,1601,1611,1621,1631,1641,1651,1661,1671,1681,1691,1701,1711,1721,1731,175 1,1761,1771,1781,1791,1801,1811,1821,1831,1841,1851,1861,1871,1881,1891,1901,192 1,1931,1941,1951,1961,1971,1981,1991,2001,2011,2021,2031,2041,2051,2061,2071,208 1,2091,2101,2111,2121,2131,2141,2151,2161,2171,2181,2191,2201,2211,2221,2231,224 1,2251,2261,2281,2301,2311,2321,2391,2401,2411,2421,2431,2441,2451,2461,2471,248 1,2491,2501,2511,2521,2541,2551,2561,2571,2581,2591,2601,2611,2621,2631,2641,265 1,2661,2671,2681,2691,2701,2711,2721,2731,2741,2751,2761,2771,2781,2791,2801,281 1,2831,2841,2851,2861,2871,2881,2891,2901,2911,2921,2931,2941,3001,3011,3021,303 1,3041,3051,3061,3071,3081,3091,3101,3111,3121,3131,3141,3151,3161,3171,3181,321 1,3221,3231,3241,3251,3261,3271,3281,3291,3301,3311,3321,3331,3341,3351,3361,341 1,3431,3441,3451,3461,3471,3481,3491,3501,3511,3521,3531,3541,3561,3571,3581,359 1,3611,3621,3631,3641,3651,3661,3671,3681,3691,3701,3711,3712,3713,3714,3715,371 6,3717,3718,3719,3720,3723,3724,3725,3727,3728,3729,3730,3731,3732,3733,3734,373 5,3736,3737,3738,3739,3740,3741,3742,3743,3744,3745,3746,3747,3749,3750,3751,375 3,3754,3755,3758,3759,3760,3763,3764,3765,3766,3767,3768,3769,3770,3771,3772,377 3,3774,3782,3783,3784,3785,3787,3789,3790,3791,3792,3793,3795,3796,3799,3800,380 1,3802,3810,3811,3812,3813,3814,3815,3816,3817,3819,3820,3821,3822,3823,3824,382 5,3826,3827,3828,3829,3830,3831,3833,3834,3835,3836,3837,3839,3840,3842,3843,384 4,3845,3846,3847,3848,3849,3851,3852,3853,3854,3855,3856,3857,3858,3860,3861,386 2,3864,3865,3866,3867,3868,3869,3870,3871,1,3,61,71,131,141,761,3601,3752,3832,3 841,3850,3859,3863,11,21,3748,3761,3762)) LEFT OUTER JOIN `nics` ON `nics`.`host _id` = `hosts`.`id` AND `nics`.`primary` = 1 LEFT OUTER JOIN `domains` ON `domai ns`.`id` = `nics`.`domain_id` AND (domains.id IN (1,11,21,31,41,51,61,71,91,101, 102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119)) LEFT O UTER JOIN `realms` ON `realms`.`id` = `hosts`.`realm_id` AND (1=0) LEFT OUTER JO IN `environments` ON `environments`.`id` = `hosts`.`environment_id` AND (environ ments.id IN (1,7,11,21,31,41,51,61,71,81,101,112,121,131,141,161,171,181,191,211 ,221,231,251,261,271,281,291,301,302,310,313)) LEFT OUTER JOIN `architectures` O N `architectures`.`id` = `hosts`.`architecture_id` LEFT OUTER JOIN `compute_reso urces` ON `compute_resources`.`id` = `hosts`.`compute_resource_id` AND (compute_ resources.id IN (21,41,51,61,71,81,91,101,121,141,161,211,221,231,241,251,261,27 1,281,291,292,293,298,302,303,307,308,309,310,311,312,313,314,315,316,317,318,31 9,320,321)) LEFT OUTER JOIN `images` ON `images`.`id` = `hosts`.`image_id` LEFT OUTER JOIN `operatingsystems` ON `operatingsystems`.`id` = `hosts`.`operatingsys tem_id` LEFT OUTER JOIN `nics` `primary_interfaces_hosts` ON `primary_interfaces _hosts`.`host_id` = `hosts`.`id` AND `primary_interfaces_hosts`.`primary` = 1 LE FT OUTER JOIN `nics` `primary_interfaces_hosts_join` ON `primary_interfaces_host s_join`.`host_id` = `hosts`.`id` AND `primary_interfaces_hosts_join`.`primary` = 1 LEFT OUTER JOIN `subnets` ON `subnets`.`id` = `primary_interfaces_hosts_join` .`subnet_id` AND (subnets.id IN (491,531,641,741,751,761,771,781,801,821,841,911 ,961,1031,1041,1051,1061,1091,1131,1141,1151,1161,1181,1201,1221,1261,1421,1431, 1451,1501,1621,1631,1681,1701,1711,1721,1731,1781,1801,2041,2062,2063,2064,2065, 2066,2075,2077,2085,2086,2087,2094,2095,2102,2104,2105,2108,2121,2125,2128,1,71, 81,261,271,301,351,381,401,421,431,501,511,541,551,561,571,581,591,601,682,851,8 71,891,951,971,1021,1101,1111,1121,1171,1251,1321,1331,1651,1661,1671,2069,2082, 2084,2096,2097,2100,2106,2124,2127,2131,2136,2139,41,51,91,101,111,181,191,201,2 11,221,231,241,251,361,391,411,441,521,611,651,671,691,721,731,831,881,941,981,1 011,1241,1301,1311,1361,1381,1401,1481,1511,1541,1551,1571,1601,1691,1751,1811,1 821,1831,1841,1851,1861,1871,1881,2021,2068,2070,2081,2083,2089,2091,2099,2107,2 114,2115,2116,2117,2120,2123,2130,2134,2138,2,3,11,21,31,61,121,131,141,151,161, 171,281,291,311,321,331,341,451,461,471,481,621,631,661,701,711,791,811,861,921, 931,991,1001,1071,1081,1191,1211,1231,1271,1281,1291,1341,1351,1371,1391,1411,14 41,1461,1471,1491,1521,1531,1561,1581,1591,1611,1641,1741,1761,1771,1901,1911,19 21,1931,1941,1961,1971,1981,1991,2001,2011,2031,2051,2061,2067,2073,2074,2076,20 78,2079,2080,2088,2090,2092,2093,2098,2101,2103,2109,2110,2111,2112,2113,2119,21 22,2126,2129,2132,2137)) AND `subnets`.`type` = 'Subnet::Ipv4' LEFT OUTER JOIN ` nics` `primary_interfaces_hosts_join_2` ON `primary_interfaces_hosts_join_2`.`ho st_id` = `hosts`.`id` AND `primary_interfaces_hosts_join_2`.`primary` = 1 LEFT O UTER JOIN `subnets` `subnet6s_hosts` ON `subnet6s_hosts`.`id` = `primary_interfa ces_hosts_join_2`.`subnet6_id` AND (subnets.id IN (491,531,641,741,751,761,771,7 81,801,821,841,911,961,1031,1041,1051,1061,1091,1131,1141,1151,1161,1181,1201,12 21,1261,1421,1431,1451,1501,1621,1631,1681,1701,1711,1721,1731,1781,1801,2041,20 62,2063,2064,2065,2066,2075,2077,2085,2086,2087,2094,2095,2102,2104,2105,2108,21 21,2125,2128,1,71,81,261,271,301,351,381,401,421,431,501,511,541,551,561,571,581 ,591,601,682,851,871,891,951,971,1021,1101,1111,1121,1171,1251,1321,1331,1651,16 61,1671,2069,2082,2084,2096,2097,2100,2106,2124,2127,2131,2136,2139,41,51,91,101 ,111,181,191,201,211,221,231,241,251,361,391,411,441,521,611,651,671,691,721,731 ,831,881,941,981,1011,1241,1301,1311,1361,1381,1401,1481,1511,1541,1551,1571,160 1,1691,1751,1811,1821,1831,1841,1851,1861,1871,1881,2021,2068,2070,2081,2083,208 9,2091,2099,2107,2114,2115,2116,2117,2120,2123,2130,2134,2138,2,3,11,21,31,61,12 1,131,141,151,161,171,281,291,311,321,331,341,451,461,471,481,621,631,661,701,71 1,791,811,861,921,931,991,1001,1071,1081,1191,1211,1231,1271,1281,1291,1341,1351 ,1371,1391,1411,1441,1461,1471,1491,1521,1531,1561,1581,1591,1611,1641,1741,1761 ,1771,1901,1911,1921,1931,1941,1961,1971,1981,1991,2001,2011,2031,2051,2061,2067 ,2073,2074,2076,2078,2079,2080,2088,2090,2092,2093,2098,2101,2103,2109,2110,2111 ,2112,2113,2119,2122,2126,2129,2132,2137)) AND `subnet6s_hosts`.`type` = 'Subnet ::Ipv6' LEFT OUTER JOIN `nics` `provision_interfaces_hosts` ON `provision_interf aces_hosts`.`host_id` = `hosts`.`id` AND `provision_interfaces_hosts`.`provision ` = 1 INNER JOIN reports ON reports.host_id = hosts.id WHERE `hosts`.`type` IN ( 'Host::Managed') AND `hosts`.`organization_id` IN (7, 263, 251, 11, 261, 2, 3, 6 ) AND `hosts`.`location_id` IN (231, 201, 211, 221, 241, 5, 51, 71, 61, 121, 141 , 171, 262, 4, 81, 91, 101, 131, 161, 181, 1, 21, 41, 31, 111, 151, 191) AND ((` hosts`.`name` LIKE '%genappcv6%' OR `hosts`.`comment` LIKE '%genappcv6%' OR `rep orts`.`origin` LIKE '%genappcv6%' OR `models`.`name` LIKE '%genappcv6%' OR `host groups`.`name` LIKE '%genappcv6%' OR `hostgroups`.`title` LIKE '%genappcv6%' OR `domains`.`name` LIKE '%genappcv6%' OR `realms`.`name` LIKE '%genappcv6%' OR `en vironments`.`name` LIKE '%genappcv6%' OR `architectures`.`name` LIKE '%genappcv %' OR `compute_resources`.`name` LIKE '%genappcv6%' OR `images`.`name` LIKE '%ge nappcv6%' OR `operatingsystems`.`name` LIKE '%genappcv6%' OR `operatingsystems`. `description` LIKE '%genappcv6%' OR `operatingsystems`.`title` LIKE '%genappcv6% ' OR `nics`.`ip` LIKE '%genappcv6%' OR `subnets`.`network` LIKE '%genappcv6%' OR `subnets`.`name` LIKE '%genappcv6%' OR `subnets`.`network` LIKE '%genappcv6%' O R `subnets`.`name` LIKE '%genappcv6%' OR `hosts`.`uuid` LIKE '%genappcv6%' OR `n ics`.`mac` LIKE '%genappcv6%')) AND (reports.reported_at BETWEEN '2018-10-17 13: 31:59.169749' AND '2018-10-17 13:37:59.169749')
Manual Run of SQL Query for reference:
SELECT COUNT(DISTINCT `hosts`.`id`) FROM `hosts` LEFT OUTER JOIN `host_status` ON `host_status`.`host_id` = `hosts`.`id` LEFT OUTER JOIN `reports` ON `reports`.`host_id` = `hosts`.`id` AND `reports`.`type` IN ('ConfigReport') LEFT OUTER JOIN `models` ON `models`.`id` = `hosts`.`model_id` LEFT OUTER JOIN `hostgroups` ON `hostgroups`.`id` = `hosts`.`hostgroup_id` AND (hostgroups.id IN (41,51,81,91,101,111,121,151,171,181,201,211,212,222,231,241,242,252,262,272,281,282,291,321,331,431,441,451,461,462,501,511,581,582,591,592,602,611,621,631,641,651,661,671,681,691,701,711,721,731,741,751,781,791,801,811,821,831,841,851,861,871,881,891,901,911,921,931,941,942,951,961,971,981,991,992,1001,1011,1031,1041,1051,1061,1071,1081,1091,1101,1111,1121,1131,1141,1142,1151,1161,1171,1181,1191,1201,1211,1221,1231,1241,1261,1281,1291,1301,1311,1321,1331,1341,1361,1371,1381,1391,1401,1411,1421,1431,1441,1451,1461,1471,1481,1491,1501,1511,1551,1561,1571,1581,1591,1601,1611,1621,1631,1641,1651,1661,1671,1681,1691,1701,1711,1721,1731,1751,1761,1771,1781,1791,1801,1811,1821,1831,1841,1851,1861,1871,1881,1891,1901,1921,1931,1941,1951,1961,1971,1981,1991,2001,2011,2021,2031,2041,2051,2061,2071,2081,2091,2101,2111,2121,2131,2141,2151,2161,2171,2181,2191,2201,2211,2221,2231,2241,2251,2261,2281,2301,2311,2321,2391,2401,2411,2421,2431,2441,2451,2461,2471,2481,2491,2501,2511,2521,2541,2551,2561,2571,2581,2591,2601,2611,2621,2631,2641,2651,2661,2671,2681,2691,2701,2711,2721,2731,2741,2751,2761,2771,2781,2791,2801,2811,2831,2841,2851,2861,2871,2881,2891,2901,2911,2921,2931,2941,3001,3011,3021,3031,3041,3051,3061,3071,3081,3091,3101,3111,3121,3131,3141,3151,3161,3171,3181,3211,3221,3231,3241,3251,3261,3271,3281,3291,3301,3311,3321,3331,3341,3351,3361,3411,3431,3441,3451,3461,3471,3481,3491,3501,3511,3521,3531,3541,3561,3571,3581,3591,3611,3621,3631,3641,3651,3661,3671,3681,3691,3701,3711,3712,3713,3714,3715,3716,3717,3718,3719,3720,3723,3724,3725,3727,3728,3729,3730,3731,3732,3733,3734,3735,3736,3737,3738,3739,3740,3741,3742,3743,3744,3745,3746,3747,3749,3750,3751,3753,3754,3755,3758,3759,3760,3763,3764,3765,3766,3767,3768,3769,3770,3771,3772,3773,3774,3782,3783,3784,3785,3787,3789,3790,3791,3792,3793,3795,3796,3799,3800,3801,3802,3810,3811,3812,3813,3814,3815,3816,3817,3819,3820,3821,3822,3823,3824,3825,3826,3827,3828,3829,3830,3831,3833,3834,3835,3836,3837,3839,3840,3842,3843,3844,3845,3846,3847,3848,3849,3851,3852,3853,3854,3855,3856,3857,3858,3860,3861,3862,3864,3865,3866,3867,3868,3869,3870,3871,1,3,61,71,131,141,761,3601,3752,3832,3841,3850,3859,3863,11,21,3748,3761,3762)) LEFT OUTER JOIN `nics` ON `nics`.`host_id` = `hosts`.`id` AND `nics`.`primary` = 1 LEFT OUTER JOIN `domains` ON `domains`.`id` = `nics`.`domain_id` AND (domains.id IN (1,11,21,31,41,51,61,71,91,101,102,103,104,105,106,107,108,109,110,111,112,113,114,115,116,117,118,119)) LEFT OUTER JOIN `realms` ON `realms`.`id` = `hosts`.`realm_id` AND (1=0) LEFT OUTER JOIN `environments` ON `environments`.`id` = `hosts`.`environment_id` AND (environments.id IN (1,7,11,21,31,41,51,61,71,81,101,112,121,131,141,161,171,181,191,211,221,231,251,261,271,281,291,301,302,310,313)) LEFT OUTER JOIN `architectures` ON `architectures`.`id` = `hosts`.`architecture_id` LEFT OUTER JOIN `compute_resources` ON `compute_resources`.`id` = `hosts`.`compute_resource_id` AND (compute_resources.id IN (21,41,51,61,71,81,91,101,121,141,161,211,221,231,241,251,261,271,281,291,292,293,298,302,303,307,308,309,310,311,312,313,314,315,316,317,318,319,320,321)) LEFT OUTER JOIN `images` ON `images`.`id` = `hosts`.`image_id` LEFT OUTER JOIN `operatingsystems` ON `operatingsystems`.`id` = `hosts`.`operatingsystem_id` LEFT OUTER JOIN `nics` `primary_interfaces_hosts` ON `primary_interfaces_hosts`.`host_id` = `hosts`.`id` AND `primary_interfaces_hosts`.`primary` = 1 LEFT OUTER JOIN `nics` `primary_interfaces_hosts_join` ON `primary_interfaces_hosts_join`.`host_id` = `hosts`.`id` AND `primary_interfaces_hosts_join`.`primary` = 1 LEFT OUTER JOIN `subnets` ON `subnets`.`id` = `primary_interfaces_hosts_join`.`subnet_id` AND (subnets.id IN (491,531,641,741,751,761,771,781,801,821,841,911,961,1031,1041,1051,1061,1091,1131,1141,1151,1161,1181,1201,1221,1261,1421,1431,1451,1501,1621,1631,1681,1701,1711,1721,1731,1781,1801,2041,2062,2063,2064,2065,2066,2075,2077,2085,2086,2087,2094,2095,2102,2104,2105,2108,2121,2125,2128,1,71,81,261,271,301,351,381,401,421,431,501,511,541,551,561,571,581,591,601,682,851,871,891,951,971,1021,1101,1111,1121,1171,1251,1321,1331,1651,1661,1671,2069,2082,2084,2096,2097,2100,2106,2124,2127,2131,2136,2139,41,51,91,101,111,181,191,201,211,221,231,241,251,361,391,411,441,521,611,651,671,691,721,731,831,881,941,981,1011,1241,1301,1311,1361,1381,1401,1481,1511,1541,1551,1571,1601,1691,1751,1811,1821,1831,1841,1851,1861,1871,1881,2021,2068,2070,2081,2083,2089,2091,2099,2107,2114,2115,2116,2117,2120,2123,2130,2134,2138,2,3,11,21,31,61,121,131,141,151,161,171,281,291,311,321,331,341,451,461,471,481,621,631,661,701,711,791,811,861,921,931,991,1001,1071,1081,1191,1211,1231,1271,1281,1291,1341,1351,1371,1391,1411,1441,1461,1471,1491,1521,1531,1561,1581,1591,1611,1641,1741,1761,1771,1901,1911,1921,1931,1941,1961,1971,1981,1991,2001,2011,2031,2051,2061,2067,2073,2074,2076,2078,2079,2080,2088,2090,2092,2093,2098,2101,2103,2109,2110,2111,2112,2113,2119,2122,2126,2129,2132,2137)) AND `subnets`.`type` = 'Subnet::Ipv4' LEFT OUTER JOIN `nics` `primary_interfaces_hosts_join_2` ON `primary_interfaces_hosts_join_2`.`host_id` = `hosts`.`id` AND `primary_interfaces_hosts_join_2`.`primary` = 1 LEFT OUTER JOIN `subnets` `subnet6s_hosts` ON `subnet6s_hosts`.`id` = `primary_interfaces_hosts_join_2`.`subnet6_id` AND (subnets.id IN (491,531,641,741,751,761,771,781,801,821,841,911,961,1031,1041,1051,1061,1091,1131,1141,1151,1161,1181,1201,1221,1261,1421,1431,1451,1501,1621,1631,1681,1701,1711,1721,1731,1781,1801,2041,2062,2063,2064,2065,2066,2075,2077,2085,2086,2087,2094,2095,2102,2104,2105,2108,2121,2125,2128,1,71,81,261,271,301,351,381,401,421,431,501,511,541,551,561,571,581,591,601,682,851,871,891,951,971,1021,1101,1111,1121,1171,1251,1321,1331,1651,1661,1671,2069,2082,2084,2096,2097,2100,2106,2124,2127,2131,2136,2139,41,51,91,101,111,181,191,201,211,221,231,241,251,361,391,411,441,521,611,651,671,691,721,731,831,881,941,981,1011,1241,1301,1311,1361,1381,1401,1481,1511,1541,1551,1571,1601,1691,1751,1811,1821,1831,1841,1851,1861,1871,1881,2021,2068,2070,2081,2083,2089,2091,2099,2107,2114,2115,2116,2117,2120,2123,2130,2134,2138,2,3,11,21,31,61,121,131,141,151,161,171,281,291,311,321,331,341,451,461,471,481,621,631,661,701,711,791,811,861,921,931,991,1001,1071,1081,1191,1211,1231,1271,1281,1291,1341,1351,1371,1391,1411,1441,1461,1471,1491,1521,1531,1561,1581,1591,1611,1641,1741,1761,1771,1901,1911,1921,1931,1941,1961,1971,1981,1991,2001,2011,2031,2051,2061,2067,2073,2074,2076,2078,2079,2080,2088,2090,2092,2093,2098,2101,2103,2109,2110,2111,2112,2113,2119,2122,2126,2129,2132,2137)) AND `subnet6s_hosts`.`type` = 'Subnet::Ipv6' LEFT OUTER JOIN `nics` `provision_interfaces_hosts` ON `provision_interfaces_hosts`.`host_id` = `hosts`.`id` AND `provision_interfaces_hosts`.`provision` = 1 WHERE `hosts`.`type` IN ('Host::Managed') AND `hosts`.`organization_id` IN (7, 263, 251, 11, 261, 2, 3, 6) AND `hosts`.`location_id` IN (231, 201, 211, 221, 241, 5, 51, 71, 61, 121, 141, 171, 262, 4, 81, 91, 101, 131, 161, 181, 1, 21, 41, 31, 111, 151, 191) AND ((`hosts`.`name` LIKE '%genappcv6%' OR `hosts`.`comment` LIKE '%genappcv6%' OR `reports`.`origin` LIKE '%genappcv6%' OR `models`.`name` LIKE '%genappcv6%' OR `hostgroups`.`name` LIKE '%genappcv6%' OR `hostgroups`.`title` LIKE '%genappcv6%' OR `domains`.`name` LIKE '%genappcv6%' OR `realms`.`name` LIKE '%genappcv6%' OR `environments`.`name` LIKE '%genappcv6%' OR `architectures`.`name` LIKE '%genappcv6%' OR `compute_resources`.`name` LIKE '%genappcv6%' OR `images`.`name` LIKE '%genappcv6%' OR `operatingsystems`.`name` LIKE '%genappcv6%' OR `operatingsystems`.`description` LIKE '%genappcv6%' OR `operatingsystems`.`title` LIKE '%genappcv6%' OR `nics`.`ip` LIKE '%genappcv6%' OR `subnets`.`network` LIKE '%genappcv6%' OR `subnets`.`name` LIKE '%genappcv6%' OR `subnets`.`network` LIKE '%genappcv6%' OR `subnets`.`name` LIKE '%genappcv6%' OR `hosts`.`uuid` LIKE '%genappcv6%' OR `nics`.`mac` LIKE '%genappcv6%')) AND (hosts.last_report > '2018-10-17 14:19:44.240529') AND (host_status.type = 'HostStatus::ConfigurationStatus') AND (
((host_status.status >> 0 & 63) = 0) and
((host_status.status >> 6 & 63) = 0)
) AND (host_status.type = 'HostStatus::ConfigurationStatus') AND (
((host_status.status >> 12 & 63) = 0) and
((host_status.status >> 18 & 63) = 0)
) AND (host_status.type = 'HostStatus::ConfigurationStatus') AND (((host_status.status >> 30 & 63) = 0)) AND `hosts`.`enabled` = 1
20.312 Sec Duration to execute
Explain Select of above query
|1|SIMPLE|hosts|ref|PRIMARY,index_hosts_on_last_report,index_hosts_on_type,hosts_location_id_fk,hosts_organization_id_fk,index_hosts_on_type_and_organization_id_and_location_id,index_hosts_on_type_and_location_id|index_hosts_on_type|768|const|6859|Using index condition; Using where|
|---|---|---|---|---|---|---|---|---|---|
|1|SIMPLE|host_status|ref|index_host_status_on_type_and_host_id,index_host_status_on_host_id|index_host_status_on_type_and_host_id|772|const,foreman.hosts.id|1|Using index condition; Using where|
|1|SIMPLE|reports|ref|index_reports_on_host_id,index_reports_on_type,index_reports_on_type_and_host_id,index_reports_on_host_id_and_type_and_id|index_reports_on_type_and_host_id|771|const,foreman.hosts.id|33|Using where|
|1|SIMPLE|models|eq_ref|PRIMARY|PRIMARY|4|foreman.hosts.model_id|1|Using where|
|1|SIMPLE|hostgroups|eq_ref|PRIMARY|PRIMARY|4|foreman.hosts.hostgroup_id|1|Using where|
|1|SIMPLE|nics|ref|index_by_host|index_by_host|5|foreman.hosts.id|1|Using where|
|1|SIMPLE|domains|eq_ref|PRIMARY|PRIMARY|4|foreman.nics.domain_id|1|Using where|
|1|SIMPLE|realms|eq_ref|PRIMARY|PRIMARY|4|foreman.hosts.realm_id|1|Using where|
|1|SIMPLE|environments|eq_ref|PRIMARY|PRIMARY|4|foreman.hosts.environment_id|1|Using where|
|1|SIMPLE|architectures|eq_ref|PRIMARY|PRIMARY|4|foreman.hosts.architecture_id|1|Using where|
|1|SIMPLE|compute_resources|eq_ref|PRIMARY|PRIMARY|4|foreman.hosts.compute_resource_id|1|Using where|
|1|SIMPLE|images|eq_ref|PRIMARY|PRIMARY|4|foreman.hosts.image_id|1|Using where|
|1|SIMPLE|operatingsystems|eq_ref|PRIMARY|PRIMARY|4|foreman.hosts.operatingsystem_id|1|Using where|
|1|SIMPLE|primary_interfaces_hosts|ref|index_by_host|index_by_host|5|foreman.hosts.id|1|Using where|
|1|SIMPLE|primary_interfaces_hosts_join|ref|index_by_host|index_by_host|5|foreman.hosts.id|1|Using where|
|1|SIMPLE|subnets|eq_ref|PRIMARY,index_subnets_on_type|PRIMARY|4|foreman.primary_interfaces_hosts_join.subnet_id|1|Using where|
|1|SIMPLE|primary_interfaces_hosts_join_2|ref|index_by_host|index_by_host|5|foreman.hosts.id|1|Using where|
|1|SIMPLE|provision_interfaces_hosts|ref|index_by_host|index_by_host|5|foreman.hosts.id|1|Using where|
Based on what I’m seeing lately with regard to my other posts- this “feels” like an index issue specific to mysql/mariadb?
I’ve also been tackling this on the DB end - utilizing the “mysqltuner” git project. Alarmingly - its showing me the following stat after only 23 minutes uptime…
[!!] Joins performed without indexes: 108920
Seems like a “lot” of stuff (at least in mysql/mariadb - i cant speak for postgresql) isn’t using the proper indexes for joins.