Производительность

[rxu]

Разработчики протестировали данный запрос и сообщили, что индекс используется.
Перепроверьте еще раз индексы на данной таблице, запостите скрин здесь.

[istepan]

Перепроверьте еще раз индексы на данной таблице, запостите скрин здесь.

Ok. Смогу сделать вечером.

[LavIgor]

istepan, сообщения с одним и тем же post_id совершенно необязательно должны идти одно за другим.
Яркий тому пример - расширение Change Post Time, позволяющее менять дату и время сообщений, а тем самым и их положение в теме.

[istepan]

сообщения с одним и тем же post_id совершенно необязательно должны идти одно за другим.

Этот запрос писал не я. Это либо форум, либо какое-то расширение.

[LavIgor]

istepan, речь была не о запросе. Давал ответ на это:

Да, но у них post_id будет все равно уникальный. Там же primary key с авто инкриментом.
В таком случае данную проблем можно решить добавив общий индекс для topic_id и post_visibility, и из ORDER BY убрать post_time.

Если убрать post_time, поменяется логика запроса.

[istepan]

поменяется логика запроса.

Ясно.

Пока еще не смотрел. Хочу развернуть 3.1 с 0 и сравнить индексы. Вероятно при миграции индексы слетели.

[istepan]

В общем индексы все на месте, и вот такой результат:

Код: Выделить всё

SELECT p.post_id
	FROM phpbb_posts p
	WHERE p.topic_id = 50479
		AND p.post_visibility = 1		
	ORDER BY p.post_time DESC, p.post_id DESC
 LIMIT 13;
/* Affected rows: 0  Найденные строки: 13  Предупреждения: 0  Длительность  1 query: 12,917 sec. */

SELECT p.post_id
	FROM phpbb_posts p
	WHERE p.topic_id = 50479
		AND p.post_visibility = 1		
	ORDER BY p.post_id DESC
 LIMIT 13;
/* Affected rows: 0  Найденные строки: 13  Предупреждения: 0  Длительность  1 query: 2,356 sec. */

Отправлено спустя 10 минут 3 секунды:
Есть еще такая информация:

Код: Выделить всё

Reading mysql slow query log from /var/log/mysql/mysql-slow.log
Count: 8  Time=223.97s (1791s)  Lock=0.00s (0s)  Rows_sent=1.0 (8), Rows_examined=1117652.2 (8941218), site[site]@localhost
  SELECT MAX(p.post_id) as last_post_id
  FROM phpbb_posts p , phpbb_topics t
  WHERE p.forum_id = N
  AND t.topic_id = p.topic_id AND t.topic_visibility = N
  AND p.post_visibility = N

Count: 2  Time=45.19s (90s)  Lock=0.00s (0s)  Rows_sent=1360.0 (2720), Rows_examined=14675.0 (29350), site[site]@localhost
  SELECT SQL_CALC_FOUND_ROWS p.post_id
  FROM phpbb_posts p
  WHERE p.poster_id = N
  AND (p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  AND p.post_visibility = N)
  AND p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  ORDER BY p.post_time DESC

Count: 4  Time=32.95s (131s)  Lock=0.00s (0s)  Rows_sent=250.0 (1000), Rows_examined=9518.5 (38074), site[site]@localhost
  SELECT p.post_id
  FROM phpbb_posts p
  WHERE p.poster_id = N
  AND (p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  AND p.post_visibility = N)
  AND p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  ORDER BY p.post_time DESC
  LIMIT N

Count: 2  Time=32.90s (65s)  Lock=0.00s (0s)  Rows_sent=250.0 (500), Rows_examined=13564.0 (27128), site[site]@localhost
  SELECT p.post_id
  FROM phpbb_posts p
  WHERE p.poster_id = N
  AND (p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  AND p.post_visibility = N)
  AND p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  ORDER BY p.post_time DESC
  LIMIT N

Count: 27  Time=31.46s (849s)  Lock=0.00s (0s)  Rows_sent=1.0 (27), Rows_examined=94260.7 (2545039), site[site]@localhost
  SELECT COUNT(p.post_id) AS prev_posts
  FROM phpbb_posts p
  WHERE p.topic_id = N
  AND p.post_visibility = N AND (p.post_time < N OR (p.post_time = N AND p.post_id <= N))

Count: 5  Time=28.93s (144s)  Lock=0.00s (0s)  Rows_sent=6764.0 (33820), Rows_examined=19927.2 (99636), site[site]@localhost
  SELECT SQL_CALC_FOUND_ROWS p.post_id
  FROM phpbb_posts p
  WHERE p.poster_id = N
  AND (p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  AND p.post_visibility = N)
  AND p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  ORDER BY p.post_time DESC

Count: 95  Time=28.87s (2742s)  Lock=0.02s (1s)  Rows_sent=25.0 (2375), Rows_examined=9847.0 (935468), site[site]@localhost
  SELECT p.post_id
  FROM phpbb_posts p
  WHERE p.topic_id = N
  AND p.post_visibility = N
  ORDER BY p.post_time DESC, p.post_id DESC
  LIMIT N, N

Count: 108  Time=27.49s (2968s)  Lock=0.01s (0s)  Rows_sent=25.0 (2700), Rows_examined=4019.8 (434141), site[site]@localhost
  SELECT p.post_id
  FROM phpbb_posts p
  WHERE p.topic_id = N
  AND p.post_visibility = N
  ORDER BY p.post_time ASC, p.post_id ASC
  LIMIT N, N

Count: 11  Time=27.09s (297s)  Lock=0.01s (0s)  Rows_sent=25.0 (275), Rows_examined=5739.1 (63130), site[site]@localhost
  SELECT p.post_id
  FROM phpbb_posts p
  WHERE p.topic_id = N
  AND p.post_visibility = N
  ORDER BY p.post_time ASC, p.post_id ASC
  LIMIT N

Count: 9  Time=25.32s (227s)  Lock=0.04s (0s)  Rows_sent=17.6 (158), Rows_examined=3835.1 (34516), site[site]@localhost
  SELECT p.post_id
  FROM phpbb_posts p
  WHERE p.topic_id = N
  AND p.post_visibility = N
  ORDER BY p.post_time DESC, p.post_id DESC
  LIMIT N

Count: 1  Time=21.40s (21s)  Lock=0.00s (0s)  Rows_sent=250.0 (250), Rows_examined=5156.0 (5156), site[site]@localhost
  SELECT t.topic_id
  FROM phpbb_topics t, phpbb_posts p
  WHERE p.poster_id = 'S'
  AND (p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  AND p.post_visibility = N)
  AND p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  AND t.topic_id = p.topic_id
  GROUP BY t.topic_id, t.topic_last_post_time
  ORDER BY t.topic_last_post_time DESC
  LIMIT N

Count: 1  Time=21.30s (21s)  Lock=0.00s (0s)  Rows_sent=250.0 (250), Rows_examined=11245.0 (11245), site[site]@localhost
  SELECT t.topic_id
  FROM phpbb_topics t, phpbb_posts p
  WHERE p.poster_id = N
  AND (p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  AND p.post_visibility = N)
  AND p.forum_id NOT IN (N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N, N)
  AND t.topic_id = p.topic_id
  GROUP BY t.topic_id, t.topic_last_post_time
  ORDER BY t.topic_last_post_time DESC
  LIMIT N

Отправлено спустя 51 секунду:
Server version: 10.0.30-MariaDB-0+deb8u1 (Debian)

[rxu]

MariaDB

Не уверен, что в MariaDB индексы работают точно так же, как в MySQL.

[istepan]

Не уверен, что в MariaDB индексы работают точно так же, как в MySQL.

OpenServer с Mysql выдает схожий результат.
Ты можешь нагенерировать себе базу с постами из рандомного текста. У нас на форуме 2 мл. сообщений и тем около 20 к. Разделов где-то 3 десятка.

Будет ли такой же результат?

Отправлено спустя 2 минуты 25 секунд:
Может VDS слишком слабая.

Код: Выделить всё

