MasterGberry

Member
Sep 4, 2012
8
0
1
cPanel Access Level
Website Owner
Hey. So I was running some intense scripts earlier that was using 9 worker threads accessing around 25k rows each and doing UPDATEs on them after getting some information from an API and I noticed my server load shot up above 2. Obviously this is some heavy data work and I need to optimize MySQL to work better.

I was hoping to get some help here on optimizing MySQL to better handle this kind of load. I'm not even sure how I can check how many queries per second and such are being done. The specs on my server are the XL 6 Server Premium XL - 1&1 Dedicated Server

I also have this screenshot of munin with MySQL InnoDB stuff and it was saying the amount of free space was critical?

6XwRTt.png

I have the following my.cnf file but I feel like it could use with some additions...

Code:
[mysqld]
set-variable = max_connections=500
log-slow-queries
safe-show-database
I am happy to answer any questions and hope to tweak the settings to handle this kind of load at nights. Thanks.
 
Last edited by a moderator:

bbrink68

Active Member
Nov 27, 2012
26
0
1
cPanel Access Level
DataCenter Provider
Yikes, max connections at 500 is pretty high... You would need a fairly large amount of memory to accommodate that.

You should download and run MySQL tuner(google). That will give you accurate recommendations

Also if you get space critical warnings you should check te MySQL were or log, might have /var filling up
 
Last edited:

MasterGberry

Member
Sep 4, 2012
8
0
1
cPanel Access Level
Website Owner
/var seems to have plenty of space, and I will take a quick look at MySQL tuner. Thanks. Will post back with results.

Code:
[email protected] [~]# perl mysqltuner.pl

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.66-cll
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 636M (Tables: 2142)
[--] Data in InnoDB tables: 35M (Tables: 80)
[--] Data in MEMORY tables: 0B (Tables: 2)
[!!] Total fragmented tables: 258

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 14d 2h 48m 16s (10M q [8.861 qps], 1M conn, TX: 16B, RX: 845M)
[--] Reads / Writes: 46% / 54%
[--] Total buffers: 34.0M global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.4G (8% of installed RAM)
[OK] Slow queries: 0% (360/10M)
[OK] Highest usage of available connections: 32% (160/500)
[OK] Key buffer size / total MyISAM indexes: 8.0M/706.2M
[OK] Key buffer hit rate: 97.0% (44M cached / 1M reads)
[!!] Query cache is disabled
[!!] Sorts requiring temporary tables: 11% (7K temp sorts / 68K sorts)
[OK] Temporary tables created on disk: 19% (70K on disk / 365K total)
[!!] Thread cache is disabled
[!!] Table cache hit rate: 0% (64 open / 301K opened)
[OK] Open file limit used: 3% (77/2K)
[OK] Table locks acquired immediately: 99% (4M immediate / 4M locks)
[!!] InnoDB data size / buffer pool: 35.5M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Set thread_cache_size to 4 as a starting value
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_size (>= 8M)
    sort_buffer_size (> 1M)
    read_rnd_buffer_size (> 256K)
    thread_cache_size (start at 4)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 35M)
Open to further suggestions/instructions...i really havent played with mysql that much before and definitely need to learn more about it.
 
Last edited: