Re: [GENERAL] ERROR: unexpected chunk number 0 (expected 1) for toast value 76753264 in pg_toast_10920100

[Christoph Berg]

Re: Harry Ambrose 2017-08-25 

> Hi All,
> 
> Sorry to open this can of worms again. However, we are still struggling
> with this issue across quite a large amount of our estate.

Hi,

we've just seen exactly this error on a customer database running
9.5.3 (postgresql95.x86_64 9.5.3-2PGDG.rhel6). Luckily just one tuple
was affected.

Symptoms were:
# select text from k... where id = 719764749;
ERROR:  unexpected chunk number 0 (expected 1) for toast value 3347468184 in 
pg_toast_922511637
The toast table itself was perfectly ok, with a single chunk:
# select * from pg_toast.pg_toast_922511637 where chunk_id = 3347468184;
chunk_id   | 3347468184
chunk_seq  | 0
chunk_data | ...valid text string... (with bytea_output = 'escape')

Updating or deleting the field/row didn't work:
# update k... set text = '...same text as above...' where id = 719764749;
ERROR:  XX000: tuple concurrently updated
ORT:  simple_heap_delete, heapam.c:3171

# delete from k... where id = 719764749;
ERROR:  XX000: tuple concurrently updated
ORT:  simple_heap_delete, heapam.c:3171

The problem persisted over the last two weeks (eventually noticed by
pg_dump starting to fail between August 15th and 18th). The server was
started on July 26th.

Besides the ERRORing statements above, I didn't actively resolve it,
suddenly SELECTing the original row just worked again. According to
pg_stat_user_tables, autovacuum didn't hit in. I can't say if there
were backends open for two weeks. At the time it resolved itself, the
oldest backend was from August 27th.

If xmin/xmax/multixact... data from this server is interesting, I can
extract it on request.

Christoph


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

[GENERAL] Veritas cluster management

[Ron Johnson]

Hi,

For any of you with those failover clusters, do you know if "pg_ctl reload" 
works (for compatible config file changes), or must we bounce the database 
using "hares -offline" then "hares -online"?


Thanks

--
World Peace Through Nuclear Pacification



--
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] Veritas cluster management

[Scott Mead]

On Wed, Aug 30, 2017 at 9:43 AM, Ron Johnson  wrote:

> Hi,
>
> For any of you with those failover clusters, do you know if "pg_ctl
> reload" works (for compatible config file changes), or must we bounce the
> database using "hares -offline" then "hares -online"?
>

pg_ctl reload does work in this case. HOWEVER, if you do something that
could cause trouble to the cluster (i.e.  a pg_hba.conf change that breaks
connectivity), this could cause veritas to try and failover.  It's
recommended that you test your changes to avoid these scenarios.

  Technically however, pg_ctl reload works just fine, just don't break
anything :)

--Scott



>
> Thanks
>
> --
> World Peace Through Nuclear Pacification
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>



-- 
--
Scott Mead
Sr. Architect
*OpenSCG *
http://openscg.com

Re: [GENERAL] Veritas cluster management

[Ron Johnson]

On 08/30/2017 08:48 AM, Scott Mead wrote:



On Wed, Aug 30, 2017 at 9:43 AM, Ron Johnson > wrote:


Hi,

For any of you with those failover clusters, do you know if "pg_ctl
reload" works (for compatible config file changes), or must we bounce
the database using "hares -offline" then "hares -online"?


pg_ctl reload does work in this case. HOWEVER, if you do something that 
could cause trouble to the cluster (i.e.  a pg_hba.conf change that breaks 
connectivity), this could cause veritas to try and failover.  It's 
recommended that you test your changes to avoid these scenarios.


  Technically however, pg_ctl reload works just fine, just don't break 
anything :)


That's great news.  Many thanks.

--
World Peace Through Nuclear Pacification

[GENERAL] clustered index benchmark comparing Postgresql vs Mariadb

[유상지]

 
I want to get help with Postgresql.
I investigated that Postgresql could be rather fast in an environment using a 
secondary index. but It came up with different results on benckmark.
The database I compared was mariadb, and the benchmark tool was sysbench 1.0.8 
with the postgresql driver.
Server environment: vmware, Ubuntu 17.04, processor: 4, RAM: 4 GB, Harddisk: 40 
GB, Mariadb (v10.3), PostgreSQL (v9.6.4)
 
The created sysbench progress statement is as follows.
Sysbench /usr/share/sysbench/oltp_read_only.lua --db-driver = mysql 
--mysql-host = 127.0.0.1 --mysql-port = 3306 --mysql-user = root 
--mysql-password = ajdajddl75 - Mysql-db = sysbench --tables = 3 --table_size = 
10 --report-interval = 10 --secondary = on --time = 60 
 
Used options
Select only, Num of threads = 1, num of tables = 3, table-size = 10 and 
Table-size = 100, secondary index select instead of primary key.
 
 
 
 My hypothesis was that  selecting by secondary index in postgresql is faster 
than in Mariadb. However, the results depend on table size.
 
Postgresql was faster than Mariadb when the table size was 100, but slower 
at 10.
 
Cluster secondary indexes were faster than those without cluster indexes in pg, 
but slower than mariadb.
 
I'd like to see the difference in architecture rather than optimization, so 
every benchmark executed with default options except for clustered index.
 I wonder if there are any settings I missed.
 
I would be very pleased if someone could explain why these results came up.

Re: [GENERAL] clustered index benchmark comparing Postgresql vs Mariadb

[Melvin Davidson]

On Wed, Aug 30, 2017 at 10:03 PM, 유상지  wrote:

>
>
> I want to get help with Postgresql.
>
> I investigated that Postgresql could be rather fast in an environment
> using a secondary index. but It came up with different results on benckmark.
>
> The database I compared was mariadb, and the benchmark tool was sysbench
> 1.0.8 with the postgresql driver.
>
> Server environment: vmware, Ubuntu 17.04, processor: 4, RAM: 4 GB,
> Harddisk: 40 GB, Mariadb (v10.3), PostgreSQL (v9.6.4)
>
>
>
> The created sysbench progress statement is as follows.
>
> Sysbench /usr/share/sysbench/oltp_read_only.lua --db-driver = mysql
> --mysql-host = 127.0.0.1 --mysql-port = 3306 --mysql-user = root
> --mysql-password = ajdajddl75 - Mysql-db = sysbench --tables = 3
> --table_size = 10 --report-interval = 10 --secondary = on --time = 60
>
>
>
> Used options
>
> Select only, Num of threads = 1, num of tables = 3, table-size = 10
> and Table-size = 100, secondary index select instead of primary key.
>
>
>
>
>
>
>
>  My hypothesis was that  selecting by secondary index in postgresql is
> faster than in Mariadb. However, the results depend on table size.
>
>
>
> Postgresql was faster than Mariadb when the table size was 100, but
> slower at 10.
>
>
>
> Cluster secondary indexes were faster than those without cluster indexes
> in pg, but slower than mariadb.
>
>
>
> I'd like to see the difference in architecture rather than optimization,
> so every benchmark executed with default options except for clustered index.
>
>  I wonder if there are any settings I missed.
>
>
>
> I would be very pleased if someone could explain why these results came up.
>

>Postgresql was faster than Mariadb when the table size was 100, but
slower at 10.

You made a general statement, but you left out a lot of important
information.

A. Did you do an ANALYZE table_name BEFORE running your test?
B. Did you verify the index was being used with EXPLAIN your_query?
C. What was the exact query you used?
D. Most important, what is the structure of the table and index?
E. How much system memory is there?
F. In postgresql.conf What are the settings for
1. shared_memory
2. work_memory
3. All Planner Cost Constants values, All Genetic Query Optimizer values
-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] clustered index benchmark comparing Postgresql vs Mariadb

[Thomas Kellerer]

유상지 schrieb am 31.08.2017 um 04:03:
> Cluster secondary indexes were faster than those without cluster indexes in 
> pg, but slower than mariadb.

There is no such thing as a "clustered index" in Postgres. 

The Postgres "cluster" command physically sorts the rows of a table according 
to the sort order of an index, but that is something completely different then 
a "clustered index". The data is still stored in the index and the table. 

A clustered index in MariaDB/MySQL stores the entire table data. So the table 
and the index is the same thing (Oracle calls that an "index organized table" 
which describes this a lot better). As the table and index are the same thing 
you can't have two clustered indexes on the same table. 

An index in Postgres only stores the data of the indexed columns (plus an 
internal row identifier). There is no technical difference between a primary 
key index and any other index. The structure and storage is always the same. So 
the term "secondary index" does not really make sense in Postgres.






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

[GENERAL] Table create time

[hamann . w]

Hi,

is there a way to add a table create (and perhaps schema modify) timestamp to 
the system?
I do occasionally create semi-temporary tables (meant to live until a problem 
is solved, i.e. longer
than a session) with conveniently short names.
Also, is there a simple query to identify tables without a table comment? (so a 
weekly cron
could remind me of tables that are already a few days old but have no 
explanatory comment)

I am running PG 9.3

Best regards
Wolfgang Hamann




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