List:General Discussion« Previous MessageNext Message »
From:Reindl Harald Date:April 9 2012 10:06am
Subject:Re: Remote mysql too slow
View as plain text  

Am 09.04.2012 11:56, schrieb J. Bakshi:
> On Mon, 09 Apr 2012 11:47:01 +0200
> Reindl Harald <h.reindl@stripped> 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



Attachment: [application/pgp-signature] OpenPGP digital signature signature.asc
Thread
Remote mysql too slowJ. Bakshi9 Apr
  • Re: Remote mysql too slowReindl Harald9 Apr
    • Re: Remote mysql too slowJ. Bakshi9 Apr
      • Re: Remote mysql too slowReindl Harald9 Apr
        • Re: Remote mysql too slowJ. Bakshi9 Apr
          • Re: Remote mysql too slowReindl Harald9 Apr
            • Re: Remote mysql too slowJ. Bakshi9 Apr
  • Re: Remote mysql too slowJohnny Withers9 Apr
    • Re: Remote mysql too slowNoel Butler9 Apr