Re: [GENERAL] Postgres as In-Memory Database?

[Florian Weimer]

On 04/02/2014 12:32 AM, Stefan Keller wrote:


It also mentions an insert-only technique: "This approach has been
adopted before in POSTGRES [21] in 1987 and was called "time-travel".
I would be interested what "time-travel" is and if this is still used by
Postgres.


Back in the old days, PostgreSQL never deleted any tuples.  Rows were 
deleted by writing the deletion time into a column.  As a result, you 
could go back to old data just by telling PostgreSQL to report rows 
which where visible at a given time.


Obviously, this approach precluded use of PostgreSQL in many scenarios. 
 For example, you wouldn't want to use it as your web application 
session store.


--
Florian Weimer / Red Hat Product Security Team


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres as In-Memory Database?

[Stefan Keller]

Hi Florian

Thanks for the remark. I've seen recently somebody from the "core" team
(was it at PgCon Rev Meeting [1] or a blog) mentioning it meaning to revive
it?

Yours, Stefan

[1] http://wiki.postgresql.org/wiki/PgCon_2013_Developer_Meeting



2014-04-07 8:15 GMT+02:00 Florian Weimer :

> On 04/02/2014 12:32 AM, Stefan Keller wrote:
>
>  It also mentions an insert-only technique: "This approach has been
>> adopted before in POSTGRES [21] in 1987 and was called "time-travel".
>> I would be interested what "time-travel" is and if this is still used by
>> Postgres.
>>
>
> Back in the old days, PostgreSQL never deleted any tuples.  Rows were
> deleted by writing the deletion time into a column.  As a result, you could
> go back to old data just by telling PostgreSQL to report rows which where
> visible at a given time.
>
> Obviously, this approach precluded use of PostgreSQL in many scenarios.
>  For example, you wouldn't want to use it as your web application session
> store.
>
> --
> Florian Weimer / Red Hat Product Security Team
>

Re: [GENERAL] Postgres as In-Memory Database?

[Stefan Keller]

Hi,

I wrote
> Finally the paper is mostly about column stores - nothing about
persistence.

Regarding column store, Hadi wrote 2014-04-03 18:43 GMT+02:00 about the
release of a PostgreSQL Columnar Store called "cstore_fdw" [1]!

@Hadi: Can you say something about usage of cstore FDW in-memory?

Regards, S.

[1] http://citusdata.com/blog/76-postgresql-columnar-store-for-analytics




2014-04-02 0:32 GMT+02:00 Stefan Keller :

> Hi Yeb
>
> Thanks for the pointers.
>
> Of course disk access is not obsolete: As I said, I suppose changes are
> streamed to disk.
>
> When I mentioned "no disk access" I meant the indices of RDBMS which
> designed to handle disk access - which seems to me different in in-memory
> dabases.
>
> The paper referred by you is coming from SAP's chief scientist and it
> confirms actually my claim, that there's no need for a primary index since
> the primary attribute (i.e. all attributes) is already kept sorted
> in-memory.
>
> It also mentions an insert-only technique: "This approach has been adopted
> before in POSTGRES [21] in 1987 and was called "time-travel".
> I would be interested what "time-travel" is and if this is still used by
> Postgres.
> Finally the paper is mostly about column stores - nothing about
> persistence. In mentions Disaster recovery" in the last section about
> future work, though.
>
> -S.
>
>
>
>
> 2014-04-01 21:57 GMT+02:00 Yeb Havinga :
>
>  On 2014-04-01 04:20, Jeff Janes wrote:
>>
>> On Sunday, March 30, 2014, Stefan Keller  wrote:
>>
>>>  Hi Jeff
>>>
>>> 2013/11/20 Jeff Janes 
>>>

  I don't know what you mean about enhancements in the buffer pool.
  For an in-memory database, there shouldn't be a buffer pool in the first
 place, as it is *all* in memory.

>>>
>>>  You are right: In-memory DBs are making buffer-pooling obsolete -
>>> except for making data persistent (see below).
>>>
>>
>>
>>  I would be very reluctant to use any database engine which considered
>> disk access obsolete.
>>
>>
>> The disk is not obsolete but something called 'anti-caching' is used:
>> http://www.vldb.org/pvldb/vol6/p1942-debrabant.pdf
>>
>>
>>
>>
>>   Are there any show cases out there?
>>>
>>
>>  What did the HANA users have to say?  Seems like they would be in the
>> best position to provide the test cases.
>>
>>
>> This paper provides some insights into the research behind HANA
>> http://www.sigmod09.org/images/sigmod1ktp-plattner.pdf
>>
>> regards
>> Yeb
>>
>>
>

