Большая нагрузка на б.д.

[BelykhIvan]

Привет!

 

Очень медленно обрабатываются запросы с форума версии 2.2.2

Версия MySQL: 4.1.21

Операционная система: FreeBSD 5.4

Количество постов на форуме: 261299

Количество топиков на форуме: 7341

Тип таблиц: MyISAM

 

Какие есть варианты оптимизации работы базы данных?

 

Ниже вставляю вырезку наиболее медленных запросов из лога MySQL:

 

# Time: 090123 18:33:33

# User@Host: root[root] @ localhost []

# Query_time: 6 Lock_time: 0 Rows_sent: 10 Rows_examined: 306269

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090123 18:40:12

# User@Host: root[root] @ localhost []

# Query_time: 9 Lock_time: 0 Rows_sent: 10 Rows_examined: 32807

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 29 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090123 18:55:15

# User@Host: root[root] @ localhost []

# Query_time: 12 Lock_time: 0 Rows_sent: 10 Rows_examined: 306279

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090123 20:01:13

# User@Host: root[root] @ localhost []

# Query_time: 7 Lock_time: 0 Rows_sent: 791 Rows_examined: 266432

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM inv_posts p

LEFT JOIN inv_topics t on (t.tid=p.topic_id)

WHERE (t.forum_id IN (2,29,40,58,54,55,56,41,34,15,19,37,9,48,39,14,42,13,32,38,24,57,5,33,46,35,43,1

8,22,23,30,31,3,50,16,51,53,60) AND t.approved=1 AND p.queued=0)

AND ( LOWER(p.post) LIKE '%Stef%' )

LIMIT 1000;

 

# Time: 090123 20:03:12

# User@Host: root[root] @ localhost []

# Query_time: 6 Lock_time: 0 Rows_sent: 5 Rows_examined: 266432

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM inv_posts p

LEFT JOIN inv_topics t on (t.tid=p.topic_id)

WHERE (t.forum_id IN (2,29,40,58,54,55,56,41,34,15,19,37,9,48,39,14,42,13,32,38,24,57,5,33,46,35,43,1

8,22,23,30,31,3,50,16,51,53,60) AND t.approved=1 AND p.queued=0)

AND ( LOWER(p.post) LIKE '%ñòåô%' )

LIMIT 1000;

 

# Time: 090123 20:04:20

# User@Host: root[root] @ localhost []

# Query_time: 12 Lock_time: 0 Rows_sent: 0 Rows_examined: 266433

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM inv_posts p

LEFT JOIN inv_topics t on (t.tid=p.topic_id)

WHERE (t.forum_id IN (2,29,40,58,54,55,56,41,34,15,19,37,9,48,39,14,42,13,32,38,24,57,5,33,46,35,43,1

8,22,23,30,31,3,50,16,51,53,60) AND t.approved=1 AND p.queued=0)

AND ( LOWER(p.post) LIKE '%ñòýô%' )

LIMIT 1000;

 

# Time: 090123 20:31:22

# User@Host: root[root] @ localhost []

# Query_time: 7 Lock_time: 0 Rows_sent: 10 Rows_examined: 306311

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090123 20:46:21

# User@Host: root[root] @ localhost []

# Query_time: 9 Lock_time: 0 Rows_sent: 10 Rows_examined: 306317

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090123 22:10:10

# User@Host: root[root] @ localhost []

# Query_time: 6 Lock_time: 0 Rows_sent: 50 Rows_examined: 16200

SELECT p.*,m.members_display_name, m.mgroup FROM inv_posts p LEFT JOIN inv_members m ON ( m.id=p.author_id ) WHERE p.topic_id=6526 AND p.queued=0 ORDER BY pid LIMIT 8050,50;

 

# Time: 090123 22:17:49

# User@Host: root[root] @ localhost []

# Query_time: 7 Lock_time: 0 Rows_sent: 0 Rows_examined: 266487

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM inv_posts p

LEFT JOIN inv_topics t on (t.tid=p.topic_id)

WHERE (t.forum_id IN (2,29,40,58,54,55,56,41,34,15,19,37,9,48,39,14,42,13,32,38,24,57,5,33,46,35,43,1

8,22,23,30,31,3,50,16,51,53,60) AND t.approved=1 AND p.queued=0)

