Re: [PERFORM] DELETE takes too much memory

[Kouber Saparev]

No. There are AFTER triggers on other tables that write to this one though.
It is an audits table, so I omitted all the foreign keys on purpose.

2016-07-04 20:04 GMT+03:00 Alvaro Herrera :

> Kouber Saparev wrote:
> > I tried to DELETE about 7 million rows at once, and the query went up to
> > 15% of the RAM (120 GB in total), which pushed some indexes out and the
> > server load went up to 250, so I had to kill the query.
> >
> > The involved table does not have neither foreign keys referring to other
> > tables, nor other tables refer to it. The size of the table itself is 19
> GB
> > (15% of 120 GB). So why the DELETE tried to put the entire table in
> memory,
> > or what did it do to take so much memory?
>
> Are there triggers in the table?  Deferred triggers in particular can
> use memory.
>
> --
> Álvaro Herrerahttp://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

Re: [PERFORM] DELETE takes too much memory

[Alvaro Herrera]

Kouber Saparev wrote:
> I tried to DELETE about 7 million rows at once, and the query went up to
> 15% of the RAM (120 GB in total), which pushed some indexes out and the
> server load went up to 250, so I had to kill the query.
> 
> The involved table does not have neither foreign keys referring to other
> tables, nor other tables refer to it. The size of the table itself is 19 GB
> (15% of 120 GB). So why the DELETE tried to put the entire table in memory,
> or what did it do to take so much memory?

Are there triggers in the table?  Deferred triggers in particular can
use memory.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

[PERFORM] DELETE takes too much memory

[Kouber Saparev]

I tried to DELETE about 7 million rows at once, and the query went up to
15% of the RAM (120 GB in total), which pushed some indexes out and the
server load went up to 250, so I had to kill the query.

The involved table does not have neither foreign keys referring to other
tables, nor other tables refer to it. The size of the table itself is 19 GB
(15% of 120 GB). So why the DELETE tried to put the entire table in memory,
or what did it do to take so much memory?

I am using 9.4.5.

Regards,
--
Kouber Saparev

Re: [HACKERS] [PERFORM] 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

[Vladimir Borodin]

> 13 июня 2016 г., в 21:58, Vladimir Borodin  написал(а):
> 
>> 
>> 13 июня 2016 г., в 0:51, Andres Freund > > написал(а):
>> 
>> Hi Vladimir,
>> 
>> Thanks for these reports.
>> 
>> On 2016-06-13 00:42:19 +0300, Vladimir Borodin wrote:
>>> perf report -g -i pg9?_all.data >/tmp/pg9?_perf_report.txt
>> 
>> Any chance you could redo the reports with --no-children --call-graph=fractal
>> added? The mode that includes child overheads unfortunately makes the
>> output hard to interpet/compare.
> 
> Of course. Not sure if that is important but I upgraded perf for that 
> (because --no-children option was introduced in ~3.16), so perf record and 
> perf report were done with different perf versions.
> 
> 
> 
> 
> 
> Also I’ve done the same test on same host (RHEL 6) but with 4.6 kernel/perf 
> and writing perf data to /dev/shm for not loosing events. Perf report output 
> is also attached but important thing is that the regression is not so 
> significant:
> 
> root@pgload05g ~ # uname -r
> 4.6.0-1.el6.elrepo.x86_64
> root@pgload05g ~ # cat /proc/sys/kernel/sched_autogroup_enabled
> 1
> root@pgload05g ~ # /tmp/run.sh
> RHEL 69.4 71634   0.893
> RHEL 69.5 54005   1.185
> RHEL 69.6 65550   0.976
> root@pgload05g ~ # echo 0 >/proc/sys/kernel/sched_autogroup_enabled
> root@pgload05g ~ # /tmp/run.sh
> RHEL 69.4 73041   0.876
> RHEL 69.5 60105   1.065
> RHEL 69.6 67984   0.941
> root@pgload05g ~ #
> 
> 
> 
> 

Andres, is there any chance that you would find time to look at those results? 
Are they actually useful?

> 
> 
>> 
>>> The results from pg9?_perf_report.txt are attached. Note that in all cases 
>>> some events were lost, i.e.:
>>> 
>>> root@pgload05g ~ # perf report -g -i pg94_all.data 
>>> >/tmp/pg94_perf_report.txt
>>> Failed to open [vsyscall], continuing without symbols
>>> Warning:
>>> Processed 537137 events and lost 7846 chunks!
>> 
>> You can reduce the overhead by reducing the sampling frequency, e.g. by
>> specifying -F 300.
>> 
>> Greetings,
>> 
>> Andres Freund
>> 
>> 
>> -- 
>> Sent via pgsql-hackers mailing list (pgsql-hack...@postgresql.org 
>> )
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-hackers 
>> 
> 
> 
> --
> May the force be with you…
> https://simply.name 

--
May the force be with you…
https://simply.name

Re: [PERFORM] Random slow queries

[devel . brain99]

On 29 June 2016 at 22:20, Tom Lane  wrote:
> Don't know if it would be practical for you at all, but if you could
> attach to a process that's stuck like this with a debugger and get a stack
> trace, that would probably be very informative.

It seems I have found the cause of my issues: my antivirus software.
When I tried to debug a stuck process, the currently executing code was in
some DLL without debugging information etc. When checking where the DLL came
from, it appeared to be from the security software I had installed.

I had been meaning to change because I have had some performance issues in
the past (nowhere near as bad as this issue though!) but hadn't yet gotten
around to it. After switching this weekend, the issue went away completely.

Even though I had previously noticed some performance issues with my
antivirus, I must say this still is a very weird failure mode - especially
as it still occurred if I disabled all realtime protection (one of the
first things I tried, even if not mentioned in my earlier emails).

Thanks for the time spent trying to help!

Best regards,
Roel



-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance