MySQL tweaks?

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. 

	MySQL tweaks? by Ken D'Ambrosio-2 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Simon Gray-8 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Ken D'Ambrosio-2 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message > 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@... > 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Jonathan Feally :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Ken D'Ambrosio-2 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message Thanks for all the pointers! Though I'm afraid your sliderule* dropped a decimal: 110^11 / 7.510^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@... > 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Jonathan Feally :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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: 110^11 / 7.510^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@... >> 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Michael Monnerie-5 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Simon Gray-8 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Simon Gray-8 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Jonathan Feally :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Simon Gray-8 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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 _______________________________________________ DBmail mailing list DBmail@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail
	Re: MySQL tweaks? by Michael Monnerie-5 :: Rate this Message: Reply to Author \| View Threaded \| Show Only this Message 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@... http://mailman.fastxs.nl/cgi-bin/mailman/listinfo/dbmail

MySQL tweaks?

MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?

Re: MySQL tweaks?