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
Re: ERROR: found xmin from before relfrozenxid
Hey, As I said, I'm getting this error for all the objects in a specific db. I cant even connect to the database, I immediatly getting this error. The bug was fixed in 9.6.10 but the db version is 9.6.10 so how can it happen ? The db was installed in that version from the first place and *no upgrade was done* בתאריך יום ד׳, 30 בינו׳ 2019 ב-11:14 מאת Alvaro Herrera < alvhe...@2ndquadrant.com>: > 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 >
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 >
pgstattupple vs pg_total_relation_size
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: 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: 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
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
"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: How can sort performance be so different
On Wed, Jan 30, 2019 at 3:57 AM Bob Jolliffe wrote: > (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. If you can find a way to use an ICU collation, it may be possible to get Laotian sort order with performance that's a lot closer to the performance you see with the C locale. The difference that you're seeing is obviously explainable in large part by the C locale using the abbreviated keys technique. The system glibc's collations cannot use this optimization. I believe that some locales have inherently more expensive normalization processes (comparisons) than others, but if you can effective amortize the cost per key by building an abbreviated key, it may not matter that much. And, ICU may be faster than glibc anyway. -- Peter Geoghegan
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: 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