[GENERAL] Initial queries of day slow

[Rebecca Clarke]

Hi all.

I'm a bit stumped. At present I'm finding that queries to my database, that
normally execute promptly, are taking a long time when they are executed
first thing in the morning (after the database has been inactive for
several hours). After the first execution, everything is back to normal.

A while back I turned autovacuum off and now instead I run a daily cron at
3am that executes a script which does a VACUUM ANALYZE on each table.

These are my details:

Debian GNU/Linux 6.0
Postgresql 9.1
Memory 4GB

shared_buffers = 1024MB
work_mem = 16MB
maintenance_work_mem = 128MB
effective_cache_size = 2048MB


Would love peoples opinions on what the issue could be.

Thanks

Re: [GENERAL] Initial queries of day slow

[Atri Sharma]

On Mon, Apr 7, 2014 at 3:02 PM, Rebecca Clarke  wrote:

> Hi all.
>
> I'm a bit stumped. At present I'm finding that queries to my database,
> that normally execute promptly, are taking a long time when they are
> executed first thing in the morning (after the database has been inactive
> for several hours). After the first execution, everything is back to
> normal.
>
> A while back I turned autovacuum off and now instead I run a daily cron at
> 3am that executes a script which does a VACUUM ANALYZE on each table.
>
> These are my details:
>
> Debian GNU/Linux 6.0
> Postgresql 9.1
> Memory 4GB
>
> shared_buffers = 1024MB
> work_mem = 16MB
> maintenance_work_mem = 128MB
> effective_cache_size = 2048MB
>
>
>
Could it be cold cache behaviour?



-- 
Regards,

Atri
*l'apprenant*

Re: [GENERAL] Initial queries of day slow

[Albe Laurenz]

Rebecca Clarke wrote:
> I'm a bit stumped. At present I'm finding that queries to my database, that 
> normally execute promptly,
> are taking a long time when they are executed first thing in the morning 
> (after the database has been
> inactive for several hours). After the first execution, everything is back to 
> normal.
> 
> A while back I turned autovacuum off and now instead I run a daily cron at 
> 3am that executes a script
> which does a VACUUM ANALYZE on each table.

It could be that during the day the necessary pages are cached in
the buffer pool or the file system cache, but have dropped out of
the cache during the night.

Try EXPLAIN (ANALYZE, BUFFERS) SELECT ...
first thing in the morning and during the day and compare the
"shared read" and "shared hit" values.

It may well be the nightly VACUUM ANALYZE that does that - is autovacuum
not doing ist job for you?
Is there anything else going on on the machine during the night, like
backups or batch jobs?

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Order By and Comparisson

[howardn...@selestial.com]

Hi,

just as I thought I had postgres mastered :)  the ordering of strings is 
causing me some confusion.


Can someone explain how the database orders strings in the ORDER BY command.

My example:

My database is encoding is UTF-8, and default language is english,

If I have a text column in a table with the following rows:

'a'
'A'
'~'

Then in UTF-8, I would expect the order to give me

'A'
'a'
'~'

But instead I get:

'~'
'a'
'A'

Is there anywhere in the documentation I can get a more detailed 
explanation of this?


Thanks.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Order By and Comparisson

[Albe Laurenz]

howardn...@selestial.com wrote:
> just as I thought I had postgres mastered :)  the ordering of strings is
> causing me some confusion.
> 
> Can someone explain how the database orders strings in the ORDER BY command.
> 
> My example:
> 
> My database is encoding is UTF-8, and default language is english,
> 
> If I have a text column in a table with the following rows:
> 
> 'a'
> 'A'
> '~'
> 
> Then in UTF-8, I would expect the order to give me
> 
> 'A'
> 'a'
> '~'
> 
> But instead I get:
> 
> '~'
> 'a'
> 'A'
> 
> Is there anywhere in the documentation I can get a more detailed
> explanation of this?

http://www.postgresql.org/docs/current/static/collation.html#AEN33298

