>
> 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
it has processed all 12,000,000 rows.
This, plus the parallelism fix, will probably convert this 30 hour program
to a <2 hour program.
Aren
On Sun, May 22, 2011 at 9:08 AM, Aren Cambre wrote:
> Just wanted to again say thanks for everyone's help.
>
> The main problem was th
ncurrent connections
to Postgres. :-)
Aren
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
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
>
> 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
>
dumping
it all out later. I have 6 GB RAM, so it should be plenty to handle this.
Aren Cambre
>
>
> 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
>
> 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
>
> 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
>
> > 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
sulted with Npgsql's developer, and he didn't see how
Npgsql itself could force Postgres to one core. (See
http://pgfoundry.org/pipermail/npgsql-devel/2011-May/001123.html.)
What can I do to improve this? Could I be inadvertently limiting Postgres to
one core?
Aren Cambre
15 matches
Mail list logo
