Mailing List Archive: DBMail: users

mysql + slow queries

1 2

View All

Index | Next | Previous | View Threaded

auryn at zirakzigil

Oct 24, 2008, 3:41 AM

Post #1 of 38 (4021 views)
Permalink

mysql + slow queries

I'm having serious problems in a production environment with
slow queries.
S.o.: freebsd 7 stable amd64. DB: mysql5.1.28 compiled from source

The query that slows the db is:
SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxxx ORDER BY messageblk_idnr

I've set up the db as instructed in the dbmail wiki. Everything was smooth
for several days, than these queries have begun to slow the db server.

I know this probably depends on the db and not on dbmail, but any suggestion
on how to solve this would be greatly appreciated...

Thanks.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

jake at vapourforge

Oct 24, 2008, 4:26 AM

Post #2 of 38 (3929 views)
Permalink

Re: mysql + slow queries [In reply to]

Giulio Ferro wrote:
> I'm having serious problems in a production environment with
> slow queries.
> S.o.: freebsd 7 stable amd64. DB: mysql5.1.28 compiled from source
>
> The query that slows the db is:
> SELECT messageblk, is_header FROM dbmail_messageblks WHERE
> physmessage_id = xxxx ORDER BY messageblk_idnr
>
>
> I've set up the db as instructed in the dbmail wiki. Everything was
> smooth
> for several days, than these queries have begun to slow the db server.
>
> I know this probably depends on the db and not on dbmail, but any
> suggestion
> on how to solve this would be greatly appreciated...
>
> Thanks.
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
try doing an explain select, but really that should be one of the
fastest queries in the thing.
how much ram do you have in the machine and how big is the database?

explain SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxxx ORDER BY messageblk_idnr

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

auryn at zirakzigil

Oct 24, 2008, 5:46 AM

Post #3 of 38 (3922 views)
Permalink

Re: mysql + slow queries [In reply to]

Jake Anderson wrote:
> Giulio Ferro wrote:
>> I'm having serious problems in a production environment with
>> slow queries.
>> S.o.: freebsd 7 stable amd64. DB: mysql5.1.28 compiled from source
>>
>> The query that slows the db is:
>> SELECT messageblk, is_header FROM dbmail_messageblks WHERE
>> physmessage_id = xxxx ORDER BY messageblk_idnr
>>
>>
>> I've set up the db as instructed in the dbmail wiki. Everything was
>> smooth
>> for several days, than these queries have begun to slow the db server.
>>
>> I know this probably depends on the db and not on dbmail, but any
>> suggestion
>> on how to solve this would be greatly appreciated...
>>
>> Thanks.
>>
>> _______________________________________________
>> DBmail mailing list
>> DBmail [at] dbmail
>> https://mailman.fastxs.nl/mailman/listinfo/dbmail
> try doing an explain select, but really that should be one of the
> fastest queries in the thing.
> how much ram do you have in the machine and how big is the database?
>
> explain SELECT messageblk, is_header FROM dbmail_messageblks WHERE
> physmessage_id = xxxx ORDER BY messageblk_idnr

This is the explain:

+----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
| id | select_type | table | type |
possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
| 1 | SIMPLE | dbmail_messageblks | index |
physmessage_id_index,physmessage_id_is_header_index | PRIMARY | 8
| NULL | 2360882 | Using where |
+----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
1 row in set (0.05 sec)

From my test, the problem with this query is the "order by".
Without that, the query takes relatively show time to complete.

Here is the funny thing:
select messageblk_idnr from dbmail_messageblks where physmessage_id=xxx
order by messageblk_idnr;
==> takes about 0.05 sec

SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxx ORDER BY messageblk_idnr;
==> takes about 4 minutes

SELECT messageblk, is_header FROM dbmail_messageblks WHERE
physmessage_id = xxx;
==> takes about 0.10 sec

Any idea?
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

Oct 24, 2008, 6:18 AM

Post #4 of 38 (3929 views)
Permalink

Re: mysql + slow queries [In reply to]

