Re: [BUGS] Massive memory use for star query
On 16/04/11 01:59, Kevin Grittner wrote: Mark Kirkwood wrote: Here's a simplified example using synthetic data (see attached to generate if desired): Doesn't work for me: kgrittn@kgrittn-desktop:~/work/starjoin$ ./gendata.pl generate cat cannot open cat.dat: No such file or directory at ./gendata.pl line 17. Apologies Kevin, I stuffed up the edit to supposedly make it easier for you all to choose your own place to write the files (left one hard coded for the table 'cat'). Cheers Mark -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Massive memory use for star query
On 16/04/11 04:43, Tom Lane wrote: Mark Kirkwood writes: I've recently seen examples of star-like queries using vast amounts of memory in one of our production systems. Here's a simplified example using synthetic data (see attached to generate if desired): SET geqo_threshold = 14; SET from_collapse_limit = 14; SET join_collapse_limit = 14; Well, if you're going to do the above, you should be expecting the planner to eat a lot of memory. There is a reason why the default values of those parameters are significantly lower than that ... Ok - so with the settings at their defaults geqo chooses a semi-random plan, and at least one of those (for the production variant of this query anyway) eat massive (>100G) amounts of temp space - not really a suitable outcome either. I guess you have answered my first question - i.e yes this should eat massive amount of ram as written - however are you sure there is no memory leaking going on here? regards Mark -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Massive memory use for star query
On Sat, Apr 16, 2011 at 8:21 AM, Mark Kirkwood wrote: > > I guess you have answered my first question - i.e yes this should eat > massive amount of ram as written - however are you sure there is no memory > leaking going on here? The planner doesn't try to free up memory while it's working, it generally assumes that producing a plan is a short process and when it's done it'll free the whole context and that's enough. The basic problem is that the number of possible plans blows up combinatorically. That is with 14 tables there are 14! possible join orderings and more something like 3^(14!) possible join strategies -- actually more if you include things like whether to materialize and which keys to use and so on. The planner uses various heuristics to avoid combinatoric growth wherever it can but there's no way to completely avoid it. -- greg -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Massive memory use for star query
Greg Stark writes: > On Sat, Apr 16, 2011 at 8:21 AM, Mark Kirkwood > wrote: >> I guess you have answered my first question - i.e yes this should eat >> massive amount of ram as written - however are you sure there is no memory >> leaking going on here? > The planner uses various heuristics to avoid combinatoric growth > wherever it can but there's no way to completely avoid it. Yeah. The collapse_limit variables can be seen as another heuristic to deal with this type of problem: they artificially limit the number of combinations considered by forcing the join search to be broken down into subproblems. The trouble of course is that this breakdown is pretty stupid and can easily prevent the best join order from ever being considered. If you've got a small number of such query types that you can afford to spend some manual effort on, here's what I'd do: 1. With those three planner variables cranked up to more than the number of relations in the query (if possible), run an EXPLAIN, or better EXPLAIN ANALYZE so you can confirm you get a good plan. 2. Observe the join order selected in the good plan. 3. Rearrange your query so that the tables are explicitly JOINed in that order. Don't use the FROM-comma-list style. 4. Now, in your production app, *reduce* join_collapse_limit to a small value, maybe even 1, to force the syntactic JOIN order to be followed. (Obviously, don't keep it there when running queries you haven't hand-optimized this way.) This will force the planner to consider only small subproblems, which will make it both much faster and much less memory-hungry than when it's trying to solve a large join problem from scratch. regards, tom lane -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
[BUGS] BUG #5983: creating accounts
The following bug has been logged online: Bug reference: 5983 Logged by: Yanish Email address: yanish_3...@hotmail.com PostgreSQL version: 9.0 Operating system: Windows Seven 64-bit Description:creating accounts Details: hello i have been using postgreSQL for my university studies and now i have a problem on windows seven as it is creating a new account called Postgres in my user account and it is shown on my welcome screen.why is it such??i dont want a new account to be able to use postgres.. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Massive memory use for star query
On 15/04/11 16:35, Mark Kirkwood wrote:
Here's a simplified example using synthetic data (see attached to
generate if desired):
For anyone else who might be want to play with this:
Patch with correction to make the directory reassignment work correctly,
plus an additional comment in the README mentioning the need to set this
in the generator and loading scripts.
Thanks
Mark
diff -c starjoin.orig/gendata.pl starjoin/gendata.pl
*** starjoin.orig/gendata.pl 2011-04-17 10:55:28.252595067 +1200
--- starjoin/gendata.pl 2011-04-17 10:55:48.684595066 +1200
***
*** 14,20
my %prevcatids;
print "generate cat\n";
! open(FH, "> /data0/dump/cat.dat") || die ("cannot open cat.dat: $!");
for ($i = 0; $i < $catrows; $i++) {
$catid = $i;
--- 14,20
my %prevcatids;
print "generate cat\n";
! open(FH, "> $dir/cat.dat") || die ("cannot open cat.dat: $!");
for ($i = 0; $i < $catrows; $i++) {
$catid = $i;
diff -c starjoin.orig/README starjoin/README
*** starjoin.orig/README 2011-04-17 10:55:28.252595067 +1200
--- starjoin/README 2011-04-17 10:56:29.292595065 +1200
***
*** 1,5
--- 1,6
Setup for (hopefully) excessive join memory use.
+ $ vi gendata.pl load.sql# set dump/load directory
$ ./gendata.pl
$ psql test < schema.sql
$ psql test < load.sql
--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] Massive memory use for star query
On 17/04/11 02:58, Tom Lane wrote: Greg Stark writes: The planner uses various heuristics to avoid combinatoric growth wherever it can but there's no way to completely avoid it. Yeah. The collapse_limit variables can be seen as another heuristic to deal with this type of problem: they artificially limit the number of combinations considered by forcing the join search to be broken down into subproblems. The trouble of course is that this breakdown is pretty stupid and can easily prevent the best join order from ever being considered. If you've got a small number of such query types that you can afford to spend some manual effort on, here's what I'd do: 1. With those three planner variables cranked up to more than the number of relations in the query (if possible), run an EXPLAIN, or better EXPLAIN ANALYZE so you can confirm you get a good plan. 2. Observe the join order selected in the good plan. 3. Rearrange your query so that the tables are explicitly JOINed in that order. Don't use the FROM-comma-list style. 4. Now, in your production app, *reduce* join_collapse_limit to a small value, maybe even 1, to force the syntactic JOIN order to be followed. (Obviously, don't keep it there when running queries you haven't hand-optimized this way.) This will force the planner to consider only small subproblems, which will make it both much faster and much less memory-hungry than when it's trying to solve a large join problem from scratch. We've sort of done an equivalent thing as a temporary fix - restricted the page generating these queries to one or two keywords to tame the number of tables joined in. We are only seeing this type of query being generated in a very specific part of the application (keyword search), and I've been encouraging a redesign in that area anyway as I don't believe it is necessary to require so many joins to achieve what they wish to do - so this is really the clincher for a redesign. I will get 'em to reduce the *collapse limits too. Thanks to all of you for your help, regards Mark -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Re: [BUGS] BUG #5983: creating accounts
On 04/15/11 12:33 PM, Yanish wrote: The following bug has been logged online: Bug reference: 5983 Logged by: Yanish Email address: yanish_3...@hotmail.com PostgreSQL version: 9.0 Operating system: Windows Seven 64-bit Description:creating accounts Details: hello i have been using postgreSQL for my university studies and now i have a problem on windows seven as it is creating a new account called Postgres in my user account and it is shown on my welcome screen.why is it such??i dont want a new account to be able to use postgres.. the 'bug' here is that the 'postgres' service account its creating became a member of 'Users', which it wasn't on XP. the postgresql server does in fact need its own account to run the database service, much like many other services do, its just the LSP (local security policy) is configured to hide these accounts from the 'welcome' login screen. go to control panel -> Administration Tools -> Computer Management. In Computer Manaagement, find Local Users and Groups -> Users, and find the 'postgres' user, right click, Properties, and select hte 'member of' tab. select Users and click Remove to remove Users from this list (it should be empty, this account is to be used for NOTHING other than the postgres database service). click Ok, and close the dialogs. Now, Postgres should no longer show up on the Welcome screen when you log off. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
