FOG Multicast Not starting anymore
-
@george1421 said in FOG Multicast Not starting anymore:
SELECT TABLE_NAME,ENGINE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = ‘fog’ and ENGINE = ‘MyISAM’;Hello
I have a result that look like your, but I get only 51 rows .I have more than 300 computer deployed with Fog so I collect info that you need.
mysql> SELECT TABLE_NAME,ENGINE -> FROM information_schema.TABLES -> WHERE TABLE_SCHEMA = 'fog' and ENGINE = 'MyISAM'; +------------------------+--------+ | TABLE_NAME | ENGINE | +------------------------+--------+ | clientUpdates | MyISAM | | dirCleaner | MyISAM | | globalSettings | MyISAM | | greenFog | MyISAM | | groupMembers | MyISAM | | groups | MyISAM | | history | MyISAM | | hookEvents | MyISAM | | hostAutoLogOut | MyISAM | | hostMAC | MyISAM | | hostScreenSettings | MyISAM | | hosts | MyISAM | | imageGroupAssoc | MyISAM | | imagePartitionTypes | MyISAM | | imageTypes | MyISAM | | images | MyISAM | | imagingLog | MyISAM | | inventory | MyISAM | | ipxeTable | MyISAM | | keySequence | MyISAM | | moduleStatusByHost | MyISAM | | modules | MyISAM | | multicastSessions | MyISAM | | multicastSessionsAssoc | MyISAM | | nfsFailures | MyISAM | | nfsGroupMembers | MyISAM | | nfsGroups | MyISAM | | notifyEvents | MyISAM | | os | MyISAM | | oui | MyISAM | | plugins | MyISAM | | powerManagement | MyISAM | | printerAssoc | MyISAM | | printers | MyISAM | | pxeMenu | MyISAM | | scheduledTasks | MyISAM | | schemaVersion | MyISAM | | snapinAssoc | MyISAM | | snapinGroupAssoc | MyISAM | | snapinJobs | MyISAM | | snapinTasks | MyISAM | | snapins | MyISAM | | supportedOS | MyISAM | | taskLog | MyISAM | | taskStates | MyISAM | | taskTypes | MyISAM | | tasks | MyISAM | | userCleanup | MyISAM | | userTracking | MyISAM | | users | MyISAM | | virus | MyISAM | +------------------------+--------+ 51 rows in set (0.00 sec) -
@lebrun78 Thank you for the feedback . Your install is suffering from having the database engine for the tables in ISAM format instead of INNODB. We can fix this no problem.
OK now my needs. I want to use your mysql database with many fog clients to record how many queries take long than 1 second to complete. With your slow database it probably will be all of them. I want to do a before and after test to see the improvements and then once we get the improvements the after scan will give us hints how to make the database go even faster.
The following commands will turn on global query logging (note it might even make your database slower so we will only let it run for 30 minutes).
Log into mysql database as root and run these commands.
SET GLOBAL slow_query_log = 'ON'; SET GLOBAL slow_query_log_file = '/var/log/mysql/slowquery.log'; SET GLOBAL log_queries_not_using_indexes = 'ON'; SET GLOBAL long_query_time = 1; FLUSH LOGS;Let this run for 30 minutes at the time of day where you would have the most computers on line and pinging the FOG server.
Then after 30 minutes log back into mysql and run this command to turn off logging.
SET GLOBAL slow_query_log = 'OFF';The last part is to make a report of the log file and post it in this thread or via a file sharing site. From the linux command prompt run this command to make the report
mysqldumpslow -a /var/log/mysql/slowquery.log >/tmp/slowreport.txtDownload the
/tmp/slowreport.txtfrom the FOG server and post it here, please. -
Here is the logfile:
Count: 4 Time=0.01s (0s) Lock=0.00s (0s) Rows=517.0 (2068), fogmaster[fogmaster]@localhost SELECT `hostIP` FROM `hosts` LEFT OUTER JOIN `hostMAC` ON `hostMAC`.`hmHostID`=`hosts`.`hostID` LEFT OUTER JOIN `images` ON `images`.`imageID`=`hosts`.`hostImage` LEFT OUTER JOIN `os` ON `os`.`osID`=`images`.`imageOSID` LEFT OUTER JOIN `imagePartitionTypes` ON `imagePartitionTypes`.`imagePartitionTypeID`=`images`.`imagePartitionTypeID` LEFT OUTER JOIN `imageTypes` ON `imageTypes`.`imageTypeID`=`images`.`imageTypeID` LEFT OUTER JOIN `hostScreenSettings` ON `hostScreenSettings`.`hssHostID`=`hosts`.`hostID` LEFT OUTER JOIN `hostAutoLogOut` ON `hostAutoLogOut`.`haloHostID`=`hosts`.`hostID` LEFT OUTER JOIN `inventory` ON `inventory`.`iHostID`=`hosts`.`hostID` WHERE `hosts`.`hostID` IN ('576','1','78','3','4','5','6','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','581','38','39','40','41','42','570','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','2','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','577','578','105','106','107','108','109','110','518','517','519','520','521','522','523','524','525','526','527','528','563','529','562','530','531','532','533','534','535','536','537','538','539','540','541','542','543','544','545','566','546','547','548','549','550','551','552','553','554','555','556','557','558','564','565','560','559','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','596','149','150','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','192','193','194','195','196','197','198','199','200','201','202','203','204','205','206','207','208','209','210','211','212','213','214','215','216','217','218','219','220','221','222','223','224','225','226','227','228','229','230','231','232','595','582','233','234','499','568','235','236','237','238','239','240','241','242','243','244','245','246','247','248','249','250','251','252','253','254','255','256','257','258','259','260','261','262','263','264','265','266','267','268','269','270','271','272','273','274','275','276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354','355','356','357','358','359','360','361','362','363','364','365','366','367','368','369','370','371','372','373','374','375','376','377','378','379','380','381','382','383','384','385','386','387','388','389','390','391','392','393','394','580','585','587','139','588','589','575','590','591','592','396','397','398','399','400','401','402','403','404','405','406','407','408','409','410','411','412','413','414','415','416','417','418','419','420','421','422','423','424','425','426','427','428','429','430','431','432','433','434','435','436','583','500','501','502','503','509','488','571','586','489','594','579','573','512','466','569','567','514','593','504','490','498','574','516','505','492','493','494','495','497') AND `hosts`.`hostName` IN ('a44cc8138256','accueil','admin01','admin02','admin07','admin08','admin09','admin12','admin13','admin14','admin15','admin16','agot','arrakis','b05-929m01','b05-929m02','b05-929m03','b05-929m04','b05-929m05','b05-929m06','b05-929m07','b05-929m08','b05-929m09','b05-929m10','b06-002m01','b06-002m02','b06-002m03','b06-002m04','b06-002m05','b06-002m06','b06-002m07','b06-002m08','b06-002m09','b06-002m10','b06-002m11','b06-006m01','b06-008fraise','b06-008m01','b06-008m02','b06-008m03','b06-008m04','b06-008m05','b06-010m02','b06-013m01','b06-013m02','b06-013m03','b06-013m04','b06-013m05','b06-013m06','b06-013m07','b06-013m08','b06-013m09','b06-013m10','b06-013m11','b06-013m12','b06-013m13','b06-013m14','b06-013m15','b06-013m16','b06-013m17','b06-013m18','b06-013m19','b06-013m20','b06-016m01','b06-016m02','b06-016m03','b06-016m04','b06-016m05','b06-016m06','b06-016m07','b06-016m08','b06-016m09','b06-016m10','b06-019-1m01','b06-019-1m02','b06-019-1m03','b06-019-1m04','b06-019-1m05','b06-019-1m06','b06-019-1m07','b06-019-1m08','b06-019-1m09','b06-019-1m10','b06-019-2m01','b06-019-2m02','b06-019-2m02par','b06-019-2m03','b06-019-2m04','b06-021m01','b06-022m01','b06-936m01','b06-937m01','b06-937m02','b06-937m03','b06-937m04','b06-937m05','b06-937m06','b06-937m07','b06-937m08','b06-937m09','b06-937m10','b06-938m01','b06-938m02','b06-938m03','b06-938m04','b06-938m05','b06-938m06','b06-pxi1','b06-pxi2','b06-pxi3','b06-pxi4','b06-pxi5','b06-pxi6','b40-011m01','b40-011m02','b40-011m03','b40-011m04','b40-011m05','b40-011m06','b40-011m07','b40-011m08','b40-011m09','b40-011m10','b40-011m11','b40-011m12','b40-011m13','b40-011m14','b40-011m15','b40-011m16','b40-011m17','b40-011m18','b40-011m19','b40-011m20','b40-011m21','b40-011m22','b40-011m23','b40-011m24','b40-011m25','b40-013m01','b40-013m02','b40-013m03','b40-013m04','b40-013m05','b40-013m06','b40-013m07','b40-013m08','b40-013m09','b40-013m10','b40-013m11','b40-013m12','b40-013m13','b40-013m14','b40-013m15','b40-013m16','b40-013m17','b40-013m18','b40-013m19','b40-013m20','b40-013m21','b40-013m22','b40-013m23','b40-013m24','b41-101m01','b41-101m02','b41-101m03','b41-101m04','b41-101m05','b41-101m06','b41-101m07','b41-101m08','b41-101m09','b41-101m10','b41-101m11','b41-102m01','b41-103m01','b41-104m01','b41-104m02','b41-104m03','b41-104m04','b41-104m05','b41-104m06','b41-104m07','b41-104m08','b41-104m09','b41-104m10','b41-104m11','b41b-009m01','b42-104m01','b42-hallm01','centos7-tp','d000m01','d000m02','d001m01','d020m01','d020m02','d020m03','d020m04','d020m05','d020m06','d020m07','d020m08','d020m09','d020m10','d022m01','d022m02','d022m03','d022m04','d022m05','d022m06','d022m07','d022m08','d022m09','d022m10','d024m01','d024m02','d024m03','d024m04','d024m05','d024m06','d024m07','d024m08','d024m09','d024m10','d026m01','d026m02','d026m03','d026m04','d026m05','d026m06','d026m07','d026m08','d026m09','d026m10','d028m01','d028m02','d028m03','d028m04','d028m05','d028m06','d028m07','d028m08','d028m09','d028m10','d122m01','d122m02','d122m03','d122m04','d122m05','d122m06','d122m07','d122m08','d122m09','d122m10','d180m01','d181m01','d181m02','d182m01','d183m01','d193m03','d265m01','d266m01','d270m01','d270m02','d272m01','d279m01','d279m02','d281m01','d282m01','d284m02','d288m01','d481d7fed37a','d4bed922bad8','d901m01','d903m01','deepml','DESKTOP-T7256EF','e003m01','e003m02','e003m03','e003m04','e003m05','e003m06','e003m07','e003m08','e003m09','e003m10','e005m01','e005m02','e005m03','e005m04','e005m05','e005m06','e005m07','e005m08','e005m09','e005m10','e007m01','e007m02','e007m03','e007m04','e007m05','e007m06','e007m07','e007m08','e007m09','e007m10','e008m01','e008m02','e008m03','e008m04','e008m05','e008m06','e008m07','e008m08','e008m09','e008m10','e010m01','e010m02','e010m03','e010m04','e010m05','e010m06','e010m07','e010m08','e010m09','e010m10','e103m01','e103m02','e103m03','e103m04','e103m05','e103m06','e103m07','e103m08','e103m09','e103m10','e105m01','e105m02','e105m03','e105m04','e105m05','e105m06','e105m07','e105m08','e105m09','e105m10','e107m01','e107m02','e107m03','e107m04','e107m05','e107m06','e107m07','e107m08','e107m09','e107m10','e108m01','e206m01','e206m02','e206m03','e206m04','e206m05','e206m06','e206m07','e206m08','e206m09','e206m10','e212m01','e212m02','e212m03','e212m04','e212m05','e212m06','e212m07','e212m08','e212m09','e212m10','e214m01','e214m02','e214m03','e214m04','e214m05','e214m06','e214m07','e214m08','e214m09','e214m10','fedora24','frioul','i050m01','i051m01','i052m01','i054m01','i054m02','i054m03','i054m04','i054m05','i054m06','i054m07','i054m08','i054m09','i054m10','i054m11','i055m01','i055m02','i055m03','i055m04','i055m05','i055m06','i055m07','i055m08','i055m09','i055m10','i055m11','i058m01','i059m01','i060m01','i203m01','i203m02','i203m03','i203m04','i203m05','i203m06','i203m07','i203m08','i203m09','i203m10','i204m01','i204m02','i204m03','i204m04','i204m05','i204m06','i204m07','i204m08','i204m09','i205m01','i205m02','i205m03','i205m04','i205m05','i205m06','i205m07','i205m08','i205m09','i205m10','i206m01','i206m02','i206m03','i206m04','i206m05','i206m06','i206m07','i206m08','i206m09','i206m10','i207m01','i207m02','i207m03','i207m04','i207m05','i207m06','i207m07','i207m08','i207m09','i207m10','i207m11','i207m12','i207m13','i207m14','i207m15','i207m16','i207m17','i207m18','i207m19','i207m20','i207m21','i207m22','i213m01','i225m01','i225m02','i225m03','i225m04','i227m01','i227m02','i227m03','i227m04','linux','maquette-secret','miage-dell','miage-dell2','miage-dell3','miage-dell4','montevideo','MX2','nimbus','ouessant','port-sec-esir','portable-HP-etu','portable-sec','PortableGilles','probookG1','probookG2','sec-esir-port1','tp-istic','tp-linux','travail-windows','virus','win10','win10-1','win10-2','Win10-3','win10-4','win10-5','win10-6','win10-client-ad','win10test') AND `hostMAC`.`hmPrimary` = '1' ORDER BY LOWER(`hosts`.`hostName`) ASC Count: 4 Time=0.01s (0s) Lock=0.00s (0s) Rows=517.0 (2068), fogmaster[fogmaster]@localhost SELECT `hostID` FROM `hosts` LEFT OUTER JOIN `hostMAC` ON `hostMAC`.`hmHostID`=`hosts`.`hostID` LEFT OUTER JOIN `images` ON `images`.`imageID`=`hosts`.`hostImage` LEFT OUTER JOIN `os` ON `os`.`osID`=`images`.`imageOSID` LEFT OUTER JOIN `imagePartitionTypes` ON `imagePartitionTypes`.`imagePartitionTypeID`=`images`.`imagePartitionTypeID` LEFT OUTER JOIN `imageTypes` ON `imageTypes`.`imageTypeID`=`images`.`imageTypeID` LEFT OUTER JOIN `hostScreenSettings` ON `hostScreenSettings`.`hssHostID`=`hosts`.`hostID` LEFT OUTER JOIN `hostAutoLogOut` ON `hostAutoLogOut`.`haloHostID`=`hosts`.`hostID` LEFT OUTER JOIN `inventory` ON `inventory`.`iHostID`=`hosts`.`hostID` WHERE `hostMAC`.`hmPrimary` = '1' ORDER BY LOWER(`hosts`.`hostName`) ASC Count: 4 Time=0.01s (0s) Lock=0.00s (0s) Rows=517.0 (2068), fogmaster[fogmaster]@localhost SELECT `hostName` FROM `hosts` LEFT OUTER JOIN `hostMAC` ON `hostMAC`.`hmHostID`=`hosts`.`hostID` LEFT OUTER JOIN `images` ON `images`.`imageID`=`hosts`.`hostImage` LEFT OUTER JOIN `os` ON `os`.`osID`=`images`.`imageOSID` LEFT OUTER JOIN `imagePartitionTypes` ON `imagePartitionTypes`.`imagePartitionTypeID`=`images`.`imagePartitionTypeID` LEFT OUTER JOIN `imageTypes` ON `imageTypes`.`imageTypeID`=`images`.`imageTypeID` LEFT OUTER JOIN `hostScreenSettings` ON `hostScreenSettings`.`hssHostID`=`hosts`.`hostID` LEFT OUTER JOIN `hostAutoLogOut` ON `hostAutoLogOut`.`haloHostID`=`hosts`.`hostID` LEFT OUTER JOIN `inventory` ON `inventory`.`iHostID`=`hosts`.`hostID` WHERE `hosts`.`hostID` IN ('576','1','78','3','4','5','6','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','33','34','35','36','581','38','39','40','41','42','570','43','44','45','46','47','48','49','50','51','52','53','54','55','56','57','58','59','60','61','62','63','64','65','66','67','68','69','70','71','72','73','74','75','76','77','2','79','80','81','82','83','84','85','86','87','88','89','90','91','92','93','94','95','96','97','98','99','100','101','102','103','104','577','578','105','106','107','108','109','110','518','517','519','520','521','522','523','524','525','526','527','528','563','529','562','530','531','532','533','534','535','536','537','538','539','540','541','542','543','544','545','566','546','547','548','549','550','551','552','553','554','555','556','557','558','564','565','560','559','111','112','113','114','115','116','117','118','119','120','121','122','123','124','125','126','127','128','129','130','131','132','133','134','596','149','150','152','153','154','155','156','157','158','159','160','161','162','163','164','165','166','167','168','169','170','171','172','173','174','175','176','177','178','179','180','181','182','183','184','185','186','187','188','189','190','191','192','193','194','195','196','197','198','199','200','201','202','203','204','205','206','207','208','209','210','211','212','213','214','215','216','217','218','219','220','221','222','223','224','225','226','227','228','229','230','231','232','595','582','233','234','499','568','235','236','237','238','239','240','241','242','243','244','245','246','247','248','249','250','251','252','253','254','255','256','257','258','259','260','261','262','263','264','265','266','267','268','269','270','271','272','273','274','275','276','277','278','279','280','281','282','283','284','285','286','287','288','289','290','291','292','293','294','295','296','297','298','299','300','301','302','303','304','305','306','307','308','309','310','311','312','313','314','315','316','317','318','319','320','321','322','323','324','325','326','327','328','329','330','331','332','333','334','335','336','337','338','339','340','341','342','343','344','345','346','347','348','349','350','351','352','353','354','355','356','357','358','359','360','361','362','363','364','365','366','367','368','369','370','371','372','373','374','375','376','377','378','379','380','381','382','383','384','385','386','387','388','389','390','391','392','393','394','580','585','587','139','588','589','575','590','591','592','396','397','398','399','400','401','402','403','404','405','406','407','408','409','410','411','412','413','414','415','416','417','418','419','420','421','422','423','424','425','426','427','428','429','430','431','432','433','434','435','436','583','500','501','502','503','509','488','571','586','489','594','579','573','512','466','569','567','514','593','504','490','498','574','516','505','492','493','494','495','497') AND `hostMAC`.`hmPrimary` = '1' ORDER BY LOWER(`hosts`.`hostName`) ASC Count: 31963 Time=0.00s (142s) Lock=0.00s (1s) Rows=1.0 (31963), fogmaster[fogmaster]@localhost SELECT COUNT(`hosts`.`hostID`) AS `total` FROM `hosts` WHERE `hostPending` = '1' LIMIT 1 Count: 3718 Time=0.00s (11s) Lock=0.00s (0s) Rows=1494.0 (5554692), fogmaster[fogmaster]@localhost SELECT `hmMAC` FROM `hostMAC` ORDER BY `hostMAC`.`hmID` ASC Count: 31963 Time=0.00s (87s) Lock=0.00s (1s) Rows=1.0 (31963), fogmaster[fogmaster]@localhost SELECT COUNT(`hostMAC`.`hmID`) AS `total` FROM `hostMAC` WHERE `hmPending` = '1' LIMIT 1 Count: 19 Time=0.00s (0s) Lock=0.00s (0s) Rows=518.0 (9842), fogmaster[fogmaster]@localhost SELECT `hmMAC` FROM `hostMAC` WHERE `hostMAC`.`hmPrimary`='1' ORDER BY `hostMAC`.`hmID` ASC Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), fogmaster[fogmaster]@localhost SELECT `ipxeID` FROM `ipxeTable` WHERE `ipxeTable`.`ipxeFilename`='undionly.kkpxe' AND `ipxeTable`.`ipxeProduct`='OptiPlex 7440 AIO' AND `ipxeTable`.`ipxeManufacturer`='OptiPlex 7440 AIO' AND `ipxeTable`.`ipxeMAC`='14:b3:1f:02:8a:f9' ORDER BY `ipxeTable`.`ipxeID` ASC Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), fogmaster[fogmaster]@localhost SELECT `ipxeID` FROM `ipxeTable` WHERE `ipxeTable`.`ipxeFilename`='undionly.kkpxe' AND `ipxeTable`.`ipxeProduct`='OptiPlex 7440 AIO' AND `ipxeTable`.`ipxeManufacturer`='OptiPlex 7440 AIO' AND `ipxeTable`.`ipxeMAC`='14:b3:1f:02:7e:ac' ORDER BY `ipxeTable`.`ipxeID` ASC Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), fogmaster[fogmaster]@localhost SELECT `ipxeID` FROM `ipxeTable` WHERE `ipxeTable`.`ipxeFilename`='undionly.kkpxe' AND `ipxeTable`.`ipxeProduct`='HP Compaq 8100 Elite CMT PC' AND `ipxeTable`.`ipxeManufacturer`='HP Compaq 8100 Elite CMT PC' AND `ipxeTable`.`ipxeMAC`='78:ac:c0:af:be:2f' ORDER BY `ipxeTable`.`ipxeID` ASC Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), fogmaster[fogmaster]@localhost SELECT `ipxeID` FROM `ipxeTable` WHERE `ipxeTable`.`ipxeFilename`='undionly.kkpxe' AND `ipxeTable`.`ipxeProduct`='OptiPlex 7440 AIO' AND `ipxeTable`.`ipxeManufacturer`='OptiPlex 7440 AIO' AND `ipxeTable`.`ipxeMAC`='14:b3:1f:02:4e:f6' ORDER BY `ipxeTable`.`ipxeID` ASC Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), fogmaster[fogmaster]@localhost SELECT `ipxeID` FROM `ipxeTable` WHERE `ipxeTable`.`ipxeFilename`='undionly.kkpxe' AND `ipxeTable`.`ipxeProduct`='OptiPlex 7440 AIO' AND `ipxeTable`.`ipxeManufacturer`='OptiPlex 7440 AIO' AND `ipxeTable`.`ipxeMAC`='14:b3:1f:02:47:fc' ORDER BY `ipxeTable`.`ipxeID` ASC Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=51.0 (255), fogmaster[fogmaster]@localhost SELECT * FROM `images` LEFT OUTER JOIN `os` ON `os`.`osID`=`images`.`imageOSID` LEFT OUTER JOIN `imagePartitionTypes` ON `imagePartitionTypes`.`imagePartitionTypeID`=`images`.`imagePartitionTypeID` LEFT OUTER JOIN `imageTypes` ON `imageTypes`.`imageTypeID`=`images`.`imageTypeID` WHERE `images`.`imageID` IN ('6','9','10','12','13','14','15','16','17','20','22','25','26','27','28','29','30','31','32','36','37','38','39','40','41','42','43','46','47','48','49','52','53','54','56','57','58','60','61','62','63','64','65','66','67','68','69','70','71','72','73') AND 1 ORDER BY LOWER(`images`.`imageName`) ASC Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=51.0 (255), fogmaster[fogmaster]@localhost SELECT `igaImageID` FROM `imageGroupAssoc` LEFT OUTER JOIN `images` ON `images`.`imageID`=`imageGroupAssoc`.`igaImageID` LEFT OUTER JOIN `os` ON `os`.`osID`=`images`.`imageOSID` LEFT OUTER JOIN `imagePartitionTypes` ON `imagePartitionTypes`.`imagePartitionTypeID`=`images`.`imagePartitionTypeID` LEFT OUTER JOIN `imageTypes` ON `imageTypes`.`imageTypeID`=`images`.`imageTypeID` LEFT OUTER JOIN `nfsGroups` ON `nfsGroups`.`ngID`=`imageGroupAssoc`.`igaStorageGroupID` WHERE `imageGroupAssoc`.`igaStorageGroupID`='1' AND `imageGroupAssoc`.`igaImageID` IN ('65','43','38','17','27','30','37','46','31','25','28','41','47','48','49','53','58','66','67','73','36','69','60','54','6','9','68','14','32','63','62','64','15','22','57','71','12','16','72','13','26','20','70','29','42','39','40','56','10','52','61') ORDER BY `imageGroupAssoc`.`igaID` ASC Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=51.0 (255), fogmaster[fogmaster]@localhost SELECT `imageID` FROM `images` LEFT OUTER JOIN `os` ON `os`.`osID`=`images`.`imageOSID` LEFT OUTER JOIN `imagePartitionTypes` ON `imagePartitionTypes`.`imagePartitionTypeID`=`images`.`imagePartitionTypeID` LEFT OUTER JOIN `imageTypes` ON `imageTypes`.`imageTypeID`=`images`.`imageTypeID` WHERE `images`.`imageEnabled`='1' AND `images`.`imageReplicate`='1' AND 1 ORDER BY LOWER(`images`.`imageName`) ASC Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=55.0 (275), fogmaster[fogmaster]@localhost SELECT `imageID` FROM `images` LEFT OUTER JOIN `os` ON `os`.`osID`=`images`.`imageOSID` LEFT OUTER JOIN `imagePartitionTypes` ON `imagePartitionTypes`.`imagePartitionTypeID`=`images`.`imagePartitionTypeID` LEFT OUTER JOIN `imageTypes` ON `imageTypes`.`imageTypeID`=`images`.`imageTypeID` WHERE 1 ORDER BY LOWER(`images`.`imageName`) ASC Count: 42 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (42), fogmaster[fogmaster]@localhost SELECT * FROM `powerManagement` WHERE `powerManagement`.`pmAction`='wol' AND `powerManagement`.`pmOndemand` IN ('0','') ORDER BY `powerManagement`.`pmID` ASC Count: 42 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (42), fogmaster[fogmaster]@localhost SELECT COUNT(`powerManagement`.`pmID`) AS `total` FROM `powerManagement` WHERE `pmAction` = 'wol' AND `pmOndemand` = '0' LIMIT 1 Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), fogmaster[fogmaster]@localhost SELECT `igaImageID` FROM `imageGroupAssoc` LEFT OUTER JOIN `images` ON `images`.`imageID`=`imageGroupAssoc`.`igaImageID` LEFT OUTER JOIN `os` ON `os`.`osID`=`images`.`imageOSID` LEFT OUTER JOIN `imagePartitionTypes` ON `imagePartitionTypes`.`imagePartitionTypeID`=`images`.`imagePartitionTypeID` LEFT OUTER JOIN `imageTypes` ON `imageTypes`.`imageTypeID`=`images`.`imageTypeID` LEFT OUTER JOIN `nfsGroups` ON `nfsGroups`.`ngID`=`imageGroupAssoc`.`igaStorageGroupID` WHERE `imageGroupAssoc`.`igaImageID` NOT IN ('65','43','38','1','3','4','17','27','30','37','46','31','25','28','41','47','48','49','53','58','66','67','73','36','69','60','54','6','9','68','14','32','63','62','64','15','22','57','71','12','16','72','13','26','20','70','29','42','39','40','56','5','10','52','61') ORDER BY `imageGroupAssoc`.`igaID` ASC Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (5), fogmaster[fogmaster]@localhost SELECT COUNT(`imageGroupAssoc`.`igaID`) AS `total` FROM `imageGroupAssoc` WHERE `igaStorageGroupID` = '1' AND `igaImageID` IN ('65','43','38','17','27','30','37','46','31','25','28','41','47','48','49','53','58','66','67','73','36','69','60','54','6','9','68','14','32','63','62','64','15','22','57','71','12','16','72','13','26','20','70','29','42','39','40','56','10','52','61') LIMIT 1 Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=8.0 (8), fogmaster[fogmaster]@localhost SELECT * FROM `snapins` WHERE `snapins`.`sID` IN ('1','2','3','4','5','6','7','8','9','10') AND `snapins`.`sEnabled`='1' AND 1 ORDER BY LOWER(`snapins`.`sName`) ASC Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=13.0 (65), fogmaster[fogmaster]@localhost SELECT * FROM `pxeMenu` ORDER BY `pxeMenu`.`pxeID` ASC Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=8.0 (40), fogmaster[fogmaster]@localhost SELECT * FROM `pxeMenu` WHERE `pxeMenu`.`pxeRegOnly` IN ('1','2','5') ORDER BY `pxeMenu`.`pxeID` ASC Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (1), fogmaster[fogmaster]@localhost SELECT COUNT(`snapins`.`sID`) AS `total` FROM `snapins` WHERE `sID` IN ('1','2','3','4','5','6','7','8','9','10') AND `sEnabled` = '1' AND 1 LIMIT 1 Count: 23678 Time=0.00s (1s) Lock=0.00s (0s) Rows=13.0 (307814), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','7','1','3','5','8','9','13','10','6','11','2','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 3452 Time=0.00s (0s) Lock=0.00s (0s) Rows=12.0 (41424), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','7','1','3','5','8','9','13','10','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 9295 Time=0.00s (0s) Lock=0.00s (0s) Rows=10.0 (92950), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','8','9','13','6','11','2','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 361 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (2527), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','13','6','11','9','5','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 2959 Time=0.00s (0s) Lock=0.00s (0s) Rows=13.0 (38467), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','7','1','3','5','8','9','13','10','6','11','2','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 1984 Time=0.00s (0s) Lock=0.00s (0s) Rows=9.0 (17856), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','8','9','13','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 711 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (4977), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','9','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 129 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (903), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','3','9','13','11','12','6') ORDER BY LOWER(`modules`.`name`) ASC Count: 3008 Time=0.00s (0s) Lock=0.00s (0s) Rows=8.0 (24064), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','5','9','13','6','11','12','8') ORDER BY LOWER(`modules`.`name`) ASC Count: 1856 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (12992), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','9','6','11','13','10','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 264 Time=0.00s (0s) Lock=0.00s (0s) Rows=10.0 (2640), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','7','1','3','9','13','10','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 128 Time=0.00s (0s) Lock=0.00s (0s) Rows=8.0 (1024), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','9','13','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 3665 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (25655), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','9','13','10','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 431 Time=0.00s (0s) Lock=0.00s (0s) Rows=12.0 (5172), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','7','1','3','5','8','9','13','10','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 9625 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (67375), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','9','13','6','11','12','5') ORDER BY LOWER(`modules`.`name`) ASC Count: 161 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (1127), fogmaster[fogmaster]@localhost SELECT `id` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','9','13','6','11') ORDER BY LOWER(`modules`.`name`) ASC Count: 1161 Time=0.00s (0s) Lock=0.00s (0s) Rows=10.0 (11610), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','8','9','13','6','11','2','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 248 Time=0.00s (0s) Lock=0.00s (0s) Rows=9.0 (2232), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','8','9','13','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 33 Time=0.00s (0s) Lock=0.00s (0s) Rows=10.0 (330), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','7','1','3','9','13','10','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 376 Time=0.00s (0s) Lock=0.00s (0s) Rows=8.0 (3008), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','5','8','9','13','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 16 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (112), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','3','9','13','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 101 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (707), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','9','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 690 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (4830), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','9','13','10','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 1248 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (8736), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','5','9','13','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 23 Time=0.00s (0s) Lock=0.00s (0s) Rows=7.0 (161), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','9','13','6','11') ORDER BY LOWER(`modules`.`name`) ASC Count: 16 Time=0.00s (0s) Lock=0.00s (0s) Rows=8.0 (128), fogmaster[fogmaster]@localhost SELECT `short_name` FROM `modules` WHERE `modules`.`id` IN ('4','3','5','9','13','6','11','12') ORDER BY LOWER(`modules`.`name`) ASC Count: 5 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (5), fogmaster[fogmaster]@localhost SELECT `pxeID` FROM `pxeMenu` WHERE `pxeMenu`.`pxeDefault`='1' ORDER BY LOWER(`pxeMenu`.`pxeName`) ASC Count: 42 Time=0.00s (0s) Lock=0.00s (0s) Rows=0.0 (0), fogmaster[fogmaster]@localhost SELECT * FROM `scheduledTasks` WHERE `scheduledTasks`.`stActive`='1' ORDER BY LOWER(`scheduledTasks`.`stName`) ASC Count: 42 Time=0.00s (0s) Lock=0.00s (0s) Rows=1.0 (42), fogmaster[fogmaster]@localhost SELECT COUNT(`scheduledTasks`.`stID`) AS `total` FROM `scheduledTasks` WHERE `stActive` = '1' LIMIT 1 -
@lebrun78 Thank you this information is very helpful. It will help us develop several indexes for the database that will speed up the responsiveness of the database. Just for my knowledge the time span was 30 minutes of data collection?
OK to work on your performance issue. We are going to use this thread as the background of the fix: https://forums.fogproject.org/topic/14254/high-cpu-usage
The first thing I need you to check is to see if your installation of mysql/mariadb has several default values that will cause us troubles during the upgrade. The upgrade itself is pretty quick.
In the mysql console run this command and post the results here:
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode sessionThis query will respond with something that looks like this:
mysql> SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session; +-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | global | session | +-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | +-------------------------------------------------------------------------------------------------------------------------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec)Please post the text of the query because we may need to copy some text back into a new command.
Please help us build the FOG community with everyone involved. It's not just about coding - way more we need people to test things, update documentation and most importantly work on uniting the community of people enjoying and working on FOG!
-
@george1421 based on the previous query this is what we will need to change. But don’t do that until we check the server default parameters and make a backup first.
ALTER TABLE clientUpdates ENGINE=InnoDB; ALTER TABLE dirCleaner ENGINE=InnoDB; ALTER TABLE globalSettings ENGINE=InnoDB; ALTER TABLE greenFog ENGINE=InnoDB; ALTER TABLE groupMembers ENGINE=InnoDB; ALTER TABLE groups ENGINE=InnoDB; ALTER TABLE history ENGINE=InnoDB; ALTER TABLE hookEvents ENGINE=InnoDB; ALTER TABLE hostAutoLogOut ENGINE=InnoDB; ALTER TABLE hostMAC ENGINE=InnoDB; ALTER TABLE hostScreenSettings ENGINE=InnoDB; ALTER TABLE hosts ENGINE=InnoDB; ALTER TABLE imageGroupAssoc ENGINE=InnoDB; ALTER TABLE imagePartitionTypes ENGINE=InnoDB; ALTER TABLE imageTypes ENGINE=InnoDB; ALTER TABLE images ENGINE=InnoDB; ALTER TABLE imagingLog ENGINE=InnoDB; ALTER TABLE inventory ENGINE=InnoDB; ALTER TABLE ipxeTable ENGINE=InnoDB; ALTER TABLE keySequence ENGINE=InnoDB; ALTER TABLE moduleStatusByHost ENGINE=InnoDB; ALTER TABLE modules ENGINE=InnoDB; ALTER TABLE multicastSessions ENGINE=InnoDB; ALTER TABLE multicastSessionsAssoc ENGINE=InnoDB; ALTER TABLE nfsFailures ENGINE=InnoDB; ALTER TABLE nfsGroupMembers ENGINE=InnoDB; ALTER TABLE nfsGroups ENGINE=InnoDB; ALTER TABLE notifyEvents ENGINE=InnoDB; ALTER TABLE os ENGINE=InnoDB; ALTER TABLE oui ENGINE=InnoDB; ALTER TABLE plugins ENGINE=InnoDB; ALTER TABLE powerManagement ENGINE=InnoDB; ALTER TABLE printerAssoc ENGINE=InnoDB; ALTER TABLE printers ENGINE=InnoDB; ALTER TABLE pxeMenu ENGINE=InnoDB; ALTER TABLE scheduledTasks ENGINE=InnoDB; ALTER TABLE schemaVersion ENGINE=InnoDB; ALTER TABLE snapinAssoc ENGINE=InnoDB; ALTER TABLE snapinGroupAssoc ENGINE=InnoDB; ALTER TABLE snapinJobs ENGINE=InnoDB; ALTER TABLE snapinTasks ENGINE=InnoDB; ALTER TABLE snapins ENGINE=InnoDB; ALTER TABLE supportedOS ENGINE=InnoDB; ALTER TABLE taskLog ENGINE=InnoDB; ALTER TABLE taskStates ENGINE=InnoDB; ALTER TABLE taskTypes ENGINE=InnoDB; ALTER TABLE tasks ENGINE=InnoDB; ALTER TABLE userCleanup ENGINE=InnoDB; ALTER TABLE userTracking ENGINE=InnoDB; ALTER TABLE users ENGINE=InnoDB; ALTER TABLE virus ENGINE=InnoDB; -
@george1421 said in FOG Multicast Not starting anymore:
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session
SELECT @@GLOBAL.sql_mode global, @@SESSION.sql_mode session -> ; +-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ | global | session | +-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION | +-----------------------------------------------------------------------------------------------------------------------+-----------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) -
@lebrun78 said in FOG Multicast Not starting anymore:
OK these two settings are going to cause us trouble.
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES, >>NO_ZERO_IN_DATE,NO_ZERO_DATE<< ,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONWe need to find the mysql settings file. Lets use this search string to see if we can find the right file.
grep -r -e "[mysqld]" /etcWe are looking for a file that has the mysqld section heading.
Just under that section heading we need to insert the corrected parameters.
[mysqld] sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONOnce that config file has been updated restart the mysql database. If the database comes back online then we can proceed to the next steps. We are almost done.
-
Now :
[root@fogus ~]# cat /etc/my.cnf.d/mysql-server.cnf # # This group are read by MySQL server. # Use it for options that only the server (but not clients) should see # # For advice on how to change settings please see # http://dev.mysql.com/doc/refman/en/server-configuration-defaults.html # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mysqld according to the # instructions in http://fedoraproject.org/wiki/Systemd [mysqld] sql-mode=ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock log-error=/var/log/mysql/mysqld.log pid-file=/run/mysqld/mysqld.pid[root@fogus ~]# systemctl status mysqld.service ● mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Active: active (running) since Tue 2022-01-11 15:56:00 CET; 1min 15s ago Process: 5245 ExecStopPost=/usr/libexec/mysql-wait-stop (code=exited, status=0/SUCCESS) Process: 5387 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS) Process: 5308 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS) Process: 5283 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS) Main PID: 5345 (mysqld) Status: "Server is operational" Tasks: 46 (limit: 26213) Memory: 471.4M CGroup: /system.slice/mysqld.service └─5345 /usr/libexec/mysqld --basedir=/usr janv. 11 15:55:59 fogus.istic.univ-rennes1.fr systemd[1]: Starting MySQL 8.0 database server... janv. 11 15:56:00 fogus.istic.univ-rennes1.fr systemd[1]: Started MySQL 8.0 database server. -
@lebrun78 Great it looks like the db restarted after the service parameters update.
Access the mysql database and run these commands. They should complete without an error.
ALTER TABLE clientUpdates ENGINE=InnoDB; ALTER TABLE dirCleaner ENGINE=InnoDB; ALTER TABLE globalSettings ENGINE=InnoDB; ALTER TABLE greenFog ENGINE=InnoDB; ALTER TABLE groupMembers ENGINE=InnoDB; ALTER TABLE groups ENGINE=InnoDB; ALTER TABLE history ENGINE=InnoDB; ALTER TABLE hookEvents ENGINE=InnoDB; ALTER TABLE hostAutoLogOut ENGINE=InnoDB; ALTER TABLE hostMAC ENGINE=InnoDB; ALTER TABLE hostScreenSettings ENGINE=InnoDB; ALTER TABLE hosts ENGINE=InnoDB; ALTER TABLE imageGroupAssoc ENGINE=InnoDB; ALTER TABLE imagePartitionTypes ENGINE=InnoDB; ALTER TABLE imageTypes ENGINE=InnoDB; ALTER TABLE images ENGINE=InnoDB; ALTER TABLE imagingLog ENGINE=InnoDB; ALTER TABLE inventory ENGINE=InnoDB; ALTER TABLE ipxeTable ENGINE=InnoDB; ALTER TABLE keySequence ENGINE=InnoDB; ALTER TABLE moduleStatusByHost ENGINE=InnoDB; ALTER TABLE modules ENGINE=InnoDB; ALTER TABLE multicastSessions ENGINE=InnoDB; ALTER TABLE multicastSessionsAssoc ENGINE=InnoDB; ALTER TABLE nfsFailures ENGINE=InnoDB; ALTER TABLE nfsGroupMembers ENGINE=InnoDB; ALTER TABLE nfsGroups ENGINE=InnoDB; ALTER TABLE notifyEvents ENGINE=InnoDB; ALTER TABLE os ENGINE=InnoDB; ALTER TABLE oui ENGINE=InnoDB; ALTER TABLE plugins ENGINE=InnoDB; ALTER TABLE powerManagement ENGINE=InnoDB; ALTER TABLE printerAssoc ENGINE=InnoDB; ALTER TABLE printers ENGINE=InnoDB; ALTER TABLE pxeMenu ENGINE=InnoDB; ALTER TABLE scheduledTasks ENGINE=InnoDB; ALTER TABLE schemaVersion ENGINE=InnoDB; ALTER TABLE snapinAssoc ENGINE=InnoDB; ALTER TABLE snapinGroupAssoc ENGINE=InnoDB; ALTER TABLE snapinJobs ENGINE=InnoDB; ALTER TABLE snapinTasks ENGINE=InnoDB; ALTER TABLE snapins ENGINE=InnoDB; ALTER TABLE supportedOS ENGINE=InnoDB; ALTER TABLE taskLog ENGINE=InnoDB; ALTER TABLE taskStates ENGINE=InnoDB; ALTER TABLE taskTypes ENGINE=InnoDB; ALTER TABLE tasks ENGINE=InnoDB; ALTER TABLE userCleanup ENGINE=InnoDB; ALTER TABLE userTracking ENGINE=InnoDB; ALTER TABLE users ENGINE=InnoDB; ALTER TABLE virus ENGINE=InnoDB;Finally run this query to make sure everything has been switched over to INNODB.
SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘fog’All of the table should now use the INNODB db engine. You will not need to do anything else it should start working right away better. I would reboot the FOG server though just to ensure everything works OK after the reboot. Once that happens you should see lower CPU usage as well as mysql and php-fpm services levels should return to normal.
-
@george1421
I got one error with the groups table:mysql> ALTER TABLE clientUpdates ENGINE=InnoDB; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE dirCleaner ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE globalSettings ENGINE=InnoDB; Query OK, 180 rows affected (0.03 sec) Records: 180 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE greenFog ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE groupMembers ENGINE=InnoDB; Query OK, 760 rows affected (0.04 sec) Records: 760 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE groups ENGINE=InnoDB; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups ENGINE=InnoDB' at line 1 mysql> ALTER TABLE history ENGINE=InnoDB; Query OK, 1969 rows affected (0.13 sec) Records: 1969 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE hookEvents ENGINE=InnoDB; Query OK, 237 rows affected (0.02 sec) Records: 237 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE hostAutoLogOut ENGINE=InnoDB; Query OK, 238 rows affected (0.02 sec) Records: 238 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE hostMAC ENGINE=InnoDB; Query OK, 1495 rows affected (0.09 sec) Records: 1495 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE hostScreenSettings ENGINE=InnoDB; Query OK, 13 rows affected (0.02 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE hosts ENGINE=InnoDB; Query OK, 518 rows affected (0.06 sec) Records: 518 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE imageGroupAssoc ENGINE=InnoDB; Query OK, 55 rows affected (0.01 sec) Records: 55 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE imagePartitionTypes ENGINE=InnoDB; Query OK, 12 rows affected (0.02 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE imageTypes ENGINE=InnoDB; Query OK, 4 rows affected (0.01 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE images ENGINE=InnoDB; Query OK, 55 rows affected (0.02 sec) Records: 55 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE imagingLog ENGINE=InnoDB; Query OK, 2415 rows affected (0.04 sec) Records: 2415 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE inventory ENGINE=InnoDB; Query OK, 490 rows affected (0.05 sec) Records: 490 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE ipxeTable ENGINE=InnoDB; Query OK, 740 rows affected (0.02 sec) Records: 740 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE keySequence ENGINE=InnoDB; Query OK, 35 rows affected (0.01 sec) Records: 35 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE moduleStatusByHost ENGINE=InnoDB; Query OK, 5300 rows affected (0.12 sec) Records: 5300 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE modules ENGINE=InnoDB; Query OK, 13 rows affected (0.01 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE multicastSessions ENGINE=InnoDB; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE multicastSessionsAssoc ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE nfsFailures ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE nfsGroupMembers ENGINE=InnoDB; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE nfsGroups ENGINE=InnoDB; Query OK, 1 row affected (0.02 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE notifyEvents ENGINE=InnoDB; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE os ENGINE=InnoDB; Query OK, 12 rows affected (0.01 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE oui ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE plugins ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE powerManagement ENGINE=InnoDB; Query OK, 187 rows affected (0.02 sec) Records: 187 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE printerAssoc ENGINE=InnoDB; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE printers ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE pxeMenu ENGINE=InnoDB; Query OK, 13 rows affected (0.02 sec) Records: 13 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE scheduledTasks ENGINE=InnoDB; Query OK, 9 rows affected (0.02 sec) Records: 9 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE schemaVersion ENGINE=InnoDB; Query OK, 1 row affected (0.01 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE snapinAssoc ENGINE=InnoDB; Query OK, 354 rows affected (0.02 sec) Records: 354 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE snapinGroupAssoc ENGINE=InnoDB; Query OK, 10 rows affected (0.01 sec) Records: 10 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE snapinJobs ENGINE=InnoDB; Query OK, 1629 rows affected (0.03 sec) Records: 1629 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE snapinTasks ENGINE=InnoDB; Query OK, 3109 rows affected (0.08 sec) Records: 3109 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE snapins ENGINE=InnoDB; Query OK, 8 rows affected (0.01 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE supportedOS ENGINE=InnoDB; Query OK, 8 rows affected (0.02 sec) Records: 8 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE taskLog ENGINE=InnoDB; Query OK, 8899 rows affected (0.06 sec) Records: 8899 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE taskStates ENGINE=InnoDB; Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE taskTypes ENGINE=InnoDB; Query OK, 21 rows affected (0.01 sec) Records: 21 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE tasks ENGINE=InnoDB; Query OK, 2048 rows affected (0.11 sec) Records: 2048 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE userCleanup ENGINE=InnoDB; Query OK, 6 rows affected (0.01 sec) Records: 6 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE userTracking ENGINE=InnoDB; Query OK, 88983 rows affected (1.32 sec) Records: 88983 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE users ENGINE=InnoDB; Query OK, 4 rows affected (0.02 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE virus ENGINE=InnoDB; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0and this error
mysql> SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = fog; ERROR 1054 (42S22): Unknown column 'fog' in 'where clause' -
@lebrun78 Well I guess I get an D- on this test.
OK
on this oneALTER TABLE groups ENGINE=InnoDB;There might be an artifact in there like a tab character because of the way I created the file. Try running that again like this:
ALTER TABLE groups ENGINE=InnoDB;In the case of this one:
mysql> SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = fog; ERROR 1054 (42S22): Unknown column 'fog' in 'where clause'I think you are missing the single ticks around the fog value. It should look like this
SELECT TABLE_NAME,ENGINE FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'fog'All this query does is prints out all of the tables in the fog database to confirm they are now all innodb format. All of the other tables converted correctly except the groups table. You should see a drop in cpu usage for by mysql and php-fpm already.
-
[root@fogus ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1852964 Server version: 8.0.17 Source distribution Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> ALTER TABLE groups ENGINE=InnoDB; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups ENGINE=InnoDB' at line 1 mysql> use fog; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> ALTER TABLE groups ENGINE=InnoDB; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups ENGINE=InnoDB' at line 1 mysql> -
mysql> use fog; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> ALTER TABLE groups ENGINE=InnoDB; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'groups ENGINE=InnoDB' at line 1 mysql> ALTER TABLE `groups` ENGINE=InnoDB; Query OK, 69 rows affected (0.02 sec) Records: 69 Duplicates: 0 Warnings: 0 -
and now:
mysql> SELECT TABLE_NAME,ENGINE -> FROM information_schema.TABLES -> WHERE TABLE_SCHEMA = 'fog' -> ; +------------------------+--------+ | TABLE_NAME | ENGINE | +------------------------+--------+ | clientUpdates | InnoDB | | dirCleaner | InnoDB | | globalSettings | InnoDB | | greenFog | InnoDB | | groupMembers | InnoDB | | groups | InnoDB | | history | InnoDB | | hookEvents | InnoDB | | hostAutoLogOut | InnoDB | | hostMAC | InnoDB | | hostScreenSettings | InnoDB | | hosts | InnoDB | | imageGroupAssoc | InnoDB | | imagePartitionTypes | InnoDB | | imageTypes | InnoDB | | images | InnoDB | | imagingLog | InnoDB | | inventory | InnoDB | | ipxeTable | InnoDB | | keySequence | InnoDB | | moduleStatusByHost | InnoDB | | modules | InnoDB | | multicastSessions | InnoDB | | multicastSessionsAssoc | InnoDB | | nfsFailures | InnoDB | | nfsGroupMembers | InnoDB | | nfsGroups | InnoDB | | notifyEvents | InnoDB | | os | InnoDB | | oui | InnoDB | | plugins | InnoDB | | powerManagement | InnoDB | | printerAssoc | InnoDB | | printers | InnoDB | | pxeMenu | InnoDB | | scheduledTasks | InnoDB | | schemaVersion | InnoDB | | snapinAssoc | InnoDB | | snapinGroupAssoc | InnoDB | | snapinJobs | InnoDB | | snapinTasks | InnoDB | | snapins | InnoDB | | supportedOS | InnoDB | | taskLog | InnoDB | | taskStates | InnoDB | | taskTypes | InnoDB | | tasks | InnoDB | | userCleanup | InnoDB | | userTracking | InnoDB | | users | InnoDB | | virus | InnoDB | +------------------------+--------+ 51 rows in set (0.00 sec)Fog Version: Fog 1.5.10
Server OS: AlmaLinux release 8.8 -
@lebrun78 Excellent. I forgot that group was a keyword AND a table name. Good find to escape the table name.
And for the table engines good, that is exactly what we need.
-
@lebrun78 You might ask, why did I have you change the engine and how will that help with performance?
The simple answer is MyISAM uses table level locking when something is updated. INNODB engine uses row level locking on an update. Under table level locking when someone goes to update a table, that processing updating the a row causes all other processes to stop updating until the write is complete. Under row level locking, only the database row that is being changed is locked all other tables and rows are free to be updated by other processes. The table level locking of MyISAM will create a back log of processes waiting their turn to update the database.
You will see this backlog by higher than normal cpu percent on the mysql process and many (many) php-fpm processes waiting their turn to update the database.
-
Il have allways too many time wait on port 9000
[root@fogus ~]# netstat -nat |grep 9000 |wc -l 855and php-fpm is allways high
top - 17:08:40 up 6 days, 3:39, 2 users, load average: 0,69, 0,65, 0,79 Tasks: 339 total, 2 running, 337 sleeping, 0 stopped, 0 zombie %Cpu(s): 6,5 us, 2,6 sy, 0,0 ni, 90,8 id, 0,0 wa, 0,1 hi, 0,1 si, 0,0 st MiB Mem : 15328,7 total, 571,3 free, 1274,2 used, 13483,2 buff/cache MiB Swap: 7812,0 total, 7138,7 free, 673,2 used. 13484,4 avail Mem PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 8776 mysql 20 0 3256016 464360 35664 S 17,9 3,0 2:31.14 mysqld 11704 apache 20 0 257736 21844 13380 S 14,6 0,1 0:04.37 php-fpm 11375 apache 20 0 257812 21868 13372 S 13,3 0,1 0:05.11 php-fpm 11608 apache 20 0 257816 21868 13376 S 13,0 0,1 0:05.32 php-fpm 11737 apache 20 0 257724 21840 13380 R 12,6 0,1 0:03.22 php-fpm 11376 apache 20 0 257736 21840 13372 S 12,0 0,1 0:05.33 php-fpm 11374 apache 20 0 257716 21276 12860 S 11,6 0,1 0:05.09 php-fpm 11377 apache 20 0 254816 21496 13052 S 11,6 0,1 0:05.17 php-fpm 11772 apache 20 0 254816 21436 12992 S 11,6 0,1 0:02.24 php-fpm 11373 apache 20 0 257724 21900 13372 S 11,3 0,1 0:05.16 php-fpm 11396 apache 20 0 2599116 17408 8900 S 5,0 0,1 0:01.30 httpd 11620 apache 20 0 2599116 17840 8948 S 5,0 0,1 0:02.10 httpd 11395 apache 20 0 2599116 19356 8920 S 1,7 0,1 0:00.63 httpd 11394 apache 20 0 2795788 23072 8948 S 0,7 0,1 0:00.33 httpd 1 root 20 0 244492 12360 7816 S 0,3 0,1 19:50.18 systemd 1173 root 20 0 200068 98172 23916 S 0,3 0,6 7:26.78 systemd-journal 3269 root 20 0 289304 16636 9884 S 0,3 0,1 7:34.19 php 11823 root 20 0 64176 5080 4152 R 0,3 0,0 0:00.03 topFog Version: Fog 1.5.10
Server OS: AlmaLinux release 8.8 -
@lebrun78 Well that didn’t seem to fix the issue. It should have, but you still have a long waiting list. How many client computers do you have in your network where the fog client is installed?
-
@lebrun78 looking at
topscreen it looks like you are using quite a bit of RAM and even dipping into swap space. Did you increase the number of php-fpm workers or increase the per worker memory limit at one time. These changes would have been made to the php-fpm config file. -
@lebrun78 I can imagine the php-fpm processes being under some heavy load some time after things were kind of offline because many clients try to check in again in a short time. But if things are fine this should “cool down” after an hour (even earlier but surely after that much time).
So if it’s still high load in php-fpm processes I suggest you also look at the Apache logs (see my signatur) and maybe even fog-client logs on machines (C:\fog.log). Post log information here so we can help interpret it.