Giulio Ferro wrote:
> Jake Anderson wrote:
>> Giulio Ferro wrote:
>>> I'm having serious problems in a production environment with
>>> slow queries.
>>> S.o.: freebsd 7 stable amd64. DB: mysql5.1.28 compiled from source
>>>
>>> The query that slows the db is:
>>> SELECT messageblk, is_header FROM dbmail_messageblks WHERE
>>> physmessage_id = xxxx ORDER BY messageblk_idnr
>>>
>>>
>>> I've set up the db as instructed in the dbmail wiki. Everything was
>>> smooth
>>> for several days, than these queries have begun to slow the db server.
>>>
>>> I know this probably depends on the db and not on dbmail, but any
>>> suggestion
>>> on how to solve this would be greatly appreciated...
>>>
>>> Thanks.
>>>
>>> _______________________________________________
>>> DBmail mailing list
>>> DBmail [at] dbmail
>>> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>> try doing an explain select, but really that should be one of the
>> fastest queries in the thing.
>> how much ram do you have in the machine and how big is the database?
>>
>> explain SELECT messageblk, is_header FROM dbmail_messageblks WHERE
>> physmessage_id = xxxx ORDER BY messageblk_idnr
>
> This is the explain:
>
> +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
>
> | id | select_type | table | type |
> possible_keys | key | key_len
> | ref | rows | Extra |
> +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+
>
> | 1 | SIMPLE | dbmail_messageblks | index |
> physmessage_id_index,physmessage_id_is_header_index | PRIMARY | 8
> | NULL | 2360882 | Using where |
> +----+-------------+--------------------+-------+-----------------------------------------------------+---------+---------+------+---------+-------------+

That don't smell right. At all!
2360882 rows is totally fucked. That query shouldn't have to scan more
than two or three rows. Your indexes are possibly messed up. The
physmessage_id_index isn't being used at all, afaict.

try:

show create table dbmail_messageblks;

maybe that will tell us something.

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

daniel at gosi

Oct 24, 2008, 6:24 AM

Post #5 of 38 (3929 views)
Permalink

Re: mysql + slow queries [In reply to]

>>
> This is the explain:
>
> +----+-------------+--------------------+-------
> +-----------------------------------------------------+---------
> +---------+------+---------+-------------+
> | id | select_type | table | type |
> possible_keys | key |
> key_len | ref | rows | Extra |
> +----+-------------+--------------------+-------
> +-----------------------------------------------------+---------
> +---------+------+---------+-------------+
> | 1 | SIMPLE | dbmail_messageblks | index |
> physmessage_id_index,physmessage_id_is_header_index | PRIMARY |
> 8 | NULL | 2360882 | Using where |
> +----+-------------+--------------------+-------
> +-----------------------------------------------------+---------
> +---------+------+---------+-------------+
> 1 row in set (0.05 sec)

This is good, it says type=index, which means it will use the index to
speed up the query.
So the database scheme for that query seems to be good.

>
>
> From my test, the problem with this query is the "order by".
> Without that, the query takes relatively show time to complete.
>
> Here is the funny thing:
> select messageblk_idnr from dbmail_messageblks where
> physmessage_id=xxx order by messageblk_idnr;
> ==> takes about 0.05 sec
> SELECT messageblk, is_header FROM dbmail_messageblks WHERE
> physmessage_id = xxx ORDER BY messageblk_idnr;
> ==> takes about 4 minutes
>
> SELECT messageblk, is_header FROM dbmail_messageblks WHERE
> physmessage_id = xxx;
> ==> takes about 0.10 sec

Well that is certainly odd, that the ordering adds that much time.
Anyhow, I ran the
querries on my 10GB database of dbmail and it does take less then 0,05
seconds.
I think you should try to increase your innodb_buffer_pool within your
mysql config.

>
>
>
> Any idea?

You can also check mysql.com for "database tuning", they have a few
guides and some
nice web cast on that topic, including stuff from Jay Pipes, just
Google the name, you will find
it for sure. Also mysql performance blog is a good point to start, and
focus on the innodb
related parameters.

Also if you post your hardware configuration and the my.cnf here
somebody might have a
hint for you.

greetings,
Daniel
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

auryn at zirakzigil

Oct 24, 2008, 6:56 AM

Post #6 of 38 (3923 views)
Permalink

Re: mysql + slow queries [In reply to]

Paul J Stevens wrote:
> That don't smell right. At all!
> 2360882 rows is totally fucked. That query shouldn't have to scan more
> than two or three rows. Your indexes are possibly messed up. The
> physmessage_id_index isn't being used at all, afaict.
>
> try:
>
> show create table dbmail_messageblks;
>
> maybe that will tell us something.
>
>

