# low performance - what am i doing wrong?

**URL:** <https://forums.percona.com/t/low-performance-what-am-i-doing-wrong/759>\
**Category:** Other MySQL® Questions\
**Created:** [May 16, 2008, 6:42am UTC](https://forums.percona.com/t/low-performance-what-am-i-doing-wrong/759 "2008-05-16T06:42:27Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![avatar](https://avatars.discourse-cdn.com/v4/letter/a/8c91f0/32.png) [@avatar](https://forums.percona.com/u/avatar)\
**Post date:** [May 16, 2008, 6:42am UTC](https://forums.percona.com/t/low-performance-what-am-i-doing-wrong/759/1 "2008-05-16T06:42:27Z")

</div>

Could anyone tell me what is wrong - after ive put the database on the server query takes ages to execute:

SELECT company.company\_id,company.enhancement FROM company LEFT JOIN company\_in\_cat\_100 ON company.company\_id = company\_in\_cat\_100.company\_id where company\_in\_cat\_100.category\_id =53;

1st run: 39627 rows in set (1 min 50.44 sec)  
later : ~10 sec

I dont really understand why - I have tested everything on my local machine, and it was maximum 0,5-1,2sec.

Structure dump:

CREATE TABLE `company_in_cat_100` ( `id` mediumint(7) NOT NULL auto\_increment, `company_id` mediumint(7) unsigned NOT NULL default ‘0’, `category_id` smallint(4) unsigned NOT NULL default ‘0’, PRIMARY KEY (`id`), KEY `category_id` (`category_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO\_INCREMENT=158105 ;

CREATE TABLE `company` ( `company_id` mediumint(7) unsigned NOT NULL auto\_increment, `name` varchar(100) NOT NULL default ‘’, `add1` varchar(64) NOT NULL default ‘’, `add2` varchar(64) NOT NULL default ‘’, `add3` varchar(64) NOT NULL default ‘’, `town_id` smallint(4) unsigned NOT NULL default ‘0’, `county_id` mediumint(6) unsigned NOT NULL default ‘0’, `postcode` varchar(9) NOT NULL default ‘’, `telephone` varchar(30) NOT NULL default ‘’, `description_text` varchar(255) NOT NULL default ‘’, `enhancement` tinyint(1) NOT NULL default ‘0’, PRIMARY KEY (`company_id`), KEY `name` (`name`), KEY `town` (`town_id`)) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO\_INCREMENT=1707942 ;

Explein Query:

±—±------------±-------------------±-------±--------------±------------±--------±----------------------------------------------±------±------------+| id | select\_type | table | type | possible\_keys | key | key\_len | ref | rows | Extra |±—±------------±-------------------±-------±--------------±------------±--------±----------------------------------------------±------±------------+| 1 | SIMPLE | company\_in\_cat\_100 | ref | category\_id | category\_id | 2 | const | 34695 | Using where || 1 | SIMPLE | company | eq\_ref | PRIMARY | PRIMARY | 3 | thisisbusnew\_en.company\_in\_cat\_100.company\_id | 1 | |±—±------------±-------------------±-------±--------------±------------±--------±----------------------------------------------±------±------------+

Show variables:

±--------------------------------±---------------------------------------------+| Variable\_name | Value |±--------------------------------±---------------------------------------------+| back\_log | 50 || basedir | / || binlog\_cache\_size | 32768 || bulk\_insert\_buffer\_size | 8388608 || character\_set\_client | latin1 || character\_set\_connection | latin1 || character\_set\_database | latin1 || character\_set\_results | latin1 || character\_set\_server | latin1 || character\_set\_system | utf8 || character\_sets\_dir | /usr/share/mysql/charsets/ || collation\_connection | latin1\_swedish\_ci || collation\_database | latin1\_swedish\_ci || collation\_server | latin1\_swedish\_ci || concurrent\_insert | ON || connect\_timeout | 5 || datadir | /var/lib/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 || 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 | NO || have\_blackhole\_engine | NO || have\_compress | YES || have\_crypt | YES || have\_csv | NO || have\_example\_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\_data\_file\_path | ibdata1:10M:autoextend || innodb\_data\_home\_dir | || innodb\_fast\_shutdown | ON || 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\_table\_locks | ON || innodb\_thread\_concurrency | 8 || interactive\_timeout | 28800 || join\_buffer\_size | 131072 || key\_buffer\_size | 8388600 || key\_cache\_age\_threshold | 300 || key\_cache\_block\_size | 1024 || key\_cache\_division\_limit | 100 || language | /usr/share/mysql/english/ || large\_files\_support | ON || lc\_time\_names | en\_US || license | GPL || local\_infile | ON || locked\_in\_memory | OFF || log | OFF || log\_bin | OFF || log\_error | || log\_slave\_updates | OFF || log\_slow\_queries | OFF || log\_update | OFF || log\_warnings | 1 || long\_query\_time | 10 || low\_priority\_updates | OFF || lower\_case\_file\_system | OFF || lower\_case\_table\_names | 0 || max\_allowed\_packet | 1048576 || max\_binlog\_cache\_size | 4294967295 || max\_binlog\_size | 1073741824 || max\_connect\_errors | 10 || max\_connections | 100 || 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\_tmp\_tables | 32 || max\_user\_connections | 0 || max\_write\_lock\_count | 4294967295 || myisam\_data\_pointer\_size | 4 || myisam\_max\_extra\_sort\_file\_size | 2147483648 || myisam\_max\_sort\_file\_size | 2147483647 || myisam\_recover\_options | OFF || myisam\_repair\_threads | 1 || myisam\_sort\_buffer\_size | 8388608 || myisam\_stats\_method | nulls\_unequal || net\_buffer\_length | 16384 || net\_read\_timeout | 30 || net\_retry\_count | 10 || net\_write\_timeout | 60 || new | OFF || old\_passwords | ON || open\_files\_limit | 1024 || pid\_file | /var/lib/mysql/STEAM3.POBOXHOSTING.CO.UK.pid || port | 3306 || preload\_buffer\_size | 32768 || prepared\_stmt\_count | 0 || protocol\_version | 10 || query\_alloc\_block\_size | 8192 || query\_cache\_limit | 1048576 || query\_cache\_min\_res\_unit | 4096 || query\_cache\_size | 0 || query\_cache\_type | ON || query\_cache\_wlock\_invalidate | OFF || query\_prealloc\_size | 8192 || range\_alloc\_block\_size | 2048 || read\_buffer\_size | 131072 || read\_only | OFF || read\_rnd\_buffer\_size | 262144 || relay\_log\_purge | ON || relay\_log\_space\_limit | 0 || rpl\_recovery\_rank | 0 || secure\_auth | OFF || server\_id | 0 || skip\_external\_locking | ON || skip\_networking | OFF || skip\_show\_database | OFF || slave\_net\_timeout | 3600 || slave\_transaction\_retries | 0 || slow\_launch\_time | 2 || socket | /var/lib/mysql/mysql.sock || sort\_buffer\_size | 2097144 || sql\_mode | || sql\_notes | ON || sql\_warnings | ON || storage\_engine | MyISAM || sync\_binlog | 0 || sync\_frm | ON || sync\_replication | 0 || sync\_replication\_slave\_id | 0 || sync\_replication\_timeout | 0 || system\_time\_zone | BST || table\_cache | 64 || table\_type | MyISAM || thread\_cache\_size | 0 || thread\_stack | 196608 || time\_format | %H:%i:%s || time\_zone | SYSTEM || tmp\_table\_size | 33554432 || tmpdir | || transaction\_alloc\_block\_size | 8192 || transaction\_prealloc\_size | 4096 || tx\_isolation | REPEATABLE-READ || version | 4.1.22-standard || version\_comment | MySQL Community Edition - Standard (GPL) || version\_compile\_machine | i686 || version\_compile\_os | pc-linux-gnu || wait\_timeout | 28800 |±--------------------------------±---------------------------------------------+

System:linux with 512 RAM memory  
One more thing - while running query mysql uses only 3% of CPU.  
Ive tried everything. Please let me know what you think.

---

<div class="post-metadata">

**Author:** ![mwallace](https://avatars.discourse-cdn.com/v4/letter/m/ee59a6/32.png) [@mwallace](https://forums.percona.com/u/mwallace)\
**Post date:** [May 16, 2008, 10:09am UTC](https://forums.percona.com/t/low-performance-what-am-i-doing-wrong/759/2 "2008-05-16T10:09:43Z")

</div>

could you please post some additional information from both machines?  
The output of:  
free

- memory consumtpion  
the header and first couple of iterations of ‘iostat 5’
- i/o and system load  
the header info from ‘top’
- additional system info  
diff output from ‘show variables’ and ‘show status’ on both machines.  
Basic configuration information about both machines - CPUs, RAM, other major hardware differences.  
Are both databases using the same storage engine? Are indexes the same across both pairs of tables?

---

<div class="post-metadata">

**Author:** ![avatar](https://avatars.discourse-cdn.com/v4/letter/a/8c91f0/32.png) [@avatar](https://forums.percona.com/u/avatar)\
**Post date:** [May 16, 2008, 11:50am UTC](https://forums.percona.com/t/low-performance-what-am-i-doing-wrong/759/3 "2008-05-16T11:50:29Z")

</div>

Sure  
Thanks for interest - I haven’t got too much database administration experience. It sad that companys PHP guy has to do admin too…

free:

total used free shared buffers cachedMem: 515760 503052 12708 0 6612 160224-/+ buffers/cache: 336216 179544Swap: 1048568 44512 1004056

iostat:

Linux 2.6.15-1.2054\_FC5 ([STEAM3.POBOXHOSTING.CO.UK](http://STEAM3.POBOXHOSTING.CO.UK)) 05/16/2008avg-cpu: %user %nice %system %iowait %idle 0.18 0.00 0.28 0.75 98.79Device: tps Blk\_read/s Blk\_wrtn/s Blk\_read Blk\_wrtnsda 1.57 9.66 28.28 206298096 603960526dm-0 3.95 9.11 27.81 194502234 594018392dm-1 0.13 0.55 0.47 11789056 9936048avg-cpu: %user %nice %system %iowait %idle 0.40 0.00 0.40 0.00 99.20Device: tps Blk\_read/s Blk\_wrtn/s Blk\_read Blk\_wrtnsda 0.00 0.00 0.00 0 0dm-0 0.20 0.00 1.60 0 8dm-1 0.00 0.00 0.00 0 0avg-cpu: %user %nice %system %iowait %idle 0.60 0.00 0.60 1.40 97.41Device: tps Blk\_read/s Blk\_wrtn/s Blk\_read Blk\_wrtnsda 0.80 0.00 35.13 0 176dm-0 4.19 0.00 33.53 0 168dm-1 0.00 0.00 0.00 0 0avg-cpu: %user %nice %system %iowait %idle 0.40 0.00 2.20 40.52 56.89Device: tps Blk\_read/s Blk\_wrtn/s Blk\_read Blk\_wrtnsda 162.87 3388.42 30.34 16976 152dm-0 167.66 3390.02 30.34 16984 152dm-1 0.00 0.00 0.00 0 0

top:

top - 17:07:52 up 247 days, 5:13, 1 user, load average: 0.71, 0.22, 0.07Tasks: 91 total, 2 running, 88 sleeping, 1 stopped, 0 zombieCpu(s): 1.7% us, 5.0% sy, 0.0% ni, 0.0% id, 93.4% wa, 0.0% hi, 0.0% siMem: 515760k total, 509368k used, 6392k free, 2508k buffersSwap: 1048568k total, 44512k used, 1004056k free, 170148k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 1951 mysql 16 0 109m 14m 2652 S 3.3 2.8 175:39.95 mysqld32315 root 21 0 1042m 280m 16m S 2.7 55.6 17:46.98 java 1 root 16 0 1992 316 292 S 0.0 0.1 1:00.23 init 2 root 34 19 0 0 0 S 0.0 0.0 0:00.10 ksoftirqd/0 3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 watchdog/0

show status:

Aborted\_clients 220Aborted\_connects 22769Binlog\_cache\_disk\_use 0Binlog\_cache\_use 0Bytes\_received 1509950981Bytes\_sent 4137678502Com\_admin\_commands 0Com\_alter\_db 0Com\_alter\_table 21Com\_analyze 1Com\_backup\_table 0Com\_begin 0Com\_change\_db 980757Com\_change\_master 0Com\_check 0Com\_checksum 0Com\_commit 0Com\_create\_db 2Com\_create\_function 0Com\_create\_index 1Com\_create\_table 530Com\_dealloc\_sql 0Com\_delete 2047Com\_delete\_multi 0Com\_do 0Com\_drop\_db 1Com\_drop\_function 0Com\_drop\_index 0Com\_drop\_table 984Com\_drop\_user 0Com\_execute\_sql 0Com\_flush 11804Com\_grant 0Com\_ha\_close 0Com\_ha\_open 0Com\_ha\_read 0Com\_help 0Com\_insert 8186319Com\_insert\_select 541Com\_kill 0Com\_load 0Com\_load\_master\_data 0Com\_load\_master\_table 0Com\_lock\_tables 14313Com\_optimize 1Com\_preload\_keys 0Com\_prepare\_sql 0Com\_purge 0Com\_purge\_before\_date 0Com\_rename\_table 0Com\_repair 1Com\_replace 0Com\_replace\_select 0Com\_reset 0Com\_restore\_table 0Com\_revoke 0Com\_revoke\_all 0Com\_rollback 0Com\_savepoint 0Com\_select 1959916Com\_set\_option 2941675Com\_show\_binlog\_events 0Com\_show\_binlogs 0Com\_show\_charsets 0Com\_show\_collations 980512Com\_show\_column\_types 0Com\_show\_create\_db 0Com\_show\_create\_table 136Com\_show\_databases 156Com\_show\_errors 0Com\_show\_fields 2762Com\_show\_grants 0Com\_show\_innodb\_status 3Com\_show\_keys 514Com\_show\_logs 0Com\_show\_master\_status 0Com\_show\_ndb\_status 0Com\_show\_new\_master 0Com\_show\_open\_tables 0Com\_show\_privileges 0Com\_show\_processlist 14Com\_show\_slave\_hosts 0Com\_show\_slave\_status 0Com\_show\_status 25Com\_show\_storage\_engines 0Com\_show\_tables 299Com\_show\_variables 980520Com\_show\_warnings 0Com\_slave\_start 0Com\_slave\_stop 0Com\_stmt\_close 0Com\_stmt\_execute 0Com\_stmt\_prepare 0Com\_stmt\_reset 0Com\_stmt\_send\_long\_data 0Com\_truncate 0Com\_unlock\_tables 14314Com\_update 13845Com\_update\_multi 0Connections 1004177Created\_tmp\_disk\_tables 9280Created\_tmp\_files 29Created\_tmp\_tables 210144Delayed\_errors 0Delayed\_insert\_threads 0Delayed\_writes 0Flush\_commands 11805Handler\_commit 0Handler\_delete 1334Handler\_discover 0Handler\_read\_first 32646Handler\_read\_key 56036266Handler\_read\_next 24846980Handler\_read\_prev 0Handler\_read\_rnd 3217171Handler\_read\_rnd\_next 2056257919Handler\_rollback 2Handler\_update 8637Handler\_write 19741888Key\_blocks\_not\_flushed 0Key\_blocks\_unused 0Key\_blocks\_used 7248Key\_read\_requests 247503495Key\_reads 6715842Key\_write\_requests 34762520Key\_writes 30179666Max\_used\_connections 12Not\_flushed\_delayed\_rows 0Open\_files 100Open\_streams 0Open\_tables 52Opened\_tables 140490Qcache\_free\_blocks 0Qcache\_free\_memory 0Qcache\_hits 0Qcache\_inserts 0Qcache\_lowmem\_prunes 0Qcache\_not\_cached 0Qcache\_queries\_in\_cache 0Qcache\_total\_blocks 0Questions 17075502Rpl\_status NULLSelect\_full\_join 106827Select\_full\_range\_join 0Select\_range 12657Select\_range\_check 0Select\_scan 869758Slave\_open\_temp\_tables 0Slave\_retried\_transactions 0Slave\_running OFFSlow\_launch\_threads 1Slow\_queries 81Sort\_merge\_passes 11Sort\_range 544Sort\_rows 19170611Sort\_scan 241650Table\_locks\_immediate 10140589Table\_locks\_waited 26Threads\_cached 0Threads\_connected 2Threads\_created 1004176Threads\_running 1Uptime 21359841

Will give you outputs from local machine on Monday when Ill be back in the office.

Differences:  
local / server  
XP (yeah…I know) / Fedora Core 5  
Intel 1,8 Ghz / Intel(R) Xeon @ 2.33GHz  
1024 RAM / 512 RAM  
80 SATA / 30 GB SCSI  
Apache 1.3 / Apache 2  
MySql 4.1.9 / MySql 4.1.22

Both databases use same storage engine, and indexes are the same.  
There are few other databases on that machine, but theyre not generating too much traffic. Maybe I should mention that these are used by Tomcat.(tomcat running on port 80, Apache on 8080 for now)

---

<div class="post-metadata">

**Author:** ![stark](https://avatars.discourse-cdn.com/v4/letter/s/dbc845/32.png) [@stark](https://forums.percona.com/u/stark)\
**Post date:** [May 22, 2008, 4:58am UTC](https://forums.percona.com/t/low-performance-what-am-i-doing-wrong/759/4 "2008-05-22T04:58:11Z")

</div>

The Keybuffer-Size (key\_buffer\_size) is set to a very small value: 8MB.

Try to raise it (start with 32MB = 33554432 Bytes) and see what happens.

---

<div class="post-metadata">

**Author:** ![avatar](https://avatars.discourse-cdn.com/v4/letter/a/8c91f0/32.png) [@avatar](https://forums.percona.com/u/avatar)\
**Post date:** [May 22, 2008, 5:16am UTC](https://forums.percona.com/t/low-performance-what-am-i-doing-wrong/759/5 "2008-05-22T05:16:35Z")

</div>

Proglem solved - server restart and upgrading memory from 500MB to 2GB did it. (Probably the problem was that tomcat was eating all available mem.)

After that + chaning buffers + enabling query cache, average query execution time = 0.5 sec.

PS - it came out we are on a shared server after all…noughty hosting company. Theyre supposed to transfer us to a dedicated one soon.

Thanks for your help guys. Really apreciate it.