Архив метки: MySQL

Советы для разработчика по работе с MySQL

Автор: | | ,
Комментарии к записи Советы для разработчика по работе с MySQL отключены

1. Оптимизируйте запросы для кэша запросов

У большинства MySQL серверов включено кэширование запросов. Один из наилучших способов улучшения производительности — просто предоставить кэширование самой базе данных. Когда какой-либо запрос повторяется много раз, его результат берется из кэша, что гораздо быстрее прямого обращения к базе данных.
Основная проблема в том, что многие просто используют запросы, которые не могут быть закэшированны:

      // запрос не будет кэширован
      $r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
      // а так будет!
      $today = DATE("Y-m-d");
      $r = mysql_query("SELECT username FROM user WHERE signup_date >= '$today'");

Причина в том, что в первом запросе используется функция CURDATE(). Это относиться ко всем функциям, подобным NOW(), RAND() и другим, результат которых недетерминирован. Если результат функции может измениться, то MySQL не кэширует такой запрос. В данном примере это можно предотвратить вычислением даты до выполнения запроса.

2. Используйте EXPLAIN для ваших запросов SELECT

Используя EXPLAIN, вы можете посмотреть, как именно MySQL выполняет ваш запрос. Это может помочь вам избавиться от слабых мест производительности и других проблем в вашем запросе или в структуре таблиц.
Результат EXPLAIN покажет вам, какие используются индексы, как выбираются и сортируются таблицы и т.д.
Возьмите ваш SELECT запрос (он может быть сложным, с объединениями) и добавьте в начало ключевое слово EXPLAIN. Для этого вы можете использовать phpmyadmin. В результате вы получите очень интересную таблицу. Для примера, пусть я забыл добавить индекс в таблицу, которая участвует в объединении:

Explain для неоптимизированного запроса

После добавления индекса для поля group_id:
Explain после оптимизации запроса

Теперь вместо 7883 строк, выбираются только 9 и 16 строк из двух таблиц. Перемножение всех чисел в столбце rows даст число прямо пропорциональное производительности запроса.

3. LIMIT 1, когда нужна единственная строка

Иногда, обращаясь к таблице, вы точно знаете, что вам нужна только одна конкретная строка. Например, нужно получить одну уникальную строку или просто проверить существование записей, удовлетворяющих запросу WHERE.
В этом случае, добавление LIMIT 1 в ваш запрос будет оптимальнее. Таким образом, база данных остановит выборку записей, после нахождения первой же, вместо того, чтобы выбрать всю таблицу или индекс.

      // есть пользователи в Alabama?
      // можно так:
      $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama'");
      IF (mysql_num_rows($r) > 0) {
      // ...
      }
      // но так лучше:
      $r = mysql_query("SELECT * FROM user WHERE state = 'Alabama' LIMIT 1");
      IF (mysql_num_rows($r) > 0) {
      // ...
      }

4. Индексируйте поля, по которым ищите

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

Результат использования индекса

Как вы можете заметить, это правило также применимо для части строк, например — «last_name LIKE ‘a%’». При поиске с начала строки, MySQL использует индекс этого столбца.
Вы так же должны понимать, что это не сработает для регулярных выражений. Например, когда вы ищите слово (т.е. «WHERE post_content LIKE ‘%apple%’»), то от обычного индекса не будет никакого толку. Лучше будет использовать полнотекстовый поиск или создать вашу собственную систему индексации.

5. Индексируйте поля для объединения и используйте для них одинаковые типы столбцов

Если ваше приложение содержит много объединений таблиц, вам необходимо проиндексировать в обеих таблицах поля, используемые для объединения. Это повлияет на то, как MySQL делает внутреннюю оптимизацию объединений.
Так же эти столбцы должны быть одного типа. Например, если вы объединяете столбец DECIMAL со столбцом INT из другой таблицы, MySQL не сможет использовать хотя бы один из индексов. Даже кодировки символов должны быть одного типа для строковых столбцов.

      // выборки компаний в штате пользователя
      $r = mysql_query("SELECT company_name FROM users
      JOIN companies ON (users.state = companies.state)
      users.id = $user_id");
      // обе колонки state должны быть проиндексированны
      // они обе должны иметь один тип данных и кодировку символов
      // а иначе MySQL сделает полную выборку из этих таблиц

6. Не используйте ORDER BY RAND()

(Имеется в виду выборка единственной строки. Примечание переводчика)

Это одна из тех вещей, который выглядят очень хорошо на первый взгляд, но многие начинающие программисты попались на эту удочку. Вы даже не представляете, какое слабое место в производительности возникнет, если будете использовать это в запросах.
Если вам действительно нужен случайный порядок строк в запросе, то есть лучшие способы сделать это. Конечно, это приведет к дополнительному коду, но позволит избавиться от слабого места в производительности, которое будет сужаться экспоненциально при увеличении данных. Проблема в том, что MySQL будет выполнять RAND() (а это нагрузка на процессор) для каждой строки при сортировке, выдавая только одну строку.

Таким образом вы выберите случайный номер, который меньше количества строк и используете его для смещения в LIMIT.

7. Избегайте SELECT *

Чем больше данных считывается из таблицы, тем медленнее запрос. Это увеличивает время работы с хранилищем данных. Также, когда сервер базы данных установлен отдельно от web-сервера, будет большая задержка при передаче данных по сети.
Прописывать, какие именно столбцы из запроса вам нужны — хорошая привычка.

      // не очень хорошо:
      $r = mysql_query(«SELECT * FROM USER WHERE user_id = 1»);
      $d = mysql_fetch_assoc($r);
      echo «Welcome {$d['username']}»;
 
      // лучше:
      $r = mysql_query(«SELECT username FROM USER WHERE user_id = 1»);
      $d = mysql_fetch_assoc($r);
      echo «Welcome {$d['username']}»;
 
      // разница более значительна при большем наборе данных.

8. Старайтесь всегда создать поле ID

В каждой таблице нужно поле id, которое будет PRIMARY KEY, AUTO_INCREMENT, а так же иметь тип INT. Так же неплохо, чтобы оно было UNSIGNED, т.к. вряд ли у идентификатора будут отрицательные значения.
Даже если в вашей таблице пользователей есть уникальное поле username, не делаете его основным ключом. Использование поля VARCHAR, как основного ключа, очень медлительно. Да и структура вашего кода, относящаяся к пользователям, будет гораздо лучше, если у каждого пользователя будет свой внутренний идентификатор.
Есть так же и внутренние операции MySQL, использующие первичный ключ. И это становиться очень важно для более сложных конфигураций базы данных (кластеры, распараллеливание и т.д.)
Исключение из этого правила составляют «таблицы ассоциаций», используемые для связи «многие-ко-многим» между 2 таблицами. Например, таблица «posts_tags», содержит 2 поля: post_id, tag_id, который используется для объединения между двумя таблицами «Posts» и «Tags». Эта таблица будет иметь первичный ключ составленный из 2 полей.

9. Используйте ENUM вместо VARCHAR

ENUM — очень быстрый и компактный тип поля. Значения в нем храниться так же, как TINYINT, но отображаются как в строковом поле. Это делает его незаменимым в некоторых случаях.
Если у вас есть поле, в котором будет вполне определенный набор значений, используйте ENUM вместо VARCHAR. Например, если есть поле «status», его значения могут быть «active», «inactive», «pending», «expired» и т.д.
Можно даже получить от MySQL «совет» о том, как перестроить таблицу. Если у вас есть поле VARCHAR, MySQL может предложить заменить его на ENUM. Для этого используется PROCEDURE ANALYSE(), описанная ниже.

10. Используйте подсказки от PROCEDURE ANALYSE()

PROCEDURE ANALYSE() анализирует структуру вашей таблицы и данные в ней, и выдает возможные советы по оптимизации. Это возможно только при наличии реальных данных в таблице, т.к. анализ делается в основном на их основе.
Например, если вы создали первичный ключ типа INT, а записей не очень много, MySQL может предложить заменить его на MEDIUMINT. Или, если используется VARCHAR в котором есть несколько уникальных значений, будет предложен ENUM.
В phpmyadmin в структуре таблице есть ссылка «Анализ структуры таблицы», результат которой может быть, например, следующим:

Подсказки в phpmyadmin от работы функции PROCEDURE ANALYSE()

11. Используйте NOT NULL, если это возможно

Если есть особые причины использовать NULL — используйте его. Но перед этим спросите себя — есть ли разница между пустой строкой и NULL (для INT — 0 или NULL). Если таких причин нет, используйте NOT NULL.
NULL занимает больше места и, к тому же, усложняет сравнения с таким полем. Избегайте его, если это возможно. Тем не менее, бывают веские причины использовать NULL, это не всегда плохо.
Из документации MySQL:
«Столбцы NULL занимают больше места в записи, из-за необходимости отмечать, что это NULL значение. Для таблиц MyISAM, каждое поле с NULL занимает 1 дополнительный бит, который округляется до ближайшего байта».

11. Используйте NOT NULL, если это возможно

Если есть особые причины использовать NULL — используйте его. Но перед этим спросите себя — есть ли разница между пустой строкой и NULL (для INT — 0 или NULL). Если таких причин нет, используйте NOT NULL.
NULL занимает больше места и, к тому же, усложняет сравнения с таким полем. Избегайте его, если это возможно. Тем не менее, бывают веские причины использовать NULL, это не всегда плохо.
Из документации MySQL:
«Столбцы NULL занимают больше места в записи, из-за необходимости отмечать, что это NULL значение. Для таблиц MyISAM, каждое поле с NULL занимает 1 дополнительный бит, который округляется до ближайшего байта».

12. Prepared Statements

Есть множество преимуществ в использовании prepared statements, как для безопасности, так и для улучшения производительности. Prepared statements фильтруют значения данных, добавляемых в запрос, что защищает запросы от SQL инъекций. Конечно, вы можете фильтровать переменные вручную, но тут может сказаться человеческая забывчивость и невнимательность. Конечно, это не столь важно при использовании какого-либо фреймворка или ORM.
Поскольку статья посвящена оптимизации, отмечу также выгоды для нее. Они проявляются, когда запрос выполняется много раз в приложении. Вы можете использовать для prepared statement разные значения, но MySQL будет разбирать запрос только один раз.
Кроме того, последние версии MySQL компилируют prepared statements в бинарную форму, что позволяет повысить эффективность.
Раньше многие программисты избегали prepared statements по одной единственной причине — они не кэшировались MySQL, но с версии 5.1 это не так.
Посмотрите mysqli extension для использования prepared statements или воспользуйтесь абстракцией базы данных, например, PDO.

      // создаем a prepared statement
      IF ($stmt = $mysqli->PREPARE("SELECT username FROM user WHERE state=?")) {
      // привязываем значения
      $stmt->bind_param("s", $state);
      // выполняем
      $stmt->EXECUTE();
      // привязываем результат
      $stmt->bind_result($username);
      // получаем данные
      $stmt->fetch();
      printf("%s is from %s\n", $username, $state);
      $stmt->close();
      }

13. Небуферизованные запросы

Обычно, делая запрос, скрипт останавливается и ждет результата его выполнения. Вы можете изменить это, используя небуферизованные запросы.
Хорошее описание есть в документации функции mysql_unbuffered_query():

«mysql_unbuffered_query() отправляет SQL-запрос в MySQL, не извлекая и не автоматически буферизуя результирующие ряды, как это делает mysql_query(). С одной стороны, это сохраняет значительное количество памяти для SQL-запросов, дающих большие результирующие наборы. С другой стороны, вы можете начать работу с результирующим набором срезу после получения первого ряда: вам не нужно ожидать выполнения полного SQL-запроса»

Однако есть определенные ограничения. Вам придется считывать все записи или вызывать mysql_free_result() прежде, чем вы сможете выполнить другой запрос. Так же вы не можете использовать mysql_num_rows() или mysql_data_seek() для результата функции.

14. Храните IP в UNSIGNED INT

Многие программисты хранят IP адреса в поле типа VARCHAR(15), не зная что можно хранить его в целочисленном виде. INT занимает 4 байта и имеет фиксированный размер поля.
Убедитесь, что используете UNSIGNED INT, т.к. IP можно записать как 32 битное беззнаковое число.
Используйте в запросе INET_ATON() для конвертирования IP адреса в число, и INET_NTOA() для обратного преобразования. Такие же, такие функции есть и в PHP — ip2long() и long2ip() (в php эти функции могут вернуть и отрицательные значения. замечание от хабраюзера The_Lion).

      $r = "UPDATE users SET ip = INET_ATON('{$_SERVER['REMOTE_ADDR']}') WHERE user_id = $user_id";

15. Таблицы фиксированного размера (статичные) — быстрее

Если каждая колонка в таблице имеет фиксированный размер, то такая таблица называется «статичной» или «фиксированного размера». Пример колонок не фиксированной длины: VARCHAR, TEXT, BLOB. Если включить в таблицу такое поле, она перестанет быть фиксированной и будет обрабатываться MySQL по-другому.
Использование таких таблицы увеличит эффективность, т.к. MySQL может просматривать записи в них быстрее. Когда надо выбрать нужную строку таблицы, MySQL может очень быстро вычислить ее позицию. Если размер записи не фиксирован, ее поиск происходит по индексу.
Так же эти таблицы проще кэшировать и восстанавливать после падения базы. Например, если перевести VARCHAR(20) в CHAR(20), запись будет занимать 20 байтов, вне зависимости от ее реального содержания.
Используя метод «вертикального разделения», вы можете вынести столбцы с переменной длиной строки в отдельную таблицу.

16. Вертикальное разделение

Вертикальное разделение — означает разделение таблицы по столбцам для увеличения производительности.
Пример 1. Если в таблице пользователей хранятся адреса, то не факт что они будут нужны вам очень часто. Вы можете разбить таблицу и хранить адреса в отдельной таблице. Таким образом, таблица пользователей сократиться в размере. Производительность возрастет.
Пример 2. У вас есть поле «last_login» в таблице. Оно обновляется при каждом входе пользователя на сайт. Но все изменения в таблице очищают ее кэш. Храня это поле в другой таблице, вы сведете изменения в таблице пользователей к минимуму.
Но если вы будете постоянно использовать объединение этих таблиц, это приведет к ухудшению производительности.

17. Разделяйте большие запросы DELETE и INSERT

Если вам необходимо сделать большой запрос на удаление или вставку данных, надо быть осторожным, чтобы не нарушить работу приложения. Выполнение большого запроса может заблокировать таблицу и привести к неправильной работе всего приложения.
Apache может выполнять несколько параллельных процессов одновременно. Поэтому он работает более эффективно, если скрипты выполняются как можно быстрее.
Если вы блокируете таблицы на долгий срок (например, на 30 секунд или дольше), то при большой посещаемости сайта, может возникнуть большая очередь процессов и запросов, что может привести к медленной работе сайта или даже к падению сервера.
Если у вас есть такие запросы, используйте LIMIT, чтобы выполнять их небольшими сериями.

      while (1) {
      mysql_query("DELETE FROM logs WHERE log_date <= '2009-10-01' LIMIT 10000");
      IF (mysql_affected_rows() == 0) {
      // удалили
      break;
      }
      // небольшая пауза
      usleep(50000);
      }

18. Маленькие столбцы быстрее

Для базы данных работа с жестким диском, возможно, является самым слабым местом. Маленькие и компактные записи обычно лучше с точки зрения производительности, т.к. уменьшают работу с диском.
В документации к MySQL есть список требований к хранилищам данных для всех типов данных.
Если ваша таблица будет хранить мало строк, то не имеет смысла делать основной ключ типом INT, возможно лучше будет сделать его MEDIUMINT, SMALLINT или даже TINYINT. Если вам не нужно хранить время, используйте DATE вместо DATETIME.
Однако будьте осторожны, что бы не вышло как с Slashdot.

19. Выбирайте правильный тип таблицы

Два основных типа таблиц — MyISAM и InnoDB, у каждого есть свои плюсы и минусы.
MyISAM хорошо считывает из таблиц большое количество данных, но он плох для записи. Даже если вы изменяете всего одну строку, блокируется вся таблица, и ни один процесс не может ничего из нее прочитать. MyISAM очень быстро выполняет запросы типа SELECT COUNT(*).
У InnoDB более сложный механизм хранения данных, и он может быть медленнее, чем MyISAM, для маленьких приложений. Но он поддерживает блокировку строк, что более эффективно при масштабировании. Так же поддерживаются некоторые дополнительные функции, такие операции как транзакции.
Подробнее:MyISAM Storage EngineInnoDB Storage Engine

20. Используте ORM

Используя ORM, можно получить определенную оптимизацию работы. Все, что можно сделать с помощью ORM, можно сделать и вручную. Но это требует дополнительной работы и более высокого уровня знаний.
ORM замечателен для «ленивой» загрузки данных. Это означает выборку данных по мере необходимости. Но необходимо быть осторожным, т.к это может привести к появлению множества маленьких запросов, что приведет к снижению производительности.
ORM также может объединять несколько запросов в пакеты, вместо отправки каждого отдельно.
Моя любимая ORM для PHP — Doctrine. Я уже писал статью об установке Doctrine в CodeIgniter.

21. Будьте осторожны с постоянными соединениями

Постоянные соединения предназначены для уменьшения расходов на установление связи с MySQL. Когда соединение создается, оно остается открытым после завершения работы скрипта. В следующий раз, этот скрипт воспользуется этим же соединением.mysql_pconnect() в PHPНо это звучит хорошо только в теории. Из моего личного опыта (и опыта других), использование этой возможности не оправдывается. У вас будут серьезные проблемы с ограничением по числу подключений, памятью и так далее.
Apache создает много параллельных потоков. Это основная причина, почему постоянные соединения не работаю так хорошо, как бы хотелось. Перед использованием mysql_pconnect() посоветуйтесь с вашим сисадмином.

Источник

Отличия типов datetime и timestamp в MySQL

Автор: | |
Комментарии к записи Отличия типов datetime и timestamp в MySQL отключены

На днях я столкнулся с тем, что многие разработчики не знают в чём отличие типов данных DATETIME и TIMESTAMP в MySQLе, а так же как хранить дату и время, если необходимо учитывать разные часовые пояса для разных пользователей веб-приложения. Поэтому хочу дать ниже разъяснения с пояснениями.

DATETIME
Хранит время в виде целого числа вида YYYYMMDDHHMMSS, используя для этого 8 байтов. Это время не зависит от временной зоны. Оно всегда отображается при выборке точно так же, как было сохранено, независимо от того какой часовой пояс установлен в MySQL. Даю пример:

mysql> CREATE TABLE `dt1` ( col datetime NOT NULL );
mysql> SET @@SESSION.time_zone='+00:00';
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-06-04 18:13:56 |
+---------------------+
 
mysql> INSERT INTO dt1 VALUES(now());
 
mysql> SET @@SESSION.time_zone='+01:00';
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-06-04 19:14:20 |
+---------------------+
 
mysql> INSERT INTO dt1 VALUES(now());
 
mysql> SET @@SESSION.time_zone='+00:00';
mysql> SELECT * FROM dt1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 18:14:10 |
| 2009-06-04 19:14:27 |
+---------------------+

TIMESTAMP
Хранит 4-байтное целое число, равное количеству секунд, прошедших с полуночи 1 января 1970 года по усреднённому времени Гринвича (т.е. нулевой часовой пояс, точка отсчёта часовых поясов). При получении из базы отображается с учётом часового пояса. Часовой пояс может быть задан в операционной системе, глобальных настройках MySQL или в конкретной сессии. Запомните, что сохраняется всегда количество секунд по UTC (универсальное координированное время, солнечное время на меридиане Гринвича), а не по локальному часовому поясу. Пример:

mysql> CREATE TABLE tm1 (col TIMESTAMP NOT NULL);
mysql> SET @@SESSION.time_zone = '+00:00';
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-06-04 18:24:54 |
+---------------------+
mysql> INSERT INTO tm1 VALUES(now());
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 18:25:08 |
+---------------------+
 
mysql> SET @@SESSION.time_zone = '+01:00';
mysql> SELECT now();
+---------------------+
| now() |
+---------------------+
| 2009-06-04 19:25:21 |
+---------------------+
mysql> INSERT INTO tm1 VALUES(now());
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 19:25:08 |
| 2009-06-04 19:25:26 |
+---------------------+
 
mysql> SET @@SESSION.time_zone = '+00:00';
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 18:25:08 |
| 2009-06-04 18:25:26 |
+---------------------+

Ещё одно отличие! TIMESTAMP по умолчанию NOT NULL, а его значение по умолчанию равно NOW().

mysql> INSERT INTO dt1 VALUES(NULL);
ERROR 1048 (23000): COLUMN 'col' cannot be NULL
mysql> INSERT INTO tm1 VALUES(NULL);
Query OK, 1 ROW affected (0.00 sec)
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 18:25:08 |
| 2009-06-04 18:25:26 |
| 2009-06-04 18:32:50 |
+---------------------+


Дополнение. Для тех, кого смущает использование функции NOW().

mysql> SET @@SESSION.time_zone = '+00:00';
mysql> INSERT INTO dt1 VALUES('2009-06-04 22:00:00');
mysql> SET @@SESSION.time_zone = '+01:00';
mysql> SELECT * FROM dt1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 22:00:00 |
+---------------------+
 
mysql> SET @@SESSION.time_zone = '+00:00';
mysql> INSERT INTO tm1 VALUES('2009-06-04 22:00:00');
mysql> SET @@SESSION.time_zone = '+01:00';
mysql> SELECT * FROM tm1;
+---------------------+
| col |
+---------------------+
| 2009-06-04 23:00:00 |
+---------------------+

Источник

Тюнинг тредов в Mysql (mysql threads tunning)

Автор: | |
Комментарии к записи Тюнинг тредов в Mysql (mysql threads tunning) отключены

mysqld это «один процесс — много тредов». В достаточно приближенном понимании тред (thread) это когда процесс копирует сам себя, и родительский процесс передает треду какую то задачу для решения.
Создание треда отличается от обычного fork() в основном тем, что fork порождает +1 процесс, тогда как деление на треды использует другую, более производительную технологию ветвления без клонирования данных в памяти.

При каждом подключении клиента, mysql создает тред, который это подключение обрабатывает.

Если у вас средне нагруженый mysqld, то убедитесь что mysqld работает на нормальной реализации тредов. К примеру, FreeBSD ниже 6 ветки имеет ненормальную, глючную реализацию тредов pthread. Основные глюки проявляются при работе на SMP (более 1 процессора). pthread «не умеет» нормально раскидывать треды по разным процессорам и в результате мы имеем ситуацию, когда Mysqld висит только на одном процессоре вместе с кучей своих детишек. Все остальные процессоры курят, даже если мускуль съел все процессорное время своего процессора.

Более детальная информация как правильно заставить работать mysql под FreeBSD может быть найдена тут: http://dev.mysql.com/doc/refman/5.1/en/freebsd.html

Итак, посмотрим что у вас творится с тредами. Если сервер слабо нагружен и имеет 1-2 подключения одновременно, то в этой оптимизации особого смысла нет. Гораздо ощутим результат, когда сервер серъезно нагружен и имеет много конкурентных запросов.

Посмотрим статус

shell> mysqladmin extended-status

Обратим внимание на значения:

Threads_cached
Threads_connected
Threads_created
Threads_running

Если Threads_cached равно нулю, у вас отключен кеш тредов. Т.е. при каждом подключении создается новый тред, а при отключении он уходит в /dev/null ;). Это плохо. Когда включен кеш тредов, то при отключении клиента тред уходит в кеш, а при новом подключении он не создается, а берется из кеша. Это гигантская экономия ресурсов при больших нагрузках. В некоторых ситуациях нагрузка уменьшалась в разы при использовании thread cache.

Threads_cached — количество тредов в кеше

Threads_connected — грубо говоря, количество открытых подключений

Threads_running — Сколько тредов сейчас «работают»

Threads_created — а вот и краеугольный камень. Сколько тредов было создано со старта сервера. Если в кеше нет свободного треда, то он создается. Если кеша нет вообще, то при каждом подключении создается тред. А при отключении убивается. Тоже самое происходит, если кеш тредов слишком маленький. Короче. Если это значение у вас измеряется тысячами, то у вас не все впорядке. Оно должно быть чуть больше Threads_cached в идеальном случае.

Что делать если Threads_created имеет слишком высокое значение?

В my.cnf есть такой параметр thread_cache_size (или просто thread_cache). Его необходимо увеличивать эксперементальным путем до той поры пока Threads_created не примет порядок цифр Threads_cached.

Для определения начального значения, вам надо узнать, сколько тредов бывает в среднем запущено в пиковых нагрузках. Для мониторинга сгодится утилитка mytop (она может не работать если mysql скомпилирован статически).

Либо периодически запускать такую команду:

shell> echo "SHOW GLOBAL STATUS" | mysql | grep Threads_connected | awk '{print $2;}'

Определили среднее значение, записали в my.cnf (thread_cache), сделали рестарт сервера. Пошли, покурили, выпили чайку. Смотри статус (команда указана выше). Если Threads_created продолжает неугомонно расти выше значения кеша, увеличиваем переменную thread_cache. И так до победы, но не советую растить эту величину выше нескольких сотен — рискуете получить Out Of Memory.

