473,809 Members | 2,617 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

different results inside transactions

Tim
Can somebody clue me in on this? I've been working on this for nearly a
week now and it's driving me bunkers.

I am using PostGIS/Mapserver and trying to get a simple demo running
with just one PostGIS layer. My layer is never drawn and the best I can
tell is the results from this transaction (not the exact one this is the
simplest form I've narrowed it down to):

begin transaction;
declare mycursor binary cursor for select asbinary(center line) from road_segments;
fetch all from mycursor;
end transaction;

if I just do select asbinary(center line) from road_segments, I get the
WKB representation of the geometry lines.

if I run the transaction, psql just gives me rows of blank lines. If I
use PgAdmin, I get

Query result with 0 rows discarded.
Query result with 0 rows discarded.
Query result with 5 rows discarded.

Query returned successfully with no result in 331 ms.

Is this possibly the problem? Anybody using PostGIS and Mapserver?

Thanks!

Tim

On Tue, Sep 14, 2004 at 11:11:38AM -0700, Jeffrey W. Baker wrote:
On Tue, 2004-09-14 at 10:28, Vivek Khera wrote:
>>>> "SW" == Shane Wright <Shane> writes:


SW> But, we have now taken the plunge and I'm in a position to do some
SW> benchmarking to actually get some data. Basically I was wondering if
SW> anyone else had any particular recommendations (or requests) about the
SW> most useful kinds of benchmarks to do.

I did a bunch of benchmarking on a 14 disk SCSI RAID array comparing
RAID 5, 10, and 50. My tests consisted of doing a full restore of a
30Gb database (including indexes) and comparing the times to do the
restore, the time to make the indexes, and the time to vacuum. Then I
ran a bunch of queries.

It was damn near impossible to pick a 'better' RAID config, so I just
went with RAID5.

You can find many of my posts on this topic on the list archives from
about august - october of last year.

Basically, you have to approach it holistically to tune the system: Pg
config parameters, memory, and disk speed are the major factors.

That and your schema needs to be not idiotic. :-)


I've recently bee frustrated by this topic, because it seems like you
can design the hell out of a system, getting everything tuned with micro
and macro benchmarks, but when you put it in production the thing falls
apart.

Current issue:

A dual 64-bit Opteron 244 machine with 8GB main memory, two 4-disk RAID5
arrays (one for database, one for xlogs). PG's config is extremely
generous, and in isolated benchmarks it's very fast.

But, in reality, performance is abyssmal. There's something about what
PG does inside commits and checkpoints that sends Linux into a catatonic
state. For instance here's a snapshot of vmstat during a parallel heavy
select/insert load:

procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
3 0 216 13852 39656 7739724 0 0 820 2664 2868 2557 16 2 74 7
0 0 216 17580 39656 7736460 0 0 3024 4700 3458 4313 42 6 52 0
0 0 216 16428 39676 7737324 0 0 840 4248 3930 4516 0 4 89 8
0 1 216 18620 39672 7736920 0 0 7576 516 2738 3347 1 4 55 39
0 0 216 14972 39672 7738960 0 0 1992 2532 2509 2288 2 3 93 3
0 0 216 13564 39672 7740592 0 0 1640 2656 2581 2066 1 3 97 0
0 0 216 12028 39672 7742292 0 0 1688 3576 2072 1626 1 2 96 0
0 0 216 18364 39680 7736164 0 0 1804 3372 1836 1379 1 4 96 0
0 0 216 16828 39684 7737588 0 0 1432 2756 2256 1720 1 3 94 2
0 0 216 15452 39684 7738812 0 0 1188 2184 2384 1830 1 2 97 0
0 1 216 15388 39684 7740104 0 0 1336 2628 2490 1974 2 3 94 2
6 0 216 15424 39684 7740240 0 0 104 3472 2757 1940 3 2 92 2
0 0 216 14784 39700 7741856 0 0 1668 3320 2718 2332 0 3 97 0

