Обсуждение: ERROR: invalid restriction selectivity: 224359728.000000
Hello! Process postmaster completly eat my proccessor for a long time and i see that message in logs. Does anybody know what does the subj means and why it occures ?
On Nov 16, 2007 11:59 AM, <xeb@mail.ru> wrote: > Hello! > Process postmaster completly eat my proccessor for a long time and i see that > message in logs. > Does anybody know what does the subj means and why it occures ? You're giving us WAY too little information to troubleshoot this problem. What message in the logs? What does top / vmstat / ps / iostat really show? What kind of query is running? Have you got query logging turned on for long running queries? Is this actually affecting the performance of your machine? Is the process chewing through all your memory? If you know what query is causing this, what does explain analyze of the query look like? The more info you can provide about the issue, the more we can help. But first we have to be sure there's a problem. If you're asking postgresql to sort 1billion rows it is going to eat up a lot of CPU and there's little you can do about it.
"Scott Marlowe" <scott.marlowe@gmail.com> writes:
> On Nov 16, 2007 11:59 AM, <xeb@mail.ru> wrote:
>> Does anybody know what does the subj means and why it occures ?
> You're giving us WAY too little information to troubleshoot this problem.
Indeed, but it seems to have something to do with a broken selectivity
estimator function (see restriction_selectivity()). What PG version
is this? Do you have any add-on datatypes or operators installed?
Exactly what is the query that triggers the problem?
regards, tom lane
В сообщении от Sunday 18 November 2007 05:00:35 Scott Marlowe написал(а): > On Nov 16, 2007 11:59 AM, <xeb@mail.ru> wrote: > > Hello! > > Process postmaster completly eat my proccessor for a long time and i see > > that message in logs. > > Does anybody know what does the subj means and why it occures ? > > You're giving us WAY too little information to troubleshoot this problem. > > What message in the logs? What does top / vmstat / ps / iostat really > show? What kind of query is running? Have you got query logging > turned on for long running queries? Is this actually affecting the > performance of your machine? Is the process chewing through all your > memory? If you know what query is causing this, what does explain > analyze of the query look like? > > The more info you can provide about the issue, the more we can help. > But first we have to be sure there's a problem. > > If you're asking postgresql to sort 1billion rows it is going to eat > up a lot of CPU and there's little you can do about it. OK, when it occures again i'll collect all information. > Have you got query logging > turned on for long running queries? How can i do it ? > Indeed, but it seems to have something to do with a broken selectivity > estimator function (see restriction_selectivity()). What PG version > is this? Do you have any add-on datatypes or operators installed? > Exactly what is the query that triggers the problem? PG 8.2.4 Database containes phpBB forum, jabberd-2 and gentoo-wiki mirror and who causes trouble and what query i don't know. I have droped gentoo-wiki yesterday and trouble did not occured yet, it seems gentoo-wiki was causing, but i want to restore this database. As you may know this database is large enough.
> On Nov 16, 2007 11:59 AM, <xeb@mail.ru> wrote:
> > Hello!
> > Process postmaster completly eat my proccessor for a long time and i see
> > that message in logs.
> > Does anybody know what does the subj means and why it occures ?
>
> You're giving us WAY too little information to troubleshoot this problem.
>
> What message in the logs? What does top / vmstat / ps / iostat really
> show? What kind of query is running? Have you got query logging
> turned on for long running queries? Is this actually affecting the
> performance of your machine? Is the process chewing through all your
> memory? If you know what query is causing this, what does explain
> analyze of the query look like?
>
> The more info you can provide about the issue, the more we can help.
> But first we have to be sure there's a problem.
>
> If you're asking postgresql to sort 1billion rows it is going to eat
> up a lot of CPU and there's little you can do about it.
Tasks: 110 total, 21 running, 88 sleeping, 0 stopped, 1 zombie
Cpu(s): 91.0%us, 0.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 9.0%si, 0.0%st
Mem: 516176k total, 506716k used, 9460k free, 3912k buffers
Swap: 987956k total, 52656k used, 935300k free, 86928k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
3327 postgres 16 0 36856 11m 10m R 14.4 2.3 2:51.27 postmaster
3439 postgres 16 0 36856 11m 10m R 14.4 2.3 0:25.17 postmaster
3300 postgres 16 0 36856 11m 10m R 13.8 2.4 3:29.03 postmaster
3383 postgres 16 0 36884 11m 10m R 13.8 2.3 0:43.03 postmaster
3364 postgres 16 0 36856 11m 10m R 10.8 2.3 1:01.03 postmaster
3288 postgres 16 0 36856 12m 11m R 10.2 2.5 4:27.12 postmaster
3338 postgres 16 0 36856 11m 10m R 9.0 2.3 1:51.57 postmaster
3360 postgres 16 0 36860 11m 10m R 8.4 2.3 1:12.65 postmaster
3278 postgres 16 0 36856 13m 11m R 5.4 2.6 5:46.71 postmaster
1 root 15 0 2960 976 936 S 0.0 0.2 0:01.14 init
# cat /proc/vmstat
nr_anon_pages 98383
nr_mapped 7015
nr_file_pages 24724
nr_slab_reclaimable 1018
nr_slab_unreclaimable 2128
nr_page_table_pages 878
nr_dirty 3
nr_writeback 0
nr_unstable 0
nr_bounce 0
nr_vmscan_write 16557
pgpgin 5992958
pgpgout 3674444
pswpin 4297
pswpout 13350
pgalloc_dma 203495
pgalloc_normal 11341678
pgalloc_high 0
pgfree 11548090
pgactivate 886342
pgdeactivate 883820
pgfault 37704524
pgmajfault 4025
pgrefill_dma 335184
pgrefill_normal 10848190
pgrefill_high 0
pgsteal_dma 47787
pgsteal_normal 1912761
pgsteal_high 0
pgscan_kswapd_dma 44246
pgscan_kswapd_normal 1627840
pgscan_kswapd_high 0
pgscan_direct_dma 12545
pgscan_direct_normal 312576
pgscan_direct_high 0
pginodesteal 1292
slabs_scanned 2673920
kswapd_steal 1643732
kswapd_inodesteal 14384
pageoutrun 31142
allocstall 4905
pgrotated 13776
error message in log:
ERROR: invalid restriction selectivity: 0.000049
COMMAND: SELECT u.username, "ОС", u.user_id, u.user_level,u.user_posts,
u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim,
u.user_yim, u.user_regdate, u.user_msnm, u.user_jabber, u.user_viewemail,
u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar,
u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_ou_sig,
u.user_ou_avatar, u.user_show_jabber_status, p.*, pt.post_text,
pt.post_subject, pt.bbcode_uid
FROM posts p, users u, posts_text pt
WHERE p.topic_id = 1721
AND pt.post_id = p.post_id
AND u.user_id = p.poster_id
ORDER BY p.post_time ASC
LIMIT 15 OFFSET 0;
After postgres restart:
phpbb=# explain SELECT u.username, "OS", u.user_id, u.user_level,u.user_posts,
u.user_from, u.user_website, u.user_email, u.user_icq, u.user_aim,
u.user_yim, u.user_regdate, u.user_msnm, u.user_jabber, u.user_viewemail,
u.user_rank, u.user_sig, u.user_sig_bbcode_uid, u.user_avatar,
u.user_avatar_type, u.user_allowavatar, u.user_allowsmile, u.user_ou_sig,
u.user_ou_avatar, u.user_show_jabber_status, p.*, pt.post_text,
pt.post_subject, pt.bbcode_uid
FROM posts p, users u, posts_text pt
WHERE p.topic_id = 1721
AND pt.post_id = p.post_id
AND u.user_id = p.poster_id
ORDER BY p.post_time ASC
LIMIT 15 OFFSET 0;
QUERY PLAN
---------------------------------------------------------------------------------------------------------------
Limit (cost=264.27..264.30 rows=15 width=522)
-> Sort (cost=264.27..264.31 rows=16 width=522)
Sort Key: p.post_time
-> Nested Loop (cost=0.00..263.95 rows=16 width=522)
-> Nested Loop (cost=0.00..175.46 rows=16 width=371)
-> Index Scan using topic_id_posts_index on posts p
(cost=0.00..50.95 rows=16 width=56)
Index Cond: (topic_id = 1721)
-> Index Scan using posts_text_pkey on posts_text pt
(cost=0.00..7.77 rows=1 width=319)
Index Cond: (pt.post_id = p.post_id)
-> Index Scan using users_pkey on users u (cost=0.00..5.52
rows=1 width=151)
Index Cond: (u.user_id = p.poster_id)
(11 rows)
xeb@mail.ru writes:
> error message in log:
> ERROR: invalid restriction selectivity: 0.000049
[ blink... ] Surely it didn't really say that, because a moment's
glance at the code shows that it's impossible:
if (result < 0.0 || result > 1.0)
elog(ERROR, "invalid restriction selectivity: %f", result);
If it really did say that, then you have either seriously flaky hardware
or a broken compiler. In either case, us mere database weenies can't
help much.
regards, tom lane
> > error message in log: > > ERROR: invalid restriction selectivity: 0.000049 > > [ blink... ] Surely it didn't really say that, because a moment's > glance at the code shows that it's impossible: > > if (result < 0.0 || result > 1.0) > elog(ERROR, "invalid restriction selectivity: %f", result); > > If it really did say that, then you have either seriously flaky hardware > or a broken compiler. In either case, us mere database weenies can't > help much. > > regards, tom lane Really, it seems that is hardware trouble, beacause it works on virtual machine (KVM), which is buggy enough :( Thanks.