List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:May 6 2011 11:50am
Subject:Re: Input needed...
View as plain text  
Am 06.05.2011 13:26, schrieb J M:
> Config:
> Running on 8G Server.. Currently utilizing 7G.. running only mysql..

> [mysqld]
> port            = 3306
> socket          = /var/lib/mysql/mysql.sock
> skip-locking
> key_buffer_size = 384M

depends on the size of all yur keys

> max_allowed_packet = 1M

is verly low if you import a bigger dump or BLOB

> table_open_cache = 512

> sort_buffer_size = 2M

512K should be enough, this is per connection and does not help most cases

> read_buffer_size = 2M
> read_rnd_buffer_size = 8M

try a becnhmark with both lowered to 256k
this could maybe much faster in real life because lower memory-allocation

> myisam_sort_buffer_size = 64M

should be verified by a "repair table" on your biggest myisam-table
because iz hurts if it would get corrupt later and a repair fails because
to low value

> thread_cache_size = 8

should be as high as the most time connections
we allow 200 connections on the webserver and thread cache is also 200

> query_cache_size = 32M

hm, this can be too low
reduce some too high buffers and give the memory to the cache
we are using 1.5 GB query_cache_size resuling in 500.000 queries
in the cache some days after start without preuns

> thread_concurrency = 8

with mysql >= 5.5 we use 16 on a 5-core-VM

> max_connections = 802

this can be dangerous with per connection-buffers, see calculation below

> wait_timeout = 15

read_rnd_buffer_size 8M x max_connections 802 = 6.416 MB
sort_buffer_size 2M x max_connections 802 = 1604 MB
be aware of to big per-connection-buffers!

> innodb_data_home_dir = /var/lib/mysql/
> innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

bad - you get one flat storage-pool which will grow
and never can be rudced with "optimize table"

innodb_file_per_table = 1

> innodb_buffer_pool_size = 384M

if you are using innodb highly you should tune htis as high
as possible, perfect would be as big as all innodb-tables

> innodb_log_file_size = 100M

on heavy writes maybe too small and not easy to change in production

Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Input needed...J M5 May
  • Re: Input needed...Reindl Harald5 May
    • Re: Input needed...J M6 May
      • RE: Input needed...Martin Gainty6 May
      • Re: Input needed...Reindl Harald6 May
        • RE: Input needed...Martin Gainty6 May
        • Re: Input needed...Peter Boros8 May