Мы создали новостной сайт. Каждый день мы будем вводить десятки тысяч данных из веб-API.
Для обеспечения точного поиска в нашей таблице используется MyISAM, создается полнотекстовый индекс (заголовок, содержание, дата). Наш сайт сейчас тестируется в Godaddy VDS с 2 ГБ ОЗУ, 30 ГБ свободного места (без свопа, потому что VDS не позволяют создавать своп).
с участием #grep “model name” /proc/cpuinfo мы получаем использование Godaddy Intel(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
РЕДАКТИРОВАТЬ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
Я не уверен, что вы можете что-либо сделать, полнотекстовый поиск mysql не очень хорош, по моему опыту.
Простые решения.
Здесь выделяется пара странных вещей.
Скачайте mysqltuner.pl (просто введите wget mysqltuner.pl ) и запустите его в своей базе данных. Скорее всего, у него будет несколько хороших предложений.
Полнотекстовый поиск MySQL в любом случае - неправильный способ справиться с этим. Сфинкс или Lucene оба хороших проекта для поиска.
похоже, что вы не используете индекс для того, что ищете .. добавить explain перед запросом и вставьте результаты.