Обсуждение: getting execution plans with multiple database connections
Hi,
I am running OSDL-DBT3 test against PostgreSQL. I found performance
difference between the runs even though the data and queries are the
same. I tried to study this problem by getting execution plans at the
beginning of each test. The following script gets execution plan for 22
queries, except query 15.
i=1
while [ $i -le 22 ]
do
if [ $i -ne 15 ]; then
$dbdriver_pgsql_path/explain_single_query.sh $i
$run_dir/plan/power_query$i.txt
fi
let "i=$i+1"
done
The script 'explain_single_query.sh' takes 2 parameters: query_number
and output file name.
It works for power test(single database connection). In other words, I
can get execution plans for all the 21 queries if there is only one
stream connecting to the database.
But for the throughput test(multiple database connections), some query
execution plan files are blank. It seems to be random, for example, for
the run at:
http://khack.osdl.org/stp/277495/results/plan/
throughput_stream1_query14.txt, throughput_stream3_query4.txt,
throughput_stream3_query5.txt, throughput_stream4_query4.txt,
throughput_stream4_query18.txt are of size 0. And if I do the run
again, a different set of files are blank.
Can anyone give me an explanation?
Thanks,
--
Jenny Zhang
Open Source Development Lab Inc
12725 SW Millikan Way
Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31
Jenny Zhang <jenny@osdl.org> writes: > It works for power test(single database connection). In other words, I > can get execution plans for all the 21 queries if there is only one > stream connecting to the database. > But for the throughput test(multiple database connections), some query > execution plan files are blank. It seems to be random, for example, for > the run at: > http://khack.osdl.org/stp/277495/results/plan/ > throughput_stream1_query14.txt, throughput_stream3_query4.txt, > throughput_stream3_query5.txt, throughput_stream4_query4.txt, > throughput_stream4_query18.txt are of size 0. And if I do the run > again, a different set of files are blank. I wonder if you are not running out of kernel file table slots. The described behavior sounds rather like the explain-ing script is failing to notice that it couldn't open its output file. If you haven't bumped up the kernel's file table size (NFILE or similar parameter) and/or adjusted Postgres' max_files_per_process parameter downwards to hold Postgres to a small number of open files per backend, it's quite easy for Postgres to eat all your file table slots. Postgres itself generally will not complain (it has strategies for dealing with ENFILE failures), but an awful lot of other stuff will fall right over when it can't open files. regards, tom lane
Tom, Thanks for your reply. It does look like hitting limit of some system resource since it is random. But I am not sure if it is the NFILE. I changed max_files_per_process to 200 and increased open files to 16384. Here is my ulimit -a output: core file size (blocks, -c) 0 data seg size (kbytes, -d) unlimited file size (blocks, -f) unlimited max locked memory (kbytes, -l) unlimited max memory size (kbytes, -m) unlimited open files (-n) 16384 pipe size (512 bytes, -p) 8 stack size (kbytes, -s) 8192 cpu time (seconds, -t) unlimited max user processes (-u) 67587 virtual memory (kbytes, -v) unlimited And I still have the same problem. Since PG should fail if no more files can be opened, my test should fail. But I got all the other results back at the end of the run. Thanks, Jenny On Fri, 2003-08-15 at 11:46, Tom Lane wrote: > Jenny Zhang <jenny@osdl.org> writes: > > It works for power test(single database connection). In other words, I > > can get execution plans for all the 21 queries if there is only one > > stream connecting to the database. > > > But for the throughput test(multiple database connections), some query > > execution plan files are blank. It seems to be random, for example, for > > the run at: > > http://khack.osdl.org/stp/277495/results/plan/ > > throughput_stream1_query14.txt, throughput_stream3_query4.txt, > > throughput_stream3_query5.txt, throughput_stream4_query4.txt, > > throughput_stream4_query18.txt are of size 0. And if I do the run > > again, a different set of files are blank. > > I wonder if you are not running out of kernel file table slots. The > described behavior sounds rather like the explain-ing script is failing > to notice that it couldn't open its output file. If you haven't bumped > up the kernel's file table size (NFILE or similar parameter) and/or > adjusted Postgres' max_files_per_process parameter downwards to hold > Postgres to a small number of open files per backend, it's quite easy > for Postgres to eat all your file table slots. Postgres itself > generally will not complain (it has strategies for dealing with ENFILE > failures), but an awful lot of other stuff will fall right over when > it can't open files. > > regards, tom lane > > > ------------------------------------------------------- > This SF.Net email sponsored by: Free pre-built ASP.NET sites including > Data Reports, E-commerce, Portals, and Forums are available now. > Download today and enter to win an XBOX or Visual Studio .NET. > http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01 > _______________________________________________ > osdldbt-general mailing list > osdldbt-general@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/osdldbt-general -- Jenny Zhang Open Source Development Lab Inc 12725 SW Millikan Way Suite 400 Beaverton, OR 97005 (503)626-2455 ext 31
Jenny Zhang <jenny@osdl.org> writes:
> And I still have the same problem. Since PG should fail if no more
> files can be opened, my test should fail. But I got all the other
> results back at the end of the run.
No, you miss my point. Usually PG will *not* fail when it hits ENFILE.
The code is designed to close other open files until it can successfully
open the required file. Only a freshly-started backend (with no pool of
already-open files) is likely to report any actual failure from ENFILE.
So a database under load can run quite happily when there are zero free
file table slots in the kernel --- but everything else in the system is
likely to show random failures.
regards, tom lane