При увеличении этой величины также надо мониторить Resident Set Size процесса mysqld в долгосрочной динамике. Это колонка RSS в утилите «top». Означает сколько mysql сейчас занимает места в оперативе. Не советую растить его выше половины объема вашей оперативной памяти, т.к. получите пенальти, когда начнут ужиматься системные кеши.

Нормальное значение thread_cache, при котором Threads_created держится на приемлимом уровне, а mysql умеренно жрет память колеблется от 5 до 100 в зависимости от нагрузки и величины доступного ОЗУ.

Слишком высокое значение thread_cache может необоснованно кушать лишнюю память.

Если вас это не спасло, thread_cache превышает мыслимые нормы и Mysql продолжает жрать память и процессор и порождать треды, то необходима комплексная оптимизация: 1. системы, 2. ядра, 3. логики скриптов и запросов 4. других стартовых параметров mysqld

mysqladmin — примеры использования

Автор: | |
Комментарии к записи mysqladmin — примеры использования отключены

В 15 примерах использования команд mysqladmin ниже, используется пароль root Mysql tmppassword. Поменяйте его на ваш пароль

1. Как изменить пароль root на Mysql?

$ mysqladmin -u root -ptmppassword password 'newpassword'
$ mysql -u root -pnewpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

2. Как проверить работает ли MySQL сервер?

