Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Below is a Snapshot of my Server info.

And the results of mysqltuner.pl

Some help with commands would be appreciated. I'm new to this and have gotten pretty far, but I'm having issue with Mysql using up ALOT of my server resources. We get an average of 100,000 visitors per month, and sometimes we get spikes of 10 or 15,000 visitors in a few hour period. When that happens mysql crashes. I've optimized the website as much as humanly possible, but I'm still noticing that Mysql is hogging up alot of memory.

Server Info

Processor Information
Total processors: 4

Processor #1

    Vendor
        GenuineIntel

    Name
        Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz

    Speed
        3101.000 MHz

    Cache
        6144 KB

Processor #2

    Vendor
        GenuineIntel

    Name
        Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz

    Speed
        1600.000 MHz

    Cache
        6144 KB

Processor #3

    Vendor
        GenuineIntel

    Name
        Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz

    Speed
        3101.000 MHz

    Cache
        6144 KB

Processor #4

    Vendor
        GenuineIntel

    Name
        Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz

    Speed
        3101.000 MHz

    Cache
        6144 KB


Memory Information

Memory: 16223556k/17823744k available (5221k kernel code, 1138260k absent, 461928k reserved, 7121k data, 1264k init)


System Information

Linux surface.slmserver4.com 2.6.32-358.2.1.el6.x86_64 #1 SMP Wed Mar 13 00:26:49 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux


Physical Disks

sd 2:0:0:0: [sda] 1953525168 512-byte logical blocks: (1.00 TB/931 GiB)
sd 2:0:0:0: [sda] 4096-byte physical blocks
sd 3:0:0:0: [sdb] 1953525168 512-byte logical blocks: (1.00 TB/931 GiB)
sd 2:0:0:0: [sda] Write Protect is off
sd 2:0:0:0: [sda] Mode Sense: 00 3a 00 00
sd 2:0:0:0: [sda] Write cache: enabled, read cache: enabled, doesn't support DPO or FUA
sd 3:0:0:0: [sdb] 4096-byte physical blocks
sd 3:0:0:0: [sdb] Write Protect is off
sd 3:0:0:0: [sdb] Mode Sense: 00 3a 00 00
sd 3:0:0:0: [sdb] Write cache: enabled, read cache: enabled, doesn't support DPO or FUA
sd 2:0:0:0: [sda] Attached SCSI disk
sd 3:0:0:0: [sdb] Attached SCSI disk
sd 2:0:0:0: Attached scsi generic sg0 type 0
sd 3:0:0:0: Attached scsi generic sg1 type 0


Current Memory Usage

             total       used       free     shared    buffers     cached
Mem:      16243228   13233616    3009612          0    3416016    8194784
-/+ buffers/cache:    1622816   14620412
Swap:      2047992     178948    1869044
Total:    18291220   13412564    4878656


Current Disk Usage

Filesystem            Size  Used Avail Use% Mounted on
/dev/sda3             915G   63G  806G   8% /
tmpfs                 7.8G     0  7.8G   0% /dev/shm
/dev/sda1             485M   62M  398M  14% /boot
/dev/sdb1             917G  200M  871G   1% /disk2
/usr/tmpDSK           4.0G  139M  3.7G   4% /tmp

MySqltuner.pl results:

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 3G (Tables: 1606)
[--] Data in InnoDB tables: 95M (Tables: 187)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[--] Data in MEMORY tables: 0B (Tables: 4)
[!!] Total fragmented tables: 245

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5d 0h 17m 56s (34M q [80.322 qps], 1M conn, TX: 5383B, RX: 6B)
[--] Reads / Writes: 58% / 42%
[--] Total buffers: 200.0M global + 2.8M per thread (5000 max threads)
[!!] Maximum possible memory usage: 13.6G (87% of installed RAM)
[OK] Slow queries: 0% (11K/34M)
[OK] Highest usage of available connections: 3% (186/5000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/585.6M
[OK] Key buffer hit rate: 99.9% (9B cached / 5M reads)
[OK] Query cache efficiency: 73.8% (18M cached / 25M selects)
[!!] Query cache prunes per day: 112731
[!!] Sorts requiring temporary tables: 14% (204K temp sorts / 1M sorts)
[!!] Joins performed without indexes: 84069
[!!] Temporary tables created on disk: 47% (1M on disk / 2M total)
[OK] Thread cache hit rate: 82% (195K created / 1M connections)
[!!] Table cache hit rate: 0% (400 open / 190K opened)
[OK] Open file limit used: 2% (650/24K)
[OK] Table locks acquired immediately: 99% (17M immediate / 17M locks)
[OK] InnoDB buffer pool / data size: 128.0M/95.1M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Reduce your overall MySQL memory footprint for system stability
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_cache gradually to avoid file descriptor limits
    Read this before increasing table_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    query_cache_size (> 16M)
    sort_buffer_size (> 2M)
    read_rnd_buffer_size (> 256K)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 400M)
    max_heap_table_size (> 32M)
    table_cache (> 400)

I'm not exactly sure what do do with these suggestions. Can someone help?

Here are the top results of TOP:

21787 mysql     20   0 2368m 165m 5300 S 200.5  1.0   2859:33 mysqld
 8240 indiana   20   0     0    0    0 Z  6.0  0.0   0:00.20 php <defunct>
 8274 z107      20   0  300m  22m 7568 S  2.3  0.1   0:00.07 php
  963 root       0 -20     0    0    0 D  1.0  0.0 163:05.69 loop0
  676 root      20   0     0    0    0 S  0.7  0.0  15:37.94 flush-8:0
11768 root      20   0  157m  11m 3572 D  0.7  0.1   0:21.37 bpbkar
 1246 root      20   0     0    0    0 S  0.3  0.0   2:56.88 kondemand/0
    1 root      20   0 19352 1088  872 S  0.0  0.0   0:02.16 init
share|improve this question

migrated from stackoverflow.com Mar 17 at 15:54

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

    
Sounds like you need more caching in your application if you're flaying your MySQL server that hard. –  tadman Mar 17 at 14:38
    
MySQL will use as much RAM as you allow it for things like buffers and query cache, and if allowed, it wants to store a copy of all indexes in memory. –  Marcus Adams Mar 17 at 14:43
    
What should I do? –  user3429203 Mar 17 at 15:01
1  
The recommendations given by the mysqltuner.pl script all seem reasonable (the General recommendations section, not necessarily the variables). If you have problems understanding any of them, please edit your question and specify where you're stuck. –  Carsten Mar 17 at 15:05
    
I'm not familiar with MySQL Tuner but this log line would seem to warrant more investigation [!!] Joins performed without indexes: 84069 –  Tony Mar 17 at 21:28
add comment

Your Answer

 
discard

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

Browse other questions tagged or ask your own question.