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

1 Answer 1

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

Your Answer

 
discard

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