где мне не нужно жестко задавать пороговое значение для общего количества, какие-либо идеи?
льку это мой первый пост, кажется, я могу опубликовать только одну ссылку, поэтому я перечислил сайты, на которые я ссылаюсь, внизу. Короче говоря, моя цель - сделать так, чтобы база данных возвращала результаты быстрее, я постарался включить как можно больше необходимой информации, чтобы помочь сформулировать вопросы в нижней части поста.
Информация о машине8 processors
model name : Intel(R) Xeon(R) CPU E5440 @ 2.83GHz
cache size : 6144 KB
cpu cores : 4
top - 17:11:48 up 35 days, 22:22, 10 users, load average: 1.35, 4.89, 7.80
Tasks: 329 total, 1 running, 328 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.0%us, 0.0%sy, 0.0%ni, 87.4%id, 12.5%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 8173980k total, 5374348k used, 2799632k free, 30148k buffers
Swap: 16777208k total, 6385312k used, 10391896k free, 2615836k cached
Однако мы собираемся перенести установку mysql на другой компьютер в кластере с 256 ГБ оперативной памяти.
Информация о таблицеМоя таблица MySQL выглядит так
CREATE TABLE ClusterMatches
(
id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
cluster_index INT,
matches LONGTEXT,
tfidf FLOAT,
INDEX(cluster_index)
);В нем приблизительно 18 миллионов строк, есть 1 миллион уникальных совпадений cluster_index и 6 тысяч уникальных совпадений. SQL-запрос, который я генерирую в PHP выглядит так.
Запрос SQL$sql_query="SELECT `matches`,sum(`tfidf`) FROM
(SELECT * FROM Test2_ClusterMatches WHERE `cluster_index` in (".$clusters."))
AS result GROUP BY `matches` ORDER BY sum(`tfidf`) DESC LIMIT 0, 10;";где $ cluster содержит строку из 3000 разделенных запятыми cluster_index's. Этот запрос использует приблизительно 50000 строк и выполняется приблизительно 15 секунд, а при повторном запуске того же запроса требуется приблизительно 1 секунда.
использованиеСодержание таблицы можно считать статическим.Небольшое количество одновременных пользователейВышеприведенный запрос в настоящее время является единственным запросом, который будет выполнен в таблице.SubqueryОсновываясь на этом посте [stackoverflow: Cache / Re-Use a Subquery in MySQL] [1] и улучшении времени запроса, я считаю, что мой подзапрос может быть проиндексирован.
mysql> EXPLAIN EXTENDED SELECT `matches`,sum(`tfidf`) FROM
(SELECT * FROM ClusterMatches WHERE `cluster_index` in (1,2,...,3000)
AS result GROUP BY `matches` ORDER BY sum(`tfidf`) ASC LIMIT 0, 10;
+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+---------------------------------+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 48528 | Using temporary; Using filesort |
| 2 | DERIVED | ClusterMatches | range | cluster_index | cluster_index | 5 | NULL | 53689 | Using where |
+----+-------------+----------------------+-------+---------------+---------------+---------+------+-------+---------------------------------+
В соответствии с этой более старой статьей [Оптимизация MySQL: запросы и индексы] [2] в разделе Дополнительная информация - плохие, которые можно увидеть здесь, «используют временные» и «используют файловую сортировку»
Информация о конфигурации MySQLКэш запросов доступен, но фактически отключен, так как размер в настоящее время установлен на ноль
mysqladmin variables;
+---------------------------------+----------------------+
| Variable_name | Value |
+---------------------------------+----------------------+
| bdb_cache_size | 8384512 |
| binlog_cache_size | 32768 |
| expire_logs_days | 0 |
| have_query_cache | YES |
| flush | OFF |
| flush_time | 0 |
| innodb_additional_mem_pool_size | 1048576 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 8388608 |
| join_buffer_size | 131072 |
| key_buffer_size | 8384512 |
| key_cache_age_threshold | 300 |
| key_cache_block_size | 1024 |
| key_cache_division_limit | 100 |
| max_binlog_cache_size | 18446744073709547520 |
| sort_buffer_size | 2097144 |
| table_cache | 64 |
| thread_cache_size | 0 |
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 0 |
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
| read_rnd_buffer_size | 262144 |
+---------------------------------+----------------------+
Основываясь на этой статье [Поворот производительности базы данных Mysql] [3], я считаю, что значения, которые мне нужно настроить,
table_cachekey_buffersort_bufferread_buffer_sizerecord_rnd_buffer (для условий GROUP BY и ORDER BY)Области, определенные для улучшения - MySQL Query твикиИзменение типа данных для совпадений на индекс, который является целым числом, указывающим на другую таблицу [MySQL действительно будет использовать динамический формат строки, если он содержит поля переменной длины, такие как TEXT или BLOB, что в данном случае означает, что сортировка должна выполняться на диске , Решение состоит не в том, чтобы отказаться от этих типов данных, а в том, чтобы разбить такие поля на связанную таблицу.] [4]Индексирование нового поля match_index так, чтобы GROUP BYmatches происходит быстрее, основываясь на выражении [«Вероятно, вы должны создавать индексы для любого поля, в котором вы выбираете, группируете, упорядочиваете или объединяете».] [5]инструментыЧтобы настроить выступление я планирую использовать
[Объяснить] [6] со ссылкой на [выходной формат] [7][ab - Apache HTTP-сервер для тестирования производительности] [8][Профилирование] [9] с [данными журнала] [10]Будущий размер базы данныхЦель состоит в том, чтобы создать систему, которая может иметь 1М уникальных значений cluster_index, 1М уникальных значений соответствия, приблизительно 3 000 000 000 строк таблицы с временем ответа на запрос около 0,5 с (при необходимости можно добавить больше оперативной памяти и распределить базу данных по кластеру).
ВопросыЯ думаю, что мы хотим сохранить весь набор записей в оперативной памяти, чтобы запрос не касался диска, если мы храним всю базу данных в кэше MySQL, устраняет ли это необходимость в memcachedb?Является ли попытка сохранить всю базу данных в кэше MySQL плохой стратегией, поскольку она не предназначена для постоянной работы? Будет ли лучше использовать что-то вроде memcachedb или redis, если да, то почему?Является ли временная таблица «результатом», созданным запросом, автоматически уничтожается после его завершения?Должны ли мы перейти с Innodb на MyISAM [так как он хорош для чтения тяжелых данных, тогда как InnoDB хорош для записи тяжелых] [11]?мой кэш не отображается как его ноль в моей [Конфигурация кеша запросов] [12], почему запрос в настоящее время выполняется быстрее при втором запуске?Могу ли я реструктурировать свой запрос, чтобы исключить случаи «использования временных» и «использования файловой сортировки», должен ли я использовать объединение вместо подзапроса?Как вы оцениваете размер MySQL [Data Cache] [13]?какие размеры для значений table_cache, key_buffer, sort_buffer, read_buffer_size, record_rnd_buffer вы бы предложили в качестве отправной точки?связи1: stackoverflow.com/questions/658937/cache-re-use-a-subquery-in-mysql2: databasejournal.com/features/mysql/article.php/10897_1382791_4/Optimizing-MySQL-Queries-and-Indexes.htm3: debianhelp.co.uk/mysqlperformance.htm4: 20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/5: 20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/6: dev.mysql.com/doc/refman/5.0/en/explain.html7: dev.mysql.com/doc/refman/5.0/en/explain-output.html8: httpd.apache.org/docs/2.2/programs/ab.html9: mtop.sourceforge.net/10: dev.mysql.com/doc/refman/5.0/en/slow-query-log.html11: 20bits.com/articles/10-tips-for-optimizing-mysql-queries-that-dont-suck/12: dev.mysql.com/doc/refman/5.0/en/query-cache-configuration.html13: dev.mysql.com/tech-resources/articles/mysql-query-cache.html