Josh Berkus | 26 Aug 00:13 2014

Re: Turn off Hyperthreading! WAS: 60 core performance with 9.3

On 08/22/2014 07:02 AM, Andres Freund wrote:
> On 2014-08-21 14:02:26 -0700, Josh Berkus wrote:
>> On 08/20/2014 07:40 PM, Bruce Momjian wrote:
>>> Not sure how you can make such a blanket statement when so many people
>>> have tested and shown the benefits of hyper-threading.  
>>
>> Actually, I don't know that anyone has posted the benefits of HT.
>> Link?
> 
> There's definitely cases where it can help. But it's highly workload
> *and* hardware dependent.

The only cases I've seen where HT can be beneficial is when you have
large numbers of idle connections.  Then the idle connections can be
"parked" on the HT virtual cores.  However, even in this case I haven't
seen a head-to-head performance comparison.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Rich | 25 Aug 22:26 2014
Picon

From: Rich

Hi pgsql


http://activebillion.com/bring.php?fzuvceubqu3101hcvfvcq






Rich


--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Jeison Bedoya Delgado | 25 Aug 20:47 2014
Picon

tuning postgresql 9.3.5 and multiple cores

hi, recently i change the hardware of my database 32 cores up to 64 
cores and 128GB Ram, but the performance is the same.  Perhaps i have to 
change any parameter in the postgresql.conf?.

Thanks by your help

-- 
Atentamente,

JEISON BEDOYA DELGADO
.

--
NOTA VERDE:
No imprima este correo a menos que sea absolutamente necesario.
Ahorre papel, ayude a salvar un arbol.

--------------------------------------------------------------------
Este mensaje ha sido analizado por MailScanner
en busca de virus y otros contenidos peligrosos,
y se considera que esta limpio.

--------------------------------------------------------------------
Este texto fue anadido por el servidor de correo de Audifarma S.A.:

Las opiniones contenidas en este mensaje no necesariamente coinciden
con las institucionales de Audifarma. La informacion y todos sus
archivos Anexos, son confidenciales, privilegiados y solo pueden ser
utilizados por sus destinatarios. Si por error usted recibe este
mensaje, le ofrecemos disculpas, solicitamos eliminarlo de inmediato,
(Continue reading)

Emi Lu | 22 Aug 22:49 2014
Picon
Picon

autocommit (true/false) for more than 1 million records

Hello,

Trying to insert into one table with 1 million records through java JDBC 
into psql8.3. May I know (1) or (2) is better please?

(1) set autocommit(true)
(2) set autocommit(false)
      commit every n records (e.g., 100, 500, 1000, etc)

Thanks a lot!
Emi

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Josh Berkus | 22 Aug 01:29 2014

Re: Turn off Hyperthreading! WAS: 60 core performance with 9.3

On 08/21/2014 04:08 PM, Steve Crawford wrote:
> On 08/21/2014 03:51 PM, Josh Berkus wrote:
>> On 08/21/2014 02:26 PM, Scott Marlowe wrote:
>>> I'm running almost the exact same setup in production as a spare. It
>>> has 4 of those CPUs, 256G RAM, and is currently set to use HT. Since
>>> it's a spare node I might be able to do some testing on it as well.
>>> It's running a 3.2 kernel right now. I could probably get a later
>>> model kernel on it even.
>> You know about the IO performance issues with 3.2, yes?
>>
> Were those 3.2 only and since fixed or are there issues persisting in
> 3.2+? The 12.04 LTS release of Ubuntu Server was 3.2 but the 14.04 is 3.13.

The issues I know of were fixed in 3.9.

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Josh Berkus | 22 Aug 00:51 2014

Re: Turn off Hyperthreading! WAS: 60 core performance with 9.3

On 08/21/2014 02:26 PM, Scott Marlowe wrote:
> I'm running almost the exact same setup in production as a spare. It
> has 4 of those CPUs, 256G RAM, and is currently set to use HT. Since
> it's a spare node I might be able to do some testing on it as well.
> It's running a 3.2 kernel right now. I could probably get a later
> model kernel on it even.

You know about the IO performance issues with 3.2, yes?

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance

Josh Berkus | 21 Aug 23:17 2014

Re: Turn off Hyperthreading! WAS: 60 core performance with 9.3