The ordering depends on the collation.
Which collations are available and how they order depends on your operating 
system.

What is your OS and what do you get for
   SHOW lc_collate;

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Initial queries of day slow

[Rebecca Clarke]

Thanks, I'll run the EXPLAIN (ANALYZE, BUFFERS) today and tomorrow morning.
I just tried it now on a query that took 109035.116 ms this morning (Which
returns one row). It has returned 675.496 ms. I will run on this same query
at 5am tomorrow. Thank you.

At present we run pg_dumps every three hours.

We orginally found autovacuum too intrusive so switched to manual. We've
had no problems with performance at all, only this. We're going to turn
autovacuum back on to see if it makes any impact to this particular issue.


On Mon, Apr 7, 2014 at 10:50 AM, Albe Laurenz wrote:

> Rebecca Clarke wrote:
> > I'm a bit stumped. At present I'm finding that queries to my database,
> that normally execute promptly,
> > are taking a long time when they are executed first thing in the morning
> (after the database has been
> > inactive for several hours). After the first execution, everything is
> back to normal.
> >
> > A while back I turned autovacuum off and now instead I run a daily cron
> at 3am that executes a script
> > which does a VACUUM ANALYZE on each table.
>
> It could be that during the day the necessary pages are cached in
> the buffer pool or the file system cache, but have dropped out of
> the cache during the night.
>
> Try EXPLAIN (ANALYZE, BUFFERS) SELECT ...
> first thing in the morning and during the day and compare the
> "shared read" and "shared hit" values.
>
> It may well be the nightly VACUUM ANALYZE that does that - is autovacuum
> not doing ist job for you?
> Is there anything else going on on the machine during the night, like
> backups or batch jobs?
>
> Yours,
> Laurenz Albe
>

Re: [GENERAL] Order By and Comparisson

[howardn...@selestial.com]

On 07/04/2014 11:58, Albe Laurenz wrote:
http://www.postgresql.org/docs/current/static/collation.html#AEN33298 
The ordering depends on the collation. Which collations are available 
and how they order depends on your operating system. What is your OS 
and what do you get for SHOW lc_collate; Yours, Laurenz Albe 


Hi Laurenz,

Thanks for the reply, I get the following from show lc_collate:

en_GB.UTF-8

Howard.


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Initial queries of day slow

[Andrew Sullivan]

On Mon, Apr 07, 2014 at 10:32:59AM +0100, Rebecca Clarke wrote:

> normally execute promptly, are taking a long time when they are executed
> first thing in the morning (after the database has been inactive for
> several hours). After the first execution, everything is back to normal.

Just guessing, but perhaps because your system's disk buffers have all
been blown away, so things that are normally in memory aren't any
more.  In particular,

> A while back I turned autovacuum off and now instead I run a daily cron at
> 3am that executes a script which does a VACUUM ANALYZE on each table.

this goes through every table in the database, and probably not in the
order such that the most-frequently-used tables are last in the set.
But also, why did you turn off autovacuum?  In the earliest
implementations of autovacuum that was sometimes worth doing for very
specific workloads, but in more recent releases (9.1.x certainly
qualifies) you are much better to tune autovacuum.

Best regards,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Order By and Comparisson

[Albe Laurenz]

howardn...@selestial.com wrote:
>> http://www.postgresql.org/docs/current/static/collation.html#AEN33298
>> The ordering depends on the collation. Which collations are available
>> and how they order depends on your operating system. What is your OS
>> and what do you get for SHOW lc_collate; Yours, Laurenz Albe

> Thanks for the reply, I get the following from show lc_collate:
> 
> en_GB.UTF-8

Then that's how that collation sorts.

You should get the same results with the UNIX command 'sort'.

Yours,
Laurenz Albe

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Initial queries of day slow

[Merlin Moncure]

On Mon, Apr 7, 2014 at 6:14 AM, Andrew Sullivan  wrote:
> On Mon, Apr 07, 2014 at 10:32:59AM +0100, Rebecca Clarke wrote:
>
>> normally execute promptly, are taking a long time when they are executed
>> first thing in the morning (after the database has been inactive for
>> several hours). After the first execution, everything is back to normal.
>
> Just guessing, but perhaps because your system's disk buffers have all
> been blown away, so things that are normally in memory aren't any
> more.  In particular,
>
>> A while back I turned autovacuum off and now instead I run a daily cron at
>> 3am that executes a script which does a VACUUM ANALYZE on each table.
>
> this goes through every table in the database, and probably not in the
> order such that the most-frequently-used tables are last in the set.
> But also, why did you turn off autovacuum?  In the earliest
> implementations of autovacuum that was sometimes worth doing for very
> specific workloads, but in more recent releases (9.1.x certainly
> qualifies) you are much better to tune autovacuum.

