Есть база данных, записей порядка 3.5М записей, идет активная разработка (т.е. впиливание новых возможностей)
Сейчас СУБД базируется на базе
- Debian 6
- Intel(R) Core(TM) i7 CPU 960 @ 3.20GHz
- RAM 24 GB
- Software Raid 5 из 4-х
В качестве хранилица InnoDB, СУБД отдано порядка 21 ГБ ОЗУ, конфигурация такая:
Все запросы типа "SELECT", новые док-ты вставляются один раз, далее только Read, на этапе разработки и миграции но новые версии выполняются скрипты, обрабатывают 3.5М записей единовременно (UPDATE)
Индексер стоит Sphinx, большинство (95%) запросов идет по Primay Key, но есть еще два индекса, строковых, 100 символов, по ним идет редко, на этапе добавления данных проверка уникальности:
Загрузка Системы во время алтеров
Проблемы:
- Альтеры выполняются край медленно (от 18 до 24 часов)
- Дико растет размер InnoDB файлов
- Скорость индексации Sphinx'ом медленно, а именно:
Перестроение полного индекса выполняется медленно за счет медленного СУБД, хотя про примари идут запросы, т.е первичное прохождение всей СУБД идет медленно, нужно быстрее, хотя при этом система делта индекс строится быстро
Стоит ли перейти на MyISAM или есть что тюнингнуть?
Сейчас СУБД базируется на базе
- Debian 6
- Intel(R) Core(TM) i7 CPU 960 @ 3.20GHz
- RAM 24 GB
- Software Raid 5 из 4-х
Код:
~# cat /proc/mdstat
Personalities : [raid1] [raid10] [raid6] [raid5] [raid4]
md2 : active raid5 sda6[0] sdd6[3] sdb6[2] sdc6[1]
2127109632 blocks super 1.2 level 5, 512k chunk, algorithm 2 [4/4] [UUUU]
~# hddtemp /dev/sd*
/dev/sda5: WDC WD7500AALX-009BA0: 46 C
/dev/sdb5: WDC WD7500AALX-009BA0: 45 C
/dev/sdc5: WDC WD7500AALX-009BA0: 47 C
/dev/sdd5: WDC WD7500AALX-009BA0: 45 C
:~#
Код:
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
skip-external-locking
bind-address = 127.0.0.1
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 192K
thread_cache_size = 8
myisam-recover = BACKUP
query_cache_limit = 1M
query_cache_size = 16M
expire_logs_days = 10
max_binlog_size = 100M
key_buffer_size = 5G
read_rnd_buffer_size = 1M
innodb_buffer_pool_size = 13G
innodb_log_buffer_size = 2M
max_connections = 512Все запросы типа "SELECT", новые док-ты вставляются один раз, далее только Read, на этапе разработки и миграции но новые версии выполняются скрипты, обрабатывают 3.5М записей единовременно (UPDATE)
Индексер стоит Sphinx, большинство (95%) запросов идет по Primay Key, но есть еще два индекса, строковых, 100 символов, по ним идет редко, на этапе добавления данных проверка уникальности:
Код:
mysql> show create table `docs`\G
*************************** 1. row ***************************
Table: docs
Create Table: CREATE TABLE `docs` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`districtId` int(10) unsigned DEFAULT NULL,
`regionId` int(10) unsigned DEFAULT NULL,
`courId` int(11) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
`step` int(11) DEFAULT NULL,
`sId` tinytext,
`sUrl` tinytext,
`sNum` tinytext,
`sDescription` mediumtext,
`sArticle` mediumtext,
`sCategory` mediumtext,
`sJudge` mediumtext,
`sPublicDate` tinytext,
`sArticleFull` text,
`sJudgeFull` text,
`sResult` tinytext,
`sResultDate` tinytext,
`sHtml` longtext,
`cHtml` longtext,
`resultDate` bigint(20) DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_surl` (`sUrl`(100)),
KEY `idx_sid` (`sId`(100))
) ENGINE=InnoDB AUTO_INCREMENT=3718334 DEFAULT CHARSET=cp1251
1 row in set (0.00 sec)
Код:
root@sudoved:~# dstat
You did not select any stats, using -cdngy by default.
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read writ| recv send| in out | int csw
0 0 98 2 0 0|2762k 822k| 0 0 | 144B 296B| 191 521
2 1 84 13 0 0| 58M 11M| 246B 1459B| 0 0 |1010 17k
1 1 83 14 0 0| 54M 18M| 106B 416B| 0 0 |1113 13k
1 1 85 13 0 0| 49M 17M| 11k 878B| 0 0 | 943 10k
1 0 89 10 0 0| 21M 1800k| 105k 3386B| 0 0 | 555 5111
1 1 85 13 0 0| 36M 10M| 106B 416B| 0 0 | 920 10k
1 0 85 14 0 0| 19M 7499k| 106B 416B| 0 0 | 700 4822
1 0 82 17 0 0| 31M 12M| 106B 416B| 0 0 | 958 8497
1 0 83 16 0 0| 14M 9224k| 106B 416B| 0 0 | 775 4841
1 0 85 14 0 0| 26M 9904k| 106B 416B| 0 0 | 956 9082
2 0 82 16 0 0| 31M 4108k| 106B 416B| 0 0 | 817 9935
1 1 86 12 0 0| 24M 11M| 308B 416B| 0 0 | 854 8979
0 0 81 18 0 0| 13M 4536k| 226B 416B| 0 0 | 703 5890
1 0 84 15 0 0| 13M 8964k| 226B 416B| 0 0 | 606 4623
1 1 85 14 0 0| 33M 7520k| 166B 416B| 0 0 | 844 8073
1 0 80 19 0 0| 32M 11M| 428B 416B| 0 0 | 842 8859
Код:
uptime
08:31:30 up 54 days, 17:04, 1 user, load average: 3.39, 3.20, 3.37Проблемы:
- Альтеры выполняются край медленно (от 18 до 24 часов)
- Дико растет размер InnoDB файлов
- Скорость индексации Sphinx'ом медленно, а именно:
Перестроение полного индекса выполняется медленно за счет медленного СУБД, хотя про примари идут запросы, т.е первичное прохождение всей СУБД идет медленно, нужно быстрее, хотя при этом система делта индекс строится быстро
Стоит ли перейти на MyISAM или есть что тюнингнуть?
Код:
source docs {
type = mysql
sql_host = localhost
sql_user =
sql_pass =
sql_db =
sql_query_pre = SET NAMES 'cp1251'
sql_query_pre = SET CHARACTER SET 'cp1251'
sql_query_pre = REPLACE INTO `sph_counter` SELECT 1, MAX(`id`) FROM `docs`
sql_query_range = SELECT MIN(id), (SELECT `max_doc_id` FROM `sph_counter` WHERE `counter_id` = '1') FROM `docs`
sql_range_step = 1000
sql_query = SELECT `id`, `districtId`, `regionId`, `courId`, `type`, `step`, `resultDate`, `sHtml` FROM `docs` WHERE `id` >= $start AND `id` <= $end
sql_attr_uint = districtId
sql_attr_uint = regionId
sql_attr_uint = courId
sql_attr_uint = type
sql_attr_uint = step
sql_attr_timestamp = resultDate
}
index docs {
source = docs
path = ./data/docs
docinfo = extern
morphology = stem_en, stem_ru
html_strip = 1
min_word_len = 2
}
source delta : docs {
type = mysql
sql_host =
sql_user =
sql_pass =
sql_db =
sql_query_pre = SET NAMES 'cp1251'
sql_query_pre = SET CHARACTER SET 'cp1251'
sql_query_range = SELECT (SELECT (`max_doc_id`+1) FROM `sph_counter` WHERE `counter_id` = '1'), MAX(`id`) FROM `docs`
sql_range_step = 1000
sql_query = SELECT `id`, `districtId`, `regionId`, `courId`, `type`, `step`, `resultDate`, `sHtml` FROM `docs` WHERE `id` >= $start AND `id` <= $end
sql_attr_uint = districtId
sql_attr_uint = regionId
sql_attr_uint = courId
sql_attr_uint = type
sql_attr_uint = step
sql_attr_timestamp = resultDate
}
index delta : docs {
source = delta
path = ./data/delta
docinfo = extern
morphology = stem_en, stem_ru
html_strip = 1
min_word_len = 2
}
indexer {
mem_limit = 1024M
}
searchd {
listen = localhost:3340
log = ./logs/sphinx.log
query_log = ./logs/query.log
read_timeout = 5
max_children = 30
pid_file = ./sphinx.pid
max_matches = 1000
seamless_rotate = 0
preopen_indexes = 0
unlink_old = 1
}
)