On 08/21/2014 02:11 PM, Bruce Momjian wrote:
> On Thu, Aug 21, 2014 at 02:02:26PM -0700, Josh Berkus wrote:
>> On 08/20/2014 07:40 PM, Bruce Momjian wrote:
>>> On Wed, Aug 20, 2014 at 12:13:50PM -0700, Josh Berkus wrote:
>>>> On a read-write test, it's 10% faster with HT off as well.
>>>>
>>>> Further, from their production machine we've seen that having HT on
>>>> causes the machine to slow down by 5X whenever you get more than 40
>>>> cores (as in 100% of real cores or 50% of HT cores) worth of activity.
>>>>
>>>> So we're definitely back to "If you're using PostgreSQL, turn off
>>>> Hyperthreading".
>>>
>>> Not sure how you can make such a blanket statement when so many people
>>> have tested and shown the benefits of hyper-threading.  
>>
>> Actually, I don't know that anyone has posted the benefits of HT.  Link?
>>  I want to compare results so that we can figure out what's different
>> between my case and theirs.  Also, it makes a big difference if there is
>> an advantage to turning HT on for some workloads.
> 
> I had Greg Smith test my system when it was installed, tested it, and
> recommended hyper-threading.  The system is Debian Squeeze
> (2.6.32-5-amd64), CPUs are dual Xeon E5620, 8 cores, 16 virtual cores.

Can you post some numerical results?

I'm serious.  It's obviously easier for our users if we can blanket
recommend turning HT off; that's a LOT easier for them than "you might
want to turn HT off if these conditions ...".  So I want to establish
(Continue reading)

Josh Berkus | 21 Aug 23:02 2014

Re: Turn off Hyperthreading! WAS: 60 core performance with 9.3

On 08/20/2014 07:40 PM, Bruce Momjian wrote:
> On Wed, Aug 20, 2014 at 12:13:50PM -0700, Josh Berkus wrote:
>> On a read-write test, it's 10% faster with HT off as well.
>>
>> Further, from their production machine we've seen that having HT on
>> causes the machine to slow down by 5X whenever you get more than 40
>> cores (as in 100% of real cores or 50% of HT cores) worth of activity.
>>
>> So we're definitely back to "If you're using PostgreSQL, turn off
>> Hyperthreading".
> 
> Not sure how you can make such a blanket statement when so many people
> have tested and shown the benefits of hyper-threading.  

Actually, I don't know that anyone has posted the benefits of HT.  Link?
 I want to compare results so that we can figure out what's different
between my case and theirs.  Also, it makes a big difference if there is
an advantage to turning HT on for some workloads.

> I am also
> unclear exactly what you tested, as I didn't see it mentioned in the
> email --- CPU type, CPU count, and operating system would be the minimal
> information required.

Ooops!  I thought I'd posted that earlier, but I didn't.

The processors in question is the Intel(R) Xeon(R) CPU E7- 4850, with 4
of them for a total of 40 cores or 80 HT cores.

OS is RHEL with 2.6.32-431.3.1.el6.x86_64.
(Continue reading)

Eli Naeher | 21 Aug 18:19 2014
Picon

Re: Window functions, partitioning, and sorting performance

Upping work_mem did roughly halve the time, but after thinking about Shaun's suggestion, I figured it's better to calculate this stuff once and then store it. So here is how the table looks now:

                                          Table "public.stop_event"
       Column        |            Type             |                        Modifiers                        
---------------------+-----------------------------+---------------------------------------------------------
 stop_time           | timestamp without time zone | not null
 stop                | integer                     | not null
 bus                 | integer                     | not null
 direction           | integer                     | not null
 route               | integer                     | not null
 id                  | bigint                      | not null default nextval('stop_event_id_seq'::regclass)
 previous_stop_event | bigint                      | 
Indexes:
    "stop_event_pkey" PRIMARY KEY, btree (id)
    "stop_event_previous_stop_event_idx" btree (previous_stop_event)
Foreign-key constraints:
    "stop_event_direction_id_fkey" FOREIGN KEY (direction) REFERENCES direction(id)
    "stop_event_previous_stop_event_fkey" FOREIGN KEY (previous_stop_event) REFERENCES stop_event(id)
    "stop_event_route_fkey" FOREIGN KEY (route) REFERENCES route(id)
    "stop_event_stop" FOREIGN KEY (stop) REFERENCES stop(id)
Referenced by:
    TABLE "stop_event" CONSTRAINT "stop_event_previous_stop_event_fkey" FOREIGN KEY (previous_stop_event) REFERENCES stop_event(id)

previous_stop_event simply references the previous (by stop_time) stop event for the combination of stop, route, and direction. I have successfully populated this column for my existing test data. However, when I try to do a test self-join using it, Postgres does two seq scans across the whole table, even though I have indexes on both id and previous_stop_event: http://explain.depesz.com/s/ctck. Any idea why those indexes are not being used?

Thank you again,
-Eli

On Thu, Aug 21, 2014 at 9:05 AM, Shaun Thomas <sthomas <at> optionshouse.com> wrote:
On 08/21/2014 08:29 AM, Eli Naeher wrote:

With around 1.2 million rows, this takes 20 seconds to run. 1.2 million
rows is only about a week's worth of data, so I'd like to figure out a
way to make this faster.

Well, you'll probably be able to reduce the run time a bit, but even with really good hardware and all in-memory processing, you're not going to see significant run-time improvements with that many rows. This is one of the reasons reporting-specific structures, such as fact tables, were designed to address.

Repeatedly processing the same week/month/year aggregate worth of several million rows will just increase linearly with each iteration as data size increases. You need to maintain up-to-date aggregates on the metrics you actually want to measure, so you're only reading the few hundred rows you introduce every update period. You can retrieve those kind of results in a few milliseconds.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas <at> optionshouse.com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email


Eli Naeher | 21 Aug 15:29 2014
Picon

Window functions, partitioning, and sorting performance

I have a table called stop_event (a stop event is one bus passing one bus stop at a given time for a given route and direction), and I'd like to get the average interval for each stop/route/direction combination.

A few hundred new events are written to the table once every minute. No rows are ever updated (or deleted, except in development).

stop_event looks like this:

              Table "public.stop_event"
  Column   |            Type             | Modifiers 
-----------+-----------------------------+-----------
 stop_time | timestamp without time zone | not null
 stop      | integer                     | not null
 bus       | integer                     | not null
 direction | integer                     | not null
 route     | integer                     | not null
Foreign-key constraints:
    "stop_event_direction_id_fkey" FOREIGN KEY (direction) REFERENCES direction(id)
    "stop_event_route_fkey" FOREIGN KEY (route) REFERENCES route(id)
    "stop_event_stop" FOREIGN KEY (stop) REFERENCES stop(id)

And my query looks like this:

SELECT (floor(date_part(E'epoch', avg(interval))) / 60)::INTEGER,
       route,
       direction,
       name,
       st_asgeojson(stop_location)::JSON
FROM
  (SELECT (stop_time - (lag(stop_time) OVER w)) AS interval,
          route,
          direction,
          name,
          stop_location
   FROM stop_event
   INNER JOIN stop ON (stop_event.stop = stop.id)
   WINDOW w AS (PARTITION BY route, direction, stop ORDER BY stop_time))
AS all_intervals
WHERE (interval IS NOT NULL)
GROUP BY route,
         direction,
         name,
         stop_location;

With around 1.2 million rows, this takes 20 seconds to run. 1.2 million rows is only about a week's worth of data, so I'd like to figure out a way to make this faster. The EXPLAIN ANALYZE is at http://explain.depesz.com/s/ntC.

Clearly the bulk of the time is spent sorting the rows in the original table, and then again sorting the results of the subselect. But I'm afraid I don't really know what to do with this information. Is there any way I can speed this up? Is my use of an aggregate key for stop_event causing problems? Would using a synthetic key help?

Thank you for any help you can provide,
-Eli
Josh Berkus | 20 Aug 21:13 2014

Re: Turn off Hyperthreading! WAS: 60 core performance with 9.3

Mark, all:

So, this is pretty damming:

Read-only test with HT ON:

[pgtest <at> db ~]$ pgbench -c 20 -j 4 -T 600 -S bench
starting vacuum...end.
transaction type: SELECT only
scaling factor: 30
query mode: simple
number of clients: 20
number of threads: 4
duration: 600 s
number of transactions actually processed: 47167533
tps = 78612.471802 (including connections establishing)
tps = 78614.604352 (excluding connections establishing)

Read-only test with HT Off:

[pgtest <at> db ~]$ pgbench -c 20 -j 4 -T 600 -S bench
starting vacuum...end.
transaction type: SELECT only
scaling factor: 30
query mode: simple
number of clients: 20
number of threads: 4
duration: 600 s
number of transactions actually processed: 82457739
tps = 137429.508196 (including connections establishing)
tps = 137432.893796 (excluding connections establishing)

On a read-write test, it's 10% faster with HT off as well.

Further, from their production machine we've seen that having HT on
causes the machine to slow down by 5X whenever you get more than 40
cores (as in 100% of real cores or 50% of HT cores) worth of activity.

So we're definitely back to "If you're using PostgreSQL, turn off
Hyperthreading".

-- 
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

--

-- 
Sent via pgsql-performance mailing list (pgsql-performance <at> postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


Gmane