현재 DB용량이 2.5GB가 넘었고

comments DB만 400메가가 넘어서 부담률이 가장 높은데

mysqld-slow 쿼리를 찍어보면

# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 24  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 22  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 21  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 21  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# Time: 140413 21:33:31
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 27  Lock_time: 0  Rows_sent: 1  Rows_examined: 688699
select * from `XE_comments` as comments   where (content like '%tonight%i\'m%fucking%you%') and list_order < 2100000000   order by list_order asc limit 0, 5;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 21  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# Time: 140413 21:33:32
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 23  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 23  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 21  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 21  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 21  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;
# Time: 140413 21:33:33
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 4  Lock_time: 0  Rows_sent: 4  Rows_examined: 222128
select files.upload_target_srl as `document_srl` from `XE_files` as files,`XE_modules` as modules,`XE_documents` as documents   where (modules.module_srl in (30628) and files.module_srl = modules.module_srl and files.direct_download = 'Y' and files.isvalid = 'Y' and documents.document_srl = files.upload_target_srl)  group by files.upload_target_srl order by documents.list_order asc limit 4;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 3  Lock_time: 0  Rows_sent: 4  Rows_examined: 222128
select files.upload_target_srl as `document_srl` from `XE_files` as files,`XE_modules` as modules,`XE_documents` as documents   where (modules.module_srl in (30628) and files.module_srl = modules.module_srl and files.direct_download = 'Y' and files.isvalid = 'Y' and documents.document_srl = files.upload_target_srl)  group by files.upload_target_srl order by documents.list_order asc limit 4;
# User@Host: ncm[ncm] @  [192.168.0.1]
# Query_time: 21  Lock_time: 0  Rows_sent: 5  Rows_examined: 571321
select * from `XE_comments` as comments   where (module_srl in (30628)) and list_order < 2100000000   order by list_order asc limit 5;

이런식으로 부하가 지속되고 있습니다.

현재 웹서버와 DB서버가 분리된 상태구요

DB서버 사양도 쿼드코어 8GB로 그리 부족한 편이 아닌데

동접이 많아봐야 100명인데 동접이 적어도 엄청난 병목현상이 있는 것으로보아

XE문제인 것 같습니다. XE는 현재 1.4.5.20 버전사용중이구요. (Innodb)

위와 같은 쿼리를 지속적으로 뜨고 사이트속도가 현저하게 많게는 4~5초간 멈춰있습니다.

개선 의뢰부탁드립니다!!