# Bad performance on a large table

**URL:** <https://forums.percona.com/t/bad-performance-on-a-large-table/687>\
**Category:** Other MySQL® Questions\
**Created:** [March 26, 2008, 8:50am UTC](https://forums.percona.com/t/bad-performance-on-a-large-table/687 "2008-03-26T08:50:49Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![beanblog](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/beanblog/32/774_2.png) [@beanblog](https://forums.percona.com/u/beanblog)\
**Post date:** [March 26, 2008, 8:50am UTC](https://forums.percona.com/t/bad-performance-on-a-large-table/687/1 "2008-03-26T08:50:49Z")

</div>

Hello!

I’m having some issues with a MySQL server and I’m hoping someone here might be able to shed some light on them.

I’ve got a MyISAM table with about 8 million records. The MYI file is 2.6G and the MYI file is 1.8G. The machine has 4G of RAM, and it’s got dual XEON processors and a RAID 5 hard disk array. When I run a query such as “create table tmp select id, amt from tbl”, the system becomes overloaded for at least a minute, causing severe performance issues with concurrent db access in other apps. When I need to modify the table or run a more advanced query, the times are 30 minutes+, and the system is pretty much unusable during that time.

Can you spot any glaring problems in any of the settings below?

free -m:

total used free shared buffers cachedMem: 4042 3831 210 0 6 3538-/+ buffers/cache: 287 3755Swap: 8191 0 8191

show variables:

auto\_increment\_increment 1auto\_increment\_offset 1automatic\_sp\_privileges ONback\_log 50basedir /usr/bdb\_cache\_size 8388600bdb\_home /srv/mysql/bdb\_log\_buffer\_size 524288bdb\_logdir bdb\_max\_lock 10000bdb\_shared\_data OFFbdb\_tmpdir /tmp/binlog\_cache\_size 32768bulk\_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\_swedish\_cicollation\_database latin1\_swedish\_cicollation\_server latin1\_swedish\_cicompletion\_type 0concurrent\_insert 1connect\_timeout 5datadir /srv/mysql/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 4engine\_condition\_pushdown OFFexpire\_logs\_days 0flush 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 4096have\_archive NOhave\_bdb YEShave\_blackhole\_engine NOhave\_compress YEShave\_crypt YEShave\_csv NOhave\_dynamic\_loading YEShave\_example\_engine NOhave\_federated\_engine NOhave\_geometry YEShave\_innodb YEShave\_isam NOhave\_merge\_engine YEShave\_ndbcluster NOhave\_openssl DISABLEDhave\_query\_cache YEShave\_raid NOhave\_rtree\_keys YEShave\_symlink YESinit\_connect init\_file init\_slave innodb\_additional\_mem\_pool\_size 1048576innodb\_autoextend\_increment 8innodb\_buffer\_pool\_awe\_mem\_mb 0innodb\_buffer\_pool\_size 8388608innodb\_checksums ONinnodb\_commit\_concurrency 0innodb\_concurrency\_tickets 500innodb\_data\_file\_path ibdata1:10M:autoextendinnodb\_data\_home\_dir innodb\_doublewrite ONinnodb\_fast\_shutdown 1innodb\_file\_io\_threads 4innodb\_file\_per\_table OFFinnodb\_flush\_log\_at\_trx\_commit 1innodb\_flush\_method innodb\_force\_recovery 0innodb\_lock\_wait\_timeout 50innodb\_locks\_unsafe\_for\_binlog OFFinnodb\_log\_arch\_dir innodb\_log\_archive OFFinnodb\_log\_buffer\_size 1048576innodb\_log\_file\_size 5242880innodb\_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\_support\_xa ONinnodb\_sync\_spin\_loops 20innodb\_table\_locks ONinnodb\_thread\_concurrency 8innodb\_thread\_sleep\_delay 10000interactive\_timeout 28800join\_buffer\_size 131072key\_buffer\_size 1073741824key\_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 OFFlog\_bin\_trust\_function\_creators OFFlog\_error log\_queries\_not\_using\_indexes OFFlog\_slave\_updates OFFlog\_slow\_queries ONlog\_warnings 1long\_query\_time 1low\_priority\_updates OFFlower\_case\_file\_system OFFlower\_case\_table\_names 0max\_allowed\_packet 16776192max\_binlog\_cache\_size 4294967295max\_binlog\_size 1073741824max\_connect\_errors 10max\_connections 100max\_delayed\_threads 20max\_error\_count 64max\_heap\_table\_size 536869888max\_insert\_delayed\_threads 20max\_join\_size 4294967295max\_length\_for\_sort\_data 1024max\_prepared\_stmt\_count 16382max\_relay\_log\_size 0max\_seeks\_for\_key 4294967295max\_sort\_length 1024max\_sp\_recursion\_depth 0max\_tmp\_tables 32max\_user\_connections 0max\_write\_lock\_count 4294967295multi\_range\_count 256myisam\_data\_pointer\_size 6myisam\_max\_sort\_file\_size 2147483647myisam\_recover\_options OFFmyisam\_repair\_threads 1myisam\_sort\_buffer\_size 8388608myisam\_stats\_method nulls\_unequalnet\_buffer\_length 16384net\_read\_timeout 30net\_retry\_count 10net\_write\_timeout 60new OFFold\_passwords OFFopen\_files\_limit 2158optimizer\_prune\_level 1optimizer\_search\_depth 62pid\_file /var/run/mysqld/mysqld.pidport 3306preload\_buffer\_size 32768prepared\_stmt\_count 0protocol\_version 10query\_alloc\_block\_size 8192query\_cache\_limit 1048576query\_cache\_min\_res\_unit 4096query\_cache\_size 0query\_cache\_type ONquery\_cache\_wlock\_invalidate OFFquery\_prealloc\_size 8192range\_alloc\_block\_size 2048read\_buffer\_size 131072read\_only OFFread\_rnd\_buffer\_size 262144relay\_log\_purge ONrelay\_log\_space\_limit 0rpl\_recovery\_rank 0secure\_auth OFFserver\_id 0skip\_external\_locking ONskip\_networking OFFskip\_show\_database OFFslave\_compressed\_protocol OFFslave\_load\_tmpdir /tmp/slave\_net\_timeout 3600slave\_skip\_errors OFFslave\_transaction\_retries 10slow\_launch\_time 2socket /var/lib/mysql/mysql.socksort\_buffer\_size 2097144sql\_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 EDTtable\_cache 1024table\_lock\_wait\_timeout 50table\_type MyISAMthread\_cache\_size 0thread\_stack 196608time\_format %H:%i:%stime\_zone SYSTEMtimed\_mutexes OFFtmp\_table\_size 536870912tmpdir /tmp/transaction\_alloc\_block\_size 8192transaction\_prealloc\_size 4096tx\_isolation REPEATABLE-READupdatable\_views\_with\_limit YESversion 5.0.27-logversion\_bdb Sleepycat Software: Berkeley DB 4.1.24: (October 21, 2006)version\_comment Source distributionversion\_compile\_machine i686version\_compile\_os redhat-linux-gnuwait\_timeout 28800

show status:

Aborted\_clients 10Aborted\_connects 0Binlog\_cache\_disk\_use 0Binlog\_cache\_use 0Bytes\_received 631Bytes\_sent 204777375Com\_admin\_commands 0Com\_alter\_db 0Com\_alter\_table 0Com\_analyze 0Com\_backup\_table 0Com\_begin 0Com\_change\_db 0Com\_change\_master 0Com\_check 0Com\_checksum 0Com\_commit 0Com\_create\_db 0Com\_create\_function 0Com\_create\_index 0Com\_create\_table 1Com\_dealloc\_sql 0Com\_delete 0Com\_delete\_multi 0Com\_do 0Com\_drop\_db 0Com\_drop\_function 0Com\_drop\_index 0Com\_drop\_table 1Com\_drop\_user 0Com\_execute\_sql 0Com\_flush 0Com\_grant 0Com\_ha\_close 0Com\_ha\_open 0Com\_ha\_read 0Com\_help 0Com\_insert 0Com\_insert\_select 0Com\_kill 1Com\_load 0Com\_load\_master\_data 0Com\_load\_master\_table 0Com\_lock\_tables 0Com\_optimize 0Com\_preload\_keys 0Com\_prepare\_sql 0Com\_purge 0Com\_purge\_before\_date 0Com\_rename\_table 0Com\_repair 0Com\_replace 0Com\_replace\_select 0Com\_reset 0Com\_restore\_table 0Com\_revoke 0Com\_revoke\_all 0Com\_rollback 0Com\_savepoint 0Com\_select 1Com\_set\_option 0Com\_show\_binlog\_events 0Com\_show\_binlogs 0Com\_show\_charsets 0Com\_show\_collations 0Com\_show\_column\_types 0Com\_show\_create\_db 0Com\_show\_create\_table 0Com\_show\_databases 0Com\_show\_errors 0Com\_show\_fields 0Com\_show\_grants 0Com\_show\_innodb\_status 0Com\_show\_keys 0Com\_show\_logs 0Com\_show\_master\_status 0Com\_show\_ndb\_status 0Com\_show\_new\_master 0Com\_show\_open\_tables 0Com\_show\_privileges 0Com\_show\_processlist 0Com\_show\_slave\_hosts 0Com\_show\_slave\_status 0Com\_show\_status 1Com\_show\_storage\_engines 0Com\_show\_tables 0Com\_show\_triggers 0Com\_show\_variables 1Com\_show\_warnings 0Com\_slave\_start 0Com\_slave\_stop 0Com\_stmt\_close 0Com\_stmt\_execute 0Com\_stmt\_fetch 0Com\_stmt\_prepare 0Com\_stmt\_reset 0Com\_stmt\_send\_long\_data 0Com\_truncate 0Com\_unlock\_tables 0Com\_update 0Com\_update\_multi 0Com\_xa\_commit 0Com\_xa\_end 0Com\_xa\_prepare 0Com\_xa\_recover 0Com\_xa\_rollback 0Com\_xa\_start 0Compression OFFConnections 113Created\_tmp\_disk\_tables 0Created\_tmp\_files 11Created\_tmp\_tables 2Delayed\_errors 0Delayed\_insert\_threads 0Delayed\_writes 0Flush\_commands 1Handler\_commit 0Handler\_delete 0Handler\_discover 0Handler\_prepare 0Handler\_read\_first 0Handler\_read\_key 0Handler\_read\_next 0Handler\_read\_prev 0Handler\_read\_rnd 0Handler\_read\_rnd\_next 8314748Handler\_rollback 0Handler\_savepoint 0Handler\_savepoint\_rollback 0Handler\_update 0Handler\_write 358Innodb\_buffer\_pool\_pages\_data 20Innodb\_buffer\_pool\_pages\_dirty 0Innodb\_buffer\_pool\_pages\_flushed 0Innodb\_buffer\_pool\_pages\_free 492Innodb\_buffer\_pool\_pages\_latched 0Innodb\_buffer\_pool\_pages\_misc 0Innodb\_buffer\_pool\_pages\_total 512Innodb\_buffer\_pool\_read\_ahead\_rnd 1Innodb\_buffer\_pool\_read\_ahead\_seq 0Innodb\_buffer\_pool\_read\_requests 269Innodb\_buffer\_pool\_reads 13Innodb\_buffer\_pool\_wait\_free 0Innodb\_buffer\_pool\_write\_requests 0Innodb\_data\_fsyncs 3Innodb\_data\_pending\_fsyncs 0Innodb\_data\_pending\_reads 0Innodb\_data\_pending\_writes 0Innodb\_data\_read 2510848Innodb\_data\_reads 26Innodb\_data\_writes 3Innodb\_data\_written 1536Innodb\_dblwr\_pages\_written 0Innodb\_dblwr\_writes 0Innodb\_log\_waits 0Innodb\_log\_write\_requests 0Innodb\_log\_writes 1Innodb\_os\_log\_fsyncs 3Innodb\_os\_log\_pending\_fsyncs 0Innodb\_os\_log\_pending\_writes 0Innodb\_os\_log\_written 512Innodb\_page\_size 16384Innodb\_pages\_created 0Innodb\_pages\_read 20Innodb\_pages\_written 0Innodb\_row\_lock\_current\_waits 0Innodb\_row\_lock\_time 0Innodb\_row\_lock\_time\_avg 0Innodb\_row\_lock\_time\_max 0Innodb\_row\_lock\_waits 0Innodb\_rows\_deleted 0Innodb\_rows\_inserted 0Innodb\_rows\_read 0Innodb\_rows\_updated 0Key\_blocks\_not\_flushed 0Key\_blocks\_unused 926842Key\_blocks\_used 999Key\_read\_requests 212218Key\_reads 1011Key\_write\_requests 994Key\_writes 527Last\_query\_cost 10.499000Max\_used\_connections 5Not\_flushed\_delayed\_rows 0Open\_files 106Open\_streams 0Open\_tables 53Opened\_tables 1Qcache\_free\_blocks 0Qcache\_free\_memory 0Qcache\_hits 0Qcache\_inserts 0Qcache\_lowmem\_prunes 0Qcache\_not\_cached 0Qcache\_queries\_in\_cache 0Qcache\_total\_blocks 0Questions 5754Rpl\_status NULLSelect\_full\_join 0Select\_full\_range\_join 0Select\_range 0Select\_range\_check 0Select\_scan 3Slave\_open\_temp\_tables 0Slave\_retried\_transactions 0Slave\_running OFFSlow\_launch\_threads 0Slow\_queries 1Sort\_merge\_passes 0Sort\_range 0Sort\_rows 0Sort\_scan 0Ssl\_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 3137Table\_locks\_waited 0Tc\_log\_max\_pages\_used 0Tc\_log\_page\_size 0Tc\_log\_page\_waits 0Threads\_cached 0Threads\_connected 2Threads\_created 112Threads\_running 1Uptime 1407

Thanks. Any help will be greatly appreciated.

Ben

---

<div class="post-metadata">

**Author:** ![safari](https://avatars.discourse-cdn.com/v4/letter/s/838e76/32.png) [@safari](https://forums.percona.com/u/safari)\
**Post date:** [March 26, 2008, 10:09am UTC](https://forums.percona.com/t/bad-performance-on-a-large-table/687/2 "2008-03-26T10:09:53Z")

</div>

I suggest 2 things to do:

- Upgrade RAM to 8MB or more
- Consider using InnoDB for your tables. This depends on your application, ie. heavy read or heavy write.

---

<div class="post-metadata">

**Author:** ![beanblog](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/beanblog/32/774_2.png) [@beanblog](https://forums.percona.com/u/beanblog)\
**Post date:** [March 27, 2008, 8:29am UTC](https://forums.percona.com/t/bad-performance-on-a-large-table/687/3 "2008-03-27T08:29:08Z")

</div>

safari,  
Thanks for the suggestions.

Since this box is a dedicated SQL server, I temporarily bumped the key\_buffer\_size up to 3G (larger than my MYI and MYD files) and saw the performance jump 100 fold. I assume it is because the large tables are now able to be completely loaded into memory?

At any rate - I’ve got 8G more RAM to put in tomorrow morning, then I should be set.

Thanks for the help!

Ben

---

<div class="post-metadata">

**Author:** ![sterin](https://avatars.discourse-cdn.com/v4/letter/s/3ab097/32.png) [@sterin](https://forums.percona.com/u/sterin)\
**Post date:** [March 27, 2008, 11:55am UTC](https://forums.percona.com/t/bad-performance-on-a-large-table/687/4 "2008-03-27T11:55:51Z")

</div>

The key buffer controlled by the key\_buffer\_size parameter does not cache table data. It only caches indexes.  
But one of the big rules for performance with MySQL is that all the indexes should fit entirely into the key buffer.

The reason for this is that index reads are by nature very often random down through the tree and that means that it first has to perform a lot of random reads through the index tree. And then it needs to perform an additional random read to find the actual data in the table.  
And random reads are one of the slowest operations that a database server can perform.

Hence recommendation to at least have so much memory so that the index can fit into it.

---

<div class="post-metadata">

**Author:** ![beanblog](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/beanblog/32/774_2.png) [@beanblog](https://forums.percona.com/u/beanblog)\
**Post date:** [March 27, 2008, 12:07pm UTC](https://forums.percona.com/t/bad-performance-on-a-large-table/687/5 "2008-03-27T12:07:58Z")

</div>

Hmm… well my total index size is around 20G, so loading all index files into memory at the same time isn’t going to happen with our current hardware. Fortunately, the largest single index file is only 2G.

Thanks for the help.  
Ben