AND ( LOWER(p.post) LIKE '%laser äåìî%' )

LIMIT 1000;

 

# Time: 090123 22:18:18

# User@Host: root[root] @ localhost []

# Query_time: 11 Lock_time: 0 Rows_sent: 43 Rows_examined: 266487

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM inv_posts p

LEFT JOIN inv_topics t on (t.tid=p.topic_id)

WHERE (t.forum_id IN (2,29,40,58,54,55,56,41,34,15,19,37,9,48,39,14,42,13,32,38,24,57,5,33,46,35,43,1

8,22,23,30,31,3,50,16,51,53,60) AND t.approved=1 AND p.queued=0)

AND ( LOWER(p.post) LIKE '%laser%' )

LIMIT 1000;

 

# Time: 090123 22:19:10

# User@Host: root[root] @ localhost []

# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 306383

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090123 22:30:57

# User@Host: root[root] @ localhost []

# Query_time: 7 Lock_time: 0 Rows_sent: 4 Rows_examined: 266490

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM inv_posts p

LEFT JOIN inv_topics t on (t.tid=p.topic_id)

WHERE (t.forum_id IN (2,29,40,58,54,55,56,41,34,15,19,37,9,48,39,14,42,13,32,38,24,57,5,33,46,35,43,1

8,22,23,30,31,3,50,16,51,53,60) AND t.approved=1 AND p.queued=0)

AND ( LOWER(p.post) LIKE '%blastarr%' )

LIMIT 1000;

 

# Time: 090123 22:37:12

# User@Host: root[root] @ localhost []

# Query_time: 10 Lock_time: 0 Rows_sent: 10 Rows_examined: 306391

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090123 22:47:47

# User@Host: root[root] @ localhost []

# Query_time: 11 Lock_time: 0 Rows_sent: 10 Rows_examined: 306391

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090123 23:43:00

# User@Host: root[root] @ localhost []

# Query_time: 6 Lock_time: 0 Rows_sent: 10 Rows_examined: 306425

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090124 0:12:39

# User@Host: root[root] @ localhost []

# Query_time: 9 Lock_time: 0 Rows_sent: 999 Rows_examined: 266525

SELECT p.pid, p.queued, t.approved, t.forum_id

FROM inv_posts p

LEFT JOIN inv_topics t on (t.tid=p.topic_id)

WHERE (t.forum_id IN (2,29,40,58,54,55,56,41,34,15,19,37,9,48,39,14,42,13,32,38,24,57,5,33,46,35,43,1

8,22,23,30,31,3,50,16,51,53,60) AND t.approved=1 AND p.queued=0)

AND ( LOWER(p.post) LIKE '%áëîã%' )

LIMIT 1000;

 

# Time: 090124 0:41:39

# User@Host: root[root] @ localhost []

# Query_time: 9 Lock_time: 0 Rows_sent: 10 Rows_examined: 306435

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090124 1:21:50

# User@Host: root[root] @ localhost []

# Query_time: 7 Lock_time: 0 Rows_sent: 10 Rows_examined: 306437

SELECT p.*, t.tid, t.title as topic_title, t.starter_name as topic_starter_name, t.forum_id FROM inv_posts p, inv_topics t WHERE p.topic_id = t.tid AND t.forum_id IN( 40 ) AND t.state != "link" AND t.approved=1 ORDER BY p.pid desc LIMIT 0,10;

 

# Time: 090124 2:00:50

# User@Host: root[root] @ localhost []

# Query_time: 18 Lock_time: 0 Rows_sent: 260980 Rows_examined: 260980

SELECT /*!40001 SQL_NO_CACHE */ * FROM `inv_posts`;

 

# Time: 090124 2:01:04

# User@Host: root[root] @ localhost []

# Query_time: 10 Lock_time: 0 Rows_sent: 0 Rows_examined: 0

UNLOCK TABLES;

 

# Time: 090124 2:11:58

# User@Host: root[root] @ localhost []

# Query_time: 19 Lock_time: 0 Rows_sent: 1 Rows_examined: 10156

SELECT count(*) as cnt FROM inv_attachments a LEFT JOIN inv_posts p ON ( p.pid=a.attach_rel_id ) WHERE p.topic_id=9486;