|
Home > Archive > Slony1 PostgreSQL Replication > September 2005 > Re: slow queries on Postgres 7.4 and slony1.1
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Re: slow queries on Postgres 7.4 and slony1.1
|
|
|
|
| Hannu Krosing 2005-09-15, 8:25 pm |
| On N, 2005-09-15 at 13:41 -0700, Marc Munro wrote:
> Folks,
> I saw a suggestion in this list about adding an index to sl_log(log_xid)
> to speed up queries. Can someone enlighten me on how to do this, I am
> getting the following error:
>
> ERROR: data type _cage2.xxid has no default operator class for access
> method "hash"
> You must specify an operator class for the index or define a
> default operator class for the data type
>
> What do I need to specify for the opclass or access method or both?
look how it's done with the existing index (\d _xxe_cluster.sl_log_1)
original one:
"sl_log_1_idx1" btree (log_origin, log_xid _xxx_cluster.xxid_ops,
log_actionseq)
what needs to be added:
"sl_log_1_idx2" btree (log_xid _xxx_cluster.xxid_ops)
--
Hannu Krosing <hannu-7C/ iILuz2RdeoWH0uzbU5w@
public.gmane.org>
| |
| Rod Taylor 2005-09-15, 8:25 pm |
| On Thu, 2005-09-15 at 23:59 +0300, Hannu Krosing wrote:[color=darkred
]
> On N, 2005-09-15 at 13:41 -0700, Marc Munro wrote:
The slony additions are broken. I first reported this in May to
Slony-general based on the below message from Tom when I was working out
a bug.
Toms reply:
ssdb=# select * from pg_operator where oid = 716373;
oprname | oprnamespace | oprowner | oprkind | oprcanhash | oprleft |
oprright | oprresult | oprcom | oprnegate | oprlsortop | oprrsortop |
oprltcmpop | oprgtcmpop | oprcode | oprrest | oprjoin
---------+--------------+----------+---------+------------+---------+----------+-----------+--------+-----------+------------+------------+------------+------------+---------------+---------+-----------
= | 2200 | 588 | b | t | 716353 |
716353 | 16 | 716373 | 716372 | 716371 | 716371 |
716371 | 716369 | _ssrep.xxideq | eqsel | eqjoinsel
(1 row)
I think you need to have a word with the Slony boys. They shouldn't be
marking the operator oprcanhash if they aren't providing a valid hash
opclass for the datatype. Per the manual:
: To be marked HASHES, the join operator must appear in a hash index
: operator class. This is not enforced when you create the operator,
since
: of course the referencing operator class couldn't exist yet. But
: attempts to use the operator in hash joins will fail at runtime if no
: such operator class exists. The system needs the operator class to
find
: the data-type-specific hash function for the operator's input data
: type. Of course, you must also supply a suitable hash function before
: you can create the operator class.
--
| |
|
|
| Michael Crozier 2005-09-15, 8:25 pm |
|
Take these suggestions lightly, I'm quite a Slony "newbie" :-)
I seemed to have greater success on my initial sync when I individually added
tables to the replicating set, adding the largest low-transaction tables
first, the largest high-transaction tables second, and so on. This seemed to
keep the queries reasonably efficient after the large copies, when it was
time to "catch up".
I also found that modifying the slon code to keep the sync group size at a
constant level (100 was a sweet spot for my database) decreased the catch up
time. Setting the desired sync time parameter to zero did not provide the
"right" effect, as the group size still incremented from zero and would
occasionally (and mysteriously) return to 1 after a time.
I'm currently distracted from my Slony-related project, but when I have an
opportunity I will try to experiment more and document my results.
[color=darkred]
> Hannu,
> Thanks very much for showing me how to index log_xid on sl_log_1.
>
> For everyone's info, I'd like to report that this does not seem to have
> helped me with my inability to catch-up after a sync. If there are
> query changes within slony that could be tried, as discussed a week or
> so ago, I would be pleased to be a guinea-pig for them. The slony
> version is 1.1.0, the postgres version 7.3.6
>
> My provider database is very heavily loaded and it may be that it is
> just working too damn hard for slony to keep up with. I am going to
> make another attempt to sync from scratch tonight without the extra
> index.
>
> In case I have done something stupid in my configuration, here it is:
>
> # Do not log to syslog
> syslog(0)
>
> # Put a timestamp in log messages
> log_timestamp(true)
>
> # Lots of logging
> log_level(2)
>
> # Do fewer sync checks than is the default?
> sync_interval(15000)
>
> # Sync levery 15 seconds instead of 1 second?
> sync_interval_timeou
t(15000)
>
> # Maximum number of syncs that may be processed in one go.
> sync_group_maxsize(1
000)
>
> #
> desired_sync_time(0)
>
> Thanks.
> __
> Marc
>
> On Thu, 2005-09-15 at 23:59 +0300, Hannu Krosing wrote:
--
Conducive Technology Corporation
Taking air cargo information
to the next level
http://www.conducivetech.com
http://www.pathfinder-web.com
Michael Crozier crozierm- 19LDBNnCZmbFzinHIz5S
+QC/G2K4zDHf@public.gmane.org
Voice: 503.445.4233
Fax: 503.274.0939
|
|
|
|
|