Poor performance on midsized site / Queries without index?

Question

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?

Answer 1

I can tell you that both of those are the semi-dynamic element queries that Craft uses to grab elements from the database.

The 2nd one is likely the result of someone calling a .total() or .count() on ElementCriteriaModel.

I see you ran EXPLAIN, but don't see the results from it. You can use that as a starting point to see what index MySQL chose to execute that query and if it's not ideal. Craft can't add an index to every content column (for obvious reasons), so if something in your templates is generating a SQL query frequently that causes MySQL to chose an index that isn't ideal and adding an index to content column would solve that for you, then that's something you can certainly do.

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?

No. It either exists or it doesn't.