On Mon, 09 Apr 2012 12:06:42 +0200
Reindl Harald <h.rei...@thelounge.net> wrote:
>
>
> 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
> __________________
>
>
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 ]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql
