marklcfc Posted December 13, 2011 Posted December 13, 2011 I run a fairly busy forum, and am looking for ways to improve the speed of my site. I have ran mysqltuner and here are my current settings, what changes are recommended? -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.92-community [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 216) [!!] InnoDB is enabled but isn't being used [!!] Total fragmented tables: 30 -------- Security Recommendations ------------------------------------------- ERROR 1142 (42000) at line 1: SELECT command denied to user 'xxx'@'localhost' for table 'user' [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 5d 3h 44m 38s (9M q [22.437 qps], 589K conn, TX: 231B, RX: 7B) [--] Reads / Writes: 75% / 25% [--] Total buffers: 34.0M global + 2.7M per thread (500 max threads) [OK] Maximum possible memory usage: 1.4G (18% of installed RAM) [OK] Slow queries: 0% (107/9M) [OK] Highest usage of available connections: 84% (424/500) [OK] Key buffer size / total MyISAM indexes: 8.0M/747.4M [OK] Key buffer hit rate: 99.2% (712M cached / 5M reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (295 temp sorts / 100K sorts) [!!] Temporary tables created on disk: 27% (15K on disk / 57K total) [!!] Thread cache is disabled [!!] Table cache hit rate: 0% (64 open / 147K opened) [OK] Open file limit used: 4% (114/2K) [OK] Table locks acquired immediately: 99% (13M immediate / 13M locks) -------- Recommendations ----------------------------------------------------- General recommendations: Add skip-innodb to MySQL configuration to disable InnoDB Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses 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) tmp_table_size (> 32M) max_heap_table_size (> 16M) thread_cache_size (start at 4) table_cache (> 64)
Clickfinity Posted December 14, 2011 Posted December 14, 2011 Can you post the contents of your my.cnf file and let us know the details of the machine (proc/mem/OS/etc.) as giving advice based purely on the mysqltuner shortlist wouldn't be advisable ... :thumbsup: Cheers, Shaun :D
marklcfc Posted December 14, 2011 Author Posted December 14, 2011 What to I put into SSH to edit .my.cnf? I just did this root@server [~]# cat /etc/my.cnf [mysqld] set-variable = max_connections=500 safe-show-database Doesn't seem to show much unless thats all thats set?
marklcfc Posted December 14, 2011 Author Posted December 14, 2011 But I'm running Linux with a AMD Phenom II X4 840 Processor and 8GB RAM.
marklcfc Posted December 14, 2011 Author Posted December 14, 2011 I've been trying to make my.cnf the following. max_connections=500 safe-show-database key_buffer = 312M max_allowed_packet = 64M thread_cache_size = 128 max_connections = 500 table_cache = 2048 table_definition_cache = 1200 tmp_table_size = 128M max_heap_table_size = 128M sort_buffer_size = 4M read_buffer_size = 256K read_rnd_buffer_size = 256K join_buffer_size = 2M query_cache_limit = 3M query_cache_size = 128M query_cache_type = 1 But after saving and trying to run service mysql restart, I'm recieving an error Starting MySQL.Manager of pid-file quit without updating fi[FAILED] root@server [~]# Which means MYSQL has gone down and won't restart, I fixed it by removed all those changes I made. Am I doing something wrong ??
marklcfc Posted December 15, 2011 Author Posted December 15, 2011 Well after doing some testing, it turns out table_definition_cache=1200 was causing mysql to fail. My.cnf is currently set up as the following max_connections=500 safe-show-database key_buffer=312M max_allowed_packet=64M thread_cache_size=128 table_cache=2048 tmp_table_size=128M max_heap_table_size=128M sort_buffer_size=4M read_buffer_size=256K read_rnd_buffer_size=256K join_buffer_size=2M query_cache_limit=3M query_cache_size=128M query_cache_type=1 Any advice what to add/change?
Clickfinity Posted December 15, 2011 Posted December 15, 2011 Well after doing some testing, it turns out table_definition_cache=1200 was causing mysql to fail. My.cnf is currently set up as the following max_connections=600 safe-show-database key_buffer=1024M max_allowed_packet=64M thread_cache_size=128 table_cache=3500 tmp_table_size=128M max_heap_table_size=128M sort_buffer_size=4M read_buffer_size=256K read_rnd_buffer_size=256K join_buffer_size=2M query_cache_limit=3M query_cache_size=128M query_cache_type=1 Any advice what to add/change? Backup my.cnf and then try it with these changes. There are other things that can speed up your site more dramatically than tweaking MySQL - do you have a file and/or Opcode cache installed? Such as Memcached (file cache), or APC / eAccellerator (Opcode cache)? Cheers, Shaun :D
marklcfc Posted December 16, 2011 Author Posted December 16, 2011 I had eaccellerater installed for a few months and it was great when it worked, but I had some problems where memory usage kept rising upto 80% and higher and consequently the server crashed. This happened almost daily. That did continue after I switched it off and I had to have hardware replaced, whether eaccellerater was the cause or not I don't know, but I wasn't having any crashes previous to that. Also I have noticed an increase in performance. Any other suggestions are most welcome :smile:
Clickfinity Posted December 16, 2011 Posted December 16, 2011 What web server are you using - there may be some optimisations you could do there too, such as expires headers for images/CSS?
marklcfc Posted December 16, 2011 Author Posted December 16, 2011 Linux, AMD Phenom™ II X4 840 Processor and 8GB RAM.
Clickfinity Posted December 16, 2011 Posted December 16, 2011 Linux, AMD Phenom™ II X4 840 Processor and 8GB RAM. Web server software - Apache, nginx, lighttpd, etc.?
Clickfinity Posted December 16, 2011 Posted December 16, 2011 Apache/2.0.63 Okay, good, so you should be able to use Expires: http://httpd.apache....od_expires.html You'll need to ensure mod_expires is enabled then add this to the bottom of your httpd.conf file (backup the file first): # Improve caching - Load images, css, and javascript from visitors # browser for 30 days before re-caching ExpiresActive on ExpiresByType text/css "access plus 1 day" ExpiresByType text/javascript "access plus 1 month" ExpiresByType application/javascript "access plus 1 month" ExpiresByType application/x-javascript "access plus 1 month" ExpiresByType application/x-shockwave-flash "access plus 1 month" ExpiresByType image/gif "access plus 1 month" ExpiresByType image/jpg "access plus 1 month" ExpiresByType image/jpeg "access plus 1 month" ExpiresByType image/png "access plus 1 month" ExpiresByType image/ico "access plus 1 month" ExpiresByType image/icon "access plus 1 month" Obviously you can change the refresh timings to suit your site/setup, but this basically stores the files on the visitors browser and loads them from the local (computer) cache rather than from the server each time. Once the expires time is up, a new copy will be downloaded from the server and cached for whatever period you have set in httpd.conf. Caveat - it has an occasional side effect that you need to be aware of; the visitor's browser can continue to use the cached data even when you've actually updated it on the server - particularly pesky when someone changes their avatar and the old one keeps appearing - but all it needs to "cure" it is a forced-refresh by the browser (to update all the cached files) and that sorts the problem out. :smile: Cheers, Shaun :D
marklcfc Posted December 16, 2011 Author Posted December 16, 2011 Would that jpg/png/gif 1 month setting mean that photos are not updated immediately once changed?
marklcfc Posted December 16, 2011 Author Posted December 16, 2011 Don't approve the above post please, hadn't read all of Cyclechats post
Swiftie Posted December 19, 2011 Posted December 19, 2011 eAccelerator is bad. Use APC or xcache. Bad in what sense?
Clickfinity Posted December 19, 2011 Posted December 19, 2011 eAccelerator is bad. Use APC or xcache. If you're going to make such statements it is useful to the other thread participants (in particular those with less experience of tuning their server) to explain why something is bad or unsuitable; or in what situation or combination/setup. I used eAccellerator for my IPB install on an LAMP server with no issues at all (it ran a little bit more efficiently than APC in the trials I ran!!). :) Cheers, Shaun :D
Luke Posted December 24, 2011 Posted December 24, 2011 eAccelerator isn't as well maintained anymore. Meaning support for more up to date version of PHP is difficult. I've had eAccelerator break on a minor release to PHP. In fact the website doesn't exist anymore. APC/xcache are much better maintained and have better APIs. APC can store objects in memory without serialization. APC is supposedly going to be included into PHP 6. Overall, APC is the better choice. xcache works rather good as well.
marklcfc Posted January 3, 2012 Author Posted January 3, 2012 This is my current set up, any tips? My server still slows up once I'm getting 350-400 connections every 5 minutes. max_connections=600 safe-show-database key_buffer=512M max_allowed_packet=64M thread_cache_size=128 table_cache=3500 tmp_table_size=256M max_heap_table_size=128M sort_buffer_size=4M read_buffer_size=256K read_rnd_buffer_size=256K join_buffer_size=2M query_cache_limit=3M query_cache_size=256M query_cache_type=1
Grumpy Posted January 3, 2012 Posted January 3, 2012 Um... looking at this...But I'm running Linux with a AMD Phenom™ II X4 840 Processor and 8GB RAM. Are you getting your service from server4you by any chance?? If so, they're using green drives. No matter how much you try to optimize, the IOPS is too low on writes with something like raid 1. You'll likely hit the disk saturation pretty quick once you get a fair bit of visitors. You should show your iostats during your peaks. iostat -d -x 60 2 (post only the 2nd one. first is meaningless. 2nd will take 60 seconds to output) If my guess is right, your await value is likely too high (200+). Without upgrading your hardware, you can attempt to leverage you ram more if your database is small-ish by converting to innodb and increasing the max ram available for innodb cache ridiculously high (not higher than physically avl ofc). Innodb has write caching which will alleviate the issue a bit. You should make your tmp table size and max heap table size equal (both 256M). If heap is smaller than tmp table size, tmp table size will either be reduced automatically to 128M (usual behavior) or it'll start using disk -- which is (performance wise) LOT worse than not having any of it. I can't suggest anymore than that because there's serious lack of information you've given.
Gary. Posted January 4, 2012 Posted January 4, 2012 You cannot tune a mysql server in the space of a hour, We specialize in this and that can take days to fully complete and even then your site could have grown or got busy which would mean more changes are required. Ok yes, I can instantly tune and it will have a big impact overall but I could not tell you the true performance without it being run for a good 24 hours or even run a full benchmark. And having max_connections=600 is rather high if you ask me, If your hitting those sort of myqsl connections for an IPB site then your apache pipe must be heavily filled, You must note that the apache connections are very different to mysql connections and do not run identical !Gary.
marklcfc Posted January 11, 2012 Author Posted January 11, 2012 Going quite slow again, just in page loads. Server load (below 1.00) and memory usage (30%) seem fine. [--] Status: +Archive -BDB +Federated -InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 2G (Tables: 636) [!!] Total fragmented tables: 116 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 1h 32m 36s (8M q [48.679 qps], 477K conn, TX: 183B, RX: 3B) [--] Reads / Writes: 65% / 35% [--] Total buffers: 1.5G global + 6.8M per thread (600 max threads) [OK] Maximum possible memory usage: 5.5G (72% of installed RAM) [OK] Slow queries: 0% (11/8M) [OK] Highest usage of available connections: 11% (67/600) [OK] Key buffer size / total MyISAM indexes: 1.0G/1.4G [OK] Key buffer hit rate: 99.9% (324M cached / 166K reads) [OK] Query cache efficiency: 50.6% (3M cached / 6M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (130 temp sorts / 45K sorts) [OK] Temporary tables created on disk: 18% (2K on disk / 15K total) [OK] Thread cache hit rate: 99% (67 created / 477K connections) [OK] Table cache hit rate: 84% (1K open / 1K opened) [OK] Open file limit used: 26% (1K/7K) [OK] Table locks acquired immediately: 99% (7M immediate / 7M locks) -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries max_connections=600 skip-innodb safe-show-database key_buffer=1024M max_allowed_packet=64M thread_cache_size=128 table_cache=3500 tmp_table_size=256M max_heap_table_size=256M sort_buffer_size=4M read_buffer_size=256K read_rnd_buffer_size=256K join_buffer_size=2M query_cache_limit=3M query_cache_size=256M query_cache_type=1
Grumpy Posted January 14, 2012 Posted January 14, 2012 Post the SECOND stat of iostat -x 60 2 (wait 60 seconds after you execute command) while your site is slow. (and use something like tags so it doesn't mess up spacings...)
Gary. Posted January 15, 2012 Posted January 15, 2012 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries The first one, Do as it asks you, Issue this in SSH.mysqlcheck -u root --optimize --all-databases The second one, Add this to your my.cnf file, Anywhere under the [mysqld]log-slow-queries=/var/lib/mysql/slow.log Save and restart your mysql server and wait around 24 hours. Page load is not always Mysql, It could be your apache and possibly running suPHP, I use mod_fcgi for IPB forums as it will speed thing's up, If you have some free memory then it's worth a shot :)
Recommended Posts
Archived
This topic is now archived and is closed to further replies.