Mysql全文search导致CPU使用率高

我们build了一个新闻网站。 我们每天都会从web apiinput数以万计的数据。

为了提供精确的search服务，我们的表使用MyISAM，build立全文索引（标题，内容，date）。 我们的网站现在在Godaddy VDS上testing，2GB内存，30GB空间（无交换，因为VDS不允许交换）。

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

这里是我们的mysqlinput，使用FROM dual避免插入重复logging，并且表的FULLTEXT索引始终打开。

• 如何更改/ home的安装点
• Howo将Dell MD3600i的多pathIO设置为主动/主动设置？
• 列出sudo用户在centos中
• 尽pipe增加了PHP的内存限制，并确认没有RLimitMEM，WordPress的网站已经重复PHP内存错误
• 如何以root身份运行tomcat？

 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."') 

这里是我们的search查询在阅读页面（我们已经优化了主页，这是一个静态页面，从crond生成，但阅读页面应该保持在实时search）：

 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 。 恐怕在这种情况下，我们的网站只能同时在线支持less数人。 但是我们的目标至less是同时在线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 

编辑2：./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