yes.  first think to check is iowait (say, via top).  If it's high,
then it could be vanilla cache warm up issue or interference from some
other long running process.  Another possible culprit is host machine
resources getting harvested or re-allocated if you're in a virtualized
environment.

merlin


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres 9.2.8 crash sporadically on Windows

[Sofer, Yuval]

Hi,

Postgres server (9.2.8) crash on Windows sporadically

Usually it happens after machine reboot that we do, once in a day

After ~4 minutes it crashes with this error in the log:

2014-04-06 08:08:01.069 GMTLOG:  server process (PID 5304) exited with exit 
code 0
2014-04-06 08:08:01.069 GMTLOG:  terminating any other active server processes
2014-04-06 08:08:01.833 GMTLOG:  all server processes terminated; reinitializing
2014-04-06 08:08:11.183 GMTFATAL:  pre-existing shared memory block is still in 
use
2014-04-06 08:08:11.183 GMTHINT:  Check if there are any old server processes 
still running, and terminate them.

This is production, Please help!




Yuval Sofer
BMC Software
CTM&D Business Unit
DBA Team
972-52-4286-282
yuval_so...@bmc.com

Re: [GENERAL] Postgres 9.2.8 crash sporadically on Windows

[Adrian Klaver]

On 04/07/2014 07:48 AM, Sofer, Yuval wrote:

Hi,

Postgres server (9.2.8) crash on Windows sporadically

Usually it happens after machine reboot that we do, once in a day

After ~4 minutes it crashes with this error in the log:

2014-04-06 08:08:01.069 GMTLOG:  server process (PID 5304) exited with
exit code 0

2014-04-06 08:08:01.069 GMTLOG:  terminating any other active server
processes

2014-04-06 08:08:01.833 GMTLOG:  all server processes terminated;
reinitializing

2014-04-06 08:08:11.183 GMTFATAL:  pre-existing shared memory block is
still in use

2014-04-06 08:08:11.183 GMTHINT:  Check if there are any old server
processes still running, and terminate them.

This is production, Please help!


Some more information is going to be needed.

1) What do the system/Postgres logs show when the system is shut down?

2) Have you checked to see if there are indeed old server processes 
still running?




*Yuval Sofer*




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Regexp matching + typecasts

[Ilya Ivanov]

Thanks Tom, that worked!


On Mon, Mar 24, 2014 at 8:47 PM, Tom Lane  wrote:

> Ilya Ivanov  writes:
> > I need to find all active (status=0) items not belonging to any active
> > trigger. The best I could come up with is this:
>
> > select count(itemid) from items where status='0' and itemid not in
> (select
> > cast(regexp_matches(expression,'{([^}]+)}','g') as integer) from triggers
> > where status='0');
>
> > However, the cast doesn't work:
> > ERROR:  cannot cast type text[] to integer
>
> Well, yeah.  You need to convert the possibly-multiple match results into
> a set.  Try putting unnest() around the regexp_matches call.
>
> regards, tom lane
>



-- 
Ilya.

[GENERAL] How do you find the row count for all your tables in Postgres?

[Nithya Soman]

Hi

How can we find the total row count for all db tables in psql version
7.4.3 ??

The query which worked fine in psql 9.2 version is :
SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY
n_live_tup DESC.

But same query throws error as 'ERROR:  column "n_live_tup" does not exist'
in psql 7.4.30. Could you please provide a valid query to get row count for
all db tables in psql  7.4.3 ?

Re: [GENERAL] Postgres 9.2.8 crash sporadically on Windows

[Adrian Klaver]

On 04/07/2014 07:48 AM, Sofer, Yuval wrote:

Hi,

Postgres server (9.2.8) crash on Windows sporadically

Usually it happens after machine reboot that we do, once in a day

After ~4 minutes it crashes with this error in the log:

