# Innodb Performance

**URL:** <https://forums.percona.com/t/innodb-performance/1377>\
**Category:** Other MySQL® Questions\
**Created:** [March 31, 2010, 3:13pm UTC](https://forums.percona.com/t/innodb-performance/1377 "2010-03-31T15:13:02Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![kostagr33k](https://avatars.discourse-cdn.com/v4/letter/k/ebca7d/32.png) [@kostagr33k](https://forums.percona.com/u/kostagr33k)\
**Post date:** [March 31, 2010, 3:13pm UTC](https://forums.percona.com/t/innodb-performance/1377/1 "2010-03-31T15:13:02Z")

</div>

Hey everyone, hope all is well.

I have a beefy server that from time to time, gets inundated with Queries taking over 30seconds to complete (normally \<1s to complete).

Through my cacti graphs, I see average of 170,000 spin rounds, and today reached 5Million at one point (per second).

Now what additonal information will be useful for me to provide in order to get some help =]

For now, I will list the /etc/my.cnf , variables, server specs and status.

Server specs are dual Quad Core Nehahlem processors with HT enabled. 24GB memory, Red Hat Enterprise Linux Server release 5.3 (Tikanga) Linux 2.6.18-128.1.10.el5 #1 SMP Wed Apr 29 13:53:08 EDT 2009 x86\_64 x86\_64 x86\_64 GNU/Linux

/etc/my.cnf

[mysqld] datadir=/data socket=/data/mysql.sock # Default to using old password format for compatibility with mysql 3.x# clients (those using the mysqlclient10 compatibility package). old\_passwords=1 server-id=106#server-id=10141644log-slave-updates log-bin=/logs/mysql-binrelay-log=/logs/relay-binlog-slow-queries=/logs/slow-queries.logexpire\_logs\_days=7 binlog\_cache\_size=1M wait\_timeout=60 # temporary query log# log=/logs/full-query-log.log#master-host = 10.4.16.218#master-port = 3306 #master-user = replicant #master-password = replicantslave-skip-errors=1062,1452,1253,1267#tmpdir=/var/tmpfs tmpdir=/data key\_buffer = 3000M table\_cache = 10000 sort\_buffer\_size = 6M read\_buffer\_size = 4M read\_rnd\_buffer\_size = 16M myisam\_sort\_buffer\_size = 8M thread\_cache\_size = 256 query\_cache\_size = 256M thread\_concurrency = 8concurrent\_insert=2tmp\_table\_size=128Mmax\_heap\_table\_size=128Mlong\_query\_time=3#tmpdir=/data/#log\_queries\_not\_using\_indexes=1default-character-set=latin1collation\_server=latin1\_general\_cimax\_allowed\_packet = 40Mmax\_connections = 1000innodb\_file\_per\_table#innodb\_log\_file\_size=512M#innodb\_buffer\_pool\_size=2G#innodb\_log\_buffer\_size=32M#innodb\_thread\_concurrency=8innodb\_log\_file\_size=1Ginnodb\_buffer\_pool\_size=8Ginnodb\_log\_buffer\_size=8Minnodb\_thread\_concurrency=36innodb\_open\_files=1000# we should change to below as to high is not good / does not do anything#innodb\_flush\_method=O\_DIRECTinnodb\_lock\_wait\_timeout = 70innodb\_flush\_log\_at\_trx\_commit=0[mysql.server]user=mysql#basedir=/var/lib[mysqld\_safe]log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid[myisamchk]key\_buffer = 256Msort\_buffer\_size = 256Mread\_buffer = 2Mwrite\_buffer = 2M

variables

Variable\_name Valueauto\_increment\_increment 1auto\_increment\_offset 1automatic\_sp\_privileges ONback\_log 50basedir /binlog\_cache\_size 1048576bulk\_insert\_buffer\_size 8388608character\_set\_client latin1character\_set\_connection latin1character\_set\_database latin1character\_set\_filesystem binarycharacter\_set\_results latin1character\_set\_server latin1character\_set\_system utf8character\_sets\_dir /usr/share/mysql/charsets/collation\_connection latin1\_general\_cicollation\_database latin1\_general\_cicollation\_server latin1\_general\_cicompletion\_type 0concurrent\_insert 2connect\_timeout 10datadir /data/date\_format %Y-%m-%ddatetime\_format %Y-%m-%d %H:%i:%sdefault\_week\_format 0delay\_key\_write ONdelayed\_insert\_limit 100delayed\_insert\_timeout 300delayed\_queue\_size 1000div\_precision\_increment 4keep\_files\_on\_create OFFengine\_condition\_pushdown OFFexpire\_logs\_days 7flush OFFflush\_time 0ft\_boolean\_syntax + -\>\<()~\*:“”&|ft\_max\_word\_len 84ft\_min\_word\_len 4ft\_query\_expansion\_limit 20ft\_stopword\_file (built-in)group\_concat\_max\_len 1024have\_archive YEShave\_bdb NOhave\_blackhole\_engine YEShave\_compress YEShave\_community\_features YEShave\_profiling YEShave\_crypt YEShave\_csv YEShave\_dynamic\_loading YEShave\_example\_engine NOhave\_federated\_engine YEShave\_geometry YEShave\_innodb YEShave\_isam NOhave\_merge\_engine YEShave\_ndbcluster DISABLEDhave\_openssl DISABLEDhave\_ssl DISABLEDhave\_query\_cache YEShave\_raid NOhave\_rtree\_keys YEShave\_symlink YEShostname lana-db3m1ainit\_connect init\_file init\_slave innodb\_additional\_mem\_pool\_size 1048576innodb\_autoextend\_increment 8innodb\_buffer\_pool\_awe\_mem\_mb 0innodb\_buffer\_pool\_size 4294967296innodb\_checksums ONinnodb\_commit\_concurrency 0innodb\_concurrency\_tickets 500innodb\_data\_file\_path ibdata1:10M:autoextendinnodb\_data\_home\_dir innodb\_adaptive\_hash\_index ONinnodb\_doublewrite ONinnodb\_fast\_shutdown 1innodb\_file\_io\_threads 4innodb\_file\_per\_table ONinnodb\_flush\_log\_at\_trx\_commit 0innodb\_flush\_method innodb\_force\_recovery 0innodb\_lock\_wait\_timeout 70innodb\_locks\_unsafe\_for\_binlog OFFinnodb\_log\_arch\_dir innodb\_log\_archive OFFinnodb\_log\_buffer\_size 8388608innodb\_log\_file\_size 1073741824innodb\_log\_files\_in\_group 2innodb\_log\_group\_home\_dir ./innodb\_max\_dirty\_pages\_pct 90innodb\_max\_purge\_lag 0innodb\_mirrored\_log\_groups 1innodb\_open\_files 300innodb\_rollback\_on\_timeout OFFinnodb\_support\_xa ONinnodb\_sync\_spin\_loops 20innodb\_table\_locks ONinnodb\_thread\_concurrency 36innodb\_thread\_sleep\_delay 10000innodb\_use\_legacy\_cardinality\_algorithm ONinteractive\_timeout 28800join\_buffer\_size 131072key\_buffer\_size 3145728000key\_cache\_age\_threshold 300key\_cache\_block\_size 1024key\_cache\_division\_limit 100language /usr/share/mysql/english/large\_files\_support ONlarge\_page\_size 0large\_pages OFFlc\_time\_names en\_USlicense GPLlocal\_infile ONlocked\_in\_memory OFFlog OFFlog\_bin ONlog\_bin\_trust\_function\_creators OFFlog\_error log\_queries\_not\_using\_indexes OFFlog\_slave\_updates ONlog\_slow\_queries ONlog\_warnings 1long\_query\_time 3low\_priority\_updates OFFlower\_case\_file\_system OFFlower\_case\_table\_names 0max\_allowed\_packet 41943040max\_binlog\_cache\_size 18446744073709547520max\_binlog\_size 1073741824max\_connect\_errors 10max\_connections 1000max\_delayed\_threads 20max\_error\_count 64max\_heap\_table\_size 134217728max\_insert\_delayed\_threads 20max\_join\_size 18446744073709551615max\_length\_for\_sort\_data 1024max\_prepared\_stmt\_count 16382max\_relay\_log\_size 0max\_seeks\_for\_key 18446744073709551615max\_sort\_length 1024max\_sp\_recursion\_depth 0max\_tmp\_tables 32max\_user\_connections 0max\_write\_lock\_count 18446744073709551615multi\_range\_count 256myisam\_data\_pointer\_size 6myisam\_max\_sort\_file\_size 9223372036853727232myisam\_recover\_options OFFmyisam\_repair\_threads 1myisam\_sort\_buffer\_size 8388608myisam\_stats\_method nulls\_unequalndb\_autoincrement\_prefetch\_sz 1ndb\_force\_send ONndb\_use\_exact\_count ONndb\_use\_transactions ONndb\_cache\_check\_time 0ndb\_connectstring net\_buffer\_length 16384net\_read\_timeout 30net\_retry\_count 10net\_write\_timeout 60new OFFold\_passwords ONopen\_files\_limit 21010optimizer\_prune\_level 1optimizer\_search\_depth 62pid\_file /data/lana-db3m1a.pidplugin\_dir port 3306preload\_buffer\_size 32768profiling OFFprofiling\_history\_size 15protocol\_version 10query\_alloc\_block\_size 8192query\_cache\_limit 1048576query\_cache\_min\_res\_unit 4096query\_cache\_size 268435456query\_cache\_type ONquery\_cache\_wlock\_invalidate OFFquery\_prealloc\_size 8192range\_alloc\_block\_size 4096read\_buffer\_size 4194304read\_only OFFread\_rnd\_buffer\_size 16777216relay\_log /logs/relay-binrelay\_log\_index relay\_log\_info\_file relay-log.inforelay\_log\_purge ONrelay\_log\_space\_limit 0rpl\_recovery\_rank 0secure\_auth OFFsecure\_file\_priv server\_id 106skip\_external\_locking ONskip\_networking OFFskip\_show\_database OFFslave\_compressed\_protocol OFFslave\_load\_tmpdir /data/slave\_net\_timeout 3600slave\_skip\_errors 1062,1253,1267,1452slave\_transaction\_retries 10slow\_launch\_time 2socket /data/mysql.socksort\_buffer\_size 6291456sql\_big\_selects ONsql\_mode sql\_notes ONsql\_warnings OFFssl\_ca ssl\_capath ssl\_cert ssl\_cipher ssl\_key storage\_engine MyISAMsync\_binlog 0sync\_frm ONsystem\_time\_zone ESTtable\_cache 10000table\_lock\_wait\_timeout 50table\_type MyISAMthread\_cache\_size 256thread\_stack 262144time\_format %H:%i:%stime\_zone SYSTEMtimed\_mutexes OFFtmp\_table\_size 134217728tmpdir /datatransaction\_alloc\_block\_size 8192transaction\_prealloc\_size 4096tx\_isolation REPEATABLE-READupdatable\_views\_with\_limit YESversion 5.0.83-community-logversion\_comment MySQL Community Edition (GPL)version\_compile\_machine x86\_64version\_compile\_os unknown-linux-gnuwait\_timeout 60

global status

Variable\_name ValueAborted\_clients 336Aborted\_connects 1Binlog\_cache\_disk\_use 0Binlog\_cache\_use 1312873Bytes\_received 1169252460976Bytes\_sent 7272683457845Com\_admin\_commands 312046576Com\_alter\_db 0Com\_alter\_table 1899Com\_analyze 0Com\_backup\_table 0Com\_begin 728656Com\_call\_procedure 14Com\_change\_db 4124360253Com\_change\_master 0Com\_check 0Com\_checksum 0Com\_commit 728651Com\_create\_db 0Com\_create\_function 0Com\_create\_index 0Com\_create\_table 441887Com\_create\_user 0Com\_dealloc\_sql 0Com\_delete 35676191Com\_delete\_multi 161068Com\_do 6074960Com\_drop\_db 0Com\_drop\_function 0Com\_drop\_index 0Com\_drop\_table 10Com\_drop\_user 0Com\_execute\_sql 0Com\_flush 2Com\_grant 0Com\_ha\_close 0Com\_ha\_open 0Com\_ha\_read 0Com\_help 0Com\_insert 194773574Com\_insert\_select 6723928Com\_kill 3Com\_load 0Com\_load\_master\_data 0Com\_load\_master\_table 0Com\_lock\_tables 60829Com\_optimize 26732Com\_preload\_keys 0Com\_prepare\_sql 0Com\_purge 0Com\_purge\_before\_date 0Com\_rename\_table 0Com\_repair 0Com\_replace 111144797Com\_replace\_select 0Com\_reset 0Com\_restore\_table 0Com\_revoke 0Com\_revoke\_all 0Com\_rollback 5Com\_savepoint 0Com\_select 1033565819Com\_set\_option 540642959Com\_show\_binlog\_events 0Com\_show\_binlogs 23901Com\_show\_charsets 1078Com\_show\_collations 1078Com\_show\_column\_types 0Com\_show\_create\_db 0Com\_show\_create\_table 612Com\_show\_databases 1080Com\_show\_errors 0Com\_show\_fields 2792Com\_show\_grants 2840Com\_show\_innodb\_status 25291Com\_show\_keys 377Com\_show\_logs 0Com\_show\_master\_status 0Com\_show\_ndb\_status 0Com\_show\_new\_master 0Com\_show\_open\_tables 2Com\_show\_privileges 0Com\_show\_processlist 451839Com\_show\_slave\_hosts 0Com\_show\_slave\_status 23800Com\_show\_status 576260Com\_show\_storage\_engines 1Com\_show\_tables 11806Com\_show\_triggers 0Com\_show\_variables 126824Com\_show\_warnings 12Com\_slave\_start 0Com\_slave\_stop 0Com\_stmt\_close 42500Com\_stmt\_execute 42500Com\_stmt\_fetch 0Com\_stmt\_prepare 42500Com\_stmt\_reset 3008Com\_stmt\_send\_long\_data 0Com\_truncate 1Com\_unlock\_tables 60829Com\_update 315044644Com\_update\_multi 12618529Com\_xa\_commit 0Com\_xa\_end 0Com\_xa\_prepare 0Com\_xa\_recover 0Com\_xa\_rollback 0Com\_xa\_start 0Compression OFFConnections 377290889Created\_tmp\_disk\_tables 1751286Created\_tmp\_files 4788Created\_tmp\_tables 98467670Delayed\_errors 0Delayed\_insert\_threads 0Delayed\_writes 0Flush\_commands 1Handler\_commit 553780791Handler\_delete 40076793Handler\_discover 0Handler\_prepare 550409386Handler\_read\_first 196241092Handler\_read\_key 111689540591Handler\_read\_next 486793458770Handler\_read\_prev 4130967Handler\_read\_rnd 12614625273Handler\_read\_rnd\_next 2460361913745Handler\_rollback 105434949Handler\_savepoint 0Handler\_savepoint\_rollback 0Handler\_update 7929786548Handler\_write 144252022792Innodb\_buffer\_pool\_pages\_data 246724Innodb\_buffer\_pool\_pages\_dirty 1529Innodb\_buffer\_pool\_pages\_flushed 39777966Innodb\_buffer\_pool\_pages\_free 1Innodb\_buffer\_pool\_pages\_misc 15419Innodb\_buffer\_pool\_pages\_total 262144Innodb\_buffer\_pool\_read\_ahead\_rnd 117867739Innodb\_buffer\_pool\_read\_ahead\_seq 2967813Innodb\_buffer\_pool\_read\_requests 2466719156656Innodb\_buffer\_pool\_reads 2735311046Innodb\_buffer\_pool\_wait\_free 0Innodb\_buffer\_pool\_write\_requests 1666436600Innodb\_data\_fsyncs 10702492Innodb\_data\_pending\_fsyncs 0Innodb\_data\_pending\_reads 0Innodb\_data\_pending\_writes 0Innodb\_data\_read 105373694955520Innodb\_data\_reads 4270800806Innodb\_data\_writes 36661904Innodb\_data\_written 1406913750016Innodb\_dblwr\_pages\_written 39777966Innodb\_dblwr\_writes 686956Innodb\_log\_waits 0Innodb\_log\_write\_requests 243864251Innodb\_log\_writes 3834773Innodb\_os\_log\_fsyncs 4177719Innodb\_os\_log\_pending\_fsyncs 0Innodb\_os\_log\_pending\_writes 0Innodb\_os\_log\_written 103293077504Innodb\_page\_size 16384Innodb\_pages\_created 1597085Innodb\_pages\_read 6431525038Innodb\_pages\_written 39777966Innodb\_row\_lock\_current\_waits 0Innodb\_row\_lock\_time 1020025605Innodb\_row\_lock\_time\_avg 110Innodb\_row\_lock\_time\_max 71936Innodb\_row\_lock\_waits 9227174Innodb\_rows\_deleted 72297391Innodb\_rows\_inserted 73356981Innodb\_rows\_read 2765931802595Innodb\_rows\_updated 74496989Key\_blocks\_not\_flushed 0Key\_blocks\_unused 896308Key\_blocks\_used 1838267Key\_read\_requests 115685330Key\_reads 87329Key\_write\_requests 1995742Key\_writes 1256391Last\_query\_cost 0.000000Max\_used\_connections 221Ndb\_cluster\_node\_id 0Ndb\_config\_from\_host Ndb\_config\_from\_port 0Ndb\_number\_of\_data\_nodes 0Not\_flushed\_delayed\_rows 0Open\_files 8202Open\_streams 0Open\_tables 8017Opened\_tables 61614Prepared\_stmt\_count 0Qcache\_free\_blocks 12510Qcache\_free\_memory 247980304Qcache\_hits 13409828Qcache\_inserts 2463254Qcache\_lowmem\_prunes 183996Qcache\_not\_cached 2191930Qcache\_queries\_in\_cache 14613Qcache\_total\_blocks 42038Queries 9729567079Questions 9637939650Rpl\_status NULLSelect\_full\_join 12098519Select\_full\_range\_join 4296Select\_range 104429270Select\_range\_check 0Select\_scan 183690038Slave\_open\_temp\_tables 0Slave\_retried\_transactions 0Slave\_running OFFSlow\_launch\_threads 0Slow\_queries 110448Sort\_merge\_passes 414912Sort\_range 70633918Sort\_rows 40111321261Sort\_scan 70952027Ssl\_accept\_renegotiates 0Ssl\_accepts 0Ssl\_callback\_cache\_hits 0Ssl\_cipher Ssl\_cipher\_list Ssl\_client\_connects 0Ssl\_connect\_renegotiates 0Ssl\_ctx\_verify\_depth 0Ssl\_ctx\_verify\_mode 0Ssl\_default\_timeout 0Ssl\_finished\_accepts 0Ssl\_finished\_connects 0Ssl\_session\_cache\_hits 0Ssl\_session\_cache\_misses 0Ssl\_session\_cache\_mode NONESsl\_session\_cache\_overflows 0Ssl\_session\_cache\_size 0Ssl\_session\_cache\_timeouts 0Ssl\_sessions\_reused 0Ssl\_used\_session\_cache\_entries 0Ssl\_verify\_depth 0Ssl\_verify\_mode 0Ssl\_version Table\_locks\_immediate 7979472Table\_locks\_waited 17046Tc\_log\_max\_pages\_used 0Tc\_log\_page\_size 0Tc\_log\_page\_waits 0Threads\_cached 237Threads\_connected 20Threads\_created 1752Threads\_running 6Uptime 3576291Uptime\_since\_flush\_status 11291

/proc/meminfo

MemTotal: 24546100 kBMemFree: 406664 kBBuffers: 202632 kBCached: 12887608 kBSwapCached: 0 kBActive: 21878348 kBInactive: 1794060 kBHighTotal: 0 kBHighFree: 0 kBLowTotal: 24546100 kBLowFree: 406664 kBSwapTotal: 4192956 kBSwapFree: 4192668 kBDirty: 28256 kBWriteback: 24 kBAnonPages: 10438848 kBMapped: 44560 kBSlab: 274884 kBPageTables: 28604 kBNFS\_Unstable: 0 kBBounce: 0 kBCommitLimit: 16466004 kBCommitted\_AS: 13406952 kBVmallocTotal: 34359738367 kBVmallocUsed: 4624 kBVmallocChunk: 34359733459 kBHugePages\_Total: 0HugePages\_Free: 0HugePages\_Rsvd: 0Hugepagesize: 2048 kB

/proc/cpuinfo

processor : 0vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 0cpu cores : 4apicid : 0fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4536.89clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 1vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 0cpu cores : 4apicid : 16fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4549.28clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 2vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 1cpu cores : 4apicid : 2fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.52clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 3vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 1cpu cores : 4apicid : 18fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.46clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 4vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 2cpu cores : 4apicid : 4fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.52clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 5vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 2cpu cores : 4apicid : 20fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.45clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 6vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 3cpu cores : 4apicid : 6fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.45clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 7vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 3cpu cores : 4apicid : 22fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.52clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 8vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 0cpu cores : 4apicid : 1fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.52clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 9vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 0cpu cores : 4apicid : 17fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.53clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 10vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 1cpu cores : 4apicid : 3fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.52clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 11vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 1cpu cores : 4apicid : 19fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.46clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 12vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 2cpu cores : 4apicid : 5fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4503.65clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 13vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 2cpu cores : 4apicid : 21fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.52clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 14vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 0siblings : 8core id : 3cpu cores : 4apicid : 7fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.52clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]processor : 15vendor\_id : GenuineIntelcpu family : 6model : 26model name : Intel(R) Xeon(R) CPU E5520 @ 2.27GHzstepping : 5cpu MHz : 2266.841cache size : 8192 KBphysical id : 1siblings : 8core id : 3cpu cores : 4apicid : 23fpu : yesfpu\_exception : yescpuid level : 11wp : yesflags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx rdtscp lm constant\_tsc nonstop\_tsc pni monitor ds\_cpl vmx est tm2 cx16 xtpr popcnt lahf\_lmbogomips : 4533.52clflush size : 64cache\_alignment : 64address sizes : 40 bits physical, 48 bits virtualpower management: [8]

