Re: Setting effective_cache size
On Thu, Jan 31, 2019 at 1:00 PM Nandakumar M wrote: > This is particularly confusing because in this thread Tom Lane says > the following > Missed to link the thread.. https://postgrespro.com/list/thread-id/1813920 Regards, Nanda
Setting effective_cache size
Hi, According to https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server : > effective_cache_size should be set to an estimate of how much memory is > available for disk caching by the operating system and within the database > itself, after taking into account what's used by the OS itself and other > applications. I intend to run a java application and postgres server in the same server machine. The java application requires 2 GB RAM max. Considering that our server machine has 4 GB RAM, should I reduce the effective_cache_size to say 768 MB or am I better off with the default 4 GB value? This is particularly confusing because in this thread Tom Lane says the following > I see no problem with a value of say 4GB; > that's very unlikely to be worse than the pre-9.4 default (128MB) on any > modern machine. PS : I got the value 768 MB from https://pgtune.leopard.in.ua/#/ by giving 1 GB as the amount of memory postgres can use. Regards, Nanda
Re: pgstattupple vs pg_total_relation_size
"Jehan-Guillaume (ioguix) de Rorthais" writes: > On Wed, 30 Jan 2019 14:19:52 +0100 > Tumasgiu Rossini wrote: >> According to the doc [1], >> pg_total_relation_size add toasted data *and* indexes to the mix. > *and* FSM *and* VM. Yeah. In this particular case, the other page presumably belongs to the toast table's index, which will have a metapage even if the table is empty. regards, tom lane
Re: pgstattupple vs pg_total_relation_size
There aren't any constraint or indexes, just a regular table. I didn't see
the fsm and vm files in the base dir. Were they created immediately for
every table or after some updates/deletes ?
On Wed, Jan 30, 2019, 3:27 PM Jehan-Guillaume (ioguix) de Rorthais <
iog...@free.fr wrote:
> On Wed, 30 Jan 2019 14:19:52 +0100
> Tumasgiu Rossini wrote:
>
> > According to the doc [1],
> > pg_total_relation_size add toasted data *and* indexes to the mix.
>
> *and* FSM *and* VM.
>
> > Any index, unique constraint, or primary key on your table ?
> >
> > [1]
> >
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
> >
> > Le mer. 30 janv. 2019 à 11:42, Mariel Cherkassky <
> > mariel.cherkas...@gmail.com> a écrit :
> >
> > > Hey,
> > > I'm using postgresql 9.6.11. I wanted to ask something about the
> functions
> > > I mentioned in the title :
> > > I created the next table :
> > > postgres=# \d students;
> > > Table "public. students "
> > > Column | Type | Modifiers
> > > --+-+---
> > > id| integer |
> > > name| text|
> > > age| integer |
> > > data | jsonb |
> > >
> > > I inserted one row. When I query the table`s size with
> > > pg_total_relation_size I see that the data occupies 2 pages :
> > >
> > > postgres=# select pg_total_relation_size(' students ');
> > > pg_total_relation_size
> > >
> > > 16384
> > > (1 row)
> > >
> > > postgres=# select pg_relation_size(' students ');
> > > pg_relation_size
> > > --
> > > 8192
> > > (1 row)
> > >
> > > When I used pgstattuple :
> > > postgres=# select * from pgstattuple('pg_toast.pg_toast_1187222');
> > > table_len | tuple_count | tuple_len | tuple_percent |
> dead_tuple_count |
> > > dead_tuple_len | dead_tuple_percent | free_space | free_percent
> > >
> > >
> ---+-+---+---+--++++--
> > > 0 | 0 | 0 | 0 |
> 0 |
> > > 0 | 0 | 0 |0
> > > (1 row)
> > >
> > > postgres=# select * from pgstattuple('students');
> > > table_len | tuple_count | tuple_len | tuple_percent |
> dead_tuple_count |
> > > dead_tuple_len | dead_tuple_percent | free_space | free_percent
> > >
> > >
> ---+-+---+---+--++++--
> > > 8192 | 1 | 1221 | 14.9 |
> 0 |
> > > 0 | 0 | 6936 |84.67
> > > (1 row)
> > >
> > > Which means, the toasted table is empty and you can see that the row I
> > > inserted should occupy only one page(8K in my system).
> > >
> > > Then, why the pg_total_relation_size shows another page ?(16KB in
> total)
> > >
> > >
> > >
> > >
> > >
>
>
>
> --
> Jehan-Guillaume de Rorthais
> Dalibo
>
Re: pgstattupple vs pg_total_relation_size
On Wed, 30 Jan 2019 14:19:52 +0100
Tumasgiu Rossini wrote:
> According to the doc [1],
> pg_total_relation_size add toasted data *and* indexes to the mix.
*and* FSM *and* VM.
> Any index, unique constraint, or primary key on your table ?
>
> [1]
> https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
>
> Le mer. 30 janv. 2019 à 11:42, Mariel Cherkassky <
> mariel.cherkas...@gmail.com> a écrit :
>
> > Hey,
> > I'm using postgresql 9.6.11. I wanted to ask something about the functions
> > I mentioned in the title :
> > I created the next table :
> > postgres=# \d students;
> > Table "public. students "
> > Column | Type | Modifiers
> > --+-+---
> > id| integer |
> > name| text|
> > age| integer |
> > data | jsonb |
> >
> > I inserted one row. When I query the table`s size with
> > pg_total_relation_size I see that the data occupies 2 pages :
> >
> > postgres=# select pg_total_relation_size(' students ');
> > pg_total_relation_size
> >
> > 16384
> > (1 row)
> >
> > postgres=# select pg_relation_size(' students ');
> > pg_relation_size
> > --
> > 8192
> > (1 row)
> >
> > When I used pgstattuple :
> > postgres=# select * from pgstattuple('pg_toast.pg_toast_1187222');
> > table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
> > dead_tuple_len | dead_tuple_percent | free_space | free_percent
> >
> > ---+-+---+---+--++++--
> > 0 | 0 | 0 | 0 |0 |
> > 0 | 0 | 0 |0
> > (1 row)
> >
> > postgres=# select * from pgstattuple('students');
> > table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
> > dead_tuple_len | dead_tuple_percent | free_space | free_percent
> >
> > ---+-+---+---+--++++--
> > 8192 | 1 | 1221 | 14.9 |0 |
> > 0 | 0 | 6936 |84.67
> > (1 row)
> >
> > Which means, the toasted table is empty and you can see that the row I
> > inserted should occupy only one page(8K in my system).
> >
> > Then, why the pg_total_relation_size shows another page ?(16KB in total)
> >
> >
> >
> >
> >
--
Jehan-Guillaume de Rorthais
Dalibo
Re: pgstattupple vs pg_total_relation_size
According to the doc [1],
pg_total_relation_size add toasted data *and* indexes to the mix.
Any index, unique constraint, or primary key on your table ?
[1]
https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-DBSIZE
Le mer. 30 janv. 2019 à 11:42, Mariel Cherkassky <
mariel.cherkas...@gmail.com> a écrit :
> Hey,
> I'm using postgresql 9.6.11. I wanted to ask something about the functions
> I mentioned in the title :
> I created the next table :
> postgres=# \d students;
> Table "public. students "
> Column | Type | Modifiers
> --+-+---
> id| integer |
> name| text|
> age| integer |
> data | jsonb |
>
> I inserted one row. When I query the table`s size with
> pg_total_relation_size I see that the data occupies 2 pages :
>
> postgres=# select pg_total_relation_size(' students ');
> pg_total_relation_size
>
> 16384
> (1 row)
>
> postgres=# select pg_relation_size(' students ');
> pg_relation_size
> --
> 8192
> (1 row)
>
> When I used pgstattuple :
> postgres=# select * from pgstattuple('pg_toast.pg_toast_1187222');
> table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
> dead_tuple_len | dead_tuple_percent | free_space | free_percent
>
> ---+-+---+---+--++++--
> 0 | 0 | 0 | 0 |0 |
> 0 | 0 | 0 |0
> (1 row)
>
> postgres=# select * from pgstattuple('students');
> table_len | tuple_count | tuple_len | tuple_percent | dead_tuple_count |
> dead_tuple_len | dead_tuple_percent | free_space | free_percent
>
> ---+-+---+---+--++++--
> 8192 | 1 | 1221 | 14.9 |0 |
> 0 | 0 | 6936 |84.67
> (1 row)
>
> Which means, the toasted table is empty and you can see that the row I
> inserted should occupy only one page(8K in my system).
>
> Then, why the pg_total_relation_size shows another page ?(16KB in total)
>
>
>
>
>
Re: How can sort performance be so different
Hi Tom After much performance measuring of VPS I believe you are right in your suspicion about locale. The database is full of Laos characters (it is a government system in Laos). When I tested on my VPS (en_US.UTF-8) I get the crazy slow performance, whereas my laptop postgresql is C.UTF-8. Modifying the query from : explain analyze select * from chart order by name; to explain analyze select * from chart order by name COLLATE "C"; and the same query runs like a rocket. Amazing, yes 1000 times faster. What I don't know yet is (i) whether the sort order makes sense for the Laos names; and (ii) what the locale settings are on the production server where the problem was first reported. There will be some turnaround before I get this information. I am guessing that the database is using "en_US" rather than anything Laos specific. In which case "C" would probably be no worse re sort order. But will know better soon. This has been a long but very fruitful investigation. Thank you all for input. Regards Bob On Tue, 29 Jan 2019 at 18:47, Tom Lane wrote: > > Bob Jolliffe writes: > > I wonder what can cause such a massive discrepancy in the sort time. > > Are you using the same locale (LC_COLLATE) setting on both machines? > Some locales sort way slower than C locale does. That's not enough > to explain a 1000X discrepancy --- I concur with the other opinions > that there's something wrong with your VPS --- but it might account > for something like 10X of it. > > regards, tom lane
Re: dsa_allocate() faliure
Hi Thomas, it is a Production system and we don’t have permanent access to it. Also to have an auto_explain feature always on, is not an option in production. I will ask the customer to give us notice asap the error present itself to connect immediately and try to get a query plan. Regards Fabio Isabettini www.voipfuture.com > On 30. Jan 2019, at 04:13:14, Thomas Munro > wrote: > > On Tue, Jan 29, 2019 at 10:32 PM Fabio Isabettini > wrote: >> we are facing a similar issue on a Production system using a Postgresql 10.6: >> >> org.postgresql.util.PSQLException: ERROR: EXCEPTION on getstatistics ; ID: >> EXCEPTION on getstatistics_media ; ID: uidatareader. >> run_query_media(2): [a1] REMOTE FATAL: dsa_allocate could not find 7 free >> pages > >> We would like not to stop the Production system and upgrade it to PG11. And >> even though would this guarantee a permanent fix? >> Any suggestion? > > Hi Fabio, > > Thanks for your report. Could you please also show the query plan > that runs on the "remote" node (where the error occurred)? > > There is no indication that upgrading to PG11 would help here. It > seems we have an undiagnosed bug (in 10 and 11), and so far no one has > been able to reproduce it at will. I personally have chewed a lot of > CPU time on several machines trying various plan shapes and not seen > this or the possibly related symptom from bug #15585 even once. But > we have about three reports of each of the two symptoms. One reporter > wrote to me off-list to say that they'd seen #15585 twice, the second > time by running the same query in a tight loop for 8 hours, and then > not seen it again in the past 3 weeks. Clearly there is issue needing > a fix here, but I don't yet know what it is. > > -- > Thomas Munro > http://www.enterprisedb.com >
Re: ERROR: found xmin from before relfrozenxid
On 2019-Jan-30, Mariel Cherkassky wrote: > It seems that the version of the db is 9.6.10 : > > psql -U db -d db -c "select version()"; > Password for user db: > version > --- > PostgreSQL 9.6.10 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 > 20120313 (Red Hat 4.4.7-23), 64-bit > (1 row) > > > and the error is still exist.. Did you apply the suggested SELECT .. FOR UPDATE to the problem table? -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
