Mailing List Archive: DBMail: users

MySQL tweaks?

Index | Next | Previous | View Threaded

ken at jots

Sep 14, 2009, 7:17 AM

Post #1 of 12 (1656 views)
Permalink

MySQL tweaks?

Hey, all. I've got a bajillion e-mails in my e-mail. (I'm using dbmail
for archiving.) I don't care about disk space -- I've got it to throw
away. But searches take close to 5+ minutes. Any clues on indexing,
etc., that might be appropriate?

-Ken

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

dbmail at tech

Sep 14, 2009, 8:44 AM

Post #2 of 12 (1587 views)
Permalink

Re: MySQL tweaks? [In reply to]

Ken D'Ambrosio wrote:
> Hey, all. I've got a bajillion e-mails in my e-mail. (I'm using dbmail
> for archiving.) I don't care about disk space -- I've got it to throw
> away. But searches take close to 5+ minutes. Any clues on indexing,
> etc., that might be appropriate?
>
> -Ken
>
>
>
How much data are you searching through? (how big is your dbmail db?)
How much ram does the db server have?
How much ram is allocated to the db software?
Are you running MySQL or Postgres? or something else?

If MySQL, How big is your innodb_buffer_pool_size variable in my.cnf ?

SG

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

ken at jots

Sep 14, 2009, 10:00 AM

Post #3 of 12 (1589 views)
Permalink

Re: MySQL tweaks? [In reply to]

> How much data are you searching through? (how big is your dbmail db?)

Roughly 100 GB; it's got 750K messages or so.

> How much ram does the db server have?

2 GB

> How much ram is allocated to the db software?

How do I check?

> Are you running MySQL or Postgres? or something else?

MySQL

> If MySQL, How big is your innodb_buffer_pool_size variable in my.cnf ?

Just bumped it to 1 GB. Doesn't seem to be affecting things. But, upon
reading the stuff that popped up when I plugged "innodb_buffer_pool_size"
into Google, it looks like I'd need HUGE amounts of RAM for it to make a
"real" difference -- at least, with a 100+ GB database.

Thanks!

-Ken

>
>
> SG
>
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is believed to be clean.
>
>

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

vulture at netvulture

Sep 14, 2009, 10:16 AM

Post #4 of 12 (1591 views)
Permalink

Re: MySQL tweaks? [In reply to]

Ken D'Ambrosio wrote:
>> How much data are you searching through? (how big is your dbmail db?)
>>
>
> Roughly 100 GB; it's got 750K messages or so.
>
You need to clean and optimize your tables. With a quick calc, that
would put you at about 140MB/message. Are you on 2.2.x or 2.3.x?

You should first remove any messages that have been deleted and just
waiting to be deleted from the tables

run this twice: dbmail-util -ay

You should then take some downtime to do for each table
OPTIMIZE TABLE dbmail_tablename;

If you are running primarily MySQL on your server, then you will want to
target about 1.5 GB to 1.75GB of ram usage for it.

The following should get you a bit better. You may need to tweak the
numbers a bit. If you have too much, then it will fail to start. I think
these will work with 2GB of ram. Probably put you at about 1.75GB of ram
usage.

my.cnf

innodb_file_per_table
skip-locking
innodb_flush_method=O_DIRECT

sort_buffer_size=64M
read_buffer_size=64M
read_rnd_buffer_size=64M

innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=512MB
innodb_flush_log_at_trx_commit=2

query_cache_size=128M

--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

ken at jots

Sep 14, 2009, 11:34 AM

Post #5 of 12 (1585 views)
Permalink

Re: MySQL tweaks? [In reply to]

Thanks for all the pointers! Though I'm afraid your sliderule* dropped a
decimal: 1*10^11 / 7.5*10^5 = 133333.3333, or 13K/message. I'm running
2.2.9 (Ubuntu package). I'll kick the tires and see what happens.

Again, thanks!

-Ken

* Use of term "sliderule" not intended as a pejorative, but, rather, from
the perspective of someone who's lost all too many decimal points while
using same.

> You need to clean and optimize your tables. With a quick calc, that
> would put you at about 140MB/message. Are you on 2.2.x or 2.3.x?
>
> You should first remove any messages that have been deleted and just
> waiting to be deleted from the tables
>
> run this twice: dbmail-util -ay
>
> You should then take some downtime to do for each table
> OPTIMIZE TABLE dbmail_tablename;
>
>
> If you are running primarily MySQL on your server, then you will want to
> target about 1.5 GB to 1.75GB of ram usage for it.
>
> The following should get you a bit better. You may need to tweak the
> numbers a bit. If you have too much, then it will fail to start. I think
> these will work with 2GB of ram. Probably put you at about 1.75GB of ram
> usage.
>
> my.cnf
>
> innodb_file_per_table skip-locking innodb_flush_method=O_DIRECT
>
> sort_buffer_size=64M read_buffer_size=64M read_rnd_buffer_size=64M
>
> innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=512MB
> innodb_flush_log_at_trx_commit=2
>
> query_cache_size=128M
>
>
> --
> Scanned for viruses and dangerous content by MailScanner
>
>
> _______________________________________________
> DBmail mailing list
> DBmail [at] dbmail
> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>
>
> --
> This message has been scanned for viruses and
> dangerous content by MailScanner, and is believed to be clean.
>
>

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

vulture at netvulture

Sep 14, 2009, 6:08 PM

Post #6 of 12 (1591 views)
Permalink

Re: MySQL tweaks? [In reply to]

Ok - but your sliderule is bent too.
139KB/message looks more correct. I must have had too many 1024's in
there to the first time.

Ken D'Ambrosio wrote:
> Thanks for all the pointers! Though I'm afraid your sliderule* dropped a
> decimal: 1*10^11 / 7.5*10^5 = 133333.3333, or 13K/message. I'm running
> 2.2.9 (Ubuntu package). I'll kick the tires and see what happens.
>
> Again, thanks!
>
> -Ken
>
> * Use of term "sliderule" not intended as a pejorative, but, rather, from
> the perspective of someone who's lost all too many decimal points while
> using same.
>
>
>
>> You need to clean and optimize your tables. With a quick calc, that
>> would put you at about 140MB/message. Are you on 2.2.x or 2.3.x?
>>
>> You should first remove any messages that have been deleted and just
>> waiting to be deleted from the tables
>>
>> run this twice: dbmail-util -ay
>>
>> You should then take some downtime to do for each table
>> OPTIMIZE TABLE dbmail_tablename;
>>
>>
>> If you are running primarily MySQL on your server, then you will want to
>> target about 1.5 GB to 1.75GB of ram usage for it.
>>
>> The following should get you a bit better. You may need to tweak the
>> numbers a bit. If you have too much, then it will fail to start. I think
>> these will work with 2GB of ram. Probably put you at about 1.75GB of ram
>> usage.
>>
>> my.cnf
>>
>> innodb_file_per_table skip-locking innodb_flush_method=O_DIRECT
>>
>> sort_buffer_size=64M read_buffer_size=64M read_rnd_buffer_size=64M
>>
>> innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=512MB
>> innodb_flush_log_at_trx_commit=2
>>
>> query_cache_size=128M
>>
>>
>> --
>> Scanned for viruses and dangerous content by MailScanner
>>
>>
>> _______________________________________________
>> DBmail mailing list
>> DBmail [at] dbmail
>> http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
>>
>>
>> --
>> This message has been scanned for viruses and
>> dangerous content by MailScanner, and is believed to be clean.
>>
>>
>>
>
>
>
>

--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

michael.monnerie at is

Sep 15, 2009, 12:13 AM

Post #7 of 12 (1581 views)
Permalink

Re: MySQL tweaks? [In reply to]

On Montag 14 September 2009 Jonathan Feally wrote:
> innodb_flush_log_at_trx_commit=2

Note:
Everybody who care about their data, you should leave this setting on
it's default "1":
innodb_flush_log_at_trx_commit=1

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

dbmail at tech

Sep 15, 2009, 2:48 AM

Post #8 of 12 (1582 views)
Permalink

Re: MySQL tweaks? [In reply to]

Michael Monnerie wrote:
> Note:
> Everybody who care about their data, you should leave this setting on
> it's default "1":
> innodb_flush_log_at_trx_commit=1
>
Also, this will only affect writes - rather than reads.

S
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

dbmail at tech

Sep 15, 2009, 2:52 AM

Post #9 of 12 (1590 views)
Permalink

Re: MySQL tweaks? [In reply to]

Ken D'Ambrosio wrote:
>> How much data are you searching through? (how big is your dbmail db?)
>>
>
> Roughly 100 GB; it's got 750K messages or so.
>
>
>> How much ram does the db server have?
>>
>
> 2 GB
You're i/o bound. How is the data stored? single disk? raid 5? etc..?
Speed the of the disks? Do you have a single array with the os/logs and
dbs all together?

You'll need to add a lot more ram (and allocate more to mysql) and more
faster disks to make access to it faster. Also consider separating the
i/o types - sequential from random to dedicated arrays.

hth,

S
_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

vulture at netvulture

Sep 15, 2009, 7:27 AM

