# mysql takin' too much memory

**URL:** <https://forums.percona.com/t/mysql-takin-too-much-memory/477>\
**Category:** Other MySQL® Questions\
**Created:** [October 4, 2007, 2:57pm UTC](https://forums.percona.com/t/mysql-takin-too-much-memory/477 "2007-10-04T14:57:06Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![Leinad](https://avatars.discourse-cdn.com/v4/letter/l/67e7ee/32.png) [@Leinad](https://forums.percona.com/u/Leinad)\
**Post date:** [October 4, 2007, 2:57pm UTC](https://forums.percona.com/t/mysql-takin-too-much-memory/477/1 "2007-10-04T14:57:06Z")

</div>

I have this game ([vaperida.com](http://vaperida.com)) which usually has btween 30 and 100 users online at everytimes.

our host is telling us that mysql is takin’ way too much resources.

I ran a show status in mysql and here are the results

can you tell me if you see anything abnormal there?? cause I have no idea 😕 but the only way to learn is to ask

Variable\_name Value  
Aborted\_clients 2  
Aborted\_connects 4  
Binlog\_cache\_disk\_use 0  
Binlog\_cache\_use 0  
Bytes\_received 2585754887  
Bytes\_sent 268500406169  
Com\_admin\_commands 3302054  
Com\_alter\_db 0  
Com\_alter\_table 5  
Com\_analyze 0  
Com\_backup\_table 0  
Com\_begin 0  
Com\_change\_db 3363237  
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 6  
Com\_dealloc\_sql 0  
Com\_delete 9787  
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 18  
Com\_grant 18  
Com\_ha\_close 0  
Com\_ha\_open 0  
Com\_ha\_read 0  
Com\_help 0  
Com\_insert 75255  
Com\_insert\_select 0  
Com\_kill 0  
Com\_load 0  
Com\_load\_master\_data 0  
Com\_load\_master\_table 0  
Com\_lock\_tables 525  
Com\_optimize 6  
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 9  
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 7259376  
Com\_set\_option 2334  
Com\_show\_binlog\_events 0  
Com\_show\_binlogs 7  
Com\_show\_charsets 155  
Com\_show\_collations 155  
Com\_show\_column\_types 0  
Com\_show\_create\_db 0  
Com\_show\_create\_table 48  
Com\_show\_databases 165  
Com\_show\_errors 0  
Com\_show\_fields 155  
Com\_show\_grants 60  
Com\_show\_innodb\_status 0  
Com\_show\_keys 59  
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 1685  
Com\_show\_slave\_hosts 0  
Com\_show\_slave\_status 0  
Com\_show\_status 2  
Com\_show\_storage\_engines 11  
Com\_show\_tables 403  
Com\_show\_variables 343  
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 7  
Com\_unlock\_tables 525  
Com\_update 1914903  
Com\_update\_multi 23  
Connections 6228  
Variable\_name Value  
Created\_tmp\_disk\_tables 3526  
Created\_tmp\_files 4  
Created\_tmp\_tables 590018  
Delayed\_errors 0  
Delayed\_insert\_threads 0  
Delayed\_writes 0  
Flush\_commands 1  
Handler\_commit 0  
Handler\_delete 68743  
Handler\_discover 0  
Handler\_read\_first 8599  
Handler\_read\_key 5373778  
Handler\_read\_next 4359485  
Handler\_read\_prev 88398917  
Handler\_read\_rnd 502955474  
Handler\_read\_rnd\_next 23347149768  
Handler\_rollback 30  
Handler\_update 985894  
Handler\_write 9221844  
Key\_blocks\_not\_flushed 0  
Key\_blocks\_unused 322129  
Key\_blocks\_used 1230  
Key\_read\_requests 22489243  
Key\_reads 1940  
Key\_write\_requests 189011  
Key\_writes 92434  
Max\_used\_connections 152  
Not\_flushed\_delayed\_rows 0  
Open\_files 168  
Open\_streams 0  
Open\_tables 102  
Opened\_tables 170  
Qcache\_free\_blocks 1823  
Qcache\_free\_memory 31220400  
Qcache\_hits 35556149  
Qcache\_inserts 7194111  
Qcache\_lowmem\_prunes 10403  
Qcache\_not\_cached 65093  
Qcache\_queries\_in\_cache 1743  
Qcache\_total\_blocks 5369  
Questions 48198716  
Rpl\_status NULL  
Select\_full\_join 0  
Select\_full\_range\_join 0  
Select\_range 0  
Select\_range\_check 0  
Select\_scan 3779581  
Slave\_open\_temp\_tables 0  
Slave\_retried\_transactions 0  
Slave\_running OFF  
Slow\_launch\_threads 0  
Slow\_queries 0  
Sort\_merge\_passes 0  
Sort\_range 0  
Sort\_rows 503136508  
Sort\_scan 399973  
Table\_locks\_immediate 9143713  
Table\_locks\_waited 121768  
Threads\_cached 6  
Threads\_connected 96  
Threads\_created 996  
Threads\_running 1  
Uptime 503350

regards )

---

<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:** [October 4, 2007, 4:54pm UTC](https://forums.percona.com/t/mysql-takin-too-much-memory/477/2 "2007-10-04T16:54:48Z")

</div>

| [B]Leinad wrote on Thu, 04 October 2007 22:27[/B] |
| 

our host is telling us that mysql is takin’ way too much resources.

 |

And what does that mean exactly? CPU heavy? Consumes a lot of RAM? That they don't want a heavy site running on their server?

Because it looks like you have a popular site there.  
From what I can gather from the status variables you are averaging  
100 queries per second.  
Which is a pretty decent figure.  
But I can’t see that anything is directly wrong from those variables.

---

<div class="post-metadata">

**Author:** ![Leinad](https://avatars.discourse-cdn.com/v4/letter/l/67e7ee/32.png) [@Leinad](https://forums.percona.com/u/Leinad)\
**Post date:** [October 6, 2007, 12:56am UTC](https://forums.percona.com/t/mysql-takin-too-much-memory/477/3 "2007-10-06T00:56:04Z")

</div>

first…thank you 😃

and it means it consumes a lot of RAM (from their point of view)  
I honestly think they didn’t expect our site to be that popular. So I’m askin’ you, the experts in mysql performance

(our server consumes like %40 of the total RAM right now (4gb))

regards…anything else? (really appreciated)

---

<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:** [October 6, 2007, 3:55am UTC](https://forums.percona.com/t/mysql-takin-too-much-memory/477/4 "2007-10-06T03:55:17Z")

</div>

Can you post the output from SHOW VARIABLES instead?  
That way we can see if any variable is strangely configured.

---

<div class="post-metadata">

**Author:** ![Leinad](https://avatars.discourse-cdn.com/v4/letter/l/67e7ee/32.png) [@Leinad](https://forums.percona.com/u/Leinad)\
**Post date:** [October 6, 2007, 4:49am UTC](https://forums.percona.com/t/mysql-takin-too-much-memory/477/5 "2007-10-06T04:49:11Z")

</div>

there ya go

Variable\_name Valueback\_log 50basedir /binlog\_cache\_size 32768bulk\_insert\_buffer\_size 8388608character\_set\_client utf8character\_set\_connection utf8character\_set\_database latin1character\_set\_results utf8character\_set\_server latin1character\_set\_system utf8character\_sets\_dir /usr/share/mysql/charsets/collation\_connection utf8\_unicode\_cicollation\_database latin1\_swedish\_cicollation\_server latin1\_swedish\_ciconcurrent\_insert ONconnect\_timeout 5datadir /var/lib/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 1000expire\_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 1024have\_archive NOhave\_bdb NOhave\_blackhole\_engine NOhave\_compress YEShave\_crypt YEShave\_csv NOhave\_example\_engine NOhave\_geometry YEShave\_innodb YEShave\_isam NOhave\_merge\_engine YEShave\_ndbcluster NOhave\_openssl NOhave\_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\_data\_file\_path ibdata1:10M:autoextendinnodb\_data\_home\_dir innodb\_fast\_shutdown ONinnodb\_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\_table\_locks ONinnodb\_thread\_concurrency 8interactive\_timeout 28800join\_buffer\_size 131072key\_buffer\_size 402653184key\_cache\_age\_threshold 300key\_cache\_block\_size 1024key\_cache\_division\_limit 100language /usr/share/mysql/english/large\_files\_support ONlc\_time\_names en\_USlicense GPLlocal\_infile ONlocked\_in\_memory OFFlog OFFlog\_bin OFFlog\_error log\_slave\_updates OFFlog\_slow\_queries OFFlog\_update OFFlog\_warnings 1long\_query\_time 10low\_priority\_updates OFFVariable\_name Valuelower\_case\_file\_system OFFlower\_case\_table\_names 0max\_allowed\_packet 1047552max\_binlog\_cache\_size 18446744073709551615max\_binlog\_size 1073741824max\_connect\_errors 10max\_connections 750max\_delayed\_threads 20max\_error\_count 64max\_heap\_table\_size

---

<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:** [October 6, 2007, 9:32am UTC](https://forums.percona.com/t/mysql-takin-too-much-memory/477/6 "2007-10-06T09:32:19Z")

</div>

I think you missed about 50% of the output because there is a lot more. 😉

But from the part I got I can see that the key\_buffer\_size is set to 400MB, so if your database is big enough (you never wrote how big it actually is in MB) that is how much MySQL will use for the index cache.

Then you have the per connection buffers lite sort buffer etc which can if you have a lot of concurrent connections open be quite large.

---

<div class="post-metadata">

**Author:** ![scoundrel](https://avatars.discourse-cdn.com/v4/letter/s/3ec8ea/32.png) [@scoundrel](https://forums.percona.com/u/scoundrel)\
**Post date:** [October 6, 2007, 9:56pm UTC](https://forums.percona.com/t/mysql-takin-too-much-memory/477/7 "2007-10-06T21:56:24Z")

</div>

| [B]sterin wrote on Sat, 06 October 2007 11:02[/B] |
| you never wrote how big it actually is in MB |

Yep, and how much memory do you have on your box, and what table types do you use, and is this box is dedicated to mysql, or it is shared between mysql and apache, etc, etc… Give us mroe information, please.