php - Mysqld 服务在启动后几分钟停止。需要大量的插入物。我怎样才能解决这个问题？

Question

这既是一个编程问题，也是一个服务器问题，所以我想我最好在这里问这个问题，而不是服务器故障。很抱歉这篇文章很长......

我有一个带有字典工具的网站。它在带有 php 5.4、mysql 5.5.25 和 memcache 2.2.6 的 Centos 6.5 上运行。字典是唯一使用mysql服务的脚本。

基本上，这就是它的作用:

1. 用户搜索某个词
2. PHP 检查内存缓存中的单词:
   • 找到单词？返回单词+含义。 END。
   • 未找到？转到3。
3. PHP 使用 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 检查 mysql 中的单词
   • 找到单词？
     • 如果flg_success = 1 ，将单词+含义存储到memcache中并返回单词+含义。
     • 如果flg_success = 0 ，显示错误。 END。
   • 未找到？转到4。
4. PHP 连接到外部 API 并接收包含单词含义的 json。
   • 找到单词？将单词+含义存储在内存缓存中，使用 flg_success = 1 在单词表中插入单词以及 item 中的含义，并返回单词+含义。
   • 未找到？使用 flg_success = 0 在单词表中插入单词

如您所见，该过程理论上经过优化，仅当该单词尚未在 memcache 中时才使用 mysql(它使用 30 天的到期日期 - 已成功检查)。 p><小时/>

表:单词(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;
    }
}

<小时/>

PHP 中的 MySQL 查询

搜索单词:

$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 服务崩溃。如果您对在哪里查找或优化哪些内容有任何想法，我将非常感激。

Answer 1

您能提供(甚至)更多信息吗？例如，表中的条目数以及您设置的不同索引和键(包括外键)？