Actions
Bug #7940
openGeneral MySQL MariaDB speed ups
Start date:
08/14/2019
Due date:
% Done:
0%
Estimated time:
Affected Version:
Appion/Leginon 3.4
Show in known bugs:
No
Workaround:
Description
General MySQL MariaDB speed ups, from the slow query log
Bad Queries:
SELECT `AcquisitionImageData16916320`.* FROM `ny_leginondb`.`AcquisitionImageData` AS `AcquisitionImageData16916320` JOIN (`ny_leginondb`.`CameraEMData` AS `CameraEMData16488768`, `ny_leginondb`.`InstrumentData` AS `InstrumentData16472928`) ON (`AcquisitionImageData16916320`.`REF|CameraEMData|camera` = `CameraEMData16488768`.`DEF_ID` AND `CameraEMData16488768`.`REF|InstrumentData|ccdcamera` = `InstrumentData16472928`.`DEF_ID`) WHERE `AcquisitionImageData16916320`.`DEF_timestamp` >= DATE_ADD(now(), INTERVAL '-30 0:0:0' DAY_SECOND) AND `CameraEMData16488768`.`frames name`="20190212_17030045" AND `InstrumentData16472928`.`hostname`="krios1-k2" ORDER BY `AcquisitionImageData16916320`.DEF_id DESC; SELECT `ApStackParticleData23462240`.* FROM `nyap_31`.`ApStackParticleData` AS `ApStackParticleData23462240` WHERE `ApStackParticleData23462240`.`REF|ApStackData|stack`="1164" ORDER BY `ApStackParticleData23462240`.DEF_id DESC; SELECT `CameraEMData16888160`.* FROM `ny_leginondb`.`AcquisitionImageData` AS `AcquisitionImageData16488768` JOIN (`ny_leginondb`.`CameraEMData` AS `CameraEMData16888160`, `ny_leginondb`.`InstrumentData` AS `InstrumentData16002720`) ON (`AcquisitionImageData16488768`.`REF|CameraEMData|camera` = `CameraEMData16888160`.`DEF_ID` AND `CameraEMData16888160`.`REF|InstrumentData|ccdcamera` = `InstrumentData16002720`.`DEF_ID`) WHERE `AcquisitionImageData16488768`.`DEF_timestamp` >= DATE_ADD(now(), INTERVAL '-30 0:0:0' DAY_SECOND) AND `CameraEMData16888160`.`frames name`="20190212_17022944" AND `InstrumentData16002720`.`hostname`="krios1-k2" ORDER BY `AcquisitionImageData16488768`.DEF_id DESC; SELECT DISTINCT im.`DEF_id` AS imgid FROM ApStackParticleData AS stpart LEFT JOIN ApParticleData AS part ON stpart.`REF|ApParticleData|particle` = part.`DEF_id` LEFT JOIN ny_leginondb.`AcquisitionImageData` AS im ON part.`REF|leginondata|AcquisitionImageData|image` = im.`DEF_id` WHERE `REF|ApStackData|stack` = '1169'; SELECT `InstrumentData16876624`.* FROM `ny_leginondb`.`AcquisitionImageData` AS `AcquisitionImageData15786176` JOIN (`ny_leginondb`.`CameraEMData` AS `CameraEMData16458768`, `ny_leginondb`.`InstrumentData` AS `InstrumentData16876624`) ON (`AcquisitionImageData15786176`.`REF|CameraEMData|camera` = `CameraEMData16458768`.`DEF_ID` AND `CameraEMData16458768`.`REF|InstrumentData|ccdcamera` = `InstrumentData16876624`.`DEF_ID`) WHERE `AcquisitionImageData15786176`.`DEF_timestamp` >= DATE_ADD(now(), INTERVAL '-30 0:0:0' DAY_SECOND) AND `CameraEMData16458768`.`frames name`="ReferenceBuffers" AND `InstrumentData16876624`.`hostname`="tf20-de20" ORDER BY `AcquisitionImageData15786176`.DEF_id DESC; SELECT stackpart.mean AS mean, stackpart.stdev AS stdev FROM `ApStackParticleData` AS stackpart WHERE `REF|ApStackData|stack` = '1164';
Fixed:
SELECT UNIX_TIMESTAMP(img.`DEF_timestamp`) AS `start` FROM `AcquisitionImageData` AS img WHERE `REF|SessionData|session` = '9593' ORDER BY img.`DEF_timestamp` ASC LIMIT 1; SELECT UNIX_TIMESTAMP(img.`DEF_timestamp`) AS `end` FROM `AcquisitionImageData` AS img WHERE `REF|SessionData|session` = '8352' ORDER BY img.`DEF_timestamp` DESC LIMIT 1;
Updated by Neil Voss about 5 years ago
Bad Queries:
SELECT `ApStackParticleData23462240`.* FROM `nyap_31`.`ApStackParticleData` AS `ApStackParticleData23462240` WHERE `ApStackParticleData23462240`.`REF|ApStackData|stack`="1164" ORDER BY `ApStackParticleData23462240`.DEF_id DESC; SELECT `CameraEMData16888160`.* FROM `ny_leginondb`.`AcquisitionImageData` AS `AcquisitionImageData16488768` JOIN (`ny_leginondb`.`CameraEMData` AS `CameraEMData16888160`, `ny_leginondb`.`InstrumentData` AS `InstrumentData16002720`) ON (`AcquisitionImageData16488768`.`REF|CameraEMData|camera` = `CameraEMData16888160`.`DEF_ID` AND `CameraEMData16888160`.`REF|InstrumentData|ccdcamera` = `InstrumentData16002720`.`DEF_ID`) WHERE `AcquisitionImageData16488768`.`DEF_timestamp` >= DATE_ADD(now(), INTERVAL '-30 0:0:0' DAY_SECOND) AND `CameraEMData16888160`.`frames name`="20190212_17022944" AND `InstrumentData16002720`.`hostname`="krios1-k2" ORDER BY `AcquisitionImageData16488768`.DEF_id DESC; SELECT DISTINCT im.`DEF_id` AS imgid FROM ApStackParticleData AS stpart LEFT JOIN ApParticleData AS part ON stpart.`REF|ApParticleData|particle` = part.`DEF_id` LEFT JOIN ny_leginondb.`AcquisitionImageData` AS im ON part.`REF|leginondata|AcquisitionImageData|image` = im.`DEF_id` WHERE `REF|ApStackData|stack` = '1169'; SELECT `InstrumentData16876624`.* FROM `ny_leginondb`.`AcquisitionImageData` AS `AcquisitionImageData15786176` JOIN (`ny_leginondb`.`CameraEMData` AS `CameraEMData16458768`, `ny_leginondb`.`InstrumentData` AS `InstrumentData16876624`) ON (`AcquisitionImageData15786176`.`REF|CameraEMData|camera` = `CameraEMData16458768`.`DEF_ID` AND `CameraEMData16458768`.`REF|InstrumentData|ccdcamera` = `InstrumentData16876624`.`DEF_ID`) WHERE `AcquisitionImageData15786176`.`DEF_timestamp` >= DATE_ADD(now(), INTERVAL '-30 0:0:0' DAY_SECOND) AND `CameraEMData16458768`.`frames name`="ReferenceBuffers" AND `InstrumentData16876624`.`hostname`="tf20-de20" ORDER BY `AcquisitionImageData15786176`.DEF_id DESC; SELECT stackpart.mean AS mean, stackpart.stdev AS stdev FROM `ApStackParticleData` AS stackpart WHERE `REF|ApStackData|stack` = '1164';
Fixed:
SELECT UNIX_TIMESTAMP(img.`DEF_timestamp`) AS `start` FROM `AcquisitionImageData` AS img WHERE `REF|SessionData|session` = '9593' ORDER BY img.`DEF_timestamp` ASC LIMIT 1; SELECT UNIX_TIMESTAMP(img.`DEF_timestamp`) AS `end` FROM `AcquisitionImageData` AS img WHERE `REF|SessionData|session` = '8352' ORDER BY img.`DEF_timestamp` DESC LIMIT 1;
Actions