Thanks,

kosta

---

<div class="post-metadata">

**Author:** ![gmouse](https://avatars.discourse-cdn.com/v4/letter/g/b9e5f3/32.png) [@gmouse](https://forums.percona.com/u/gmouse)\
**Post date:** [April 1, 2010, 4:35pm UTC](https://forums.percona.com/t/innodb-performance/1377/2 "2010-04-01T16:35:37Z")

</div>

Are those queries really optimized?

Why such a small buffer pool?

I would disable query cache.

---

<div class="post-metadata">

**Author:** ![kostagr33k](https://avatars.discourse-cdn.com/v4/letter/k/ebca7d/32.png) [@kostagr33k](https://forums.percona.com/u/kostagr33k)\
**Post date:** [April 1, 2010, 4:39pm UTC](https://forums.percona.com/t/innodb-performance/1377/3 "2010-04-01T16:39:13Z")

</div>

Thanks. We just bumped up the innodb\_files\_open to about 1000 and doubled the innodb buffer pool to 8gb (was 4gb before).

Queries are in a mixed state as we have a mixture of old application and a new one. The new one is being tuned on a weekly basis and we always send more queries for them to optimize.

However this seems to only happen when we get overwhelmed with traffic.

I noticed that normally we have about 10mbps inbound traffic to mysql and at that time it hit 100mbps.

Also why do you recommend disabling the query cache? I know its only about 50% utilized but does something stick out from the info above? What will be pros/cons be against this? We have started to use memcache for some data as well.

kosta