2014-04-06 08:08:01.069 GMTLOG:  server process (PID 5304) exited with
exit code 0

2014-04-06 08:08:01.069 GMTLOG:  terminating any other active server
processes

2014-04-06 08:08:01.833 GMTLOG:  all server processes terminated;
reinitializing

2014-04-06 08:08:11.183 GMTFATAL:  pre-existing shared memory block is
still in use

2014-04-06 08:08:11.183 GMTHINT:  Check if there are any old server
processes still running, and terminate them.

This is production, Please help!


Should have explained my reasoning behind my previous questions. They 
are motivated by a hunch that the shutdown procedure is not cleanly 
stopping Postgres and that on start up there is a collision with 
detritus left over from the shutdown. So the idea is to look at the tail 
of the logs on the shutdown side for clues. Would not hurt to look at 
the head of the same logs on start up to see if there are any complaints 
before the server process exits.




*Yuval Sofer*




--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres as In-Memory Database?

[Hadi Moshayedi]

Hey Stefan,

@Hadi: Can you say something about usage of cstore FDW in-memory?
>
>
We designed cstore_fdw with the applications in mind where volume of data
is much larger than main memory. In general, columnar stores usually bring
two benefits:

1. Doing less disk I/O than row stores. We can skip reading entire columns
or column blocks that are not related to the given query. This is effective
when (a) volume of data is larger than main memory so OS cannot cache whole
dataset, (b) most of our queries only require a small subset of columns to
complete.

2. Vector processing and making better use of CPU. This usually helps most
when data is in memory. If data is in disk and is not cached, I/O cost is
usually higher than CPU cost, and vector processing may not help much.

cstore_fdw tries to optimize for #1. Also note that because we use
compression, more data can be cached in memory and chance of hitting disk
decreases.

But we don't do vector processing yet, and it is not our three month
timeline.

If you want to be able use more CPU cores in PostgreSQL, you can have a
look at CitusDB [1] which is built upon PostgreSQL and distributes queries
to use all cpu cores in a single or more machines.

[1] http://citusdata.com/

-- Hadi

Re: [GENERAL] Log file monitoring and event notification

[Steve Crawford]

On 04/05/2014 08:47 AM, Andy Colson wrote:

Hi All.

I've started using replication, and I'd like to monitor my logs for 
any errors or problems.  I don't want to do it manually, and I'm not 
interested in stats (a la PgBadger).


What I'd like, is the instant PG logs: "FATAL: wal segment already 
removed" (or some such bad thing), I'd like to get an email


As one component of our monitoring we route logging through syslog which 
has all messages go to one location for use by PgBadger and friends and 
simultaneously any message with a WARN or higher priority goes to a 
separate temporary "postgresql_trouble.log."


A cron-job checks this file periodically (currently we use 5-minutes) 
for content. If the file has content the script sends the appropriate 
emails and truncates the trouble log.


Cheers,
Steve



--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Initial queries of day slow

[Jeff Janes]

On Mon, Apr 7, 2014 at 3:58 AM, Rebecca Clarke  wrote:

> Thanks, I'll run the EXPLAIN (ANALYZE, BUFFERS) today and tomorrow
> morning. I just tried it now on a query that took 109035.116 ms this
> morning (Which returns one row). It has returned 675.496 ms. I will run
> on this same query at 5am tomorrow. Thank you.
>

If the problem is largely encapsulated by that one query, I'd just write a
cron job to execute that query every morning 15 minutes before you open for
business.


>
> At present we run pg_dumps every three hours.
>
> We orginally found autovacuum too intrusive so switched to manual. We've
> had no problems with performance at all, only this. We're going to turn
> autovacuum back on to see if it makes any impact to this particular issue.
>

Did you go from 'Autovacuum only' to 'nightly vacuum, no autovac' in one
step?  Mostly likely adding the nightly vacuum while leaving autovac on
would have solved the problem, while being less likely to cause other
problems.  (This is a side note--having autovac off is unlikely to be
causing the particular problem you are reporting here.)

Cheers,

Jeff

Re: [GENERAL] Postgres as In-Memory Database?

[Stefan Keller]

Hi Hadi, hi all

It makes sense to me to design cstore_fdw for volume of data which is
larger than main memory.

Coming back to my original thread, I'd like to ponder further on what makes
in-memory special - and how to configure or extend Postgres to implement
that.

