|
List Info
Thread: Very slow database
|
|
| Very slow database |
 
Netherlands |
2007-02-20 03:30:59 |
Hi all,
I have some issues with my database. Everytime there is some
database
action it is extremely slow and the mysql server is putting
lines in the
"slow" log file.
Here is some from the log file:
# Query_time: 56 Lock_time: 0 Rows_sent: 447
Rows_examined: 9444548
SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
JOIN dbmail_messages m ON v.physmessage_id=m.physmessage_id
JOIN
dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr JOIN
dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
AND message_idnr BETWEEN 15651805 AND 16208824 AND
lower(headername) IN
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
# Time: 070220 10:10:13
# User Host: dbmail[dbmail] [10.0.0.10]
# Query_time: 55 Lock_time: 0 Rows_sent: 447
Rows_examined: 9444581
SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
JOIN dbmail_messages m ON v.physmessage_id=m.physmessage_id
JOIN
dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr JOIN
dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
AND message_idnr BETWEEN 15651805 AND 16208824 AND
lower(headername) IN
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
# Time: 070220 10:16:00
# UserHost: dbmail[dbmail] [10.0.0.10]
# Query_time: 55 Lock_time: 0 Rows_sent: 2825
Rows_examined: 9444701
SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
JOIN dbmail_messages m ON v.physmessage_id=m.physmessage_id
JOIN
dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr JOIN
dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
AND message_idnr BETWEEN 15651805 AND 16210400 AND
lower(headername) IN
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
I am not sure if the "Rows_examined" section is
ok. But allmost 9,5
million rows looks very mucht to me. When i take a look in
the mailq I
found the following messages:
87B3F1059C 3080 Mon Feb 19 20:35:45 appdbexample.com
(delivery temporarily suspended: lost connection with
10.0.0.10[10.0.0.10] while sending end of data -- message
may be sent
more than once)
maikelexample.com
F3B76105A9 1752 Mon Feb 19 22:57:28 spamexample.com
(delivery temporarily suspended: lost connection with
10.0.0.10[10.0.0.10] while sending end of data -- message
may be sent
more than once)
maikelexample.com
7699C1059A 7453 Mon Feb 19 20:30:02 mailerexample.com
(delivery temporarily suspended: lost connection with
10.0.0.10[10.0.0.10] while sending end of data -- message
may be sent
more than once)
maikelexample.com
79694105AE 3181 Tue Feb 20 09:16:14 appdbexample.com
(lost connection with 10.0.0.10[10.0.0.10] while sending end
of data --
message may be sent more than once)
maikelexample.com
I hope someone have a clue.
Thx in advance.
Maikel
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
| Re: Very slow database |
 
