Два милионна документов и MySQL

[Активист]

Есть более двух милионнов документов, рост 0.5-1% в день.

1. Какой движок лучше (иннобд, майисам)?
2. Как уменьшить нагрузку на диск? Сейчас WA большой (23-30%) при добавллении записей/чтение записей.
3. Сейчас использую иннобд, как уменьшить размер ibdata
-rw-rw---- 1 mysql mysql 45G 2011-06-20 10:14 ibdata1

Что еще можно сделать для оптимизации?

1. Sphinx как-то медленно индексирует, после 50 000 тысяч начинает сильно тормозить (фактически встает)
> сollected 45475 docs, 420.8 MB

 CREATE TABLE `docs` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `courId` int(11) default NULL,
  `type` int(11) default NULL,
  `step` int(11) default NULL,
  `loaded` enum('0','1','2') default '0',
  `sId` tinytext,
  `sDescription` mediumtext,
  `sArticle` mediumtext,
  `sCategory` mediumtext,
  `sJudge` mediumtext,
  `sPublicDate` tinytext,
  `sArticleFull` text,
  `sJudgeFull` text,
  `sResult` tinytext,
  `sResultDate` tinytext,
  `sHtml` longtext,
  `rand` int(11) default NULL,
  PRIMARY KEY  (`id`),
  KEY `idx_sid` (`sId`(100)),
  KEY `idx_loaded` (`loaded`),
  KEY `idx_rand` (`rand`),
  KEY `idx_loaded_rand` (`loaded`,`rand`)
) ENGINE=InnoDB AUTO_INCREMENT=2062951 DEFAULT CHARSET=cp1251

source docs {
        type = mysql
        sql_host = localhost
    sql_user = root
    sql_pass = 
    sql_db = docs

        sql_query_pre = SET NAMES 'cp1251'
        sql_query_pre = SET CHARACTER SET 'cp1251'
        sql_query_range = SELECT 1, COUNT(*) FROM `docs` WHERE `loaded` = '1'
        sql_range_step = 128
        sql_query = SELECT `id`, `sHtml` FROM `docs` WHERE `loaded` = '1' && '$end' LIMIT $start, 128
}

index docs {
        source = docs
        path = data/docs.1
        docinfo = extern
        morphology = stem_en, stem_ru
        html_strip = 1
        min_word_len = 2
}

searchd {
        port = 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

serv002:~# dstat
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq| read  writ| recv  send|  in   out | int   csw
  9   1  85   5   0   0| 560k 1013k|   0     0 |  22B   26B| 310  1432
 24   2  49  26   0   0|3248k  544k|  11k   62k|   0     0 | 328  1760
  4   0  80  16   0   0|3232k  272k|  10k  302k|   0     0 | 478  1340
  6   2  67  25   0   0|2632k 2824k|  12k   33k|   0     0 | 277  1676
  9   2  49  40   0   0|  18M 4216k|7945B   44k|   0     0 | 385  1947
 33   2  41  25   0   0|6528k  528k|6659B  112k|   0     0 | 315  1975
 33   4  40  23   0   0|3472k  432k|  11k   87k|   0     0 | 280  3191
 30   3  43  24   0   0|4208k   32k|  12k   69k|   0     0 | 279  2743
 25   4  43  28   0   0|3176k 1792k|  14k  162k|   0     0 | 404  3460
  7   3  52  38   0   0|3304k 6784k|  35k  768k|   0     0 |1278  2006
 13   3  58  25   0   0|2984k 5976k|  41k 1120k|   0     0 |1771  2289
  8   1  54  36   0   0|3224k  160k|  11k  199k|   0     0 | 429  1568 ^C
serv002:~#

В топе MySQL
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
4871 mysql 20 0 145m 59m 6132 S 112 3.0 463:45.55 mysqld

 

[Активист]

Щас в топе это (на 4-ре ядра)

top - 11:28:17 up 158 days, 15:47,  2 users,  load average: 6.68, 6.08, 4.78
Tasks: 142 total,   6 running, 135 sleeping,   0 stopped,   1 zombie
Cpu(s): 69.4%us,  5.5%sy,  0.0%ni, 10.5%id, 14.2%wa,  0.0%hi,  0.4%si,  0.0%st
Mem:   2037916k total,  1988016k used,    49900k free,    72060k buffers
Swap:  2097144k total,     8972k used,  2088172k free,  1564692k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 4871 mysql     20   0  145m  59m 6132 S  194  3.0 489:21.62 mysqld

 

[grigori]

я в твоем топе вижу mysql
ты уверен, что тормозит сфинкс, а не mysql при выборках c лимитом?
ему памяти хватает?

 

[Активист]

Убрал лимиты , переделал структуру из за этого (не все доки нужно в индекс), рандж сделал по id, полет нормальный. В чем там грабли?

 

[grigori]

там - где? в mysql? в limit?

 

[Активист]

в MySQL (лимите)

 

[zerkms]

Активист
В mysql - в лимите )))

 

[Активист]

Хм. Давайте по подробней. Хорошо, чисто тиоретически, мне понадобиться юзать лимит

SELECT `id` FROM `docs` WHERE `loaded` = '1' LIMIT 35000, 1000;

Где на loaded висит индекс - в чем трабл?

 

[Gas]

Тем, что при LIMIT 35000, 1000 mysql сначала должен перебрать 35000, а уже потом достать 1000. А при sql_range_step=128 и 2M записей неудивительно что всё "не быстро" индексировалось.

Даже в примере sphinx'а используется конструкция:

sql_query_range	= SELECT MIN(id),MAX(id) FROM documents
sql_range_step = 1000
sql_query = SELECT * FROM documents WHERE id>=$start AND id<=$end

 

[DiMA]

> Тем, что при LIMIT 35000, 1000 mysql сначала должен перебрать 35000, а уже потом достать 1000

Ага, хрен вам. Тупая мыскля сначала считает весь результат (т.е. "переберет"), только потом приступит к урезке по лимиту.

 

[Mols]

Недавно делал вот такой обход. Работает очень быстро.

  WHERE id > ? ORDER BY id  LIMIT 1000

Делать выборку с лимитом и без упорядочивания вроде как вообще низя.

 

[Ragazzo]

Mols
>>Делать выборку с лимитом и без упорядочивания вроде как вообще низя.
почему?

 

[Вурдалак]

Хотя бы руководствуясь здравым смыслом: разные «страницы» могут содержать одни и те же строки.

 

[Techmind]

Недавно делал вот такой обход. Работает очень быстро.

  WHERE id > ? ORDER BY id  LIMIT 1000

Делать выборку с лимитом и без упорядочивания вроде как вообще низя.

Можно, для иннобд будет порядок вставки, для майсама - уже не скажу но скорее всего тоже, просто уповать, что порядок будет корректным хреново, но в принципе можно.

 

[Вурдалак]

Не слышал такое про InnoDB, но для MyISAM порядок точно не будет соответствовать порядку вставки. После UPDATE/DELETE данные вполне могут перемешиваться.

 

[Mols]

просто уповать, что порядок будет корректным хреново, но в принципе можно.

Очень убедительно.
Хорошо бы ещё ссылку на оф документацию по этому вопросу.