I found e.g. some brand new functions of SQL Server called
"Memory-optimized tables" which "fully reside in memory and can’t be paged
out", are garbage collected, have special index, are persisting changes
using transaction log and checkpoint streams, and are monitored for not
running out-of-memory [1][2] - i.e. pretty much what has been discussed
here - although little bit reluctantly :-)

Yours, Stefan

[1] "SQL Server In-Memory OLTP Internals Overview for CTP2" (PDF)
http://t.co/T6zToWc6y6
[2] "SQL Server 2014 In-Memory OLTP: Memory Management for Memory-Optimized
Tables"
http://blogs.technet.com/b/dataplatforminsider/archive/2013/11/14/sql-server-2014-in-memory-oltp-memory-management-for-memory-optimized-tables.aspx



2014-04-07 17:40 GMT+02:00 Hadi Moshayedi :

> Hey Stefan,
>
> @Hadi: Can you say something about usage of cstore FDW in-memory?
>>
>>
> We designed cstore_fdw with the applications in mind where volume of data
> is much larger than main memory. In general, columnar stores usually bring
> two benefits:
>
> 1. Doing less disk I/O than row stores. We can skip reading entire columns
> or column blocks that are not related to the given query. This is effective
> when (a) volume of data is larger than main memory so OS cannot cache whole
> dataset, (b) most of our queries only require a small subset of columns to
> complete.
>
> 2. Vector processing and making better use of CPU. This usually helps most
> when data is in memory. If data is in disk and is not cached, I/O cost is
> usually higher than CPU cost, and vector processing may not help much.
>
> cstore_fdw tries to optimize for #1. Also note that because we use
> compression, more data can be cached in memory and chance of hitting disk
> decreases.
>
> But we don't do vector processing yet, and it is not our three month
> timeline.
>
> If you want to be able use more CPU cores in PostgreSQL, you can have a
> look at CitusDB [1] which is built upon PostgreSQL and distributes queries
> to use all cpu cores in a single or more machines.
>
> [1] http://citusdata.com/
>
> -- Hadi
>
>

Re: [GENERAL] Postgres as In-Memory Database?

[Andrew Sullivan]

On Mon, Apr 07, 2014 at 10:43:58PM +0200, Stefan Keller wrote:
> running out-of-memory [1][2] - i.e. pretty much what has been discussed
> here - although little bit reluctantly :-)

It is just possible that some of the reluctance is because (1) this
has been discussed many times in the past, partly with the arguments
you've already seen in this thread, and with much the same results;
(2) nobody seems to be saying, "I have $n to spend on this effort and
$thesepatches to contribute towards this end along with $thisdesign,"
but instead to be saying, "It'd be nice if someone else did this
work;" and (3) there _are_ several in-memory-only databases on the
market, including free-software ones, so it isn't clear what Postgres
would contribute, especially since its basic design isn't obviously
amenable to this sort of use.

Best regards,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Initial queries of day slow

[Rebecca Clarke]

Hi Jeff

Unfortunately it's not just the one particular query, there's no pattern
that I can see besides the time they're being executed.

We did go from Autovac only to nightly vac. I'm going to implement autovac
again, we've been operating without for a few months now. Will run both
nightly manual and autovac to see how things go. On a side not, we're not
doing a vacuumdb, but individual vacuum analyze statements on each table.
Not sure if that makes any difference.


On Mon, Apr 7, 2014 at 9:13 PM, Jeff Janes  wrote:

> On Mon, Apr 7, 2014 at 3:58 AM, Rebecca Clarke wrote:
>
>> Thanks, I'll run the EXPLAIN (ANALYZE, BUFFERS) today and tomorrow
>> morning. I just tried it now on a query that took 109035.116 ms this
>> morning (Which returns one row). It has returned 675.496 ms. I will run
>> on this same query at 5am tomorrow. Thank you.
>>
>
> If the problem is largely encapsulated by that one query, I'd just write a
> cron job to execute that query every morning 15 minutes before you open for
> business.
>
>
>>
>> At present we run pg_dumps every three hours.
>>
>> We orginally found autovacuum too intrusive so switched to manual. We've
>> had no problems with performance at all, only this. We're going to turn
>> autovacuum back on to see if it makes any impact to this particular issue.
>>
>
> Did you go from 'Autovacuum only' to 'nightly vacuum, no autovac' in one
> step?  Mostly likely adding the nightly vacuum while leaving autovac on
> would have solved the problem, while being less likely to cause other
> problems.  (This is a side note--having autovac off is unlikely to be
> causing the particular problem you are reporting here.)
>
> Cheers,
>
> Jeff
>

Re: [GENERAL] How do you find the row count for all your tables in Postgres?

[Michael Paquier]

On Mon, Apr 7, 2014 at 6:46 PM, Nithya Soman
 wrote:
> How can we find the total row count for all db tables in psql version  7.4.3
> ??
Are you aware that 7.4 is EOL for 5 years? 7.4.3 is missing as well at
least 4 years of bug fixes in its stable branch.

> The query which worked fine in psql 9.2 version is :
> SELECT schemaname,relname,n_live_tup FROM pg_stat_user_tables ORDER BY
> n_live_tup DESC.
> But same query throws error as 'ERROR:  column "n_live_tup" does not exist'
> in psql 7.4.30. Could you please provide a valid query to get row count for
> all db tables in psql  7.4.3 ?
The documentation of 7.4 does not mention explicitely what are the
columns of this view, but connecting to a 7.4 server and issuing "¥d
pg_stat_user_tables" would help and you might be able to get an
equivalent of what you are trying to do.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Postgres as In-Memory Database?

[Stefan Keller]

Hi Andrew

2014-04-07 23:37 GMT+02:00 Andrew Sullivan wrote:
> (1) this has been discussed many times in the past (...)

Can you point me to one of these discussions?

Actually, I browsed once again the mailing list and this is one of the few
posts I found:
"In-Memory Columnar Store" 9.12.13 by knizhnik.

> (2) nobody seems to be saying, "I have $n to spend on this effort and
> $thesepatches to contribute towards this end along with $thisdesign,"
> but instead to be saying, "It'd be nice if someone else did this work;"

>
Can't see that anybody suggested that. In contrary:
I said, that I'd like to discuss things before I code.

> (3) there _are_ several in-memory-only databases on the
> market, including free-software ones, so it isn't clear what Postgres
> would contribute, especially since its basic design isn't obviously
> amenable to this sort of use.

To me it's unclear why design of Postgres should prevent implementation of
"in-memory tables" e.g. as foreign data wrappers (see e.g. white papers
for SQL Server mentioned before).

Regards,
Stefan


2014-04-07 23:37 GMT+02:00 Andrew Sullivan :

> On Mon, Apr 07, 2014 at 10:43:58PM +0200, Stefan Keller wrote:
> > running out-of-memory [1][2] - i.e. pretty much what has been discussed
> > here - although little bit reluctantly :-)
>
> It is just possible that some of the reluctance is because (1) this
> has been discussed many times in the past, partly with the arguments
> you've already seen in this thread, and with much the same results;
> (2) nobody seems to be saying, "I have $n to spend on this effort and
> $thesepatches to contribute towards this end along with $thisdesign,"
> but instead to be saying, "It'd be nice if someone else did this
> work;" and (3) there _are_ several in-memory-only databases on the
> market, including free-software ones, so it isn't clear what Postgres
> would contribute, especially since its basic design isn't obviously
> amenable to this sort of use.
>
> Best regards,
>
> A
>
> --
> Andrew Sullivan
> a...@crankycanuck.ca
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Postgres as In-Memory Database?

[Andrew Sullivan]

On Tue, Apr 08, 2014 at 12:46:37AM +0200, Stefan Keller wrote:
> Hi Andrew
> 
> 2014-04-07 23:37 GMT+02:00 Andrew Sullivan wrote:
> > (1) this has been discussed many times in the past (...)
> 
> Can you point me to one of these discussions?
> 
> Actually, I browsed once again the mailing list and this is one of the few
> posts I found:
> "In-Memory Columnar Store" 9.12.13 by knizhnik.

I think you have to go back in time further than that.  I recall
in-memory table pinning being a recurrent topic during the 8.x series.
I also seem to recall it being mostly on the hackers list.

> I said, that I'd like to discuss things before I code.

Ok.  I think in the past what has been successful is some basic design
combined with a POC or some such, generally discussed on -hackers
since that's where all the people who really know the back end hang
out.  It seems to me that most of the "built in replication" stuff
that ended up happening worked that way, and that seems to be roughly
similar size of work to this, but I haven't thought about it too much.