cat /proc/meminfo
MemTotal:        1036628 kB
MemFree:          125808 kB
MemAvailable:      53724 kB
Buffers:           19744 kB
Cached:           140312 kB
SwapCached:            0 kB
Active:           758764 kB
Inactive:          53676 kB
Active(anon):     719888 kB
Inactive(anon):     8032 kB
Active(file):      38876 kB
Inactive(file):    45644 kB
Unevictable:        5196 kB
Mlocked:            5196 kB
SwapTotal:             0 kB
SwapFree:              0 kB
Dirty:              3344 kB
Writeback:             0 kB
AnonPages:        657752 kB
Mapped:            48688 kB
Shmem:             72092 kB
Slab:              41468 kB
SReclaimable:      13864 kB
SUnreclaim:        27604 kB
KernelStack:        4304 kB
PageTables:        12480 kB
NFS_Unstable:          0 kB
Bounce:                0 kB
WritebackTmp:          0 kB
CommitLimit:      518312 kB
Committed_AS:    1943588 kB
VmallocTotal:   34359738367 kB
VmallocUsed:           0 kB
VmallocChunk:          0 kB
HardwareCorrupted:     0 kB
AnonHugePages:         0 kB
HugePages_Total:       0
HugePages_Free:        0
HugePages_Rsvd:        0
HugePages_Surp:        0
Hugepagesize:       2048 kB
DirectMap4k:       46972 kB
DirectMap2M:     4294656 kB

Код: Выделить всё

cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 0
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 0
initial apicid  : 0
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 1
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 1
initial apicid  : 1
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 2
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 2
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 2
initial apicid  : 2
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 3
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 3
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 3
initial apicid  : 3
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 4
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 4
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 4
initial apicid  : 4
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 5
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 5
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 5
initial apicid  : 5
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 6
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 6
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 6
initial apicid  : 6
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 7
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 7
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 7
initial apicid  : 7
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 8
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 8
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 8
initial apicid  : 8
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 9
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 9
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 9
initial apicid  : 9
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 10
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 10
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 10
initial apicid  : 10
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

processor       : 11
vendor_id       : GenuineIntel
cpu family      : 6
model           : 6
model name      : QEMU Virtual CPU version 2.5+
stepping        : 3
microcode       : 0x1
cpu MHz         : 2266.746
cache size      : 4096 KB
physical id     : 11
siblings        : 1
core id         : 0
cpu cores       : 1
apicid          : 11
initial apicid  : 11
fpu             : yes
fpu_exception   : yes
cpuid level     : 13
wp              : yes
flags           : fpu de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pse36 clflush mmx fxsr sse sse2 syscall nx lm rep_good nopl eagerfpu pni cx16 x2apic hypervisor lahf_lm
bugs            :
bogomips        : 4533.49
clflush size    : 64
cache_alignment : 64
address sizes   : 40 bits physical, 48 bits virtual
power management:

Отправлено спустя 2 минуты 2 секунды:
Хотя если просто читать форум, то работает очень шустро.
Для гостей еще сделал кеш на стороне nginx, на 30 минут. Чтоб не грузили сервер.

[Mr. Anderson]

Может VDS слишком слабая.

Я правильно понял - 1Гб RAM?
Если да, то вопросов больше не имею.

[nissin]

В общем индексы все на месте, и вот такой результат

Приложите EXPLAIN запросов.

[istepan]

Если да, то вопросов больше не имею.

Выше 800 мб. не поднимается. Стоит еще взять?

[nissin]

Для гостей еще сделал кеш на стороне nginx, на 30 минут

Конечно многое зависит от реализации, но потенциальные проблемы вполне возможны.

[istepan]

Конечно многое зависит от реализации,

Печенька в админке 2017s_u

Код: Выделить всё

set $no_cache 1;

if ($cookie_2017s_u = 1){
    set $no_cache 0;
}

if ($request_method != GET){
    set $no_cache 1;
}

if ($request_uri ~* ^/(search|ucp)){
    set $no_cache 1;
}

fastcgi_cache fcgi;
fastcgi_cache_key "$request_method|$http_if_modified_since|$http_if_none_match|$host|$request_uri";
fastcgi_no_cache $no_cache;
fastcgi_cache_bypass $no_cache;
fastcgi_pass_header Set-Cookie;
fastcgi_ignore_headers Cache-Control Expires Set-Cookie;
fastcgi_cache_valid 200 301 302 304 7d;

Отправлено спустя 1 час 33 минуты 41 секунду:
Добавил два индекса, посмотрим что будет дальше.

Код: Выделить всё

CREATE INDEX tid_pv_post_time ON phpbb_posts (topic_id, post_visibility, post_time);
CREATE INDEX tid_pv ON phpbb_posts (topic_id, post_visibility);

Как минимум в некоторых запросах во where будут задействованы.

[Sumanai]

Выше 800 мб. не поднимается

Под такие объёмы БД надо тюнить, и докупить ещё памяти. ГБ 2-4 занять раз плюнуть. но всё это надо делать вместе, по отдельности смысла нет.