Post #10 of 12 (1579 views)
Permalink

Re: MySQL tweaks? [In reply to]

Simon Gray wrote:
> Michael Monnerie wrote:
>
>> Note:
>> Everybody who care about their data, you should leave this setting on
>> it's default "1":
>> innodb_flush_log_at_trx_commit=1
>>
>>
> Also, this will only affect writes - rather than reads.
>
>
If you have minimal writes and the server is on a UPS, this setting
won't make too much difference, but if you have a lot of messages coming
in, then a setting of 1 would be safer, but further reduce read
performance under a mixed load. Moving towards 2.3.x should lend some
speed increases depending on what searching you are doing. If you are
searching single folders, then reducing the number of messages in each
folder will help speed it up. Moving messages by date received to a
sub-folder of year, quarter, or month can make a big difference. Bigger
mailing lists I use by month "2009-09_Sep" to keep the message count <
10k per folder.

I agree that the majority of your problems lie in disk i/o bandwidth.
Use a good hardware raid 10 (not a fake hardware raid that is really
software aka promise sata) with disks that have low seek times, a larger
on disk buffer, and good sustained read throughputs. You should never
use a raid5 for a database as the write performance will be very poor
when writing out data that is less than the stripe size, causing all
disks to be read, computed, then written to the affected disks. I use a
3ware 9650SE-4LPML, with 4 SATA 1 disks in raid 10. My database is only
20GB though and I have 6.5 GB of ram on mysqld. I'm planning on
replacing the disks with some SATA 2 drives in the future, but just
haven't procured them yet. I have about 550K messages right now in the
database spread over 10 users, my account being the largest. With 2.3.x
my database is about 2x the amount of mail stored, which is an
improvement over 2.2.x.

You might also check into OS level tweaks for your disks to get every
last bit out of them and the controller.

-Jon

--
Scanned for viruses and dangerous content by MailScanner

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

dbmail at tech

Sep 15, 2009, 7:47 AM

Post #11 of 12 (1583 views)
Permalink

Re: MySQL tweaks? [In reply to]

Jonathan Feally wrote:
> Simon Gray wrote:
>
>> Michael Monnerie wrote:
>>
>>
>>> Note:
>>> Everybody who care about their data, you should leave this setting on
>>> it's default "1":
>>> innodb_flush_log_at_trx_commit=1
>>>
>>>
>>>
>> Also, this will only affect writes - rather than reads.
>>
>>
>>
> If you have minimal writes and the server is on a UPS, this setting
> won't make too much difference, but if you have a lot of messages coming
> in, then a setting of 1 would be safer, but further reduce read
> performance under a mixed load.
Yes, assuming you don't separate your data types.

> Moving towards 2.3.x should lend some
> speed increases depending on what searching you are doing. If you are
> searching single folders, then reducing the number of messages in each
> folder will help speed it up. Moving messages by date received to a
> sub-folder of year, quarter, or month can make a big difference. Bigger
> mailing lists I use by month "2009-09_Sep" to keep the message count <
> 10k per folder.
>
Good to hear 2.3 should improve things.
> I agree that the majority of your problems lie in disk i/o bandwidth.
> Use a good hardware raid 10 (not a fake hardware raid that is really
> software aka promise sata) with disks that have low seek times, a larger
> on disk buffer, and good sustained read throughputs. You should never
> use a raid5 for a database as the write performance will be very poor
> when writing out data that is less than the stripe size, causing all
> disks to be read, computed, then written to the affected disks.
This depends, raid 5 yelds more spindles to read from - but does have
the parity overhead on writes. Depends on your environment.

S

michael.monnerie at is

Sep 15, 2009, 8:07 AM

Post #12 of 12 (1582 views)
Permalink

Re: MySQL tweaks? [In reply to]

On Dienstag 15 September 2009 Jonathan Feally wrote:
> If you have minimal writes and the server is on a UPS, this setting
> won't make too much difference

I have a home server on UPS, and can tell you from real experience what
crashes I hade despite all this:
1) Broken power supply -> UPS doesn't help
2) My daughter turning the server off -> UPS doesn't help

But you always risk your transactions for a very small performance gain.
Even for your home server, never make a setup that can break your DB.
It's better to use faster hardware instead, if you really, really need
it.

mfg zmi
--
// Michael Monnerie, Ing.BSc ----- http://it-management.at
// Tel: 0660 / 415 65 31 .network.your.ideas.
// PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import"
// Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4
// Keyserver: wwwkeys.eu.pgp.net Key-ID: 1C1209B4

_______________________________________________
DBmail mailing list
DBmail [at] dbmail
http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads