Это вопрос как по программированию, так и по серверу, поэтому я решил, что лучше спросить об этом здесь, а не о сбое сервера. Простите за длинный пост...
У меня есть веб-сайт, на котором есть словарь. Он работает на Centos 6.5 с php 5.4, mysql 5.5.25 и memcache 2.2.6. Словарь — единственный сценарий, использующий службу mysql.
В основном, это то, что он делает:
- Пользователь ищет слово
- PHP checks for word in memcache:
- Word found? return word+meanings. END.
- Не найдено? Перейдите к 3.
- PHP checks for word in mysql with
SELECT word_item.definition, word_item.extra, word.flg_success FROM word LEFT JOIN word_item ON word.id_word = word_item.id_word WHERE word.lang = :lang AND word.word = :word
- Word found?
- If
flg_success = 1
, store the word+meanings in memcache and return word+meanings. - Если
flg_success = 0
, показать ошибку. КОНЕЦ.
- If
- Не нашли? Перейдите к 4.
- Word found?
- PHP connects to an external API and receives a json with the word meanings.
- Word found? Store the word+meanings in memcache, insert word in the word table with
flg_success = 1
and meanings in item, and return word+meanings. - Не найдено? Вставьте слово в таблицу word с помощью
flg_success = 0
.
- Word found? Store the word+meanings in memcache, insert word in the word table with
Как видите, процесс теоретически оптимизирован для использования mysql только в том случае, если слово еще не находится в кэше памяти (в котором используется 30-дневный срок действия — это было успешно проверено).
таблица: слово (InnoDB) с 26919 записями
таблица: элемент (InnoDB) с 76194 записями
журнал mysqld
Таким образом, когда слишком много пользователей ищут слова, что-то происходит с таблицами mysql, и служба mysqld останавливается.
После проверки журнала
tail -40 /var/lig/mysqld.log
это результат:
141220 11:35:21 InnoDB: Completed initialization of buffer pool
141220 11:35:21 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
141220 11:35:21 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 28512664
141220 10:48:04 InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percents: 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99
InnoDB: Apply batch completed
141220 10:48:04 InnoDB: Waiting for the background threads to start
141220 10:48:05 InnoDB: 5.5.41 started; log sequence number 28512664
141220 10:48:05 [Note] Server hostname (bind-address): '0.0.0.0'; port: 3306
141220 10:48:05 [Note] - '0.0.0.0' resolves to '0.0.0.0';
141220 10:48:05 [Note] Server socket created on IP: '0.0.0.0'.
141220 10:48:05 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
141220 10:48:05 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'threads' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_thread_by_event_name' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'events_waits_summary_by_instance' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'events_waits_summary_global_by_event_name' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'file_summary_by_event_name' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'file_summary_by_instance' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'mutex_instances' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'rwlock_instances' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'cond_instances' has the wrong structure
141220 10:48:05 [ERROR] Native table 'performance_schema'.'file_instances' has the wrong structure
141220 10:48:05 [Note] Event Scheduler: Loaded 0 events
141220 10:48:05 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.5.41' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server (GPL) by Remi
141220 10:54:18 [ERROR] Incorrect definition of table mysql.proc: expected column 'comment' at position 15 to have type text, found type char(64).
PHP
Эта функция позволяет скрипту подключаться к базе данных. Он запускается только тогда, когда требуется соединение с mysql.
function cnn() {
static $pdo;
if(!isset($pdo)) {
$conf = [
PDO::ATTR_TIMEOUT => 30,
PDO::ATTR_PERSISTENT => false,
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'
];
try {
# DB Settings
$config['db']['host'] = 'localhost';
$config['db']['name'] = 'my_db';
$config['db']['user'] = 'root';
$config['db']['pass'] = 'somepassword';
$pdo = new PDO('mysql:host='.$config['db']['host'].';dbname='.$config['db']['name'], $config['db']['user'], $config['db']['pass'], $conf);
return $pdo;
} catch(PDOException $e) {
http_response_code(503);
echo $e->getCode().': '.$e->getMessage();
die();
}
} else {
return $pdo;
}
}
Запросы MySQL в PHP
Найдите слово:
$sql = 'SELECT word_item.definition, word_item.extra, word.flg_success FROM word LEFT JOIN word_item ON word.id_word = word_item.id_word WHERE word.lang = :lang AND word.word = :word';
$stmt = cnn()->prepare($sql);
$stmt->bindValue(':lang', $lang, PDO::PARAM_STR);
$stmt->bindValue(':word', urldecode($word), PDO::PARAM_STR);
$stmt->execute();
Вставить новое слово и значение
$sql = 'INSERT INTO word (lang, word, flg_success) VALUES (:lang, :word, :flg_success)';
$stmt = cnn()->prepare($sql);
$stmt->bindValue(':lang', $lang, PDO::PARAM_STR);
$stmt->bindValue(':word', urldecode($word), PDO::PARAM_STR);
$stmt->bindValue(':flg_success', true, PDO::PARAM_INT);
$stmt->execute();
if($last_insert_id = cnn()->lastInsertId()) {
# db: insert new definitions
foreach($definitions as $k=>$item) {
$fields[] = '(:id_word, :definition_'.$k.', :extra_'.$k.')';
}
$sql = 'INSERT INTO word_item (id_word, definition, extra) VALUES '.implode($fields, ',');
$stmt = cnn()->prepare($sql);
# bind parameters
foreach($definitions as $k=>$item) {
$stmt->bindValue(':id_word', $last_insert_id, PDO::PARAM_INT);
$stmt->bindValue(':definition_'.$k, $item['definition'], PDO::PARAM_STR);
$stmt->bindValue(':extra_'.$k, $item['extra'], PDO::PARAM_STR);
# fill return value
$result['data'][] = array('definition'=>$item['definition'], 'extra'=>$item['extra']);
}
$stmt->execute();
}
Я не знаю, что на самом деле вызывает крушение службы mysqld. Я буду очень признателен, если у вас есть какие-либо идеи о том, где искать или что оптимизировать.