> To me it's unclear why design of Postgres should prevent implementation of
> "in-memory tables" e.g. as foreign data wrappers (see e.g. white papers
> for SQL Server mentioned before).

I don't think it does.  But new code in the back end isn't free: it
presents a future maintenance burden that others may not be willing to
pay.  These things always have to be traded off.

Best regards,

A

-- 
Andrew Sullivan
a...@crankycanuck.ca


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] import .sql file into PostgreSQL database

[Gaurav Jindal]

How to import the .sql file using PSQL or PgAdmin? Can anyone guide me with
details and step by step instructions.

I tried \i filename.sql command but permission denied. I don't know how to
make it work. Can anyone helps me out. Thanks!

Gaurav

[GENERAL] Server Timezone

[Robert DiFalco]

Is there any way to set the timezone of the postgres server differently
than the OS? I *have* tried setting timezone = 'UTC' in my postgresql.conf
file but that seems to be a VIEW of time zone. It doesn't seem to behave
the same as running a postgresql server on a machine that is in UTC. The
machine I tried this setting on was a MAC machine running in the
America/Los_Angeles time zone.

Thanks!

Re: [GENERAL] import .sql file into PostgreSQL database

[Michael Paquier]

On Tue, Apr 8, 2014 at 12:33 PM, Gaurav Jindal  wrote:
> How to import the .sql file using PSQL or PgAdmin? Can anyone guide me with
> details and step by step instructions.
>
> I tried \i filename.sql command but permission denied. I don't know how to
> make it work. Can anyone helps me out. Thanks!
As you mentionned "¥i $FILE" (as is psql -f $FILE) is just fine, as
you are getting a permission error just be sure that the user that is
using the client application has a read access to the sql file you are
trying to use.
-- 
Michael


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server Timezone

[Tom Lane]

Robert DiFalco  writes:
> Is there any way to set the timezone of the postgres server differently
> than the OS? I *have* tried setting timezone = 'UTC' in my postgresql.conf
> file but that seems to be a VIEW of time zone. It doesn't seem to behave
> the same as running a postgresql server on a machine that is in UTC.

You would need to be more precise about "doesn't seem to behave the same"
for anyone to help you.  Setting the timezone parameter (and perhaps
log_timezone) should cover it, so far as the server is concerned.

Are you sure your client-side code isn't doing something helpful with
what it thinks the timezone is?

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Server Timezone

[Robert DiFalco]

You're right. This was my error. 

Sent from my iPhone

> On Apr 7, 2014, at 8:53 PM, Tom Lane  wrote:
> 
> Robert DiFalco  writes:
>> Is there any way to set the timezone of the postgres server differently
>> than the OS? I *have* tried setting timezone = 'UTC' in my postgresql.conf
>> file but that seems to be a VIEW of time zone. It doesn't seem to behave
>> the same as running a postgresql server on a machine that is in UTC.
> 
> You would need to be more precise about "doesn't seem to behave the same"
> for anyone to help you.  Setting the timezone parameter (and perhaps
> log_timezone) should cover it, so far as the server is concerned.
> 
> Are you sure your client-side code isn't doing something helpful with
> what it thinks the timezone is?
> 
>regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Increase in max_connections

[Kevin Grittner]

"Anand Kumar, Karthik"  wrote:

> We finally made some headway on this - we noticed messages like
> the below

> in /var/log/messages whenever the issue happened:
>
> Mar 26 07:39:58 site-db01b kernel: postmaster: page allocation failure.

> Anyone have any idea why memory was so fragmented, and what
> causes memory to be defragged? Is it something postgres does? Are
> there any kernel specific settings that control it?

While I agree with other replies that you would benefit from
transaction-based connection pooling, there may be another issue at
play here.  I can't be sure from evidence so far, but this might be
related to something I've been looking at related to NUMA memory
and how the OS buffers and PostgreSQL shared_buffers interact with
it.  Most of the available benefit can be realized without any
change to the PostgreSQL code by using the cpuset features of the
OS.  If you want to investigate this, the first thing would be to
get a look at the shape of things.  Please post the output of this:

numactl --hardware

The above just reports on the hardware -- it doesn't change
anything.

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general