Mmh, I've used the standard sql files to create the dbmail db

mysql> show create table dbmail_messageblks;
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create
Table
|
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| dbmail_messageblks | CREATE TABLE `dbmail_messageblks` (
`messageblk_idnr` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0',
`messageblk` longblob NOT NULL,
`blocksize` bigint(20) unsigned NOT NULL DEFAULT '0',
`is_header` tinyint(3) unsigned NOT NULL DEFAULT '0',
PRIMARY KEY (`messageblk_idnr`),
KEY `physmessage_id_index` (`physmessage_id`),
KEY `physmessage_id_is_header_index` (`physmessage_id`,`is_header`),
CONSTRAINT `dbmail_messageblks_ibfk_1` FOREIGN KEY (`physmessage_id`)
REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=747417 DEFAULT CHARSET=utf8 |
+--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.06 sec)

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

auryn at zirakzigil

Oct 24, 2008, 7:07 AM

Post #7 of 38 (3925 views)
Permalink

Re: mysql + slow queries [In reply to]

Daniel Urst�ger wrote:
>>
>> Here is the funny thing:
>> select messageblk_idnr from dbmail_messageblks where
>> physmessage_id=xxx order by messageblk_idnr;
>> ==> takes about 0.05 sec
>> SELECT messageblk, is_header FROM dbmail_messageblks WHERE
>> physmessage_id = xxx ORDER BY messageblk_idnr;
>> ==> takes about 4 minutes
>>
>> SELECT messageblk, is_header FROM dbmail_messageblks WHERE
>> physmessage_id = xxx;
>> ==> takes about 0.10 sec
>
> Well that is certainly odd, that the ordering adds that much time.
> Anyhow, I ran the
> querries on my 10GB database of dbmail and it does take less then 0,05
> seconds.

My dbmail db is about 18.5 GB.

> I think you should try to increase your innodb_buffer_pool within your
> mysql config.
>
I've set innodb_buffer_pool_size to 1500M and the db creates 3 files of that
size. I can't have them any bigger because the system complains that
there is a 4 GB limit, even though the system is full 64 (and I've
compiled mysql
on it)
>
> You can also check mysql.com for "database tuning", they have a few
> guides and some
> nice web cast on that topic, including stuff from Jay Pipes, just
> Google the name, you will find
> it for sure. Also mysql performance blog is a good point to start, and
> focus on the innodb
> related parameters.
>
I'll try that...

> Also if you post your hardware configuration and the my.cnf here
> somebody might have a
> hint for you.

Ok, here it is:

------------------------------------------------------------------------------------------------
[client]
#password = [your_password]
port = 3306
socket = /tmp/mysql.sock

[mysqld]

port = 3306
socket = /tmp/mysql.sock

back_log = 50

max_connections = 10000

max_connect_errors = 10

table_cache = 2048

max_allowed_packet = 100M

binlog_cache_size = 1M

max_heap_table_size = 64M

sort_buffer_size = 80M

join_buffer_size = 80M

thread_cache_size = 8

thread_concurrency = 8

query_cache_size = 64M

query_cache_limit = 2M

ft_min_word_len = 4

default_table_type = MYISAM

thread_stack = 192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 64M

log-bin=mysql-bin

log_slow_queries

long_query_time = 2

log_long_format

server-id = 1

key_buffer_size = 32M

read_buffer_size = 2M

read_rnd_buffer_size = 16M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 10G

myisam_max_extra_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 1500M

innodb_data_file_path = ibdata1:100M:autoextend

innodb_file_io_threads = 8

innodb_thread_concurrency = 16

innodb_flush_log_at_trx_commit = 1

innodb_log_buffer_size = 8M
innodb_log_file_size = 1200M
innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout = 120

[mysqldump]
quick

max_allowed_packet = 1000M

[mysql]
no-auto-rehash

[isamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[myisamchk]
key_buffer = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 80000
------------------------------------------------------------------------------------------------

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

daniel at gosi

Oct 24, 2008, 7:25 AM

Post #8 of 38 (3923 views)
Permalink

Re: mysql + slow queries [In reply to]

Am 24.10.2008 um 16:07 schrieb Giulio Ferro:

> Daniel Urst�ger wrote:
>>>
>>> Here is the funny thing:
>>> select messageblk_idnr from dbmail_messageblks where
>>> physmessage_id=xxx order by messageblk_idnr;
>>> ==> takes about 0.05 sec
>>> SELECT messageblk, is_header FROM dbmail_messageblks WHERE
>>> physmessage_id = xxx ORDER BY messageblk_idnr;
>>> ==> takes about 4 minutes
>>>
>>> SELECT messageblk, is_header FROM dbmail_messageblks WHERE
>>> physmessage_id = xxx;
>>> ==> takes about 0.10 sec
>>
>> Well that is certainly odd, that the ordering adds that much time.
>> Anyhow, I ran the
>> querries on my 10GB database of dbmail and it does take less then
>> 0,05 seconds.
>
> My dbmail db is about 18.5 GB.
>
>> I think you should try to increase your innodb_buffer_pool within
>> your mysql config.
>>
> I've set innodb_buffer_pool_size to 1500M and the db creates 3 files
> of that
> size. I can't have them any bigger because the system complains that
> there is a 4 GB limit, even though the system is full 64 (and I've
> compiled mysql
> on it)
>>
>> You can also check mysql.com for "database tuning", they have a few
>> guides and some
>> nice web cast on that topic, including stuff from Jay Pipes, just
>> Google the name, you will find
>> it for sure. Also mysql performance blog is a good point to start,
>> and focus on the innodb
>> related parameters.
>>
> I'll try that...
>
>> Also if you post your hardware configuration and the my.cnf here
>> somebody might have a
>> hint for you.
>
>
> Ok, here it is:

Well, seems like I have been wrong, as Paul pointed out the selected
rows are
way to many, so there seems to be something wrong within the database.
I just checked
for myself and on my explains there are usually from 2 to well 10 rows
but
not 2 000 000.

Greetings,
Daniel_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

Oct 24, 2008, 7:32 AM

Post #9 of 38 (3921 views)
Permalink

Re: mysql + slow queries [In reply to]

Giulio Ferro wrote:

>> Also if you post your hardware configuration and the my.cnf here
>> somebody might have a
>> hint for you.

No hardware specs?

>
>
> Ok, here it is:

> max_connections = 10000

this is *very* large. Default for 5.1 is 151.

> innodb_log_file_size = 1200M

also very large

I'm under the impression that you've been fiddling with my.cnf a lot.
Perhaps it's better to start with a boilerplate version as included in
the tarball, leaving most things to their default values.

Other than that: I haven't used 5.1 yet myself, so ymmv.

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

auryn at zirakzigil

Oct 24, 2008, 7:57 AM

Post #10 of 38 (3923 views)
Permalink

Re: mysql + slow queries [In reply to]

Paul J Stevens wrote:
> Giulio Ferro wrote:
>
>
>>> Also if you post your hardware configuration and the my.cnf here
>>> somebody might have a
>>> hint for you.
>>>
>
> No hardware specs?
>
>

It's a intel dual proc quad core with 8GB RAM
Then the only things I've significantly changed are the
innodb_buffer_pool_size and innodb_log_file_size, as advised
in the dbmail wiki.
I've imported the current db from another mysql db using a simple
mysqldump command where I had experienced similar problems.
On the old db there was a very high number of record in the
dbmail_messageblks (more than 515000). Now there are 544891 and
growing steadily.

Can you think of anything that may have caused this strange behavior
and how should I bring things back to normal?

Thanks
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

jake at vapourforge

Oct 24, 2008, 8:36 AM

Post #11 of 38 (3926 views)
Permalink

Re: mysql + slow queries [In reply to]

Giulio Ferro wrote:
> Paul J Stevens wrote:
>> Giulio Ferro wrote:
>>
>>
>>>> Also if you post your hardware configuration and the my.cnf here
>>>> somebody might have a
>>>> hint for you.
>>>>
>>
>> No hardware specs?
>>
>>
>
> It's a intel dual proc quad core with 8GB RAM
> Then the only things I've significantly changed are the
> innodb_buffer_pool_size and innodb_log_file_size, as advised
> in the dbmail wiki.
> I've imported the current db from another mysql db using a simple
> mysqldump command where I had experienced similar problems.
> On the old db there was a very high number of record in the
> dbmail_messageblks (more than 515000). Now there are 544891 and
> growing steadily.
>
> Can you think of anything that may have caused this strange behavior
> and how should I bring things back to normal?
>
> Thanks
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
how big are the emails your dealing with?
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

jake at vapourforge

Oct 24, 2008, 8:37 AM

Post #12 of 38 (3925 views)
Permalink

Re: mysql + slow queries [In reply to]

Giulio Ferro wrote:
> Paul J Stevens wrote:
>> Giulio Ferro wrote:
>>
>>
>>>> Also if you post your hardware configuration and the my.cnf here
>>>> somebody might have a
>>>> hint for you.
>>>>
>>
>> No hardware specs?
>>
>>
>
> It's a intel dual proc quad core with 8GB RAM
> Then the only things I've significantly changed are the
> innodb_buffer_pool_size and innodb_log_file_size, as advised
> in the dbmail wiki.
> I've imported the current db from another mysql db using a simple
> mysqldump command where I had experienced similar problems.
> On the old db there was a very high number of record in the
> dbmail_messageblks (more than 515000). Now there are 544891 and
> growing steadily.
>
> Can you think of anything that may have caused this strange behavior
> and how should I bring things back to normal?
>
> Thanks
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
Oh perhaps a "fix" would be to upgrade to 2.3 series and then imap copy
everything over.
that might help things along a little

--

Vapour Forge

Jake Anderson

Project Manager

Mobile: 0412 897 125

Email: jake [at] vapourforge

Web Page: www.vapourforge.com

Your source for custom IT services

Attachments:

vf.gif (1.45 KB)

auryn at zirakzigil

Oct 25, 2008, 2:42 AM

Post #13 of 38 (3902 views)
Permalink

Re: mysql + slow queries [In reply to]

Giulio Ferro wrote:
> Paul J Stevens wrote:
>> That don't smell right. At all!
>> 2360882 rows is totally fucked. That query shouldn't have to scan more
>> than two or three rows. Your indexes are possibly messed up. The
>> physmessage_id_index isn't being used at all, afaict.
>>
>> try:
>>
>> show create table dbmail_messageblks;
>>
>> maybe that will tell us something.
>>
>>
>
> Mmh, I've used the standard sql files to create the dbmail db
>
> mysql> show create table dbmail_messageblks;
> +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>
> | Table | Create
> Table
> |
> +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>
> | dbmail_messageblks | CREATE TABLE `dbmail_messageblks` (
> `messageblk_idnr` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
> `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0',
> `messageblk` longblob NOT NULL,
> `blocksize` bigint(20) unsigned NOT NULL DEFAULT '0',
> `is_header` tinyint(3) unsigned NOT NULL DEFAULT '0',
> PRIMARY KEY (`messageblk_idnr`),
> KEY `physmessage_id_index` (`physmessage_id`),
> KEY `physmessage_id_is_header_index` (`physmessage_id`,`is_header`),
> CONSTRAINT `dbmail_messageblks_ibfk_1` FOREIGN KEY (`physmessage_id`)
> REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE
> CASCADE
> ) ENGINE=InnoDB AUTO_INCREMENT=747417 DEFAULT CHARSET=utf8 |
> +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>
> 1 row in set (0.06 sec)

If you explain to me what this table do and point me in the right direction,
I can try and write a script to correct the messed up data, if you deem
it possible.

I'm looking for any solution short of creating a new db and importing
the boxes
with imapsyc, given how bad this utility works...
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

Oct 25, 2008, 6:06 AM

Post #14 of 38 (3895 views)
Permalink

Re: mysql + slow queries [In reply to]

Giulio Ferro wrote:
> Giulio Ferro wrote:
>> Paul J Stevens wrote:
>>> That don't smell right. At all!
>>> 2360882 rows is totally fucked. That query shouldn't have to scan more
>>> than two or three rows. Your indexes are possibly messed up. The
>>> physmessage_id_index isn't being used at all, afaict.
>>>
>>> try:
>>>
>>> show create table dbmail_messageblks;
>>>
>>> maybe that will tell us something.
>>>
>>>
>>
>> Mmh, I've used the standard sql files to create the dbmail db
>>
>> mysql> show create table dbmail_messageblks;
>> +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>>
>> | Table | Create
>> Table
>> |
>> +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>>
>> | dbmail_messageblks | CREATE TABLE `dbmail_messageblks` (
>> `messageblk_idnr` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
>> `physmessage_id` bigint(20) unsigned NOT NULL DEFAULT '0',
>> `messageblk` longblob NOT NULL,
>> `blocksize` bigint(20) unsigned NOT NULL DEFAULT '0',
>> `is_header` tinyint(3) unsigned NOT NULL DEFAULT '0',
>> PRIMARY KEY (`messageblk_idnr`),
>> KEY `physmessage_id_index` (`physmessage_id`),
>> KEY `physmessage_id_is_header_index` (`physmessage_id`,`is_header`),
>> CONSTRAINT `dbmail_messageblks_ibfk_1` FOREIGN KEY (`physmessage_id`)
>> REFERENCES `dbmail_physmessage` (`id`) ON DELETE CASCADE ON UPDATE
>> CASCADE
>> ) ENGINE=InnoDB AUTO_INCREMENT=747417 DEFAULT CHARSET=utf8 |
>> +--------------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
>>
>> 1 row in set (0.06 sec)
>
> If you explain to me what this table do and point me in the right
> direction,
> I can try and write a script to correct the messed up data, if you deem
> it possible.

How did you fill the table to begin with?

>
> I'm looking for any solution short of creating a new db and importing
> the boxes
> with imapsyc, given how bad this utility works...
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> https://mailman.fastxs.nl/mailman/listinfo/dbmail
>

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

Oct 25, 2008, 6:08 AM

Post #15 of 38 (3908 views)
Permalink

Re: mysql + slow queries [In reply to]

Jake Anderson wrote:
> Oh perhaps a "fix" would be to upgrade to 2.3 series and then imap copy
> everything over.
> that might help things along a little

Dude, don't do that.

It doesnt explain the problems, and I seriously doubt it would solve them.

I'd start by downgrading mysql from 5.1 to 5.0 since 5.1 is not even GA yet.

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

auryn at zirakzigil

Oct 25, 2008, 6:11 AM

Post #16 of 38 (3911 views)
Permalink

Re: mysql + slow queries [In reply to]

Paul J Stevens wrote:
>> If you explain to me what this table do and point me in the right
>> direction,
>> I can try and write a script to correct the messed up data, if you deem
>> it possible.
>>
>
> How did you fill the table to begin with?
>
>
Part of them grew naturally with use, part I imported with
imapsync from an old vpopmail / courier server.
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

Oct 25, 2008, 6:21 AM

Post #17 of 38 (3898 views)
Permalink

Re: mysql + slow queries [In reply to]

auryn at zirakzigil

Oct 25, 2008, 6:31 AM

Post #18 of 38 (3895 views)
Permalink

Re: mysql + slow queries [In reply to]

Paul J Stevens wrote:
>>> No hardware specs?
>>>
>>>
>>>
>> It's a intel dual proc quad core with 8GB RAM
>>
>
> So there's no problem there, but what about your storage? Did you put
> the database on NFS or another network-store?
>
>
Nope. It's standard freebsd ufs2 partition on a 7TB dell md1000
disk enclosure (40MB/sec write, 200MB/sec read)

It is possible however that, in the server where the database run,
the file system got messed up. I had to mysqldump the dbmail db
and import it in the new server.
I checked the old mysql db, and there were about 500000 rows in that
table, so maybe the problem arose in the past.

Anyway I'm willing to try a "cleaning" script to restore things back to
normal, if you tell me exactly what it should do.

Thanks.
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

ml at kiewel-online

Oct 25, 2008, 6:37 AM

Post #19 of 38 (3895 views)
Permalink

Re: mysql + slow queries [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Giulio Ferro schrieb:
> Paul J Stevens wrote:
>>>> No hardware specs?
>>>>
>>>>
>>> It's a intel dual proc quad core with 8GB RAM
>>>
>>
>> So there's no problem there, but what about your storage? Did you put
>> the database on NFS or another network-store?
>>
>>
> Nope. It's standard freebsd ufs2 partition on a 7TB dell md1000
> disk enclosure (40MB/sec write, 200MB/sec read)
>
> It is possible however that, in the server where the database run,
> the file system got messed up. I had to mysqldump the dbmail db
> and import it in the new server.
> I checked the old mysql db, and there were about 500000 rows in that
> table, so maybe the problem arose in the past.
>

BTW: Did you ever run dbmail-util?

Uwe
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iQIcBAEBAgAGBQJJAyEhAAoJEEJXG7BUuynnkS8P/1eSY1JLy6QfX9nGEA5sPx1f
k9N6RWO+dWfhAaRcnQn8dz73WjO4pTBUi4vnuUSfZGJw1mO/rb5UVp+qDJujzsA6
6ryk19FV4DGRxq/FRCYkwrlVOujZXukZcumPa0L2DfiLI00a7WeZpBKj3Z90HQBk
C0Va5D7qukSlCpFuN2lEyMz1Yse5SbMC23aGeNJrwG3jph9hK4pk/+MtUWIbEsmb
VQl3iZlLSYrrgbo+tFiM+S+GUa2Ay88lr9gFfp8o0MXXcNktG3WF5p+vrHvi5qOb
HWQ9Hz9DqoKffsBknxnoV5zb5glEjwFf+ZV1qlJTeWaXEnlyZZsteGsl+VSn35Ir
G3kJtC7iVAiaRnLOiWU7tjIkg4P8j+LkUk4VrDbAWCaBugPJhdgShrPdr7vNcYfa
rPrt2Z20JUYHZXGxAaGkUnB3pw85h1Bvjc2wIU8A3Wdigb2oyiu+w7A3xgWuXVbW
Lqqt5mPxKw6uHKrNNGjZCQdwn8SPhtvcvyMcgJkRC1ogE6lu4wJomTRShbEtP+CA
BnEZrOK0CycMx51kLB9605S4pUHseV7+7yxYjurHs6NZHYLM4qCOreQKf7F2cbjX
sEAn1tHS6gTVxW8nYqffZ3tlCQxTcOpX/458IFMXe0cxYhH0k5q11hSC5obHlsEX
PRKsZ4hmw4t5pjwl2Jrv
=bXZd
-----END PGP SIGNATURE-----
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

auryn at zirakzigil

Oct 25, 2008, 6:43 AM

Post #20 of 38 (3897 views)
Permalink

Re: mysql + slow queries [In reply to]

Uwe Kiewel wrote:
> BTW: Did you ever run dbmail-util?
>
No, never.
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

ml at kiewel-online

Oct 25, 2008, 6:54 AM

Post #21 of 38 (3895 views)
Permalink

Re: mysql + slow queries [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Giulio Ferro schrieb:
> Uwe Kiewel wrote:
>> BTW: Did you ever run dbmail-util?
>>
> No, never.

So, you will keep *every* deleted message!

You know about the cleanup functionality of that utility? It is highly
recommended to run "dbmail-util -c -t -u -p -d -y" periodically, e.g.
nightly.

smtp:~# dbmail-util
*** dbmail-util ***
Use this program to maintain your DBMail database.
See the man page for more info. Summary:

-a perform all checks (in this release: -ctubpds)
-c clean up database (optimize/vacuum)
-t test for message integrity
-u null message check
-b body/header/envelope cache check
-p purge messages have the DELETE status set
-d set DELETE status for deleted messages
-s remove dangling/invalid aliases and forwards
-r time clear the replycache used for autoreply/vacation
-l time clear the IP log used for IMAP/POP-before-SMTP
the time syntax is [<hours>h][<minutes>m]
valid examples: 72h, 4h5m, 10m

Common options for all DBMail utilities:
-f file specify an alternative config file
-q quietly skip interactive prompts
use twice to suppress error messages
-n show the intended action but do not perform it, no to all
-y perform all proposed actions, as though yes to all
-v verbose details
-V show the version
-h show this help message

HTH,
Uwe

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iQIcBAEBAgAGBQJJAyUmAAoJEEJXG7BUuynn9H8P/3m0LTMgN/QsXFIZOQnbS1rY
lDS4zrh3J2+HuLeJ1ZF1mQ0Yn/39dBe/lPbm9BtKd16eEJLl5kB0EUstXN/GDOOM
qxEW3zy8KEreAZ70OZwaafTJMMg8lMQWn68nHVa2or+KbRlgbu7BjZT/12Oq8d3A
J+rVEAhRToIgYYNPXtt/TR/YSbPSv350Ey8VRkyC5nShdSppNFQuTYRajLpLrpkT
ICu23anirBOtxake8/FUSbNCadV58dCeRE4xAY8PlpwEA5lOKnIdmsy3O+m1OXBY
9lU/4mN+k/FGJ9kl3KP3/WfYibI9TxGyUv1iuDD+lJDtSzaRgdETSZOs3XoANxoV
TTSldPSy7JpINovXRdYRu82QSrfoolhx2kPwQNuBRYFMKbhMX9XQIG84Ufnx5DoQ
YY91XHeM8kZmk2Vi0RQNWt+UiR1+p2ccUIt9xFe3/YFrF97FWQ9P7l9vEZBTf2Ae
/KgsjL/pAbuIH8L8vtiFkOueYjC+xBl9RZpYQvkalAQ9MmNjrYoPMDczAEdS3BMe
G9EGyGq/ovtbgbb2/AxV0SUOUvTjISEmSjYE/gKUZrdemypyyTXOrV+EizmeO0QK
u2KFjGYPgY6VSd2Ly+wXrVdBBSj6C4nOrQak2TpZFLHlPAoDz5JBKxh5dUlDkJAJ
fCmmChZ9LY2LjCcfIdL7
=/Lkb
-----END PGP SIGNATURE-----
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

auryn at zirakzigil

Oct 25, 2008, 7:08 AM

Post #22 of 38 (3897 views)
Permalink

Re: mysql + slow queries [In reply to]

Uwe Kiewel wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Giulio Ferro schrieb:
>
>> Uwe Kiewel wrote:
>>
>>> BTW: Did you ever run dbmail-util?
>>>
>>>
>> No, never.
>>
>
> So, you will keep *every* deleted message!
>
> You know about the cleanup functionality of that utility? It is highly
> recommended to run "dbmail-util -c -t -u -p -d -y" periodically, e.g.
> nightly.
>
>

Can it be that the cause of the mess in the dbmail-messageblks
table? I will run it, if it can be any good...
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

ml at kiewel-online

Oct 25, 2008, 7:15 AM

Post #23 of 38 (3898 views)
Permalink

Re: mysql + slow queries [In reply to]

Giulio Ferro schrieb:
> Uwe Kiewel wrote:
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>> Giulio Ferro schrieb:
>>
>>> Uwe Kiewel wrote:
>>>
>>>> BTW: Did you ever run dbmail-util?
>>>>
>>> No, never.
>>>
>>
>> So, you will keep *every* deleted message!
>>
>> You know about the cleanup functionality of that utility? It is highly
>> recommended to run "dbmail-util -c -t -u -p -d -y" periodically, e.g.
>> nightly.
>>
>>
>
> Can it be that the cause of the mess in the dbmail-messageblks
> table? I will run it, if it can be any good...

Might be so. Run it twice, because of "set delete flag" and "delete
messages".

Paul can explain it better, for sure :-)

HTH,
Uwe
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

auryn at zirakzigil

Oct 25, 2008, 7:20 AM

Post #24 of 38 (3898 views)
Permalink

Re: mysql + slow queries [In reply to]

Uwe Kiewel wrote:
>> Can it be that the cause of the mess in the dbmail-messageblks
>> table? I will run it, if it can be any good...
>>
>
> Might be so. Run it twice, because of "set delete flag" and "delete
> messages".
>
> Paul can explain it better, for sure :-)
>

Ok, I'm waiting for what he says... :-)
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

paul at nfg

Oct 25, 2008, 7:39 AM

Post #25 of 38 (3898 views)
Permalink

Re: mysql + slow queries [In reply to]

Giulio Ferro wrote:
> Uwe Kiewel wrote:
>>> Can it be that the cause of the mess in the dbmail-messageblks
>>> table? I will run it, if it can be any good...
>>>
>>
>> Might be so. Run it twice, because of "set delete flag" and "delete
>> messages".
>>
>> Paul can explain it better, for sure :-)
>>
>
> Ok, I'm waiting for what he says... :-)

Run it twice to get rid of all deleted messages. If this deletes a lot
of messages you should probably also run

OPTIMIZE_TABLE <tablename>;

for - at least - dbmail_messages and dbmail_messageblks

All of this will take a long time to run.

--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
https://mailman.fastxs.nl/mailman/listinfo/dbmail

1 2

View All

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads