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.t xt
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_stre am1_query14.txt , throughput_stre am3_query4.txt,
throughput_stre am3_query5.txt, throughput_stre am4_query4.txt,
throughput_stre am4_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
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster 2 2059
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_p rocess 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 <je***@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_stre am1_query14.txt , throughput_stre am3_query4.txt, throughput_stre am3_query5.txt, throughput_stre am4_query4.txt, throughput_stre am4_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_p rocess 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/psa00...t_072303_01/01 _______________ _______________ _______________ __ osdldbt-general mailing list os************* @lists.sourcefo rge.net https://lists.sourceforge.net/lists/...sdldbt-general
--
Jenny Zhang
Open Source Development Lab Inc
12725 SW Millikan Way
Suite 400
Beaverton, OR 97005
(503)626-2455 ext 31
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Jenny Zhang <je***@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
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: trotter |
last post by:
I want to know if there is a "best-practice" for setting up Database
Maintenance Plans in SQL Server 7 or 2000. To be more specific, I want
to know the order in which I complete the tasks. Do I complete
optimization first, then integrity checks, then translog backup, then
full backup??? OR is there a better order which should be used?
Should I ALWAYS backup the transaction Log before I complete a full
database backup, and if so, why??
...
|
by: James Walker |
last post by:
Hi there - hoping someone can help me here!
I have a database that has been underperforming on a number of queries
recently - in a test environment they take only a few seconds, but on
the live data they take up to a minute or so to run. This is using the
same data.
Every evening a copy of the live data is copied to a backup 'snapshot'
database on the same server and also, on this copy the queries only
take a second or so to run....
|
by: Jenny Zhang |
last post by:
The osdl-dbt3 test starts with building and vacuuming the database.
The execution plans were taken after the vacuuming.
I did two tests with the same database parameters:
1. run two osdl-dbt3 runs on one box without rebooting the stystem.
Though the execution plans are the same, the costs are different. The
system status are different for the two runs, for example, some page
cache are not released after the first one. Does that make...
|
by: Evan Smith |
last post by:
Scenario: new functionality was recently deployed in a 3-tier business
application. Since deployment, CPU use has shot up to very high
levels.
Using event monitors to try to track down the culprits hasn't fingered
any bad query in particular who's hogging CPU. One interesting anomaly
is that there is a single type of query that averages 70 seconds of
real time (with highs of 100-300 seconds) while expending zero seconds
of CPU. The...
|
by: Abhishek Srivastava |
last post by:
Hello All,
In IIS 6.0 We have a concept of worker processes and application pools.
As I understand it, we can have multiple worker process per appliction
pool. Each worker process is dedicated to a pool. If I assign only one
application to a applicaton pool and have multiple worker processes
assigned to that pool. Will my application be processed by many worker
processes?
| |
by: Ina Schmitz |
last post by:
Hi NG,
does IBM Universal Database 8.2 make any difference between actual and
estimated execution plans like in SQL Server ("set showplan_all on" for
estimated execution plan and "set statistics profile on" for actual
execution plan)? Does "explain plan selection for" generate the *estimated*
execution plan? Didn't find any distinction of actual or estimated execution
plans in the information center.
Thanks for help,
|
by: Joachim Pense |
last post by:
Is there recommended way to get the execution plan for queries involving
global temporary tables (from the UNIX command line or within a script)?
I run the queries in Perl scripts, and the only way that comes to my mind
is creating permanent tables that look like the temporary ones and run test
versions of the scripts that use the permanent tables; then I can let the
script stop wherever I want and start db2expln.
But this way looks a...
|
by: Yelena Varshal via AccessMonster.com |
last post by:
Hello,
What are the pre-requisites / conditions for the ability to create
multiple connections to MS ACCESS database and what is the precedence of its
application? adModeShareDenyNone in the code, Exclusive checkbox in the ODBC
source, Tools->Options->Advanced->Shared in the Database Options, what else?
I have a problem when multiple connections could not be opened at the same
time from my code on one server but could be on another server....
|
by: sqlgirl |
last post by:
Hi,
We are trying to solve a real puzzle. We have a stored procedure that
exhibits *drastically* different execution times depending on how its
executed.
When run from QA, it can take as little as 3 seconds. When it is
called from an Excel vba application, it can take up to 180 seconds.
Although, at other times, it can take as little as 20 seconds from
Excel.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |