Thanks I thought it might be missing too but wasn'T sure as I didn'T
check the mysql/oracle ones.
@Paul: in this case it might be worth adding those to the create/migrate
scripts speed things up?
Regards
On 2013-10-15 11:25, Alan Hicks wrote:
You are missing three indexes, they are in the MySQL and Oracle create
scripts.
CREATE INDEX dbmail_header_headername_id_key on
dbmail_header(headername_id);
CREATE INDEX dbmail_header_headervalue_id_key on
dbmail_header(headervalue_id);
CREATE INDEX dbmail_header_physmessage_id_key on
dbmail_header(physmessage_id);
As soon as I added them the queries were sub second on my system.
Alan
On 15/10/2013 07:43, Thomas Raschbacher wrote:
hi.
I just noticed this in my cronjob log:
Repairing DBMAIL headernames integrity...
Oct 15 03:15:08 lordvan.com dbmail-util[2340]: [0x114b030] Error:[db]
db_query(+360): SQLException: ERROR: canceling statement due to
statement timeout
Oct 15 03:15:08 lordvan.com dbmail-util[2340]: [0x114b030] Error:[db]
db_query(+361): failed query [SELECT hn.id FROM dbmail_headername hn
LEFT JOIN dbmail_header h ON hn.id = h.headername_id WHERE
h.headername_id IS NULL]
Ok. Found [0] unconnected headernames.
--- Repairing unconnected headernames took 300 seconds
now I was wondering .. why is this taking so long so I took the query
and (tried to) run it .. it really runs like forever ..
after checking some postgres config (shared_mem,..) i did some tuning
but to no avail ..
then I looked at the db schema:
-- Name: dbmail_header; Type: TABLE; Schema: public; Owner: dbmail;
Tablespace:
--
CREATE TABLE dbmail_header (
physmessage_id bigint NOT NULL,
headername_id bigint NOT NULL,
headervalue_id bigint NOT NULL
);
ALTER TABLE public.dbmail_header OWNER TO dbmail;
--
-- Name: dbmail_headername_id_seq; Type: SEQUENCE; Schema: public;
Owner: dbmail
--
CREATE SEQUENCE dbmail_headername_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.dbmail_headername_id_seq OWNER TO dbmail;
--
-- Name: dbmail_headername; Type: TABLE; Schema: public; Owner:
dbmail;
Tablespace:
--
CREATE TABLE dbmail_headername (
id bigint DEFAULT nextval('dbmail_headername_id_seq'::regclass)
NOT
NULL,
headername character varying(100) DEFAULT
'BROKEN_HEADER'::character varying NOT NULL
);
ALTER TABLE public.dbmail_headername OWNER TO dbmail;
--
-- Name: dbmail_headervalue_id_seq; Type: SEQUENCE; Schema: public;
Owner: dbmail
--
CREATE SEQUENCE dbmail_headervalue_id_seq
START WITH 1
INCREMENT BY 1
NO MINVALUE
NO MAXVALUE
CACHE 1;
ALTER TABLE public.dbmail_headervalue_id_seq OWNER TO dbmail;
And then I was confused .. dbmail-util issues a query which does check
a
column set in the database as NOT NULL is NULL ... seems pointless
since
the DB wouldn't allow that ..
Is this some remnant of the past or for some other DB ?
In any case it seems pointless to me to run this at all - at least on
postgres- and from what I remember the other DB systems should also
not
allow this ..
as for why the query takes forever (>5min) i don'T know since there
are
no columns where this is NULL anyway .. (running the query without the
WHERE clause is as fast as one would expect)
maybe I'm missing something here .. if so please enlighten me.
Regards
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
--
Persistent Objects Ltd
128 Lilleshall Road
Morden SM4 6DR
The Home of Lasting Solutions
t 0208 544 5292
m 079 3030 5004
w p-o.co.uk
skype: alan-hicks-london
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
[email protected]
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
