Hi Melvin,

thanks for the response.

> Am 20.05.2015 um 14:26 schrieb Melvin Davidson <melvin6...@gmail.com>:
> 
> First, are your postgresql.conf options the same on master and slave2 with 
> regard to memory allocation and all Planner Method Configuration options?

slave2 has less shared_buffers (256m vs 2048m), temp_buffers (16m vs 128m), 
work_mem (8m vs 16m) and maintenance_work_mem (8m vs 16m) due to hardware 
constraints. All other settings are the same and mostly default.

> Next, is it possible you have a corrupted index on slave2.?

Unlikely, as I replaced the slave2 db with a fresh backup from master yesterday 
to rule this out.

> I would suggest verifying there is no difference in the query plan between 
> master and slave2.
> IE: EXPLAIN SELECT * FROM MyTable WHERE email = 'f...@example.com 
> <mailto:f...@example.com>';

All three (master, slave1, slave2) use the same plan:

 Index Scan using mytable_pk on mytable  (cost=0.42..8.44 rows=1 width=205)
   Index Cond: ((email)::text = 'f...@example.com'::text)

> Check your indexes with:
> 
> SELECT n.nspname as schema,
>        i.relname as table,
>        i.indexrelname as index,
>        i.idx_scan,
>        i.idx_tup_read,
>        i.idx_tup_fetch,
>        CASE WHEN idx.indisprimary
>             THEN 'pkey'
>             WHEN idx.indisunique
>             THEN 'uidx'
>             ELSE 'idx'
>             END AS type,
>        pg_get_indexdef(idx.indexrelid),
>        CASE WHEN idx.indisvalid
>             THEN 'valid'
>             ELSE 'INVALID'
>             END as istatus,
>        pg_relation_size(quote_ident(n.nspname)|| '.' || 
> quote_ident(i.relname)) as size_in_bytes,
>        pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' || 
> quote_ident(i.relname))) as size
>   FROM pg_stat_all_indexes i
>   JOIN pg_class c ON (c.oid = i.relid)
>   JOIN pg_namespace n ON (n.oid = c.relnamespace)
>   JOIN pg_index idx ON (idx.indexrelid =  i.indexrelid )
>  WHERE i.relname = 'MyTable'
> ORDER BY 1, 2, 3;

master:
 schema |  table  |            index            | idx_scan | idx_tup_read | 
idx_tup_fetch | type |                                   pg_get_indexdef        
                         | istatus | size_in_bytes |  size
--------+---------+-----------------------------+----------+--------------+---------------+------+-----------------------------------------------------------------------------------+---------+---------------+--------
 public | mytable | mytable_emailaddressref_idx |        6 |           11 |     
        4 | idx  | CREATE INDEX mytable_emailaddressref_idx ON mytable USING 
btree (emailaddressref) | valid   |     123609088 | 118 MB
 public | mytable | mytable_pk                  |  1291541 |      1305655 |     
  1291371 | pkey | CREATE UNIQUE INDEX mytable_pk ON mytable USING btree 
(email)                     | valid   |     123609088 | 118 MB
 public | mytable | mytable_syncstatus_idx      |     3710 |      2250428 |     
        0 | idx  | CREATE INDEX mytable_syncstatus_idx ON mytable USING btree 
(syncstatus)           | valid   |     123609088 | 118 MB
 public | mytable | mytable_userref_idx         |        0 |            0 |     
        0 | idx  | CREATE INDEX mytable_userref_idx ON mytable USING btree 
(userref)                 | valid   |     123609088 | 118 MB

slave1:
 schema |  table  |           index             | idx_scan | idx_tup_read | 
idx_tup_fetch | type |                                   pg_get_indexdef        
                         | istatus | size_in_bytes |  size
--------+---------+-----------------------------+----------+--------------+---------------+------+-----------------------------------------------------------------------------------+---------+---------------+--------
 public | mytable | mytable_emailaddressref_idx |        0 |            0 |     
        0 | idx  | CREATE INDEX mytable_emailaddressref_idx ON mytable USING 
btree (emailaddressref) | valid   |     123609088 | 118 MB
 public | mytable | mytable_pk                  |        3 |           13 |     
        3 | pkey | CREATE UNIQUE INDEX mytable_pk ON mytable USING btree 
(email)                     | valid   |     123609088 | 118 MB
 public | mytable | mytable_syncstatus_idx      |        0 |            0 |     
        0 | idx  | CREATE INDEX mytable_syncstatus_idx ON mytable USING btree 
(syncstatus)           | valid   |     123609088 | 118 MB
 public | mytable | mytable_userref_idx         |        0 |            0 |     
        0 | idx  | CREATE INDEX mytable_userref_idx ON mytable USING btree 
(userref)                 | valid   |     123609088 | 118 MB

slave2:
 schema |  table  |            index            | idx_scan | idx_tup_read | 
idx_tup_fetch | type |                                   pg_get_indexdef        
                         | istatus | size_in_bytes |  size
--------+---------+-----------------------------+----------+--------------+---------------+------+-----------------------------------------------------------------------------------+---------+---------------+--------
 public | mytable | mytable_emailaddressref_idx |        1 |            3 |     
        1 | idx  | CREATE INDEX mytable_emailaddressref_idx ON mytable USING 
btree (emailaddressref) | valid   |     123609088 | 118 MB
 public | mytable | mytable_pk                  |       15 |            0 |     
        0 | pkey | CREATE UNIQUE INDEX mytable_pk ON mytable USING btree 
(email)                     | valid   |     123609088 | 118 MB
 public | mytable | mytable_syncstatus_idx      |        0 |            0 |     
        0 | idx  | CREATE INDEX mytable_syncstatus_idx ON mytable USING btree 
(syncstatus)           | valid   |     123609088 | 118 MB
 public | mytable | mytable_userref_idx         |        0 |            0 |     
        0 | idx  | CREATE INDEX mytable_userref_idx ON mytable USING btree 
(userref)                 | valid   |     123609088 | 118 MB

Now there are a few different numbers, although I wasn't really successful 
trying to quickly read up what that means. And I can understand that different 
stats there can lead to different performance, but I think whatever the stats 
are and the execution plan is, this must not lead to a false result, right?

Besides, the scales look similar in slave1 and slave2, although the query 
results are different.

Maik


> 
> On Wed, May 20, 2015 at 2:52 AM, Musall Maik <li...@musall.de 
> <mailto:li...@musall.de>> wrote:
> Hi,
> 
> I have a strange case where a SELECT for a primary key returns 0 rows on one 
> slave, while it returns the correct 1 row on another slave and on the master. 
> It does however return that row on all slaves when queried with LIKE and 
> trailing or leading wildcard.
> 
> psql version is 9.3.5, because that's what comes with Ubuntu 14.04 LTS
> Master runs Ubuntu 14.04 LTS
> Slave 1 runs also Ubuntu 14.04 LTS
> Slave 2 runs Mac OS X 10.7, pgsql installed via homebrew
> 
> Both slaves are configured with streaming replication, and I've been using 
> that setup for years, starting with psql 9.1, with no problems so far. 
> Suspecting some weird problem, I already re-initialized slave 2 with a fresh 
> backup and started replication from the beginning, so the database is fresh 
> from a master copy, and is verified to be current.
> 
> 2015-05-19 20:53:43.937 CEST LOG:  entering standby mode
> 2015-05-19 20:53:43.974 CEST LOG:  redo starts at 31/3F000028
> 2015-05-19 20:53:45.522 CEST LOG:  consistent recovery state reached at 
> 31/40CCE6E8
> 2015-05-19 20:53:45.523 CEST LOG:  database system is ready to accept read 
> only connections
> 2015-05-19 20:53:45.604 CEST LOG:  started streaming WAL from primary at 
> 31/41000000 on timeline 1
> 
> 
> So here's the query.
> 
>         SELECT * FROM MyTable WHERE email = 'f...@example.com 
> <mailto:f...@example.com>';
> 
> This returns 1 row on master and slave 1, but 0 on slave 2, while this query:
> 
>         SELECT * FROM MyTable WHERE email LIKE 'f...@example.com 
> <mailto:f...@example.com>%';
> 
> or this one
> 
>         SELECT * FROM MyTable WHERE email LIKE '%f...@example.com 
> <mailto:f...@example.com>';
> 
> returns the correct 1 row on all three systems. Note that this works with the 
> wildcard on either end, or also somewhere in the middle, doesn't matter. 
> Note: "email" is the primary key on this table.
> 
> This behaviour is the same with any address to be queried, and is also the 
> same on a similar second table. This does NOT occur on any other table, which 
> all have integer primary keys. There is also no problem when I select for 
> other attributes on these tables.
> 
> Does anyone have a hint?
> 
> Thanks
> Maik
> 
> 
> 
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org 
> <mailto:pgsql-general@postgresql.org>)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general 
> <http://www.postgresql.org/mailpref/pgsql-general>
> 
> 
> 
> -- 
> Melvin Davidson
> I reserve the right to fantasize.  Whether or not you 
> wish to share my fantasy is entirely up to you. 

Reply via email to