Re: Multithreaded query onto 4 postgresql instances - Mailing list pgsql-general

From Alban Hertroys
Subject Re: Multithreaded query onto 4 postgresql instances
Date
Msg-id E354E152-578D-4425-961B-36C9AABC6D40@solfertje.student.utwente.nl
Whole thread Raw
In response to Re: Multithreaded query onto 4 postgresql instances  (Alessandro Candini <candini@meeo.it>)
Responses Re: Multithreaded query onto 4 postgresql instances  (Alessandro Candini <candini@meeo.it>)
List pgsql-general
On 14 Feb 2011, at 9:38, Alessandro Candini wrote:

> I performed tests with a query returning more or less 100000 records and using my C module I obtain the following
results(every test performed cleaning cache before): 
> - single db: 9.555 sec
> - splitted in 4: 5.496 sec

Is that a single query on that one DB compared to 4 queries on 4 DB's? How does a single DB with 4 parallel queries
perform?I'd expect that to win from 4 DB's, due to the overhead those extra DB instances are generating. 
I do suppose you tried tuning that DB before you started making things complicated?

> So I think this can be a good approach...
> I have already read this
>
> http://www.postgresql.org/docs/9.0/interactive/xfunc-c.html

Did you read all the way to section 35.9.10? That explains how to create SRF's like yours, including examples. If the
stepfrom simple functions to SRF's is too large for you, create a few sample-functions to learn how the intermediary
stepswork. We can't upload the info to your brain, after all - you'll have to do the learning part by yourself. 

> I posted it in my previous thread. But it is not clear to me how to embed my C function into postgresql.
> I mean, I know how to compile and insert it into postgresql, I've already done it for simple function which return a
boolean,for example. 
> But it is not clear to me how to do this if I want as return a complete table, or set of rows...
> Can you give me a minimalistic example?

I'm not aware of any difference between adding a function returning a scalar vs. one that returns a set. You have to
createa wrapper function to your library so that PG knows about your function, but apart from the return types (BOOLEAN
vs.SETOF something), that declaration is exactly the same. 

> By the way, my goal is to perform a SELECT query in the smallest time possible.
> For that reason an SQL function is not good beacuse slower than a C function...

I have no idea what you're referring to, since you're top-posting, but I never suggested to use SQL functions. I just
pointedout that the document you linked was about SQL functions instead of C functions, in the assumption that was what
wascausing your confusion. You didn't explain what you were confused about after all, I had to guess. 

>> On 10 Feb 2011, at 9:01, Alessandro Candini wrote:
>>
>>> I have installed 4 different instances of postgresql-9.0.2 on the same machine, on ports 5433, 5434, 5435, 5436.
>> I do hope you intend to put those databases on different machines eventually, or some such? Otherwise you probably
didn'tgain anything by splitting your database up like that - you've just reduced the available resources on that
singlemachine. 
>>
>>> Why I have to do something tricky like this is long too explain...
>> It would help to know what you're trying to achieve by splitting your database up like this. We don't need the full
story,just a summary is fine; Maybe this is some experimental setup that's more related to multi-threading than to the
actualdatabase design? Maybe management smoked something outlandish and put you up with this? Maybe this is a
macroscopicattempt to table partitioning? 
>>
>>> Then I have developed a C function using libpq which creates 4 threads, each one which query a 1/4 of the db. After
thatI merge the results in one single response. 
>>>
>>> My function works fine, but I need to include it inside a postgresql instance in order to launch it as a normal SQL
query(SELECT myfunc(...);). 
>>> I have read the documentation here
http://www.postgresql.org/docs/9.0/static/xfunc-sql.html#XFUNC-SQL-FUNCTIONS-RETURNING-SET,but I'm pretty confuse and I
don'tknow if this is the right way to accomplish my goal. 
>> What are you confused about? That's a chapter about set-returning functions written in SQL - perhaps you're looking
forinformation about set-returning functions from an external library like yours? Perhaps you're looking for:
http://www.postgresql.org/docs/9.0/interactive/xfunc-c.html
>>
>> You say you don't know if this is the right way to accomplish your goal - which is...?
>>
>>> Have you got any ideas or suggestions?
>>
>> As others have suggested, you should probably have a look at table-partitioning, possibly in combination with
tablespacesif you want to divide your database among multiple disks/filesystems. Putting them in different servers on
thesame hardware is probably not going to be a very good solution. As you've already found out, it makes querying the
datasilly difficult. But, we don't know the reason you're doing that of course. 
>>
>> Alban Hertroys
>>
>> --
>> If you can't see the forest for the trees,
>> cut the trees and you'll see there is no forest.
>>
>>
>>
>>
>>
>
>
> --
> Alessandro Candini
>
> MEEO S.r.l.
> Via Saragat 9
> I-44122 Ferrara, Italy
> Tel: +39 0532 1861501
> Fax: +39 0532 1861637
> http://www.meeo.it
>
> ========================================
> "ATTENZIONE:le informazioni contenute in questo messaggio sono
> da considerarsi confidenziali ed il loro utilizzo è riservato unicamente
> al destinatario sopra indicato. Chi dovesse ricevere questo messaggio
> per errore è tenuto ad informare il mittente ed a rimuoverlo
> definitivamente da ogni supporto elettronico o cartaceo."
>
> "WARNING:This message contains confidential and/or proprietary
> information which may be subject to privilege or immunity and which
> is intended for use of its addressee only. Should you receive this
> message in error, you are kindly requested to inform the sender and
> to definitively remove it from any paper or electronic format."
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Alban Hertroys

--
Screwing up is an excellent way to attach something to the ceiling.


!DSPAM:737,4d59822411731063283927!



pgsql-general by date:

Previous
From: Tom Lane
Date:
Subject: Re: Using Bitmap scan instead of Seq scan
Next
From: Edwin Giraldo
Date:
Subject: ...