Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I noticed my server is consuming almost 500 MB of RAM, with 19 threads started, as shown in the following image.

MYSQL Threads top image: http://i.stack.imgur.com/5CakQ.jpg

Here is the configuration:

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.41-3ubuntu12
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 136M (Tables: 256)
[--] Data in InnoDB tables: 85M (Tables: 29)
[--] Data in MEMORY tables: 126K (Tables: 1)
[!!] Total fragmented tables: 70

-------- Performance Metrics -------------------------------------------------
[--] Up for: 8d 18h 35m 35s (8M q [11.203 qps], 509K conn, TX: 4B, RX: 934M)
[--] Reads / Writes: 56% / 44%
[--] Total buffers: 72.0M global + 2.8M per thread (100 max threads)
[OK] Maximum possible memory usage: 347.8M (33% of installed RAM)
[OK] Slow queries: 0% (74/8M)
[OK] Highest usage of available connections: 16% (16/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/58.1M
[OK] Key buffer hit rate: 99.9% (317M cached / 259K reads)
[OK] Query cache efficiency: 61.9% (3M cached / 4M selects)
[!!] Query cache prunes per day: 60422
[OK] Sorts requiring temporary tables: 0% (5 temp sorts / 328K sorts)
[!!] Joins performed without indexes: 18909
[OK] Temporary tables created on disk: 22% (32K on disk / 140K total)
[OK] Thread cache hit rate: 99% (54 created / 509K connections)
[!!] Table cache hit rate: 0% (64 open / 40K opened)
[OK] Open file limit used: 10% (112/1K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
[!!] InnoDB data size / buffer pool: 86.0M/8.0M

What's wrong or how I can reduce mysqld thread count?

Server has no special traffic, it's a vps hosting two websites with low-med traffic.

Thanks a lot.

share|improve this question

migrated from stackoverflow.com Mar 30 '11 at 20:32

This question came from our site for professional and enthusiast programmers.

    
You don't have a lot of threads: you have 16, and your max is configured to 100. –  Konerak Mar 30 '11 at 19:19
add comment

1 Answer

You might have persistence connections turn on in PHP.

But 500MB for MYSQL is not big, it's small.

There are several problems with the report

  1. Joins performed without indexes: 18909
    you need to look into to add indexes for your foreign columns
  2. Table cache hit rate: 0%
    you need to increase table cache values of mysql
  3. InnoDB data size / buffer pool: 86.0M/8.0M
    increase innodb buffer values
share|improve this answer
    
I have no presence of mysql_pconnect. –  Daniel Mar 30 '11 at 19:17
    
On my previous server had the same config and only used 300 MB of memory, and were only 10 threads. –  Daniel Mar 30 '11 at 19:18
    
Changing a view to algorith=temptable may reduce that memory usage? –  Daniel Mar 30 '11 at 19:19
    
What does show global variables like "wait%"; return? –  Pentium10 Mar 30 '11 at 20:08
    
innodb_lock_wait_timeout 50 table_lock_wait_timeout 50 wait_timeout 28800 –  Daniel Mar 30 '11 at 20:11
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.