I'm running a mid sized website (couple hundred articles, between 30-50k visits per day) on a Digital Ocean 16GB / 8 Cores web server and I have two questions:
Most of the time the website runs smoothly, however, several times per day MySQL starts using all of the CPU and I see a spike on disk IO.
Running show process list on MySQL shows that most queries are "Copying to tmp table" and look like this
I'm new using Craft and I want to know is this queries are normal and if there's a way to optimize them:
EXPLAIN SELECT `elements`.`id`, `elements`.`type`, `elements`.`enabled`, `elements`.`archived`, `elements`.`dateCreated`, `elements`.`dateUpdated`, `elements_i18n`.`slug`, `elements_i18n`.`uri`, `elements_i18n`.`enabled` AS `localeEnabled`, `content`.`id` AS `contentId`, `content`.`title`, `content`.`field_airingDate`, `content`.`field_airingFrom`, `content`.`field_airingTo`, `content`.`field_awsPath`, `content`.`field_descripcion`, `content`.`field_descripcionSocialMedia`, `content`.`field_subtitulo`, `content`.`field_texto`, `content`.`field_titulo`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`, `structureelements`.`root`, `structureelements`.`lft`, `structureelements`.`rgt`, `structureelements`.`level`
FROM `elements` `elements`
JOIN `elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `content` `content` ON content.elementId = elements.id
JOIN `entries` `entries` ON entries.id = elements.id
JOIN `sections` `sections` ON sections.id = entries.sectionId
LEFT JOIN `structures` `structures` ON structures.id = sections.structureId
LEFT JOIN `structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
WHERE (((((elements_i18n.locale = 'es') AND (content.locale = 'es')) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate <= '2017-04-11 16:56:24') AND ((entries.expiryDate is null) OR (entries.expiryDate > '2017-04-11 16:56:24')))) AND (elements_i18n.enabled = 1)) AND (sections.handle='articulos')
GROUP BY `elements`.`id`
Another example:
SELECT count(`derivedElementsTable`.`elementsId`)
FROM (SELECT `elements`.`id` AS `elementsId`, `entries`.`sectionId`, `entries`.`typeId`, `entries`.`authorId`, `entries`.`postDate`, `entries`.`expiryDate`
FROM `elements` `elements`
JOIN `elements_i18n` `elements_i18n` ON elements_i18n.elementId = elements.id
JOIN `content` `content` ON content.elementId = elements.id
LEFT JOIN `relations` `sources1` ON (sources1.targetId = elements.id)
LEFT JOIN `relations` `targets1` ON (targets1.sourceId = elements.id)
JOIN `entries` `entries` ON entries.id = elements.id
JOIN `sections` `sections` ON sections.id = entries.sectionId
LEFT JOIN `structures` `structures` ON structures.id = sections.structureId
LEFT JOIN `structureelements` `structureelements` ON (structureelements.structureId = structures.id) AND (structureelements.elementId = entries.id)
WHERE (((((elements_i18n.locale = 'es') AND (content.locale = 'es')) AND (elements.archived = 0)) AND ((elements.enabled = 1) AND (elements_i18n.enabled = 1) AND (entries.postDate <= '2017-04-11 16:56:33') AND ((entries.expiryDate is null) OR (entries.expiryDate > '2017-04-11 16:56:33')))) AND (elements_i18n.enabled = 1)) AND ((sources1.sourceId='175') OR (targets1.targetId='175'))
I understand that the i18n tables are where the locales data is stored. Is there a way to disable a locale without losing any data?