$ mysqladmin -u root -p ping
Enter password:
mysqld is alive

3. Как посмотреть какая версия MySQL используетсяI am running?

Эта команда так же отображает текущий статус сервера.

$ mysqladmin -u root -ptmppassword version
mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version          5.1.25-rc-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 107 days 6 hours 11 min 44 sec
Threads: 1  Questions: 231976  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

4. Как посмотреть статус MySQL сервера?

$ mysqladmin -u root -ptmppassword status
Uptime: 9267148
Threads: 1  Questions: 231977  Slow queries: 0  Opens: 17067
Flush tables: 1  Open tables: 64  Queries per second avg: 0.25

Команда status отображает следующую информациюcommand displays the following information:

  • Uptime: Время безотказной работы в секундах
  • Threads: Общее количество клиентов, подключенных к серверу.
  • Questions: Общее количество запросов к серверу с момента запуска.
  • Slow queries: Общее количество запросов, чьё время выполнения было больше чем значение переменной long_query_time.
  • Opens: Total number of tables opened by the server.
  • Flush tables: How many times the tables were flushed.
  • Open tables: Total number of open tables in the database.

5. Как просмотреть статус переменных MySQL и их текущее значение?

$ mysqladmin -u root -ptmppassword extended-status
+-----------------------------------+-----------+
| Variable_name                       | Value     |
+-----------------------------------+-----------+
| Aborted_clients                       | 579       |
| Aborted_connects                    | 8         |
| Binlog_cache_disk_use             | 0         |
| Binlog_cache_use                    | 0         |
| Bytes_received                       | 41387238  |
| Bytes_sent                            | 308401407 |
| Com_admin_commands          | 3524      |
| Com_assign_to_keycache        | 0         |
| Com_alter_db                        | 0         |
| Com_alter_db_upgrade           | 0         |