You can see there's not much progress being made there. In the
presence of a farily pathetic writeout, there's a tiny trickle of disk
reads, userspace isn't making any progress, the kernel isn't busy, and
few processes are in iowait. So what the heck is going on?

This state of non-progress persists as long as the checkpoint subprocess
is active. I'm sure there's some magic way to improve this but I
haven't found it yet.

PS this is with Linux 2.6.7.

Regards,
jwb

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

Nov 23 '05 #1
0 1064

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
439
by: Avanish Pandey | last post by:
Hello All We have 3 differen services (in 3 different server) Service A,B,C . We want to implement distributed transaction when call methods of B and C from A. Is it possible? if yes then how? I have read the doc regarding this: http://www.developer.com/net/asp/article.php/3385631 but it will not work when methods are in different services on
12
6396
by: Steve Elliott | last post by:
I have a query set up to gather together data between two specified dates. Shown in the query column as: Between #24/09/2004# And #01/10/2004# Is it possible to enter several different date ranges, ie between 24/09/2004 and 01/10/2004 together with 05/10/2004 and 07/10/2004 ? If I enter the "Between" criteria on different lines it returns no data.
0
1065
by: Dante | last post by:
I have moved a heavy calculation inside my application to multiple threads. Previously I was saving the results to a file and after finishing I collected them all in one mdb file. Unfortunately this approach is too slow so I decided to save all in one mdb file; now each thread is writing into its own temporary table inside the MDB to avoid thread collisions and locking. But, is this approach thread safe? Currently I am only guessing, but...
0
1258
by: Patrox | last post by:
hi ! in mysql doc it is stated that : "MySQL supports local transactions (within a given client connection) " ref : http://dev.mysql.com/doc/refman/5.1/en/transactional-commands.html but what happens if for instance 2 different users tries to modify same data in 2 different transactions in 2 different connections ? does the last one which finish simply "win" (erasing the other's work) or does mysql is able to lock rows in a correct way...
10
1819
by: ypjofficial | last post by:
Hello All, since the programs' stack is shared among all the function inside the program, I was just doing some R&D to see whether the same stack space is used for the variables inside the different functions are not. below is my code //program start void fun1() {int i;
2
4203
gauravgmbhr
by: gauravgmbhr | last post by:
hi i am having problem using transactions inside a function i have to insert records in two tables which have interdependent foreign keys. so the insertion should be done inside a transaction and i need to use a single stored procedure to do that. SO can someone help me by telling me how to use transactions inside a function or f possible kindly email to gaurav.gambhir.er@gmail.com
3
3970
by: Emma Middlebrook | last post by:
Hi there, I've been trying to implement a repeater control in an ASP.NET 2 page but I can't seem to get the layout exactly how I want and I'm not sure if it's something that I am doing wrong or maybe the repeater control doesn't have the capabilities...? The page needs to display a custom number of sections that appear the same but have different data..
4
1990
by: Barbiturico76 | last post by:
Hi guys, anybody knows if it is possible to generate a <div id="myMap" style="width: 400; height:300; left: 0px; top: 0px;" /> inside your intranet site, to represent a programmatically generated map with the "http://dev.virtualearth.net/mapcontrol/mapcontrol.ashx?v=6.1" script reference?
3
2985
by: tc3driver | last post by:
Hello Folks, I have an interesting problem, let me start off with, this database is not of my design, is an absolute mess, and there is nothing I can do about it. SELECT distinct(`Tickets`.`EffectiveId`), `Tickets`.`Subject`, `Tickets`.`Created`, `Transactions`.`OldValue`, `Transactions`.`NewValue`, `Transactions`.`Created` as `Tcreated`, `ObjectCustomFieldValues`.`Content` as `sev`, `ObjectCustomFieldValues`.`Content` as `tt` from...
0
9603
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,...
0
10640
Oralloy
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...
0
10120
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9200
agi2029
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...
1
7662
isladogs
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...
0
6881
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5689
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4332
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3861
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.