Re: [PERFORM] Replication Lag Causes
Hello Mike, what kind of load does the slave get? what does the recovery process do on the slave during the times when lag is being observed? Does it use 100% of the CPU? WAL can be replayed by only one process, so no need to increase the max_wal_senders. Cheers, -- Valentine Gogichashvili On Sun, Nov 2, 2014 at 1:33 AM, Mike Wilson mfwil...@gmail.com wrote: I have two 9.3.4 PG instances that back a large internet website that has very seasonal traffic and can generate large query loads. My instances are in a master-slave streaming replication setup and are stable and in general perform very well. The only issues we have with the boxes is that when the master is busy the slave may start to lag excessively. I can give specifics as to what heavily loaded means and additionally the postgresql.conf for both boxes but my basic questions are: * What causes streaming replication lag to increase? * What parameters can be tuned to reduce streaming replication lag? * Can a loaded slave affect lag adversely? * Can increasing max_wal_senders help reduce lag? The reason I ask this is that as mentioned above the servers are stable and are real troopers in general as they back a very popular web site that puts the master under heavy seasonal load at times. At those times though we see an almost exponential growth in streaming replication lag compared to load on the master. For example, the master is a very beefy Solaris: * 4 Recent Intel Zeons (16 physical cores) * 256 GB of ECC RAM * 12 TB of ZFS (spindle and SSD internal storage) * DB on disk size is 2TB * ZFS ARC cache of roughly 250G. * ZFS ARC2/ZIL configured for SSD’s (this is awesome by the way) Basic PG Config: shared_buffers = 2GB work_mem = 128MB max_connections = 1700 (supports roughly 100 web servers) wal_keep_segments = 256 (roughly enough for 24 hours of operation under heavy load) wal_sender_timeout = 60s replication_timeout=(not set) wal_receiver_status_interval=10s max_wal_senders=6 * wal archiving is off * 98% of the queries on the master complete in under 500ms. * No hung or very long running queries in general. The master on a normal day maintains a load of about 0.5, during which replication lag to the slave is in hundreds milliseconds. When the production db server is heavily hit though the load may go as high as 4 on the master and the streaming replication lag may increase to more than 2 hours relatively quickly. Load on the slave is generally below 1 even when the master is heavily loaded. The traffic to the master is primarily read with about 10% DML (new users, purchase records, etc). DML statements increase proportionally when under load though. The master and slave are connected via dedicated 10G fiber link and even under heavy load the utilization of the link is nowhere near close to saturation. BTW, the slave does run some reported related queries throughout the day that might take up to a minute to complete. I have the task of figuring out why this otherwise healthy DB starts to lag so badly under load and if there is anything that we could do about it. I’ve been wondering particularly if we should up the max_wal_senders but from the docs it is unclear if that would help. In my testing with pg_bench on our dev boxes which were the previous production hardware for these servers I have determined that it doesn’t take much DML load on the master to get the slave to start lagging severely. I was wondering if this was expected and/or some design consideration? Possibly streaming replication isn’t meant to be used for heavily hit databases and maintain small lag times? I would like to believe that the fault is something we have done though and that there is some parameter we could tune to reduce this lag. Any recommendations would be very helpful. Mike Wilson Predicate Logic Consulting
Re: [PERFORM] UPDATE execution time is increasing
On Sun, Oct 7, 2012 at 3:49 PM, vi...@chem.elte.hu wrote: Hello! I would like to ask following question: I have created a table and I updated all records. And I executed this update command again and again Execution time was growing after each step. I cannot understand this behavior. First update command took 6 sec, 30th update (same) command took 36 sec (6x times greater value!!!). Can somebody explain me why increasing this update time? -- 1st update: 6175 ms -- 5th update: 9265 ms -- 10th update: 15669 ms -- 20th update: 26940 ms -- 20th update: 36198 ms PGSQL version: 9.1.5, parameters: default install used Thanks your answer in advance! SCRIPT: DROP SCHEMA IF EXISTS tempdb CASCADE; CREATE SCHEMA tempdb; SET search_path TO tempdb; DROP TABLE IF EXISTS t; CREATE TABLE t ( id SERIAL , num int NOT NULL, PRIMARY KEY (id) ); insert into t SELECT *,0 FROM generate_series(1,10); update t set num=num+1; -- 1st update: 6175 ms update t set num=num+1; update t set num=num+1; Hello, could you do the same putting VACUUM t; between your updates? What is the change in UPDATE time? -- Valentin
Re: [PERFORM] multicolumn index column order
On 7/24/07, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: valgog [EMAIL PROTECTED] wrote .. On Jul 23, 7:00 pm, [EMAIL PROTECTED] (Tom Lane) wrote: valgog [EMAIL PROTECTED] writes: how to build an multicolumn index with one column order ASCENDING and another column order DESCENDING? Use 8.3 ;-) In existing releases you could fake it with a custom reverse-sorting operator class, but it's a pain in the neck to create one. I've often gotten what I want by using a calculated index on (f1, -f2). ORDER BY will take an expression, e.g. ORDER BY f1, -f2. Simpler than a custom operator. Yes, this is true, but I do now know how to make text order be reversible? There is no - (minus) operator for text value. By now it is not a problem for me, but theoretically I do not see other chance to reverse text fields order...
Re: [PERFORM] Cannot make GIN intarray index be used by the planner
Hello again,
I got the opclass for the index and it looks like it is a default one
myvideoindex=# select pg_opclass.*, pg_type.typname
myvideoindex-# from pg_index, pg_opclass, pg_type
myvideoindex-# where pg_index.indexrelid =
'idx_nonnulls_myintarray_int4_gin'::regclass
myvideoindex-#and pg_opclass.oid = any (pg_index.indclass::oid[] )
myvideoindex-#and pg_type.oid = pg_opclass.opcintype;
opcamid | opcname | opcnamespace | opcowner | opcintype | opcdefault |
opckeytype | typname
-+---+--+--+---+++-
2742 | _int4_ops | 11 | 10 | 1007 | t
| 23 | _int4
(1 row)
The search_path is set to the following
myvideoindex=# show search_path;
search_path
versionA, public
(1 row)
With best regards,
-- Valentine
On 5/9/07, Tom Lane [EMAIL PROTECTED] wrote:
[cc'ing to pgsql-hackers since this is looking like a contrib/intarray
bug]
Valentine Gogichashvili [EMAIL PROTECTED] writes:
here is the DT
That works fine for me in 8.2:
regression=# explain SELECT id, (myintarray_int4)
FROM myintarray_table_nonulls
WHERE ARRAY[8] @ myintarray_int4;
QUERY PLAN
--
Index Scan using idx_nonnulls_myintarray_int4_gin on
myintarray_table_nonulls (cost=0.00..8.27 rows=1 width=36)
Index Cond: ('{8}'::integer[] @ myintarray_int4)
(2 rows)
What I am betting is that you've installed contrib/intarray in this
database and that's bollixed things up somehow. In particular, intarray
tries to take over the position of default gin opclass for int4[],
and the opclass that it installs as default has operators named just
like the built-in ones. If somehow your query is using pg_catalog.@
instead of intarray's public.@, then the planner wouldn't think the
index is relevant.
In a quick test your example still works with intarray installed, because
what it's really created is public.@ (integer[], integer[]) which is
an exact match and therefore takes precedence over the built-in
pg_catalog.@ (anyarray, anyarray). But if for example you don't have
public in your search_path then the wrong operator would be chosen.
Please look at the pg_index entry for your index, eg
select * from pg_index where indexrelid =
'versionA.idx_nonnulls_myintarray_int4_gin'::regclass;
and see whether the index opclass is the built-in one or not.
Note to hackers: we've already discussed that intarray shouldn't be
trying to take over the default gin opclass, but I am beginning to
wonder if it still has a reason to live at all. We should at least
consider removing the redundant operators to avoid risks like this one.
regards, tom lane
--
ვალენტინ გოგიჩაშვილი
Valentine Gogichashvili
[PERFORM] Cannot make GIN intarray index be used by the planner
Hello all, I am trying to move from GiST intarray index to GIN intarray index, but my GIN index is not being used by the planner. The normal query is like that select * from sourcetablewith_int4 where ARRAY[myint] @ myint_array and some_other_filters (with GiST index everything works fine, but GIN index is not being used) If I create the same table populating it with text[] data like select myint_array::text[] as myint_array_as_textarray into newtablewith_text from sourcetablewith_int4 and then create a GIN index using this new text[] column the planner starts to use the index and queries run with grate speed when the query looks like that: select * from newtablewith_text where ARRAY['myint'] @ myint_array_as_textarray and some_other_filters Where the problem can be with _int4 GIN index in this constellation? by now the enable_seqscan is set to off in the configuration. With best regards, -- Valentine Gogichashvili
Re: [PERFORM] Cannot make GIN intarray index be used by the planner
I have experimented quite a lot. So first I did when starting the attempt to move from GiST to GIN, was to drop the GiST index and create a brand new GIN index... after that did not bring the results, I started to create all this tables with different sets of indexes and so on... So the answer to the question is: no there in only GIN index on the table. Thank you in advance, Valentine On 5/9/07, Oleg Bartunov [EMAIL PROTECTED] wrote: Do you have both indexes (GiST, GIN) on the same table ? On Wed, 9 May 2007, Valentine Gogichashvili wrote: Hello all, I am trying to move from GiST intarray index to GIN intarray index, but my GIN index is not being used by the planner. The normal query is like that select * from sourcetablewith_int4 where ARRAY[myint] @ myint_array and some_other_filters (with GiST index everything works fine, but GIN index is not being used) If I create the same table populating it with text[] data like select myint_array::text[] as myint_array_as_textarray into newtablewith_text from sourcetablewith_int4 and then create a GIN index using this new text[] column the planner starts to use the index and queries run with grate speed when the query looks like that: select * from newtablewith_text where ARRAY['myint'] @ myint_array_as_textarray and some_other_filters Where the problem can be with _int4 GIN index in this constellation? by now the enable_seqscan is set to off in the configuration. With best regards, -- Valentine Gogichashvili Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- ვალენტინ გოგიჩაშვილი Valentine Gogichashvili