6. Как отобразить все системные переменные MySQL сервера и их значения?

$ mysqladmin  -u root -ptmppassword variables
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| auto_increment_increment        | 1                               |
| basedir                         | /                               |
| big_tables                      | OFF                             |
| binlog_format                   | MIXED                           |
| bulk_insert_buffer_size         | 8388608                         |
| character_set_client            | latin1                          |
| character_set_database          | latin1                          |
| character_set_filesystem        | binary                          |
skip.....
| time_format                     | %H:%i:%s                        |
| time_zone                       | SYSTEM                          |
| timed_mutexes                   | OFF                             |
| tmpdir                          | /tmp                            |
| tx_isolation                    | REPEATABLE-READ                 |
| unique_checks                   | ON                              |
| updatable_views_with_limit      | YES                             |
| version                         | 5.1.25-rc-community             |
| version_comment                 | MySQL Community Server (GPL)    |
| version_compile_machine         | i686                            |
| version_compile_os              | redhat-linux-gnu                |
| wait_timeout                    | 28800                           |
+---------------------------------+---------------------------------+

7. Как отобразить все запущенные процессы/запросы в базе mysql?

$ mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

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

$ mysqladmin -u root -ptmppassword -i 1 processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 36   |       |                  |
| 23 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 24 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

8. Как создать базу MySQL?

$ mysqladmin -u root -ptmppassword create testdb
$ mysql -u root -ptmppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 705
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| sugarcrm           |
| testdb             |
+--------------------+
4 rows in set (0.00 sec)

Замечание: Для отображения все таблиц в базе данных, общего количества колонок, строк, индексов и прочее…. используйте команду mysqlshow.

9. Как удалить существующую базу MySQL?

$ mysqladmin -u root -ptmppassword drop testdb
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the 'testdb' database [y/N] y
Database “testdb” dropped
$ mysql -u root -ptmppassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 707
Server version: 5.1.25-rc-community MySQL Community Server (GPL)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> show databases;
+——————–+
| Database           |
+——————–+
| information_schema |
| mysql              |
| sugarcrm           |
+——————–+
3 rows in set (0.00 sec)

10. Как перегрузить все привилегии и права на таблицы?

$ mysqladmin -u root -ptmppassword reload;

Команда Refresh сбросит все таблица и закроет/откроет лог-файлы.

$ mysqladmin -u root -ptmppassword refresh

11. Как образом выполнить безопасную остановку MySQL сервера?

$ mysqladmin -u root -ptmppassword shutdown
$ mysql -u root -ptmppassword
ERROR 2002 (HY000): Can't connect to local MySQL server
through socket '/var/lib/mysql/mysql.sock'

Вы можете использовать команду “/etc/rc.d/init.d/mysqld stop” для остановки сервера. Для запуска выполните “/etc/rc.d/init.d/mysql start”

12. Список всех mysqladmin flush комманд.

$ mysqladmin -u root -ptmppassword flush-hosts
$ mysqladmin -u root -ptmppassword flush-logs
$ mysqladmin -u root -ptmppassword flush-privileges
$ mysqladmin -u root -ptmppassword flush-status
$ mysqladmin -u root -ptmppassword flush-tables
$ mysqladmin -u root -ptmppassword flush-threads
  • flush-hosts: Сбросить всю информацию в кэше хостов.
  • flush-privileges: Перезагрузить права.
  • flush-status: Очистить статус переменных.
  • flush-threads: Flush the thread cache.

13. Как убить подвешенный клиентский процесс в MySQL ?

Сперва определите подвешенный процесс используя команду processlist.

$ mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 20 | root | localhost |    | Sleep   | 64   |       |                  |
| 24 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

Затем используйте команду kill и нужный process_id. Для завершения нескольких процессов разделите process id запятыми.

$ mysqladmin -u root -ptmppassword kill 20
$ mysqladmin -u root -ptmppassword processlist
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 26 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+

14. Как запустить или остановить репликацию MySQL на slave-сервер?

$ mysqladmin  -u root -ptmppassword stop-slave
Slave stopped
$ mysqladmin  -u root -ptmppassword start-slave
mysqladmin: Error starting slave: The server is not configured as slave;
fix in config file or with CHANGE MASTER TO

15. Как скомбинировать несколько команд mysqladmin вместе?

В примере ниже скомбинированы команды process-list, status и version для полного вывода статуса сервера.

$ mysqladmin  -u root -ptmppassword process status version
+----+------+-----------+----+---------+------+-------+------------------+
| Id | User | Host      | db | Command | Time | State | Info             |
+----+------+-----------+----+---------+------+-------+------------------+
| 43 | root | localhost |    | Query   | 0    |       | show processlist |
+----+------+-----------+----+---------+------+-------+------------------+
Uptime: 3135
Threads: 1  Questions: 80  Slow queries: 0  Opens: 15  Flush tables: 3
Open tables: 0  Queries per second avg: 0.25
mysqladmin  Ver 8.42 Distrib 5.1.25-rc, for redhat-linux-gnu on i686
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version          5.1.25-rc-community
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /var/lib/mysql/mysql.sock
Uptime:                 52 min 15 sec

Вы можете также использовать краткую форму записи:

$ mysqladmin  -u root -ptmppassword pro stat ver

Используйте опцию -h для подключения к удаленному MySQL серверу и выполнения команды.

$ mysqladmin  -h 192.168.1.112 -u root -ptmppassword pro stat ver

Хранение изображений в базе данных MySQL

Автор: | | , ,
Комментарии к записи Хранение изображений в базе данных MySQL отключены

Для хранения изображений в базе данных MySQL необходимо определить одно из полей таблицы как производное от типа BLOB. Сокращение BLOB означает большой двоичный объект. Тип хранения данных BLOB обладает несколькими вариантами:

  • TINYBLOB — может хранить до 255 байт
  • BLOB — может хранить до 64 килобайт информации
  • MEDIUMBLOB — до 16 мегабайт
  • LONGBLOB — до 4 гигабайт

Соответсвенно, для хранения изображений нам надо создать таблицу images с двумя полями:

  • id — уникальный ID изображения
  • content — поле для хранения изображения

Для сохранения файла изображения в базе данных необходимо прочитать файл в переменную и создать запрос на добавление данных в таблицу. Допустим у нас есть форма для загрузки файла изображения на сервер:

<form action="putimage.php" enctype="multipart/form-data" method="post"> Изображение: <input name="image" type="file" />
<input type="submit" value="Загрузить" />
</form>Обработчик формы - файл putimage.php:
// Проверяем пришел ли файл
if( !empty( $_FILES['image']['name'] ) ) {
// Проверяем, что при загрузке не произошло ошибок
if ( $_FILES['image']['error'] == 0 ) {
// Если файл загружен успешно, то проверяем - графический ли он
if( substr($_FILES['image']['type'], 0, 5)=='image' ) {
// Читаем содержимое файла
$image = file_get_contents( $_FILES['image']['tmp_name'] );
// Экранируем специальные символы в содержимом файла
$image = mysql_escape_string( $image );
// Формируем запрос на добавление файла в базу данных
$query="INSERT INTO `images` VALUES(NULL, '".$image."')";
// После чего остается только выполнить данный запрос к базе данных
mysql_query( $query );
}
}
}
?>

Извлечь сохраненный файл изображения можно следующим образом (файл image.php):

if ( isset( $_GET['id'] ) ) {
// Здесь $id номер изображения
$id = (int)$_GET['id'];
if ( $id > 0 ) {
$query = "SELECT `content` FROM `images` WHERE `id`=".$id;
// Выполняем запрос и получаем файл
$res = mysql_query($query);
if ( mysql_num_rows( $res ) == 1 ) {
$image = mysql_fetch_array($res);
// Отсылаем браузеру заголовок, сообщающий о том, что сейчас будет передаваться файл изображения
header("Content-type: image/*");
// И  передаем сам файл
echo $image['content'];
}
}
}
?>

Чтобы вывести изображение в HTML-документе, делаем так:

<img src="image.php?id=17" alt="" />

И последнее: графические файлы иногда имеют довольно большой размер, убедитесь, что настройки сервера позволяют работать с таким объемом данных. В файле php.ini это директивы post_max_size — определяет максимальный объем данных передаваемых методом POST, и upload_max_filesize — определяет максимальный размер загружаемого файла. Так же проверьте, позволяют ли настройки MySQL обрабатывать запросы с большим объемом данных (директива max_allowed_packet файла my.ini).