# mysql\> SHOW STATUS; Optimization of my.cnf **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [February 17, 2007, 5:33pm UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205 "2007-02-17T17:33:03Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![red\_wolf](https://avatars.discourse-cdn.com/v4/letter/r/7bcc69/32.png) [@red\_wolf](https://forums.percona.com/u/red_wolf)\ **Post date:** [February 17, 2007, 5:33pm UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/1 "2007-02-17T17:33:03Z")
mysql\> SHOW STATUS;±---------------------------±-----------+| Variable\_name | Value |±---------------------------±-----------+| Aborted\_clients | 8010 || Aborted\_connects | 2 || Binlog\_cache\_disk\_use | 0 || Binlog\_cache\_use | 0 || Bytes\_received | 3657146200 || Bytes\_sent | 224393733 || Com\_admin\_commands | 383491 || Com\_alter\_db | 0 || Com\_alter\_table | 0 || Com\_analyze | 0 || Com\_backup\_table | 0 || Com\_begin | 0 || Com\_change\_db | 383522 || Com\_change\_master | 0 || Com\_check | 0 || Com\_checksum | 0 || Com\_commit | 0 || Com\_create\_db | 0 || Com\_create\_function | 0 || Com\_create\_index | 0 || Com\_create\_table | 0 || Com\_dealloc\_sql | 0 || Com\_delete | 261857 || Com\_delete\_multi | 0 || Com\_do | 0 || Com\_drop\_db | 0 || Com\_drop\_function | 0 || Com\_drop\_index | 0 || Com\_drop\_table | 0 || Com\_drop\_user | 0 || Com\_execute\_sql | 0 || Com\_flush | 0 || Com\_grant | 0 || Com\_ha\_close | 0 || Com\_ha\_open | 0 || Com\_ha\_read | 0 || Com\_help | 0 || Com\_insert | 78712 || Com\_insert\_select | 3713 || Com\_kill | 0 || Com\_load | 0 || Com\_load\_master\_data | 0 || Com\_load\_master\_table | 0 || Com\_lock\_tables | 0 || Com\_optimize | 0 || Com\_preload\_keys | 0 || Com\_prepare\_sql | 0 || Com\_purge | 0 || Com\_purge\_before\_date | 0 || Com\_rename\_table | 0 || Com\_repair | 0 || Com\_replace | 0 || Com\_replace\_select | 0 || Com\_reset | 0 || Com\_restore\_table | 0 || Com\_revoke | 0 || Com\_revoke\_all | 0 || Com\_rollback | 0 || Com\_savepoint | 0 || Com\_select | 2134999 || Com\_set\_option | 0 || Com\_show\_binlog\_events | 0 || Com\_show\_binlogs | 0 || Com\_show\_charsets | 0 || Com\_show\_collations | 0 || Com\_show\_column\_types | 0 || Com\_show\_create\_db | 0 || Com\_show\_create\_table | 0 || Com\_show\_databases | 4 || Com\_show\_errors | 0 || Com\_show\_fields | 0 || Com\_show\_grants | 0 || Com\_show\_innodb\_status | 0 || Com\_show\_keys | 0 || Com\_show\_logs | 0 || Com\_show\_master\_status | 0 || Com\_show\_ndb\_status | 0 || Com\_show\_new\_master | 0 || Com\_show\_open\_tables | 0 || Com\_show\_privileges | 0 || Com\_show\_processlist | 0 || Com\_show\_slave\_hosts | 0 || Com\_show\_slave\_status | 0 || Com\_show\_status | 32 || Com\_show\_storage\_engines | 0 || Com\_show\_tables | 13 || Com\_show\_variables | 30 || Com\_show\_warnings | 0 || Com\_slave\_start | 0 || Com\_slave\_stop | 0 || Com\_stmt\_close | 0 || Com\_stmt\_execute | 0 || Com\_stmt\_prepare | 0 || Com\_stmt\_reset | 0 || Com\_stmt\_send\_long\_data | 0 || Com\_truncate | 0 || Com\_unlock\_tables | 0 || Com\_update | 555381 || Com\_update\_multi | 0 || Connections | 5208 || Created\_tmp\_disk\_tables | 93842 || Created\_tmp\_files | 0 || Created\_tmp\_tables | 373179 || Delayed\_errors | 0 || Delayed\_insert\_threads | 0 || Delayed\_writes | 0 || Flush\_commands | 2 || Handler\_commit | 0 || Handler\_delete | 87953 || Handler\_discover | 0 || Handler\_read\_first | 39857 || Handler\_read\_key | 927004079 || Handler\_read\_next | 701287506 || Handler\_read\_prev | 0 || Handler\_read\_rnd | 54818936 || Handler\_read\_rnd\_next | 3501770244 || Handler\_rollback | 0 || Handler\_update | 270485714 || Handler\_write | 59013466 || Key\_blocks\_not\_flushed | 0 || Key\_blocks\_unused | 377684 || Key\_blocks\_used | 144707 || Key\_read\_requests | 2502344390 || Key\_reads | 238504 || Key\_write\_requests | 2206636 || Key\_writes | 524553 || Max\_used\_connections | 17 || Not\_flushed\_delayed\_rows | 0 || Open\_files | 170 || Open\_streams | 0 || Open\_tables | 146 || Opened\_tables | 146 || Qcache\_free\_blocks | 4496 || Qcache\_free\_memory | 93720960 || Qcache\_hits | 3359978 || Qcache\_inserts | 2132197 || Qcache\_lowmem\_prunes | 0 || Qcache\_not\_cached | 2802 || Qcache\_queries\_in\_cache | 10143 || Qcache\_total\_blocks | 24808 || Questions | 9585208 || Rpl\_status | NULL || Select\_full\_join | 0 || Select\_full\_range\_join | 0 || Select\_range | 224361 || Select\_range\_check | 0 || Select\_scan | 591332 || Slave\_open\_temp\_tables | 0 || Slave\_retried\_transactions | 0 || Slave\_running | OFF || Slow\_launch\_threads | 0 || Slow\_queries | 653 || Sort\_merge\_passes | 0 || Sort\_range | 342065 || Sort\_rows | 1262701352 || Sort\_scan | 395665 || Table\_locks\_immediate | 5921110 || Table\_locks\_waited | 7383 || Threads\_cached | 2 || Threads\_connected | 15 || Threads\_created | 21 || Threads\_running | 1 || Uptime | 145450 |±---------------------------±-----------+163 rows in set (0.44 sec) this is what SHOW STATUS; commands give me can anyone provide me any helpful tips on what I can adjust in my.cnf to lower my server load which is above 10 during peak hours and mysql is the only thing eating this serve up I have lots og 3gb ram and a 64bit AMD 2800 server Heres what my.cnf looks like [mysqld]safe-show-database#old\_passwordsback\_log = 100skip-innodbmax\_connections = 600key\_buffer = 768Mmyisam\_sort\_buffer\_size = 64Mjoin\_buffer\_size = 1Mread\_buffer\_size = 1Msort\_buffer\_size = 3Mtable\_cache = 93072thread\_cache\_size = 320wait\_timeout = 30connect\_timeout = 10tmp\_table\_size = 128Mmax\_heap\_table\_size = 64Mmax\_allowed\_packet = 64Mmax\_connect\_errors = 10read\_rnd\_buffer\_size = 5Mbulk\_insert\_buffer\_size = 16Mquery\_cache\_limit = 5Mquery\_cache\_size = 100Mquery\_cache\_type = 1query\_prealloc\_size = 163840query\_alloc\_block\_size = 32768default-storage-engine = MyISAMlow\_priority\_updates=1 Heres a screenshot of TOP COmmand [URL=“http://[img300.imageshack.us/img300/3771/untitledaf0.gif](http://img300.imageshack.us/img300/3771/untitledaf0.gif)”][http://img300.imageshack.us/img300/3771/untitledaf0.gif[/URL]](http://img300.imageshack.us/img300/3771/untitledaf0.gif%5B/URL%5D) If you need anything else tell me the command and I will run it and give screenshot please help me ---
**Author:** ![Alexey](https://avatars.discourse-cdn.com/v4/letter/a/47e85d/32.png) [@Alexey](https://forums.percona.com/u/Alexey)\ **Post date:** [February 17, 2007, 5:44pm UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/2 "2007-02-17T17:44:22Z")
Adjusting my.cnf won’t help much. There are however some things that can be changed: 1. decrease size of key buffer since you’re not using most of it 2. unset low\_priority\_updates unless you really did it knowingly and it helped 3. unset query\_prealloc\_size and query\_alloc\_block\_size unless your values really work better than default ones 4. decrease table\_cache - you’re using 146, there’s no need to set it at 93k (although, like with key\_buffer, having larger value doesn’t hurt) 5. set both tmp\_table\_size and max\_heap\_table\_size to the same value (having them different is weird). 6. decrease sort\_buffer\_size as MySQL has a bug with it Remember, all this stuff won’t help much with performance. You’ll have to analyze slow queries and optimize indexes and/or queries. ---
**Author:** ![red\_wolf](https://avatars.discourse-cdn.com/v4/letter/r/7bcc69/32.png) [@red\_wolf](https://forums.percona.com/u/red_wolf)\ **Post date:** [February 17, 2007, 7:19pm UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/3 "2007-02-17T19:19:31Z")
well do but can you tell me the exact size to lower down to? what else can I do to make Mysql use more ram and increase performances ---
**Author:** ![dmeiners](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@dmeiners](https://forums.percona.com/u/dmeiners)\ **Post date:** [February 18, 2007, 1:15pm UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/4 "2007-02-18T13:15:26Z")
a good place to start is to turn on the slow queries log # log slow queries log-slow-queries=/var/log/mysql/slow-queries.log # defines a slow query as any query \>= 1 second long\_query\_time=1 then run those queries with EXPLAIN to find out if they are using indexes etc and how you might improve them. if you are using 5.0 you can also log-queries-not-using-indexes after you do that, then start tweaking your other settings for memory etc. ---
**Author:** ![red\_wolf](https://avatars.discourse-cdn.com/v4/letter/r/7bcc69/32.png) [@red\_wolf](https://forums.percona.com/u/red_wolf)\ **Post date:** [February 19, 2007, 8:59am UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/5 "2007-02-19T08:59:01Z")
I added that in my.cnf and restarted mysql but nothing was logged its a empty blank file?? ---
**Author:** ![red\_wolf](https://avatars.discourse-cdn.com/v4/letter/r/7bcc69/32.png) [@red\_wolf](https://forums.percona.com/u/red_wolf)\ **Post date:** [February 19, 2007, 9:04am UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/6 "2007-02-19T09:04:53Z")
# log slow queries log-slow-queries=/www/mysql-slow.log # defines a slow query as any query \>= 1 second long\_query\_time=1 thats what I placed in my.cnf ---
**Author:** ![dmeiners](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@dmeiners](https://forums.percona.com/u/dmeiners)\ **Post date:** [February 19, 2007, 11:06am UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/7 "2007-02-19T11:06:25Z")
does the mysql user have permissions to write to /www/mysql-slow.sql ? probably not, and you probably do not want mysql to have write permission to /www # make a log directory for mysql under /var/log mkdir -p /var/log/mysql # chown it to the mysql user chown mysql.mysql /var/log/mysql then change your log-slow-queries line to this… log-slow-queries=/var/log/mysql/mysql-slow.log ---
**Author:** ![red\_wolf](https://avatars.discourse-cdn.com/v4/letter/r/7bcc69/32.png) [@red\_wolf](https://forums.percona.com/u/red_wolf)\ **Post date:** [February 19, 2007, 3:13pm UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/8 "2007-02-19T15:13:21Z")
I followed your instruction I will check back in 3 hours for any logs and will post them ---
**Author:** ![dmeiners](https://avatars.discourse-cdn.com/v4/letter/d/e274bd/32.png) [@dmeiners](https://forums.percona.com/u/dmeiners)\ **Post date:** [February 19, 2007, 3:17pm UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/9 "2007-02-19T15:17:30Z")
if you have the proper permissions you should get a little message at the beginning of your slow log like this… /usr/sbin/mysqld, Version: 4.1.11-standard-log. started with: Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock ---
**Author:** ![red\_wolf](https://avatars.discourse-cdn.com/v4/letter/r/7bcc69/32.png) [@red\_wolf](https://forums.percona.com/u/red_wolf)\ **Post date:** [February 20, 2007, 6:26am UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/10 "2007-02-20T06:26:29Z")
Time Id Command Argument# Time: 070219 19:17:04# User@Host: admin\_db[admin\_db] @ localhost # Query\_time: 11 Lock\_time: 0 Rows\_sent: 0 Rows\_examined: 13273805use admin\_db17;SELECT word\_id FROM phpbb\_search\_wordmatch GROUP BY word\_id HAVING COUNT(word\_id) \> 259734; This is one of the logged queries, what can I do to optimze this query? ---
**Author:** ![Alexey](https://avatars.discourse-cdn.com/v4/letter/a/47e85d/32.png) [@Alexey](https://forums.percona.com/u/Alexey)\ **Post date:** [February 23, 2007, 6:48am UTC](https://forums.percona.com/t/mysql-show-status-optimization-of-my-cnf/205/11 "2007-02-23T06:48:53Z")
| [B]red\_wolf wrote on Tue, 20 February 2007 06:56[/B] | | Time Id Command Argument# Time: 070219 19:17:04# User@Host: admin\_db[admin\_db] @ localhost # Query\_time: 11 Lock\_time: 0 Rows\_sent: 0 Rows\_examined: 13273805use admin\_db17;SELECT word\_id FROM phpbb\_search\_wordmatch GROUP BY word\_id HAVING COUNT(word\_id) \> 259734; This is one of the logged queries, what can I do to optimze this query? | There’s nothing you can do about it. It’s a very bad application design. Consider disabling phpBB search, or finding some advanced phpBB fulltext search extension. For a big forum I’d switch to one of the proprietary scripts like VB or IPB.