MySQL Optimize For Server
Hi,
I have Dedicated Server with E3-1230v2, 8GB RAM, 2TB HDD SATA2 (RAID 0) for Shared Hosting. I have fewer large database ex: 200Mb - 500Mb and mysql used 43% of memory
/http://i.imgur.com/iP84nHs.png
There is result of mysqltuner.pl
top - 22:27:47 up 4 days, 20:58, 1 user, load average: 3.94, 5.30, 5.06
Tasks: 256 total, 1 running, 255 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.9%us, 1.9%sy, 13.1%ni, 76.8%id, 7.2%wa, 0.0%hi, 0.2%si, 0.0%st
Mem: 8031044k total, 7318368k used, 712676k free, 112324k buffers
Swap: 0k total, 0k used, 0k free, 2616332k cachedThere is result of mysqltuner.pl
root@serv989 [~]# ./mysqltuner.pl
>> MySQLTuner 1.2.0 - Major Hayden
>> Bug reports, feature requests, and downloads at -
Hello :) I just want to point out that memory usage is not always a bad thing with Linux. There is a thread on this at: Memory Usage Higher Than Expected That being said, there are likely optimizations that can be made to your MySQL configuration. I will leave this thread open to allow for user feedback to your MySQL tuner results. Thank you. 0 -
Hi, I have problem with mysql. When i'm trying Optimize with 2 database, got same error. Please look at below with database have 3Mb and 400Mb /http://i.imgur.com/2C4ohPH.png /http://i.imgur.com/2BYS7je.png 0 -
Based on that table_cache (> 5000) table_cache=10000 [!!] InnoDB data size / buffer pool: 1.0G/256.0M innodb_buffer_pool_size (>= 1G) It seems like you got different my.cnf, and didn't restart mysql with that config Anyways correct those: read_buffer_size=128K sort_buffer_size=256K thread_cache_size=50 query_cache_size=100M innodb_buffer_pool_size=1300M This will increase RAM usage, since you need bigger innodb buffer, but will increase a speed a bit You got [OK] Maximum possible memory usage: 2.0G (25% of installed RAM) So still, not much 0 -
I am still getting 500 error. I dont know what that? i got it when server load avarage: 0.5+ not more. 0 -
its error from apache/php which handler you use ? mod_php, fastcgi ? you should check apache/php error logs 0 -
[quote="thinkbot, post: 1491202">its error from apache/php which handler you use ? mod_php, fastcgi ? you should check apache/php error logs
It from phpmyadmin when i trying optimize database. I use Litespeed with handler suPHP.0 -
so check litespeed error log 0 -
Error in Processing Request Error code: 500 Error text: Internal Error I dont found anything error on error log. Why show that on phpmyadmin when i trying optimize database? ---------- Updated ---------- I checked and see optimize database with MyISAM is normal and fine. If with InnoDB is show error like top. Although small database InnoDB still error. Not need larger 0 -
enable php display_errors and display_startup_errors, if you got 500 error you should have entry in logs anyways 0 -
I'm facing I/O high load. Look at picture /http://up9x.net/X root@serv989 [~]# ./mysqltuner.pl >> MySQLTuner 1.2.0 - Major Hayden >> 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.5.32-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 2G (Tables: 2483) [--] Data in InnoDB tables: 1G (Tables: 2116) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 1M (Tables: 349) [!!] Total fragmented tables: 285 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 54m 31s (1M q [364.504 qps], 20K conn, TX: 6B, RX: 166M) [--] Reads / Writes: 72% / 28% [--] Total buffers: 2.0G global + 1.9M per thread (300 max threads) [OK] Maximum possible memory usage: 2.6G (33% of installed RAM) [OK] Slow queries: 0% (7/1M) [OK] Highest usage of available connections: 13% (40/300) [OK] Key buffer size / total MyISAM indexes: 512.0M/520.3M [OK] Key buffer hit rate: 99.2% (5M cached / 43K reads) [OK] Query cache efficiency: 86.2% (908K cached / 1M selects) [!!] Query cache prunes per day: 852747 [OK] Sorts requiring temporary tables: 0% (72 temp sorts / 24K sorts) [!!] Joins performed without indexes: 12 [OK] Temporary tables created on disk: 19% (5K on disk / 27K total) [OK] Thread cache hit rate: 99% (40 created / 20K connections) [OK] Table cache hit rate: 98% (1K open / 1K opened) [OK] Open file limit used: 4% (1K/24K) [OK] Table locks acquired immediately: 99% (268K immediate / 268K locks) [OK] InnoDB data size / buffer pool: 1.1G/1.3G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes Variables to adjust: query_cache_size (> 100M) join_buffer_size (> 1.0M, or always use indexes with joins)
there is current my.cnf[mysqld] default-storage-engine=MyISAM innodb_file_per_table=1 local-infile=0 port=3306 socket=/var/lib/mysql/mysql.sock max_connections=300 max_user_connections=30 max_connect_errors=15 key_buffer=512M myisam_sort_buffer_size=64M join_buffer_size=1M read_buffer_size=128K sort_buffer_size=256K table_cache=10000 thread_cache_size=50 wait_timeout=300 connect_timeout=30 max_allowed_packet=16M query_cache_limit=1M query_cache_size=100M query_cache_type=1 tmp_table_size=128M max_heap_table_size=128M innodb_buffer_pool_size=1300M slow_query_log=1 delayed_insert_timeout=1 open_files_limit=24440 [mysql.server] user=mysql [safe_mysqld] err-log=mysqld.log pid-file=/var/lib/mysql/mysql.pid [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash0
Please sign in to leave a comment.
Comments
10 comments