>
> It's always good to hear when these things work out. Thanks for
> reporting back.
>
> Using the set-based nature of relational databases to your advantage,
> writing smarter queries that do more work server-side with fewer
> round-trips, and effective batching can make a huge difference.
>
Gla
On 23/05/11 12:09, Aren Cambre wrote:
> Also, thanks for the advice on batching my queries. I am now using a
> very efficient bulk data read and write methods for Postgres.
>
> My program bulk reads 100,000 rows, processes those rows (during which
> it does a few SELECTs), and then writes 100,000
Also, thanks for the advice on batching my queries. I am now using a very
efficient bulk data read and write methods for Postgres.
My program bulk reads 100,000 rows, processes those rows (during which it
does a few SELECTs), and then writes 100,000 rows at a time.
It cycles through this until it
Just wanted to again say thanks for everyone's help.
The main problem was that my program was running in serial, not parallel,
even though I thought I used a textbook example of PLINQ. Your assistance
helped me get to the point where I could conclusively determine everything
was running in serial.
Just want to again say thanks for this query. It seriously sped up part of
my program.
Aren
On Thu, May 12, 2011 at 1:27 PM, Aren Cambre wrote:
> This is a perfect example of a place where you could push some work out of
>> the application and into the database. You can consolidate your 1 to 10
On 05/12/2011 11:07 AM, Tom Lane wrote:
I find it odd that replacing the subquery with a temp table helps,
though, because (unless you stuck in an ANALYZE you didn't mention)
it would have no stats at all about the number of groups in the temp
table.
I did have an analyze initially for exactly
Shaun Thomas writes:
> On 05/12/2011 09:51 AM, Tom Lane wrote:
>> It does. I was a bit surprised that Shaun apparently got a plan that
>> didn't include a materialize step, because when I test a similar query
>> here, I get:
> Remember when I said "old version" that prevented us from using CTEs?
>
> This is a perfect example of a place where you could push some work out of
> the application and into the database. You can consolidate your 1 to 101
> queries into a single query. If you use:
>
> WHERE rte_nm='SH71' AND rm >= 206 AND rm <= 306 ORDER BY abs(rm - 256), rm
> - 256 DESC LIMIT 1
>
Everyone,
Just wanted to say thanks for your help with my performance question. You
have given me plenty of things to investigate. Further, I think the problem
is almost certainly with my app, so I need to do more work there!
I really like the idea of just loading everything in memory and then du
On Wed, May 11, 2011 at 9:17 PM, Aren Cambre wrote:
> *2. Not TxDPS reference markers correspond to TxDOT reference markers.*
>
> Now, if I've matched a route, I have to find the reference marker.
>
> The TxDOT database is pretty good but not 100% complete, so some TxDPS
> tickets' reference mark
On 05/12/2011 09:51 AM, Tom Lane wrote:
> It does. I was a bit surprised that Shaun apparently got a plan that
> didn't include a materialize step, because when I test a similar query
> here, I get:
Remember when I said "old version" that prevented us from using CTEs?
We're still on 8.2 (basical
Josh Berkus writes:
> On 5/11/11 3:04 PM, Shaun Thomas wrote:
>> The original query, with our very large tables, ran for over *two hours*
>> thanks to a nested loop iterating over the subquery. My replacement ran
>> in roughly 30 seconds. If we were using a newer version of PG, we could
>> have us
On 05/12/2011 03:30 AM, Michael Graham wrote:
Do you happen to produce slides for these lunch n learns or are they
more informal than that? I guess you can work out where I'm going with
this ;)
Oh of course. I use rst2s5 for my stuff, so I have the slideshow and
also generate a PDF complete
On Wed, 2011-05-11 at 17:04 -0500, Shaun Thomas wrote:
> We hold regular Lunch'n'Learns for our developers to teach them the
> good/bad of what they're doing, and that helps significantly. Even
> hours later, I see them using the techniques I showed them. The one
> I'm presenting soon is entitled
12.05.11 06:18, Aren Cambre ???(??):
> Using one thread, the app can do about 111 rows per second, and it's
> only exercising 1.5 of 8 CPU cores while doing this. 12,000,000
rows /
> 111 rows per second ~= 30 hours.
I don't know how I missed that. You ARE maxing out one
On 5/11/2011 9:17 PM, Aren Cambre wrote:
So here's what's going on.
If I were doing this, considering the small size of the data set, I'd
read all the data into memory.
Process it entirely in memory (with threads to saturate all the
processors you have).
Then write the results to the DB.
On Wed, May 11, 2011 at 9:20 PM, Aren Cambre wrote:
>> Using unix sockets, you can expect about 10-20.000 queries/s on small
>> simple selects per core, which is quite a feat. TCP adds overhead, so it's
>> slower. Over a network, add ping time.
>
> I'm talking to a Postgres on localhost, so in the
>
>
> I suspect your app is doing lots of tiny single-row queries instead of
>> efficiently batching things. It'll be wasting huge amounts of time
>> waiting for results. Even if every query is individually incredibly
>> fast, with the number of them you seem to be doing you'll lose a LOT of
>> ti
>
> > Using one thread, the app can do about 111 rows per second, and it's
> > only exercising 1.5 of 8 CPU cores while doing this. 12,000,000 rows /
> > 111 rows per second ~= 30 hours.
>
> I don't know how I missed that. You ARE maxing out one cpu core, so
> you're quite right that you need more
>
> I suspect your app is doing lots of tiny single-row queries instead of
> efficiently batching things. It'll be wasting huge amounts of time
> waiting for results. Even if every query is individually incredibly
> fast, with the number of them you seem to be doing you'll lose a LOT of
> time if y
On 5/11/11 3:04 PM, Shaun Thomas wrote:
> The original query, with our very large tables, ran for over *two hours*
> thanks to a nested loop iterating over the subquery. My replacement ran
> in roughly 30 seconds. If we were using a newer version of PG, we could
> have used a CTE. But do you get wh
Original message
>Date: Wed, 11 May 2011 17:04:50 -0500
>From: pgsql-performance-ow...@postgresql.org (on behalf of Shaun Thomas
>)
>Subject: Re: [PERFORM] Postgres refusing to use >1 core
>To:
>Cc: Scott Marlowe ,Craig Ringer
>,Aren Cambre
>,
>
On 05/11/2011 02:53 PM, gnuo...@rcn.com wrote:
So, the $64 question: how did you find an engagement where, to bend
Shakespeare, "first thing we do, is kill all the coders" isn't
required?
It's just one of those things you have to explain. Not just how to fix
it, but *why* doing so fixes it.
On Wed, May 11, 2011 at 1:53 PM, wrote:
> So, the $64 question: how did you find an engagement where, to bend
> Shakespeare, "first thing we do, is kill all the coders" isn't required?
> This RBAR mentality, abetted by xml/NoSql/xBase, is utterly pervasive. They
> absolutely refuse to lear
Original message
>Date: Wed, 11 May 2011 11:04:49 -0500
>From: pgsql-performance-ow...@postgresql.org (on behalf of Shaun Thomas
>)
>Subject: Re: [PERFORM] Postgres refusing to use >1 core
>To: Scott Marlowe
>Cc: Craig Ringer ,Aren Cambre
>,
>
>On 05/1
This is a problem I encounter constantly wherever I go. Programmer
selects millions of rows from giant table. Programmer loops through
results one by one doing some magic on them. Programmer submits queries
back to the database. Even in batches, that's going to take ages.
Reminds me of a
On 05/10/2011 11:26 PM, Scott Marlowe wrote:
I.e. don't grab 1,000 rows and work on them on the client side and
then insert data, do the data mangling in the query in the database.
My experience has been that moving things like this into the database
can result in performance gains of several fa
I suspect your app is doing lots of tiny single-row queries instead of
efficiently batching things. It'll be wasting huge amounts of time
waiting for results. Even if every query is individually incredibly
fast, with the number of them you seem to be doing you'll lose a LOT of
time if you loop o
On Tue, May 10, 2011 at 7:35 PM, Craig Ringer
wrote:
> On 11/05/11 05:34, Aren Cambre wrote:
>
>> Using one thread, the app can do about 111 rows per second, and it's
>> only exercising 1.5 of 8 CPU cores while doing this. 12,000,000 rows /
>> 111 rows per second ~= 30 hours.
>
> I don't know how
On 11/05/11 05:34, Aren Cambre wrote:
> Using one thread, the app can do about 111 rows per second, and it's
> only exercising 1.5 of 8 CPU cores while doing this. 12,000,000 rows /
> 111 rows per second ~= 30 hours.
I don't know how I missed that. You ARE maxing out one cpu core, so
you're quite
On 05/11/2011 05:34 AM, Aren Cambre wrote:
> Using one thread, the app can do about 111 rows per second, and it's
> only exercising 1.5 of 8 CPU cores while doing this. 12,000,000 rows /
> 111 rows per second ~= 30 hours.
>
> I hoped to speed things up with some parallel processing.
>
> When the a
On Mon, May 9, 2011 at 9:40 PM, Aren Cambre wrote:
>> how are you reading through the table? if you are using OFFSET, you
>> owe me a steak dinner.
>>
>
> Nope. :-)
> Below is my exact code for the main thread. The C# PLINQ statement is
> highlighted. Let me know if I can help to explain this.
>
>
On 10/05/11 10:40, Aren Cambre wrote:
> how are you reading through the table? if you are using OFFSET, you
> owe me a steak dinner.
>
>
> Nope. :-)
>
> Below is my exact code for the main thread. The C# PLINQ statement is
> highlighted. Let me know if I can help to explain this.
Lookin
>
> how are you reading through the table? if you are using OFFSET, you
> owe me a steak dinner.
>
>
Nope. :-)
Below is my exact code for the main thread. The C# PLINQ statement is
highlighted. Let me know if I can help to explain this.
NpgsqlConnection arrestsConnection = new NpgsqlC
On Mon, May 9, 2011 at 10:15 PM, Aren Cambre wrote:
>> Your OS won't *see* eight processors if you turn of HT. :-)
>> I'm going to pursue this digression just a little further, because
>> it probably will be biting you sooner or later. We make sure to
>> configure the BIOS on our database server
On Mon, May 9, 2011 at 8:15 PM, Aren Cambre wrote:
>> Your OS won't *see* eight processors if you turn of HT. :-)
>> I'm going to pursue this digression just a little further, because
>> it probably will be biting you sooner or later. We make sure to
>> configure the BIOS on our database servers
>
> Your OS won't *see* eight processors if you turn of HT. :-)
>
> I'm going to pursue this digression just a little further, because
> it probably will be biting you sooner or later. We make sure to
> configure the BIOS on our database servers to turn off
> hyperthreading. It really can make a
>
> so follow the advice above. we need to see pg_stat_activity, and/or
> pg_locks while your test is running (especially take note of pg_lock
> records with granted=f)
Attached.
The database is named de. The process with procpid 3728 has the SQL query
for my "main" thread--the one that reads th
On 05/09/2011 05:59 PM, Kevin Grittner wrote:
I'm going to pursue this digression just a little further, because
it probably will be biting you sooner or later. We make sure to
configure the BIOS on our database servers to turn off
hyperthreading. It really can make a big difference in performa
Aren Cambre wrote:
>>>- Core i7 processor--4 physical cores, but OS sees 8 cores
>>>via hyper-threading
>>
>> Most benchmarks I've seen comparing hyper-threading show that
>> PostgreSQL performs better if you don't try to convince it that
>> one core is actually two different cores. Wit
On Mon, May 9, 2011 at 4:50 PM, Aren Cambre wrote:
>> Are you sure you are really using > 1 connection? While your test is
>> running, log onto postgres with psql and grab the output of
>> pg_stat_activity a few times. What do you see?
>
> Thanks. If a connection corresponds to a process, then t
>
> > Postgres won't use more than 1 CPU core.
>
> One *connection* to PostgreSQL won't directly use more than one
> core. As Merlin suggests, perhaps you're really only running one
> query at a time? The other possibility is that you're somehow
> acquiring locks which cause one process to block
>
> Are you sure you are really using > 1 connection? While your test is
> running, log onto postgres with psql and grab the output of
> pg_stat_activity a few times. What do you see?
>
Thanks. If a connection corresponds to a process, then this suggests I am
using 1 connection for my main threa
Aren Cambre wrote:
> Postgres won't use more than 1 CPU core.
One *connection* to PostgreSQL won't directly use more than one
core. As Merlin suggests, perhaps you're really only running one
query at a time? The other possibility is that you're somehow
acquiring locks which cause one process
On Mon, May 9, 2011 at 4:23 PM, Aren Cambre wrote:
> I have a multi-threaded app. It uses ~22 threads to query Postgres.
> Postgres won't use more than 1 CPU core. The 22-threaded app only has 3% CPU
> utilization because it's mostly waiting on Postgres.
> Here's the details:
> The app has a "main
I have a multi-threaded app. It uses ~22 threads to query Postgres.
Postgres won't use more than 1 CPU core. The 22-threaded app only has 3% CPU
utilization because it's mostly waiting on Postgres.
Here's the details:
The app has a "main" thread that reads table A's 11,000,000 rows, one at a
tim
46 matches
Mail list logo
