Am 09.04.2012 11:56, schrieb J. Bakshi: > On Mon, 09 Apr 2012 11:47:01 +0200 > Reindl Harald <h.rei...@thelounge.net> wrote: > >> >> >> Am 09.04.2012 10:57, schrieb J. Bakshi: >>> >>> Hello, >>> >>> I have been provided a muscular linux server to use as a Mysql server >>> in our organization. The server is located just beside the web server >>> and within the same network. This dedicated server has 8GB RAM, i5 >>> processors >>> and running mysql as service. No apache, php ..... nothing. All resources >>> are >>> dedicated to mysql only. >> >> generally this depends on your network connection >> and yow your queries are written >> >> keep in mind that only connect has 15-20% overhead >> compared with a unix socket and if your network is >> too slow you notice latency more and more >> >> additionally your queries have more impact >> if you have usually very small results by optimized queries >> this makles the db-server himslef possibly better suited >> but keep in mind taht your querie himself must over the wire >> >>> I run the mysqltuner directly on the remote mysql server; and here is the >>> result >> >> [OK] Key buffer size / total MyISAM indexes: 2.0G/268.5M >> [!!] InnoDB data size / buffer pool: 3.6G/8.0M >> >> why are you wasting 2GB of RAM fpr key_buffer while >> your innodb_buffer_pool is way to small? >> ______________________ >> > > Could you suggest the optimized settings ?
mysqltuner did innodb_buffer_pool is in the best case as large as the database what is mostly not possible but it makes clear that "as big as possible" is the best value key_buffer_size does never need to be bigger as the size of all keys, usually it can even be smaller without negatvie imapct - so reduce it to 200 MB and you have automatically 1800 MB additionally for innodb_buffer_size __________________ additionally there must be other buffers way too large [--] Total buffers: 2.2G global + 20.4M per thread (150 max threads) we have 1.2 MB per theard on the big inno_db-dbmail-server and 3.2 MB per theard on the big webserver for some hundret domains making the follwojing settings useless too big has a negative impact because the memory has to be assigend for every connection which is overhead and as long the query can not benefit from it it will be only useless overhead key_buffer_size = 256M sort_buffer_size = 512K myisam_sort_buffer_size = 5M join_buffer_size = 2M preload_buffer_size = 256K read_buffer_size = 256K read_rnd_buffer_size = 256K
signature.asc
Description: OpenPGP digital signature
