List:General Discussion« Previous MessageNext Message »
From:Peter Boros Date:May 8 2011 11:39am
Subject:Re: Input needed...
View as plain text  
Hi,

My 2 cents about this inline.

Peter Boros

On 5/6/11 1:50 PM, Reindl Harald wrote:
> 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
For high concurrency environments query cache can actually hurt because 
of the coarse invalidation. Oprofiling the system can show you if you 
spend too much time waiting for the query cache mutex.
>> thread_concurrency = 8
>
> with mysql>= 5.5 we use 16 on a 5-core-VM
This is a Solaris only parameter. I don't understand why people are 
setting this on Linux.
>> 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
>

Thread
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