# Server Optimization Request, please help.

**URL:** <https://forums.percona.com/t/server-optimization-request-please-help/390>\
**Category:** Other MySQL® Questions\
**Created:** [July 25, 2007, 5:22am UTC](https://forums.percona.com/t/server-optimization-request-please-help/390 "2007-07-25T05:22:03Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![kaahbonk](https://avatars.discourse-cdn.com/v4/letter/k/d2c977/32.png) [@kaahbonk](https://forums.percona.com/u/kaahbonk)\
**Post date:** [July 25, 2007, 5:22am UTC](https://forums.percona.com/t/server-optimization-request-please-help/390/1 "2007-07-25T05:22:03Z")

</div>

Please help me optimize this server. I am pretty sure a lot could be done to improve its performance. Any help will be most appreciated )

Server Specs:  
Quad Opteron 842+  
4096MB RAM  
2 x 73GB U320 SCSI Raid  
FreeBSD 6.2  
apache/IIS version: apache 2.2.4  
PHP version: PHP Version 5.2.2  
MySQL version: mysql 5.0.27

my.cnf:  
skip-locking  
key\_buffer = 768M  
max\_allowed\_packet = 10M  
table\_cache =1536  
sort\_buffer\_size = 16M  
read\_buffer\_size = 16M  
read\_rnd\_buffer\_size = 16M  
myisam\_sort\_buffer\_size = 512M  
thread\_cache\_size = 64  
query\_cache\_size = 256M

# Try number of CPU’s\*2 for thread\_concurrency

thread\_concurrency = 8  
max\_connections = 1000  
query\_cache\_limit=4M  
query\_cache\_type=1

MySQL stats:

---------------------------------±-----------------------------------------------------------+| Variable\_name | Value |±--------------------------------±-----------------------------------------------------------+| auto\_increment\_increment | 1 || auto\_increment\_offset | 1 || automatic\_sp\_privileges | ON || back\_log | 50 || basedir | /usr/local/ || bdb\_cache\_size | 8388600 || bdb\_home | /var/db/mysql/ || bdb\_log\_buffer\_size | 786432 || bdb\_logdir | || bdb\_max\_lock | 10000 || bdb\_shared\_data | OFF || bdb\_tmpdir | /var/tmp/ || binlog\_cache\_size | 32768 || bulk\_insert\_buffer\_size | 8388608 || character\_set\_client | latin1 || character\_set\_connection | latin1 || character\_set\_database | latin1 || character\_set\_filesystem | binary || character\_set\_results | latin1 || character\_set\_server | latin1 || character\_set\_system | utf8 || character\_sets\_dir | /usr/local/share/mysql/charsets/ || collation\_connection | latin1\_swedish\_ci || collation\_database | latin1\_swedish\_ci || collation\_server | latin1\_swedish\_ci || completion\_type | 0 || concurrent\_insert | 1 || connect\_timeout | 5 || datadir | /var/db/mysql/ || date\_format | %Y-%m-%d || datetime\_format | %Y-%m-%d %H:%i:%s || default\_week\_format | 0 || delay\_key\_write | ON || delayed\_insert\_limit | 100 || delayed\_insert\_timeout | 300 || delayed\_queue\_size | 1000 || div\_precision\_increment | 4 || engine\_condition\_pushdown | OFF || expire\_logs\_days | 0 || flush | OFF || flush\_time | 0 || ft\_boolean\_syntax | + -\>\<()~\*:“”&| || ft\_max\_word\_len | 84 || ft\_min\_word\_len | 4 || ft\_query\_expansion\_limit | 20 || ft\_stopword\_file | (built-in) || group\_concat\_max\_len | 1024 || have\_archive | NO || have\_bdb | YES || have\_blackhole\_engine | NO || have\_compress | YES || have\_crypt | YES || have\_csv | NO || have\_dynamic\_loading | YES || have\_example\_engine | NO || have\_federated\_engine | NO || have\_geometry | YES || have\_innodb | YES || have\_isam | NO || have\_merge\_engine | YES || have\_ndbcluster | NO || have\_openssl | NO || have\_query\_cache | YES || have\_raid | NO || have\_rtree\_keys | YES || have\_symlink | YES || init\_connect | || init\_file | || init\_slave | || innodb\_additional\_mem\_pool\_size | 1048576 || innodb\_autoextend\_increment | 8 || innodb\_buffer\_pool\_awe\_mem\_mb | 0 || innodb\_buffer\_pool\_size | 8388608 || innodb\_checksums | ON || innodb\_commit\_concurrency | 0 || innodb\_concurrency\_tickets | 500 || innodb\_data\_file\_path | ibdata1:10M:autoextend || innodb\_data\_home\_dir | || innodb\_doublewrite | ON || innodb\_fast\_shutdown | 1 || innodb\_file\_io\_threads | 4 || innodb\_file\_per\_table | OFF || innodb\_flush\_log\_at\_trx\_commit | 1 || innodb\_flush\_method | || innodb\_force\_recovery | 0 || innodb\_lock\_wait\_timeout | 50 || innodb\_locks\_unsafe\_for\_binlog | OFF || innodb\_log\_arch\_dir | || innodb\_log\_archive | OFF || innodb\_log\_buffer\_size | 1048576 || innodb\_log\_file\_size | 5242880 || innodb\_log\_files\_in\_group | 2 || innodb\_log\_group\_home\_dir | ./ || innodb\_max\_dirty\_pages\_pct | 90 || innodb\_max\_purge\_lag | 0 || innodb\_mirrored\_log\_groups | 1 || innodb\_open\_files | 300 || innodb\_support\_xa | ON || innodb\_sync\_spin\_loops | 20 || innodb\_table\_locks | ON || innodb\_thread\_concurrency | 8 || innodb\_thread\_sleep\_delay | 10000 || interactive\_timeout | 28800 || join\_buffer\_size | 131072 || key\_buffer\_size | 805306368 || key\_cache\_age\_threshold | 300 || key\_cache\_block\_size | 1024 || key\_cache\_division\_limit | 100 || language | /usr/local/share/mysql/english/ || large\_files\_support | ON || large\_page\_size | 0 || large\_pages | OFF || lc\_time\_names | en\_US || license | GPL || local\_infile | ON || locked\_in\_memory | OFF || log | OFF || log\_bin | ON || log\_bin\_trust\_function\_creators | OFF || log\_error | || log\_queries\_not\_using\_indexes | OFF || log\_slave\_updates | OFF || log\_slow\_queries | ON || log\_warnings | 2 || long\_query\_time | 15 || low\_priority\_updates | OFF || lower\_case\_file\_system | OFF || lower\_case\_table\_names | 0 || max\_allowed\_packet | 10484736 || max\_binlog\_cache\_size | 4294967295 || max\_binlog\_size | 1073741824 || max\_connect\_errors | 10000 || max\_connections | 1000 || max\_delayed\_threads | 20 || max\_error\_count | 64 || max\_heap\_table\_size | 16777216 || max\_insert\_delayed\_threads | 20 || max\_join\_size | 4294967295 || max\_length\_for\_sort\_data | 1024 || max\_prepared\_stmt\_count | 16382 || max\_relay\_log\_size | 0 || max\_seeks\_for\_key | 4294967295 || max\_sort\_length | 1024 || max\_sp\_recursion\_depth | 0 || max\_tmp\_tables | 32 || max\_user\_connections | 0 || max\_write\_lock\_count | 4294967295 || multi\_range\_count | 256 || myisam\_data\_pointer\_size | 6 || myisam\_max\_sort\_file\_size | 2147483647 || myisam\_recover\_options | OFF || myisam\_repair\_threads | 1 || myisam\_sort\_buffer\_size | 536870912 || myisam\_stats\_method | nulls\_unequal || net\_buffer\_length | 16384 || net\_read\_timeout | 30 || net\_retry\_count | 1000000 || net\_write\_timeout | 60 || new | OFF || old\_passwords | OFF || open\_files\_limit | 11095 || optimizer\_prune\_level | 1 || optimizer\_search\_depth | 62 || pid\_file | /var/db/mysql/databas.wcmprem.com.pid || port | 3306 || preload\_buffer\_size | 32768 || prepared\_stmt\_count | 0 || protocol\_version | 10 || query\_alloc\_block\_size | 8192 || query\_cache\_limit | 4194304 || query\_cache\_min\_res\_unit | 4096 || query\_cache\_size | 268435456 || query\_cache\_type | ON || query\_cache\_wlock\_invalidate | OFF || query\_prealloc\_size | 8192 || range\_alloc\_block\_size | 2048 || read\_buffer\_size | 16773120 || read\_only | OFF || read\_rnd\_buffer\_size | 16773120 || relay\_log\_purge | ON || relay\_log\_space\_limit | 0 || rpl\_recovery\_rank | 0 || secure\_auth | OFF || server\_id | 1 || skip\_external\_locking | ON || skip\_networking | OFF || skip\_show\_database | OFF || slave\_compressed\_protocol | OFF || slave\_load\_tmpdir | /var/tmp/ || slave\_net\_timeout | 3600 || slave\_skip\_errors | OFF || slave\_transaction\_retries | 10 || slow\_launch\_time | 2 || socket | /tmp/mysql.sock || sort\_buffer\_size | 16777208 || sql\_big\_selects | ON || sql\_mode | || sql\_notes | ON || sql\_warnings | OFF || ssl\_ca | || ssl\_capath | || ssl\_cert | || ssl\_cipher | || ssl\_key | || storage\_engine | MyISAM || sync\_binlog | 0 || sync\_frm | ON || system\_time\_zone | CEST || table\_cache | 1536 || table\_lock\_wait\_timeout | 50 || table\_type | MyISAM || thread\_cache\_size | 64 || thread\_stack | 196608 || time\_format | %H:%i:%s || time\_zone | SYSTEM || timed\_mutexes | OFF || tmp\_table\_size | 33554432 || tmpdir | /var/tmp/ || transaction\_alloc\_block\_size | 8192 || transaction\_prealloc\_size | 4096 || tx\_isolation | REPEATABLE-READ || updatable\_views\_with\_limit | YES || version | 5.0.27-log || version\_bdb | Sleepycat Software: Berkeley DB 4.1.24: (October 21, 2006) || version\_comment | FreeBSD port: mysql-server-5.0.27 || version\_compile\_machine | i386 || version\_compile\_os | portbld-freebsd6.1 || wait\_timeout | 28800 |±--------------------------------±-----------------------------------------------------------+±----------------------------------±-----------+| Variable\_name | Value |±----------------------------------±-----------+| Aborted\_clients | 80 || Aborted\_connects | 13 || Binlog\_cache\_disk\_use | 0 || Binlog\_cache\_use | 29115 || Bytes\_received | 58300033 || Bytes\_sent | 301428993 || Com\_admin\_commands | 30 || Com\_alter\_db | 0 || Com\_alter\_table | 0 || Com\_analyze | 0 || Com\_backup\_table | 0 || Com\_begin | 0 || Com\_change\_db | 405 || 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 | 5562 || 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 | 16661 || Com\_insert\_select | 0 || 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 | 19 || 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 | 115389 || Com\_set\_option | 43 || Com\_show\_binlog\_events | 0 || Com\_show\_binlogs | 1 || Com\_show\_charsets | 3 || Com\_show\_collations | 3 || Com\_show\_column\_types | 0 || Com\_show\_create\_db | 0 || Com\_show\_create\_table | 0 || Com\_show\_databases | 3 || Com\_show\_errors | 0 || Com\_show\_fields | 0 || Com\_show\_grants | 1 || 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 | 30 || Com\_show\_slave\_status | 0 || Com\_show\_status | 7 || Com\_show\_storage\_engines | 0 || Com\_show\_tables | 12 || Com\_show\_triggers | 0 || Com\_show\_variables | 43 || Com\_show\_warnings | 0 || Com\_slave\_start | 0 || Com\_slave\_stop | 0 || Com\_stmt\_close | 0 || Com\_stmt\_execute | 0 || Com\_stmt\_fetch | 0 || Com\_stmt\_prepare | 0 || Com\_stmt\_reset | 0 || Com\_stmt\_send\_long\_data | 0 || Com\_truncate | 3 || Com\_unlock\_tables | 0 || Com\_update | 40054 || Com\_update\_multi | 0 || Com\_xa\_commit | 0 || Com\_xa\_end | 0 || Com\_xa\_prepare | 0 || Com\_xa\_recover | 0 || Com\_xa\_rollback | 0 || Com\_xa\_start | 0 || Compression | OFF || Connections | 233456 || Created\_tmp\_disk\_tables | 1266 || Created\_tmp\_files | 13 || Created\_tmp\_tables | 2079 || Delayed\_errors | 0 || Delayed\_insert\_threads | 0 || Delayed\_writes | 0 || Flush\_commands | 1 || Handler\_commit | 58230 || Handler\_delete | 103 || Handler\_discover | 0 || Handler\_prepare | 58230 || Handler\_read\_first | 12412 || Handler\_read\_key | 42935537 || Handler\_read\_next | 273099367 || Handler\_read\_prev | 77 || Handler\_read\_rnd | 1333194 || Handler\_read\_rnd\_next | 117571738 || Handler\_rollback | 0 || Handler\_savepoint | 0 || Handler\_savepoint\_rollback | 0 || Handler\_update | 675414 || Handler\_write | 1079186 || Innodb\_buffer\_pool\_pages\_data | 491 || Innodb\_buffer\_pool\_pages\_dirty | 11 || Innodb\_buffer\_pool\_pages\_flushed | 17015 || Innodb\_buffer\_pool\_pages\_free | 0 || Innodb\_buffer\_pool\_pages\_latched | 13 || Innodb\_buffer\_pool\_pages\_misc | 20 || Innodb\_buffer\_pool\_pages\_total | 512 || Innodb\_buffer\_pool\_read\_ahead\_rnd | 29078 || Innodb\_buffer\_pool\_read\_ahead\_seq | 42605 || Innodb\_buffer\_pool\_read\_requests | 775770445 || Innodb\_buffer\_pool\_reads | 7306943 || Innodb\_buffer\_pool\_wait\_free | 0 || Innodb\_buffer\_pool\_write\_requests | 193413 || Innodb\_data\_fsyncs | 49863 || Innodb\_data\_pending\_fsyncs | 0 || Innodb\_data\_pending\_reads | 0 || Innodb\_data\_pending\_writes | 0 || Innodb\_data\_read | 1111085056 || Innodb\_data\_reads | 7487542 || Innodb\_data\_writes | 62401 || Innodb\_data\_written | 595682816 || Innodb\_dblwr\_pages\_written | 17015 || Innodb\_dblwr\_writes | 852 || Innodb\_log\_waits | 0 || Innodb\_log\_write\_requests | 26736 || Innodb\_log\_writes | 47982 || Innodb\_os\_log\_fsyncs | 48185 || Innodb\_os\_log\_pending\_fsyncs | 0 || Innodb\_os\_log\_pending\_writes | 0 || Innodb\_os\_log\_written | 38043648 || Innodb\_page\_size | 16384 || Innodb\_pages\_created | 407 || Innodb\_pages\_read | 7932003 || Innodb\_pages\_written | 17015 || Innodb\_row\_lock\_current\_waits | 0 || Innodb\_row\_lock\_time | 5853 || Innodb\_row\_lock\_time\_avg | 7 || Innodb\_row\_lock\_time\_max | 75 || Innodb\_row\_lock\_waits | 768 || Innodb\_rows\_deleted | 813 || Innodb\_rows\_inserted | 800 || Innodb\_rows\_read | 270720967 || Innodb\_rows\_updated | 22619 || Key\_blocks\_not\_flushed | 2 || Key\_blocks\_unused | 657947 || Key\_blocks\_used | 39873 || Key\_read\_requests | 33252925 || Key\_reads | 40644 || Key\_write\_requests | 77752 || Key\_writes | 15515 || Last\_query\_cost | 0.000000 || Max\_used\_connections | 176 || Not\_flushed\_delayed\_rows | 0 || Open\_files | 456 || Open\_streams | 0 || Open\_tables | 424 || Opened\_tables | 438 || Qcache\_free\_blocks | 3847 || Qcache\_free\_memory | 252542352 || Qcache\_hits | 341345 || Qcache\_inserts | 89561 || Qcache\_lowmem\_prunes | 0 || Qcache\_not\_cached | 25895 || Qcache\_queries\_in\_cache | 10025 || Qcache\_total\_blocks | 24064 || Questions | 753954 || Rpl\_status | NULL || Select\_full\_join | 56 || Select\_full\_range\_join | 0 || Select\_range | 13250 || Select\_range\_check | 0 || Select\_scan | 18888 || Slave\_open\_temp\_tables | 0 || Slave\_retried\_transactions | 0 || Slave\_running | OFF || Slow\_launch\_threads | 0 || Slow\_queries | 373 || Sort\_merge\_passes | 4 || Sort\_range | 12783 || Sort\_rows | 10790387 || Sort\_scan | 5819 || Table\_locks\_immediate | 241827 || Table\_locks\_waited | 396 || Tc\_log\_max\_pages\_used | 0 || Tc\_log\_page\_size | 0 || Tc\_log\_page\_waits | 0 || Threads\_cached | 23 || Threads\_connected | 76 || Threads\_created | 184 || Threads\_running | 27 || Uptime | 1804 |±----------------------------------±-----------+Uptime: 1804 Threads: 76 Questions: 753955 Slow queries: 373 Opens: 438 Flush tables: 1 Open tables: 424 Queries per second avg: 417.935mysqladmin Ver 8.41 Distrib 5.0.27, for portbld-freebsd6.1 on i386Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult ABThis software comes with ABSOLUTELY NO WARRANTY. This is free software,and you are welcome to modify and redistribute it under the GPL licenseServer version 5.0.27-logProtocol version 10Connection Localhost via UNIX socketUNIX socket /tmp/mysql.sockUptime: 30 min 4 secThreads: 76 Questions: 753955 Slow queries: 373 Opens: 438 Flush tables: 1 Open tables: 424 Queries per second avg: 417.935

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [August 16, 2007, 9:18am UTC](https://forums.percona.com/t/server-optimization-request-please-help/390/2 "2007-08-16T09:18:52Z")

</div>

you’re actively using Innodb but you have not set any Innodb settings.

Plus your local buffers (sort\_buffer, read\_buffer, read\_rnd\_buffer) may be too large.

---

<div class="post-metadata">

**Author:** ![allworknoplay](https://avatars.discourse-cdn.com/v4/letter/a/dbc845/32.png) [@allworknoplay](https://forums.percona.com/u/allworknoplay)\
**Post date:** [September 8, 2007, 10:53pm UTC](https://forums.percona.com/t/server-optimization-request-please-help/390/3 "2007-09-08T22:53:13Z")

</div>

Peter is right, you only have a 4GIG server.

Your buffers are massive…

sort\_buffer\_size = 16M  
read\_buffer\_size = 16M  
read\_rnd\_buffer\_size = 16M

Assuming that you have dedicated this box entirely  
for MySQL they should be around:

sort\_buffer\_size = 4M  
read\_buffer\_size = 4M  
read\_rnd\_buffer\_size = 4M

---

<div class="post-metadata">

**Author:** ![kaahbonk](https://avatars.discourse-cdn.com/v4/letter/k/d2c977/32.png) [@kaahbonk](https://forums.percona.com/u/kaahbonk)\
**Post date:** [September 10, 2007, 1:15am UTC](https://forums.percona.com/t/server-optimization-request-please-help/390/4 "2007-09-10T01:15:30Z")

</div>

Thanks, yes the box is dedicated for MySQL.  
I just recently upgraded to 8GB. Should the buffers double up too?

---

<div class="post-metadata">

**Author:** ![allworknoplay](https://avatars.discourse-cdn.com/v4/letter/a/dbc845/32.png) [@allworknoplay](https://forums.percona.com/u/allworknoplay)\
**Post date:** [September 10, 2007, 8:21am UTC](https://forums.percona.com/t/server-optimization-request-please-help/390/5 "2007-09-10T08:21:21Z")

</div>

| [B]kaahbonk wrote on Mon, 10 September 2007 02:45[/B] |
| Thanks, yes the box is dedicated for MySQL. I just recently upgraded to 8GB. Should the buffers double up too? |

You could probably safely double them, I guess it also depends  
on what else you have done to up the ante since you’ve upgraded to 8GIG…