☰

Mysql全文搜索导致CPU占用高

tag-icon • tag-icon mysql centos5 cpu-usage full-text-search
Mysql全文搜索导致CPU占用高

我们建立了一个新闻网站，每天我们会从web api输入数万条数据。

为了提供精准的搜索服务，我们的表使用了MyISAM，建立了全文索引（标题，内容，日期）。我们的网站现在在Godaddy VDS上测试，2GB内存，30GB空间（没有swap，因为VDS不允许建立swap）。

我们#grep “model name” /proc/cpuinfo使用GodaddyIntel(R) Xeon(R) CPU L5609 @ 1.87GHz

这是我们的mysql输入，使用FROM dual避免插入重复记录，并且表的FULLTEXT索引始终打开。

INSERT INTO newstable
(title,link,content,date,source,image,imagesource) 
SELECT '".$title."','".$link."','','".$content."','".$date."','".$source."','".$image."','".$imagesource."' 
FROM dual WHERE not exists 
(SELECT content FROM newstable WHERE newstable.content = '".$content."')

以下是我们在阅读页面中的搜索查询（我们已经优化了主页，它是一个静态页面，由 crond 生成，但阅读页面应该保留以进行实时搜索）：

SELECT  id,title,link,content,date,source,image,imagesource 
FROM newstable 
WHERE (MATCH (title,content,date) 
AGAINST ('$boolean' IN BOOLEAN MODE)) 
Order By date DESC Limit '.($_POST['number']).', 10

每个页面都有 2 或 3 个类似上述的查询。（* 我已重命名表名和字段名）

对于一个新闻网站来说，我们需要在顶级网站上保持新鲜的新闻，所以这sort by date是必需的。

现在，我们的问题是: Mysql full text search will cause high usage CPU。#top用于服务器监控，打开每个页面都会花费一些钱10% CPU。我担心在这种情况下，我们的网站只能支持几个人同时在线。但我们的目标是至少100人同时在线。非常感谢。

Cpu(s):  10.4%us,  1.4%sy,  0.0%ni, 88.5%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   2097152k total,   570364k used,  1526788k free,        0k buffers
Swap:        0k total,        0k used,        0k free,        0k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
28265 mysql     15   0  385m  75m 5752 S 129.3  3.7 751:49.13 mysqld
 1313 root      15   0 35040  18m 6400 S  7.0  0.9   0:03.55 php
    1 root      15   0  2156  664  576 S  0.0  0.0   0:04.42 init
 1215 root      15  -4  2260  652  436 S  0.0  0.0   0:00.00 udevd
 1359 root      15   0  2240 1004  812 R  0.0  0.0   0:00.00 top
 1585 root      25   0  2832  868  700 S  0.0  0.0   0:00.00 xinetd
...

编辑：解释查询结果：

id  select_type table   type    possible_keys   key key_len ref rows    Extra
1   PRIMARY newstable   fulltext    index_name  index_name  0       1   Using where

EDIT2： ./mysqltuner.pl 结果

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.20
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 396M (Tables: 39)
[--] Data in InnoDB tables: 208K (Tables: 8)
[!!] Total fragmented tables: 9

-------- Security Recommendations  -------------------------------------------
[!!] User '@ip-XX-XX-XX-XX.ip.secureserver.net'
[!!] User '@localhost'

-------- Performance Metrics -------------------------------------------------
[--] Up for: 17h 27m 58s (1M q [20.253 qps], 31K conn, TX: 513M, RX: 303M)
[--] Reads / Writes: 61% / 39%
[--] Total buffers: 168.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 573.8M (28% of installed RAM)
[OK] Slow queries: 0% (56/1M)
[!!] Highest connection usage: 100%  (152/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/162.5M
[OK] Key buffer hit rate: 100.0% (2B cached / 882K reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 17K sorts)
[!!] Temporary tables created on disk: 49% (32K on disk / 64K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (400 open / 298K opened)
[OK] Open file limit used: 41% (421/1K)
[!!] Table locks acquired immediately: 77%
[OK] InnoDB data size / buffer pool: 208.0K/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
    Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (>= 8M)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_cache (> 400)

编辑3：my.cnf

[mysqld]
port            = 3306
socket          = /tmp/mysql.sock
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 16M
max_connections = 1024
wait_timeout = 5
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 2M
myisam_sort_buffer_size = 128M
thread_cache_size = 8
query_cache_size= 256M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
ft_min_word_len = 2
read_rnd_buffer_size=2M
tmp_table_size=128M

答案1

这里有几件奇怪的事情引人注目。

  1. 您的系统空闲率为 94.5%，但 MySQL 却占用了 129% 的 CPU。这里面一定有什么地方不太对劲。
  2. MySQL 只使用了 3% 的可用内存。我看到那里也有一个 PHP 进程在运行，所以我猜这是一个组合的 Web 和数据库框，但尽管如此，我还是希望您为 MySQL 提供更多 RAM，只需确保为 Web 进程留出足够的空间即可。

获取 mysqltuner.pl（只需输入wget mysqltuner.pl）并在您的数据库上运行它。它很可能会有一些好的建议。

无论如何，MySQL 全文搜索并不是处理此问题的正确方法。 狮身人面像或者Lucene都是很好的搜索项目。

答案2

我不确定你能做什么，根据我的经验，mysql 全文搜索并不是很好。

简单的解决方案。

  • 运行较少的查询。每次页面浏览的全文搜索对于 CPU 来说将会非常昂贵。
    • 重新设计数据库/应用程序，以便在插入内容时更新某种索引表，而不是在查看页面时更新。每次查看页面时进行大量搜索似乎很浪费。
  • 购买更多硬件以便支持当前的设计
  • 使用 mysql 以外的其他东西进行全文搜索。也许基于 Lucene 的东西？

答案3

听起来你没有在搜索的内容上使用索引..explain在查询前添加并粘贴结果。

相关内容