List:General Discussion« Previous MessageNext Message »
From:"J. Bakshi" <joydeep.bakshi Date:April 9 2012 11:05am
Subject:Re: Remote mysql too slow
View as plain text  
On Mon, 09 Apr 2012 12:06:42 +0200
Reindl Harald <h.reindl@stripped> wrote:

> 
> 
> 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
> __________________
> 
> 

I have reset these two now

` ` ` ` 
key_buffer_size=200M
innodb_buffer_pool_size=4G

` ` ` `

After restarting the mysql; the mysqltuner suggest as following

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 4777)
[--] Data in InnoDB tables: 3G (Tables: 5543)
[--] Data in MEMORY tables: 0B (Tables: 136)
[!!] Total fragmented tables: 5570

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5m 6s (27K q [90.712 qps], 4K conn, TX: 42M, RX: 7M)
[--] Reads / Writes: 87% / 13%
[--] Total buffers: 438.0M global + 20.4M per thread (150 max threads)
[OK] Maximum possible memory usage: 3.4G (43% of installed RAM)
[OK] Slow queries: 0% (130/27K)
[OK] Highest usage of available connections: 19% (29/150)
[OK] Key buffer size / total MyISAM indexes: 200.0M/269.4M
[OK] Key buffer hit rate: 99.2% (26K cached / 200 reads)
[OK] Query cache efficiency: 21.9% (4K cached / 20K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 78 sorts)
[OK] Temporary tables created on disk: 24% (67 on disk / 270 total)
[OK] Thread cache hit rate: 99% (29 created / 4K connections)
[OK] Table cache hit rate: 24% (10K open / 41K opened)
[OK] Open file limit used: 29% (9K/32K)
[OK] Table locks acquired immediately: 100% (13K immediate / 13K locks)
[!!] InnoDB data size / buffer pool: 3.6G/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
Variables to adjust:
    innodb_buffer_pool_size (>= 3G)


NOTE: Now the max memory usage has decreased 

` ` ` `
Maximum possible memory usage: 3.4G (43% of installed RAM)
` ` ` `

The remote mysql has not become faster but I wounder what is the actual
impact ? should I comment [ innodb_buffer_pool_size=4G ]
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