Portugal |
2007-02-20 05:23:19 |
Hum... one thing that may help for start, in my.ini in the
[mysql] secction
put
skip-name-resolve
and restart mysql and see if there's any improvement
----- Original Message -----
From: ""Maikel Dollé [Hebbizz BV]""
<maikelhebbizz.nl>
To: <dbmaildbmail.org>
Sent: Tuesday, February 20, 2007 9:30 AM
Subject: [Dbmail] Very slow database
> Hi all,
>
> I have some issues with my database. Everytime there is
some database
> action it is extremely slow and the mysql server is
putting lines in the
> "slow" log file.
>
> Here is some from the log file:
>
> # Query_time: 56 Lock_time: 0 Rows_sent: 447
Rows_examined: 9444548
> SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
> JOIN dbmail_messages m ON
v.physmessage_id=m.physmessage_id JOIN
> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
> AND message_idnr BETWEEN 15651805 AND 16208824 AND
lower(headername) IN
>
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
>
> # Time: 070220 10:10:13
> # UserHost: dbmail[dbmail] [10.0.0.10]
> # Query_time: 55 Lock_time: 0 Rows_sent: 447
Rows_examined: 9444581
> SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
> JOIN dbmail_messages m ON
v.physmessage_id=m.physmessage_id JOIN
> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
> AND message_idnr BETWEEN 15651805 AND 16208824 AND
lower(headername) IN
>
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
>
> # Time: 070220 10:16:00
> # UserHost: dbmail[dbmail] [10.0.0.10]
> # Query_time: 55 Lock_time: 0 Rows_sent: 2825
Rows_examined: 9444701
> SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
> JOIN dbmail_messages m ON
v.physmessage_id=m.physmessage_id JOIN
> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
> AND message_idnr BETWEEN 15651805 AND 16210400 AND
lower(headername) IN
>
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
>
> I am not sure if the "Rows_examined" section
is ok. But allmost 9,5
> million rows looks very mucht to me. When i take a look
in the mailq I
> found the following messages:
>
> 87B3F1059C 3080 Mon Feb 19 20:35:45 appdbexample.com
> (delivery temporarily suspended: lost connection with
> 10.0.0.10[10.0.0.10] while sending end of data --
message may be sent
> more than once)
> maikelexample.com
>
> F3B76105A9 1752 Mon Feb 19 22:57:28 spamexample.com
> (delivery temporarily suspended: lost connection with
> 10.0.0.10[10.0.0.10] while sending end of data --
message may be sent
> more than once)
> maikelexample.com
>
> 7699C1059A 7453 Mon Feb 19 20:30:02 mailerexample.com
> (delivery temporarily suspended: lost connection with
> 10.0.0.10[10.0.0.10] while sending end of data --
message may be sent
> more than once)
> maikelexample.com
>
> 79694105AE 3181 Tue Feb 20 09:16:14 appdbexample.com
> (lost connection with 10.0.0.10[10.0.0.10] while
sending end of data --
> message may be sent more than once)
> maikelexample.com
>
> I hope someone have a clue.
>
> Thx in advance.
>
> Maikel
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
| Re: Very slow database |
Netherlands |
2007-02-20 05:57:51 |
|
I tried to put that in my MySQL config but it won't make any different.
> Hum... one thing that may help for start, in my.ini in the
[mysql]
> secction
> put
> skip-name-resolve
>;
> and restart mysql and see if there's any improvement
>
>
> ----- Original Message -----
>
From: ""Maikel Dollé [Hebbizz BV]""
<maikelhebbizz.nl>
> To: <dbmaildbmail.org>
> Sent: Tuesday, February 20, 2007 9:30 AM
> Subject:
[Dbmail] Very slow database
>
>
>> Hi all,
>>
>> I have some issues with my database. Everytime
there is some database
>> action it is extremely slow and the
mysql server is putting lines in the
>> "slow" log
file.
>>
>> Here is some from the log file:
>>
>> # Query_time: 56 Lock_time: 0 Rows_sent: 447
Rows_examined: 9444548
>> SELECT
message_idnr,headername,headervalue FROM dbmail_headervalue v
>> JOIN dbmail_messages m ON v.physmessage_id=m.physmessage_id
JOIN
>> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
>> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
>> AND message_idnr BETWEEN 15651805 AND
16208824 AND lower(headername) IN
>>
('date','to','cc','from','subject','x-priority','importance','priority','content-type');
>>
>> # Time: 070220 10:10:13
>> #
UserHost: dbmail[dbmail] [10.0.0.10]
>> # Query_time: 55
Lock_time: 0 Rows_sent: 447 Rows_examined: 9444581
>> SELECT
message_idnr,headername,headervalue FROM dbmail_headervalue v
>> JOIN dbmail_messages m ON v.physmessage_id=m.physmessage_id
JOIN
>> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
>> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
>> AND message_idnr BETWEEN 15651805 AND
16208824 AND lower(headername) IN
>>
('date','to','cc','from','subject','x-priority','importance','priority','content-type');
>>
>> # Time: 070220 10:16:00
>> #
UserHost: dbmail[dbmail] [10.0.0.10]
>> # Query_time: 55
Lock_time: 0 Rows_sent: 2825 Rows_examined: 9444701
>> SELECT
message_idnr,headername,headervalue FROM dbmail_headervalue v
>> JOIN dbmail_messages m ON v.physmessage_id=m.physmessage_id
JOIN
>> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
>> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
>> AND message_idnr BETWEEN 15651805 AND
16210400 AND lower(headername) IN
>>
('date','to','cc','from','subject','x-priority','importance','priority','content-type');
>>
>> I am not sure if the "Rows_examined"
section is ok. But allmost 9,5
>> million rows looks very mucht
to me. When i take a look in the mailq I
>> found the following
messages:
>>
>> 87B3F1059C 3080 Mon Feb 19
20:35:45 appdbexample.com
>> (delivery temporarily suspended:
lost connection with
>> 10.0.0.10[10.0.0.10] while sending end
of data -- message may be sent
>> more than once)
>>
maikelexample.com
>>
>> F3B76105A9 1752 Mon Feb 19 22:57:28
spamexample.com
>> (delivery temporarily suspended: lost
connection with
>> 10.0.0.10[10.0.0.10] while sending end of
data -- message may be sent
>> more than once)
>>
maikelexample.com
>>
>> 7699C1059A 7453 Mon Feb 19 20:30:02 mailerexample.com
>> (delivery temporarily suspended: lost connection with
>> 10.0.0.10[10.0.0.10] while sending end of data -- message may
be sent
>> more than once)
>>
maikelexample.com
>>
>> 79694105AE
3181 Tue Feb 20 09:16:14 appdbexample.com
>> (lost
connection with 10.0.0.10[10.0.0.10] while sending end of data --
>>; message may be sent more than once)
>>
maikelexample.com
>>
>> I
hope someone have a clue.
>>
>> Thx in advance.
>>
>> Maikel
>>
_______________________________________________
>> DBmail
mailing list
>> DBmaildbmail.org
>>
https://mailman.fastxs.nl/mailman/listinfo/dbmail
>>
>
> _______________________________________________
>
DBmail mailing list
> DBmaildbmail.org
>
https://mailman.fastxs.nl/mailman/listinfo/dbmail
>
|
| Re: Very slow database |
Portugal |
2007-02-20 06:07:08 |
[mysqld]
sorry
----- Original Message -----
From: "Jorge Bastos" <mysql.jorgedecimal.pt>
To: "DBMail mailinglist" <dbmaildbmail.org>
Sent: Tuesday, February 20, 2007 11:23 AM
Subject: Re: [Dbmail] Very slow database
Hum... one thing that may help for start, in my.ini in the
[mysql] secction
put
skip-name-resolve
and restart mysql and see if there's any improvement
----- Original Message -----
From: ""Maikel Dollé [Hebbizz BV]""
<maikelhebbizz.nl>
To: <dbmaildbmail.org>
Sent: Tuesday, February 20, 2007 9:30 AM
Subject: [Dbmail] Very slow database
> Hi all,
>
> I have some issues with my database. Everytime there is
some database
> action it is extremely slow and the mysql server is
putting lines in the
> "slow" log file.
>
> Here is some from the log file:
>
> # Query_time: 56 Lock_time: 0 Rows_sent: 447
Rows_examined: 9444548
> SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
> JOIN dbmail_messages m ON
v.physmessage_id=m.physmessage_id JOIN
> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
> AND message_idnr BETWEEN 15651805 AND 16208824 AND
lower(headername) IN
>
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
>
> # Time: 070220 10:10:13
> # UserHost: dbmail[dbmail] [10.0.0.10]
> # Query_time: 55 Lock_time: 0 Rows_sent: 447
Rows_examined: 9444581
> SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
> JOIN dbmail_messages m ON
v.physmessage_id=m.physmessage_id JOIN
> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
> AND message_idnr BETWEEN 15651805 AND 16208824 AND
lower(headername) IN
>
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
>
> # Time: 070220 10:16:00
> # UserHost: dbmail[dbmail] [10.0.0.10]
> # Query_time: 55 Lock_time: 0 Rows_sent: 2825
Rows_examined: 9444701
> SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
> JOIN dbmail_messages m ON
v.physmessage_id=m.physmessage_id JOIN
> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
> AND message_idnr BETWEEN 15651805 AND 16210400 AND
lower(headername) IN
>
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
>
> I am not sure if the "Rows_examined" section
is ok. But allmost 9,5
> million rows looks very mucht to me. When i take a look
in the mailq I
> found the following messages:
>
> 87B3F1059C 3080 Mon Feb 19 20:35:45 appdbexample.com
> (delivery temporarily suspended: lost connection with
> 10.0.0.10[10.0.0.10] while sending end of data --
message may be sent
> more than once)
> maikelexample.com
>
> F3B76105A9 1752 Mon Feb 19 22:57:28 spamexample.com
> (delivery temporarily suspended: lost connection with
> 10.0.0.10[10.0.0.10] while sending end of data --
message may be sent
> more than once)
> maikelexample.com
>
> 7699C1059A 7453 Mon Feb 19 20:30:02 mailerexample.com
> (delivery temporarily suspended: lost connection with
> 10.0.0.10[10.0.0.10] while sending end of data --
message may be sent
> more than once)
> maikelexample.com
>
> 79694105AE 3181 Tue Feb 20 09:16:14 appdbexample.com
> (lost connection with 10.0.0.10[10.0.0.10] while
sending end of data --
> message may be sent more than once)
> maikelexample.com
>
> I hope someone have a clue.
>
> Thx in advance.
>
> Maikel
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
| Re: Very slow database |
 
Estonia |
2007-02-20 08:49:13 |
"Maikel Dollé [Hebbizz BV]" wrote:
> I have some issues with my database. Everytime there is
some database
> action it is extremely slow and the mysql server is
putting lines in the
> "slow" log file.
Hi,
You should show us your InnoDB settings from the my.ini file
(or better
yet attach the whole conf file) and describe the machine in
terms of
RAM, CPU etc. As well as how it is used, number of imap
users etc.
Alex
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
| Re: Very slow database |
Netherlands |
2007-02-20 15:28:33 |
Maikel,
I have this same problem on a memory starved server. The sql
below
happens when you open a large mailbox with a lot of unread
messages
using thunderbird.
"Maikel Dollé [Hebbizz BV]" wrote:
> # Query_time: 56 Lock_time: 0 Rows_sent: 447
Rows_examined: 9444548
> SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
> JOIN dbmail_messages m ON
v.physmessage_id=m.physmessage_id JOIN
> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
> AND message_idnr BETWEEN 15651805 AND 16208824 AND
lower(headername) IN
>
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
--
____________________________________________________________
____
Paul Stevens paul at
nfg.nl
NET FACILITIES GROUP GPG/PGP:
1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
| Re: Very slow database |
Netherlands |
2007-02-21 06:01:45 |
Hoi Paul,
This message exist on our server also on small mailboxes. I
probably found what the problem is / was. It
seems there is something wrong with the InnoDB index file.
I'm running my migration again to see if it
resolves the problem.
I will let the list know what my solution was for my problem
maybe it will work for others.
Thx in advance.
Maikel
> Maikel,
>
>
> I have this same problem on a memory starved server.
The sql below
> happens when you open a large mailbox with a lot of
unread messages
> using thunderbird.
>
>
> "Maikel Dollé [Hebbizz BV]" wrote:
>> # Query_time: 56 Lock_time: 0 Rows_sent: 447
Rows_examined: 9444548
>> SELECT message_idnr,headername,headervalue FROM
dbmail_headervalue v
>> JOIN dbmail_messages m ON
v.physmessage_id=m.physmessage_id JOIN
>> dbmail_mailboxes b ON m.mailbox_idnr=b.mailbox_idnr
JOIN
>> dbmail_headername n ON v.headername_id=n.id WHERE
m.mailbox_idnr = 2700
>> AND message_idnr BETWEEN 15651805 AND 16208824 AND
lower(headername) IN
>>
('date','to','cc','from','subject','x-priority','importance'
,'priority','content-type');
>
>
> --
>
____________________________________________________________
____
> Paul Stevens
paul at nfg.nl
> NET FACILITIES GROUP GPG/PGP:
1024D/11F8CD31
> The Netherlands________________________________http://www.nfg.nl
> _______________________________________________
> DBmail mailing list
> DBmaildbmail.org
> htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
>
_______________________________________________
DBmail mailing list
DBmaildbmail.org
htt
ps://mailman.fastxs.nl/mailman/listinfo/dbmail
|
|
[1-7]
|
|