Разница между временем выполнения локального запроса MySQL и производственным сервером

Я создаю сайт, который использует много запросов к базе данных, поэтому я боялся, что это может произойти.

Итак, проблема в том, что у меня есть несколько запросов, которые используют много JOIN В некоторых таблицах есть пара тысяч записей, а в других - около 200-300 тысяч записей. У меня был опыт замедления работы сайта, и мне пришлось оптимизировать некоторые запросы.

Дело в том, что в этом случае на моем локальном компьютере конкретный раздел, использующий эти запросы, загружается примерно с 2,5 секундами с включенным дросселированием сети в качестве обычного Wi-Fi. При хорошем Wi-Fi загрузка занимает около 1,3 секунды.

На моем производственном сервере, который является виртуальной машиной в DigitalOcean, это занимает около 5 минут! загрузить точно такой же контент с точно таким же запросом. Сейчас я не эксперт, но мой компьютер не в 120 раз быстрее, чем рабочий сервер на DigitalOcean.

У моего ноутбука следующие характеристики: Intel Core i7-6700 HQ, 16 ГБ оперативной памяти DDR4, а сервер работает на жестком диске со скоростью 5400 об / мин, его нет даже на моем SSD-накопителе, а только там, где используется движок MySQL.

Рабочий сервер изначально был базовым экземпляром DO с 1 ГБ ОЗУ и 1 VCPU. Я подумал, что это, вероятно, нуждается в некотором повышении, поэтому я временно обновил его, чтобы иметь 2VCPU и 2 ГБ оперативной памяти, но это не имело никакого значения. Другие разделы загружаются невероятно быстро, за исключением того, который использует много соединений.

Теперь я не эксперт, но мой компьютер не в 120 раз быстрее сервера, и он также выполняет множество других процессов. У меня есть GeForce 1070M, но я не думаю, что это влияет на производительность MySQL.

Я попытался разделить запрос как можно меньше JOIN Это возможно, а затем выполнить несколько простых запросов, чтобы добавить дополнительную информацию в мой массив данных, но тогда у меня возникла другая проблема. С такой логикой даже на моем компьютере он завис на 4-5 секунд, а затем неожиданно загрузил содержимое.

Ниже приведены скриншоты вкладки сети Chrome, показывающие разницу во времени. Как видите, все остальное загружается невероятно быстро, за исключением начальной загрузки. Я почти уверен, что это проблема MySQL, но разница ошеломляет. Я подумываю о попытке загрузить сайт на 16 ГБ экземпляра памяти с 6VCPU на DigitalOcean, чтобы увидеть, связано ли это с памятью / процессором, но я не уверен, что мой клиент хотел бы платить 80 долларов США в месяц или больше за такую ​​виртуальную машину,

Одним из возможных решений, о котором я думал, было разделение Localidades а также Asentamientos Таблицы (у них около 200-300 тыс. записей) в 32 меньшие таблицы, по одной для каждого штата Мексики, и для каждого штата есть специальная функция для ссылки на другую таблицу, но я не думаю, что это было бы ни масштабируемой, ни хорошей практикой,

Я также добавил расчетную стоимость запроса ниже.

Мой локальный компьютер имеет:

  • Windows 10 1803
  • Apache / 2.4.25 (Win64)
  • MySQL 5.7.23

Мой производственный сервер имеет:

  • Ubuntu 18.04.1 LTS
  • Apache / 2.4.29 (Ubuntu)
  • 5.7.24-0ubuntu0.18.04.1

Любая идея, что я могу сделать, чтобы решить это?

Сгенерированный запрос выглядит следующим образом:

SELECT 
    `Propiedades`.*,
    `Propiedades`.`directorio` AS `main_dir`,
    DATEDIFF(Propiedades.fecha_finalizacion,
            '2018-12-02 11:11:49') AS quedan,
    `OperacionesPorPropiedad`.*,
    `Operaciones`.`nombre_operacion`,
    `Operaciones`.`nombre_operacion_slug`,
    `TiposDePropiedades`.*,
    `FotografiasPorPropiedad`.*,
    `Empresas`.`nombre_empresa`,
    `Estados`.*,
    `Municipios`.*,
    `Localidades`.*,
    `Asentamientos`.*,
    `Clientes`.`nombres`,
    `Clientes`.`apellidos`,
    `Clientes`.`email`,
    `TiposDeClientes`.*
FROM
    `Propiedades`
        JOIN
    `OperacionesPorPropiedad` ON `OperacionesPorPropiedad`.`id_propiedad` = `Propiedades`.`id_propiedad`
        JOIN
    `Operaciones` ON (`Operaciones`.`id_operacion` = `OperacionesPorPropiedad`.`id_operacion`
        AND `OperacionesPorPropiedad`.`id_propiedad` = Propiedades.id_propiedad)
        JOIN
    `TiposDePropiedades` ON `TiposDePropiedades`.`id_tipo` = `Propiedades`.`id_tipo`
        JOIN
    `FotografiasPorPropiedad` ON (`FotografiasPorPropiedad`.`id_propiedad` = `Propiedades`.`id_propiedad`
        AND `FotografiasPorPropiedad`.`orden` = 1)
        JOIN
    `Empresas` ON `Empresas`.`id_empresa` = `Propiedades`.`id_empresa`
        JOIN
    `Estados` ON `Estados`.`id_estado` = `Propiedades`.`id_estado`
        LEFT OUTER JOIN
    `Municipios` ON `Municipios`.`id_municipio` = `Propiedades`.`id_municipio`
        LEFT OUTER JOIN
    `Localidades` ON `Localidades`.`id_localidad` = `Propiedades`.`id_localidad`
        LEFT OUTER JOIN
    `Asentamientos` ON `Asentamientos`.`id_asentamiento` = `Propiedades`.`id_asentamiento`
        JOIN
    `Clientes` ON `Clientes`.`id_cliente` = `Empresas`.`id_cliente`
        JOIN
    `TiposDeClientes` ON (`Clientes`.`id_tipo_cliente` = `TiposDeClientes`.`id_tipo_cliente`
        AND `Clientes`.`id_cliente` = `Empresas`.`id_cliente`)
WHERE
    `Propiedades`.`id_estatus_propiedad` = 1
GROUP BY `Propiedades`.`id_propiedad`
ORDER BY FIELD(`Propiedades`.`destacada`, '1', '0') , FIELD(`Clientes`.`id_tipo_cliente`, 1, 2, 3) , RAND()
LIMIT 24

Источник

2 ответа

Решение

Простите, что нашли время, ребята... Это была ошибка новичка, в которой я не читал сообщения об ошибках при импорте базы данных.

Когда я сгенерировал mysqldump, некоторые имена таблиц были сгенерированы некорректно с использованием строчных букв, что приводило к ошибке при импорте.

Поскольку индексы всего были после ошибочных инструкций, они никогда не выполнялись, поэтому я в основном выполнял неиндексированные полные просмотры таблиц, и поэтому загрузка результатов длилась как всегда.

Я исправил свой SQL-файл и снова создал базу данных, и она работала как чудо. Простите за потраченное время, ребята.

PS: Я на самом деле увеличил сервер до 16 ГБ ОЗУ и 6VCPU, и это не имело никакого значения.

Источник

Это даст вам разумные 24 строки? Или вы зависите от фильтрации из других таблиц?

    WHERE  P.`id_estatus_propiedad` = 1
    ORDER BY  FIELD(P.`destacada`, '1', '0') ,
              FIELD(C.`id_tipo_cliente`, 1, 2, 3) ,
              RAND()
    LIMIT  24

Если это так, то подумайте о следующем:

Ваш текущий запрос перетаскивает полные строки из множества таблиц, затем перетасовывает их, и в итоге получается только 24.

Лучший способ - выяснить, какие 24, а затем перейти к деталям:

SELECT lots-of-stuff
    FROM ( SELECT id_propiedad
               FROM Propiedades AS P1
               JOIN ...   -- as few as needed to get to Clientes
               JOIN  `Clientes` AS C1  ON C1.`id_cliente` = Em.`id_cliente`
               WHERE  P1.`id_estatus_propiedad` = 1
               ORDER BY  FIELD(P1.`destacada`, '1', '0') ,
                         FIELD(C1.`id_tipo_cliente`, 1, 2, 3) ,
                         RAND()
               LIMIT  24
         ) AS x
    JOIN  `Propiedades` AS P  ON P.id_propiedad = x.id_propiedad
    JOIN  `OperacionesPorPropiedad` AS OP  ON OP.`id_propiedad` = P.`id_propiedad`
    JOIN  `Operaciones` AS O  ON (O.`id_operacion` = OP.`id_operacion` ...
    ...
    -- no WHERE, GROUP BY, or LIMIT, but repeat the ORDER BY:
    ORDER BY  FIELD(P.`destacada`, '1', '0') ,
              FIELD(C.`id_tipo_cliente`, 1, 2, 3) , RAND()

Вернуться к вопросу о разнице в производительности...

  • Ваша личная машина имеет большую ценность для innodb_buffer_pool_size чем крошечная виртуальная машина в облаке?
  • Вы извлекаете все столбцы из множества строк примерно десятка таблиц.
  • Вы (в настоящее время) сначала собираете массу потенциальных выходных строк, а затем используете GROUP BY на устраненные споры и, наконец, LIMITing до всего лишь 24. Размер временной таблицы, вероятно, огромен. (синдром "раздувать-раздувать" JOIN плюс GROUP BY,
  • У вас возможно есть TEXT столбцы в некоторых из тех * списки столбцов; это усугубляет проблему временной таблицы.

Они объединяются, чтобы вызвать высокую / медленную производительность. Мое предложение, если оно осуществимо, устраняет большинство из них.

Также FotografiasPorPropiedad потребности INDEX(id_propiedad, orden) (в любом порядке).

Источник
Другие вопросы по тегам