# Room for tweaking? **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [January 11, 2008, 10:14pm UTC](https://forums.percona.com/t/room-for-tweaking/596 "2008-01-11T22:14:08Z")\ **Posts on this page:** 3\ **Page:** 1
**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\ **Post date:** [January 11, 2008, 10:14pm UTC](https://forums.percona.com/t/room-for-tweaking/596/1 "2008-01-11T22:14:08Z")
I run a moderately busy SMF forum which generates approximately 1M queries per day. I use only InnoDB tables (MyISAM is of course still used by MySQL internally). Things are running great now, but I’m always interested in making them faster still! I have about 500 MB of RAM for use by MySQL (rest goes to PHP + apc cache, nginx, etc). Most of the queries consist of SELECTs (often with multiple joins) and simple UPDATEs. I’m mostly interested in reducing query latency, if possible. Any suggestions? Here’s my cropped SHOW STATUS. ±----------------------------------±-----------+| Variable\_name | Value |±----------------------------------±-----------+| Aborted\_clients | 6692 || Aborted\_connects | 13 || Binlog\_cache\_disk\_use | 2 || Binlog\_cache\_use | 10563522 || Compression | OFF || Connections | 3608 || Created\_tmp\_disk\_tables | 0 || Created\_tmp\_files | 513 || Created\_tmp\_tables | 1 || Delayed\_errors | 0 || Delayed\_insert\_threads | 0 || Delayed\_writes | 0 || Flush\_commands | 1 || Innodb\_buffer\_pool\_pages\_data | 16139 || Innodb\_buffer\_pool\_pages\_dirty | 40 || Innodb\_buffer\_pool\_pages\_flushed | 7346864 || Innodb\_buffer\_pool\_pages\_free | 6 || Innodb\_buffer\_pool\_pages\_latched | 0 || Innodb\_buffer\_pool\_pages\_misc | 239 || Innodb\_buffer\_pool\_pages\_total | 16384 || Innodb\_buffer\_pool\_read\_ahead\_rnd | 395 || Innodb\_buffer\_pool\_read\_ahead\_seq | 1051 || Innodb\_buffer\_pool\_read\_requests | 3501300205 || Innodb\_buffer\_pool\_reads | 222583 || Innodb\_buffer\_pool\_wait\_free | 0 || Innodb\_buffer\_pool\_write\_requests | 100636072 || Innodb\_data\_fsyncs | 17574485 || Innodb\_data\_pending\_fsyncs | 0 || Innodb\_data\_pending\_reads | 0 || Innodb\_data\_pending\_writes | 0 || Innodb\_data\_read | 661716992 || Innodb\_data\_reads | 264561 || Innodb\_data\_writes | 23367666 || Innodb\_data\_written | 3365293568 || Innodb\_dblwr\_pages\_written | 7346691 || Innodb\_dblwr\_writes | 196613 || Innodb\_log\_waits | 0 || Innodb\_log\_write\_requests | 20216582 || Innodb\_log\_writes | 17001514 || Innodb\_os\_log\_fsyncs | 17170639 || Innodb\_os\_log\_pending\_fsyncs | 0 || Innodb\_os\_log\_pending\_writes | 0 || Innodb\_os\_log\_written | 3060873216 || Innodb\_page\_size | 16384 || Innodb\_pages\_created | 38798 || Innodb\_pages\_read | 302532 || Innodb\_pages\_written | 7346864 || Innodb\_row\_lock\_current\_waits | 0 || Innodb\_row\_lock\_time | 2380021 || Innodb\_row\_lock\_time\_avg | 13 || Innodb\_row\_lock\_time\_max | 5934 || Innodb\_row\_lock\_waits | 182364 || Innodb\_rows\_deleted | 489705 || Innodb\_rows\_inserted | 8687733 || Innodb\_rows\_read | 1131330067 || Innodb\_rows\_updated | 7790645 || Key\_blocks\_not\_flushed | 16 || Key\_blocks\_unused | 14481 || Key\_blocks\_used | 1512 || Key\_read\_requests | 1872484 || Key\_reads | 11571 || Key\_write\_requests | 537762 || Key\_writes | 64 || Last\_query\_cost | 0.000000 || Max\_used\_connections | 18 || Not\_flushed\_delayed\_rows | 0 || Open\_files | 26 || Open\_streams | 0 || Open\_tables | 64 || Opened\_tables | 0 || Prepared\_stmt\_count | 0 || Qcache\_free\_blocks | 2679 || Qcache\_free\_memory | 10535504 || Qcache\_hits | 17852871 || Qcache\_inserts | 19669677 || Qcache\_lowmem\_prunes | 586426 || Qcache\_not\_cached | 585632 || Qcache\_queries\_in\_cache | 3368 || Qcache\_total\_blocks | 9440 || Questions | 57178634 || Rpl\_status | NULL || Select\_full\_join | 0 || Select\_full\_range\_join | 0 || Select\_range | 0 || Select\_range\_check | 0 || Select\_scan | 1 || Slave\_open\_temp\_tables | 0 || Slave\_retried\_transactions | 0 || Slave\_running | OFF || Slow\_launch\_threads | 0 || Slow\_queries | 0 || Sort\_merge\_passes | 0 || Sort\_range | 0 || Sort\_rows | 0 || Sort\_scan | 0 || Table\_locks\_immediate | 66591870 || Table\_locks\_waited | 753 || Tc\_log\_max\_pages\_used | 0 || Tc\_log\_page\_size | 0 || Tc\_log\_page\_waits | 3 || Threads\_cached | 0 || Threads\_connected | 17 || Threads\_created | 66 || Threads\_running | 1 || Uptime | 1491742 |±----------------------------------±-----------+ I’ve also got the following set in my.cnf thread\_stack = 128Kthread\_cache\_size = 8innodb\_flush\_method = O\_DIRECTinnodb\_buffer\_pool\_size = 256Minnodb\_log\_file\_size = 256Minnodb\_log\_buffer\_size = 4Minnodb\_thread\_concurrency = 8query\_cache\_limit = 1Mquery\_cache\_size = 16M I realize that 99.9936% of my reads are cached, but what about the query settings? Is there anything I can tweak there? ---
**Author:** ![safari](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@safari](https://forums.percona.com/u/safari)\ **Post date:** [January 16, 2008, 9:11am UTC](https://forums.percona.com/t/room-for-tweaking/596/2 "2008-01-16T09:11:02Z")
one thing I suggest is to increase the thread\_cache\_size. Try mysqlreport ([URL][http://hackmysql.com/mysqlreport[/URL]](http://hackmysql.com/mysqlreport%5B/URL%5D)) to report the mysql status every hour, you could see how well your mysql server running. I’m using this tool and it help me a lot in tuning mysql parameter. ---
**Author:** ![MarkRose](https://avatars.discourse-cdn.com/v4/letter/m/85f322/32.png) [@MarkRose](https://forums.percona.com/u/MarkRose)\ **Post date:** [January 16, 2008, 11:47am UTC](https://forums.percona.com/t/room-for-tweaking/596/3 "2008-01-16T11:47:43Z")
Looks like a handy tool. Great!