Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
Dhil_ Posted January 29, 2013 Author Posted January 29, 2013 I have successfully replaced my configuration file with #config date 01/28/13 [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking myisam-recover = BACKUP bind-address = 127.0.0.1 concurrent_insert = 2 key_buffer = 512M max_allowed_packet = 8M thread_cache_size = 16 max_heap_table_size = 64M tmp_table_size = 64M key_buffer_size = 512M thread_cache_size = 16 query_cache_type = 1 query_cache_size = 128M log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 10 expire_logs_days = 2 max_binlog_size = 50M skip-bdb [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ log-error=/var/log/mysql/error.log I belive i will have to wait 24 hours before using suggestions from tuner and primer script. in the meantime, i was wondering if i should look at my apache optimization as well :smile: output of current mysqltuner -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.51a-24+lenny5-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 427) [--] Data in InnoDB tables: 13M (Tables: 608) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 45 -------- Security Recommendations ------------------------------------------- -------- Performance Metrics ------------------------------------------------- [--] Up for: 2m 31s (9K q [64.861 qps], 1K conn, TX: 117M, RX: 1M) [--] Reads / Writes: 67% / 33% [--] Total buffers: 714.0M global + 2.7M per thread (100 max threads) [OK] Maximum possible memory usage: 989.0M (16% of installed RAM) [OK] Slow queries: 0% (0/9K) [OK] Highest usage of available connections: 10% (10/100) [OK] Key buffer size / total MyISAM indexes: 512.0M/367.0M [OK] Key buffer hit rate: 95.2% (190K cached / 9K reads) [OK] Query cache efficiency: 58.0% (3K cached / 6K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 109 sorts) [OK] Temporary tables created on disk: 9% (21 on disk / 217 total) [OK] Thread cache hit rate: 99% (10 created / 1K connections) [!!] Table cache hit rate: 1% (64 open / 4K opened) [OK] Open file limit used: 11% (117/1K) [OK] Table locks acquired immediately: 99% (5K immediate / 5K locks) [!!] InnoDB data size / buffer pool: 13.3M/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Increase table_cache gradually to avoid file descriptor limits Variables to adjust: table_cache (> 64) innodb_buffer_pool_size (>= 13M)
GreenLinks Posted January 29, 2013 Posted January 29, 2013 If you are having memory problems , drop apache and use nginx instead.
thompsone Posted January 29, 2013 Posted January 29, 2013 Ok good. I'm glad your database is no longer using 173% of your resources. You should notice a tremendous improvement just at this point. I would recommend that you remove random queries from your installed Invision products. These can and do include things like get random images from the gallery, get random files from the file downloads, get random posts from the board software etc. Without knowing what you have installed I have to leave it to you to determine what you want to start minimizing. Random returns work fine for smaller boards but once the database starts getting fairly large a single random request can start compounding into problems rather quickly. Of course there are other optimization items like turning off the ability for guests to search or use the online members, etc. and I would highly recommend you look at ways to make your board software run more efficiently. There is a thread around here about optimizing your boards and maybe someone can link you to it or you can search for it. It has a wealth of information in it. Of course there are some finer tuning items you can do with MYSQL but you have to remember that the out of the box distro of MYSQL was built for performance. Changing too much or changing things without benchmarking is foolish. You cannot just assume that because you give something a bigger value in my.cnf that it will have the affect you want. In fact, it is usually the opposite that occurs because it is more harmful from a performance standpoint to make MYSQL believe it has more resources than it does. Ideally with MYSQL you want it to run in memory as much as possible having to read or write to disk constantly is what causes most issues. And so it is with most software. Don't get to hung up on mysqltuner they are just guides. I did some testing with my log files yesterday after reading a post so I restarted mysqld so generally my reports are more complete, but just so you can compare here is what mine looks like. [root@trinity home]# perl tuner.pl >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> >> 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.29-cll [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 1032) [--] Data in InnoDB tables: 27M (Tables: 4) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [--] Data in MEMORY tables: 249K (Tables: 4) [!!] Total fragmented tables: 29 -------- Performance Metrics ------------------------------------------------- [--] Up for: 18h 43m 21s (3M q [56.585 qps], 156K conn, TX: 351B, RX: 24B) [--] Reads / Writes: 63% / 37% [--] Total buffers: 888.0M global + 2.8M per thread (151 max threads) [OK] Maximum possible memory usage: 1.3G (16% of installed RAM) [OK] Slow queries: 0% (13/3M) [OK] Highest usage of available connections: 37% (56/151) [OK] Key buffer size / total MyISAM indexes: 540.0M/498.1M [OK] Key buffer hit rate: 99.7% (261M cached / 725K reads) [OK] Query cache efficiency: 68.8% (1M cached / 2M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 1% (875 temp sorts / 68K sorts) [OK] Temporary tables created on disk: 2% (4K on disk / 163K total) [OK] Thread cache hit rate: 99% (151 created / 156K connections) [!!] Table cache hit rate: 6% (400 open / 5K opened) [OK] Open file limit used: 61% (634/1K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) [OK] InnoDB data size / buffer pool: 27.6M/128.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Increase table_cache gradually to avoid file descriptor limits Variables to adjust: table_cache (> 400) This on a machine that's had solid uptime of a little over 2.5 years, 905 days to be more specific. Anyway, there are some really talented people that know a heck of a lot more than I do and generally folks don't like to "optimize" other people's equipment hands off sight unseen and for free. There's to many variables and so many to tune for performance depending on what is needed by the output in the logs and how your machine is performing. Just throwing in a config file here and there might help but ultimately you're going to have to watch performance, load, disk usage, memory usage, etc. Keeping your numbers low and your uptime high is the key to success. Now for optimizing apache. That's a whole new beast that requires removing unused objects and tuning forks and child processes. Again unless there's a need the stock versions usually run best. The exceptions are the googles, facebooks, and those people who enjoy squeezing every last Byte out of their memory. Please let us know how things turn out for you. It will be nice to hear.
Dhil_ Posted January 29, 2013 Author Posted January 29, 2013 First of all, thankyou for all the suggestion. I have been running in performance mode the past few days as the board didnt work pretty well. I have toogled it off now, so lets see how things turn up. I enjoying tweaking and learning new things about mysql and apache .. so it was awesome to get some feedback from hardcore people like you guys. current Top and its off peak right now.. in a couple of hours i would be able to see if the configuration helped. top - 10:37:05 up 12 days, 17:52, 1 user, load average: 1.81, 1.81, 1.87 Tasks: 153 total, 4 running, 149 sleeping, 0 stopped, 0 zombie Cpu(s): 68.4%us, 0.7%sy, 0.0%ni, 30.7%id, 0.0%wa, 0.1%hi, 0.1%si, 0.0%st Mem: 6123388k total, 4860388k used, 1263000k free, 227776k buffers Swap: 1060248k total, 12376k used, 1047872k free, 3556776k cached So NginX is better than apache for IPB ?
GreenLinks Posted January 29, 2013 Posted January 29, 2013 Nginx is 100 times better then memory hog Apache ( crapache ) but configuration of nginx is more complex then Apache.
Dhil_ Posted January 29, 2013 Author Posted January 29, 2013 Nginx is 100 times better then memory hog Apache ( crapache ) but configuration of nginx is more complex then Apache. does IPB team recommend nginx rather than apache ? and how about lightpd
GreenLinks Posted January 29, 2013 Posted January 29, 2013 does IPB team recommend nginx rather than apache ? and how about lightpd I can't say IPB team suggests one or another as i am not within that team. Lighttpd is better then apache also as it uses way less resources then apache. However last i tested lighttpd , it had a serious memory_leak issue. If you know your way around and a good sysadmin , that is not a major issue as you can close that hole with some advanced techniques but if you are not advanced you should use nginx over lighttpd.
thompsone Posted January 29, 2013 Posted January 29, 2013 I have a decent size community and a handful of customers on my machine all running various forums and sites. I run everything on a single box (apache, mysql, etc) my machine is old and is due for an upgrade. But I don't see much in the way CPU demand that I'm seeing on your box. Loads can get into the 3s and low 4s for me at ultra peak times. But I wonder what else you have running that's chewing up so much processor time. I think you need to concentrate on getting more into memory which could include running eaccelerator, and checking to see how you're caching files etc. Your board might be busy but your downtime is at 68% load? I'd start with your process list, services that can be disabled, and a good old fashioned hog hunt. I'm running old Xeon L5410 @ 2.33GHz processors with 8Gigs of Memory, nothing compared to your more modern processor and memory. top - 05:59:41 up 905 days, 21:41, 2 users, load average: 0.96, 0.97, 1.00 Tasks: 205 total, 2 running, 203 sleeping, 0 stopped, 0 zombie Cpu(s): 5.5%us, 0.4%sy, 0.0%ni, 88.3%id, 5.7%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 8178072k total, 8042472k used, 135600k free, 400036k buffers Swap: 2104504k total, 19400k used, 2085104k free, 4903124k cached
Dhil_ Posted January 29, 2013 Author Posted January 29, 2013 I have been optimzing apache and mysql over the years.. now mysql is back to basic settings, maybe i should consider the same for Apache settings.. i am running apache prefork # prefork MPM # StartServers: number of server processes to start # MinSpareServers: minimum number of server processes which are kept spare # MaxSpareServers: maximum number of server processes which are kept spare # MaxClients: maximum number of server processes allowed to start # MaxRequestsPerChild: maximum number of requests a server process serves <IfModule mpm_prefork_module> StartServers 20 MinSpareServers 20 MaxSpareServers 40 # ThreadsPerChild 100 # StartThreads 100 # ThreadLimit 100 ServerLimit 256 MaxClients 256 MaxRequestsPerChild 1000 </IfModule> <VirtualHost *:80> ServerAdmin admin@domain.com DocumentRoot /home/www/web1/html # SuexecUserGroup web1 web1 # ScriptAlias /cgi-bin/ /home/www/web1/html/cgi-bin/ ErrorLog ${APACHE_LOG_DIR}/domain.com.error.log LogLevel warn CustomLog ${APACHE_LOG_DIR}/domain.com.access.log combined <Directory /> Options FollowSymLinks AllowOverride None </Directory> <Directory /home/www/web1/html> Options -Indexes FollowSymLinks MultiViews AllowOverride All Order allow,deny allow from all DirectoryIndex index.php index.html </Directory> #php_admin_value open_basedir /home/www/web1/html/:/home/www/web1/phptmp/:/home/www/web1/files/:/home/www/web1/at$ #php_admin_value file_uploads 1 #php_admin_value upload_tmp_dir /home/www/web1/phptmp/ #php_admin_flag safe_mode off #php_admin_value open_basedir /home/www/web1/html/ </VirtualHost>
AlexJ Posted January 29, 2013 Posted January 29, 2013 Make this higher:innodb_buffer_pool_size = 64M table_cache = 1200 Performance would suck a bit if table cache hit rate is very low for MyISAM. Your IO seems high. Do iotop and check which process is using most of the I/O and optimize accordingly. Try to use Memory or InnoDB for session tables. That would also speed up things.I don't follow this, can you explain? "Just keep tuning things based on their description until you receive best I/O performance for innodb_log_file_size"What value in my.cnf do you think the daemon is check summing against the log file that would halt the daemon?I'm learning a lot, thank you.If you have small innodb_log_file_size value it will increase your I/O on busy DB. If you use InnoDB and have lots of writes, increasing innodb_log_file_size will improve performance but it will also make crash recovery slower. So always tweak based on your needs.
Dhil_ Posted January 29, 2013 Author Posted January 29, 2013 Thankyou alexJ, I have inserted the variables.. i have also isntalled iotop.. 5639 be/4 mysql 0.00 B/s 35.40 K/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v~ --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 5065 be/4 mysql 0.00 B/s 31.47 K/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v~ --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 5082 be/4 mysql 0.00 B/s 35.40 K/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v~ --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 4965 be/4 mysql 0.00 B/s 11.80 K/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v~ --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 5009 be/4 mysql 0.00 B/s 31.47 K/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v~ --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 5012 be/4 mysql 0.00 B/s 39.33 K/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v~ --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 5086 be/4 mysql 0.00 B/s 35.40 K/s 0.00 % 0.00 % mysqld --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/v~ --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock 1 be/4 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % init [2] 2 be/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [kthreadd] 3 rt/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/0] 4 be/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/0] 5 rt/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [watchdog/0] 6 rt/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/1] 7 be/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/1] 8 rt/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [watchdog/1] 9 rt/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/2] 10 be/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/2] 11 rt/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [watchdog/2] 12 rt/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [migration/3] 13 be/3 root 0.00 B/s 0.00 B/s 0.00 % 0.00 % [ksoftirqd/3]
thompsone Posted January 29, 2013 Posted January 29, 2013 It's your machine and ultimately you're going to have to make the final decisions how you want it to run. I offer you some documentation on table_cache from Peter Zaitsev's blog: http://www.mysqlperformanceblog.com/2009/11/16/table_cache-negative-scalability/ I mention this because I purposely detuned your table_cache back to default for performance. Now unless I'm mistaken and something has changed with 3.4.x (I don't have it yet) IPB doesn't use INNODB. You weren't specific about other installed software that is using INNODB so I assumed, maybe poorly, that it was something like Plesk or CPanel which is rarely used. So my thinking was your INNODB tables being rarely used and not having a large enough buffer pool for a single read won't matter. However, changing that value won't hurt either. The one thing it will do is stop mysqltuner from complaining about the INNODB buffer size. That said I'm going to bow out and get some work done. Take care all, it's been enjoyable.
Grumpy Posted January 29, 2013 Posted January 29, 2013 huzzah for people who are confused! AlexJ's post partly refers to thompsone's post of top but makes recommendation to Dhil_'s setting. Ironically somewhat still applies. lol @Dhil_, your %wa is virtually at 0. You likely have no real gain from optimizing mysql. You can stop wasting time. ^^ But you do have quite high CPU usage. You should optimize PHP (which I'm assuming is your main resource eater... which I don't know with certainty because you didn't paste all of your top... I have a peeve about this. Half the people only post stats at top and the other hand only post processes at the bottom. sigh... ). Install things like apc or xcache if you haven't already. You don't have any memory issue, so apache -> nginx + something won't be as big of a factor but should still be a notable factor. If you have, only way to get better performance is to get a better CPU without digging into code.
Grumpy Posted January 29, 2013 Posted January 29, 2013 I have a decent size community and a handful of customers on my machine all running various forums and sites. I run everything on a single box (apache, mysql, etc) my machine is old and is due for an upgrade. But I don't see much in the way CPU demand that I'm seeing on your box. Loads can get into the 3s and low 4s for me at ultra peak times. But I wonder what else you have running that's chewing up so much processor time. I think you need to concentrate on getting more into memory which could include running eaccelerator, and checking to see how you're caching files etc. Your board might be busy but your downtime is at 68% load? I'd start with your process list, services that can be disabled, and a good old fashioned hog hunt. I'm running old Xeon L5410 @ 2.33GHz processors with 8Gigs of Memory, nothing compared to your more modern processor and memory. top - 05:59:41 up 905 days, 21:41, 2 users, load average: 0.96, 0.97, 1.00 Tasks: 205 total, 2 running, 203 sleeping, 0 stopped, 0 zombie Cpu(s): 5.5%us, 0.4%sy, 0.0%ni, 88.3%id, 5.7%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 8178072k total, 8042472k used, 135600k free, 400036k buffers Swap: 2104504k total, 19400k used, 2085104k free, 4903124k cached Your stats show a real problem. But at the lack of any other info (other than somewhat useless mysql tuner), I can't say what. Your %wa is > %us. It means your disk is doing nothing but thrashing.
thompsone Posted January 29, 2013 Posted January 29, 2013 Your stats show a real problem. But at the lack of any other info (other than somewhat useless mysql tuner), I can't say what. Your %wa is > %us. It means your disk is doing nothing but thrashing. Thanks. I did notice that about three days ago. Good eye and reinforcements are on their way. I have a failing disk in my array. You're not grumpy just wise, consider a name change.
AlexJ Posted January 29, 2013 Posted January 29, 2013 @Dhil_, your %wa is virtually at 0. You likely have no real gain from optimizing mysql. You can stop wasting time. ^^Little incorrect. His first mysql settings where horrible and using above 100% of available system memory. Max connection set to 150and all connections where getting used up where I believe his site was reaching super slow or hang state.Once he optimizes mysql he will have more available system memory to use it for apache. :smile:
GreenLinks Posted January 29, 2013 Posted January 29, 2013 Additionally table locks create heavy CPU usage where as memory usage can still be on normal levels. I highly suspect table locks as he has MyISAM tables
Grumpy Posted January 30, 2013 Posted January 30, 2013 Little incorrect. His first mysql settings where horrible and using above 100% of available system memory. Max connection set to 150and all connections where getting used up where I believe his site was reaching super slow or hang state. Once he optimizes mysql he will have more available system memory to use it for apache. :smile: I'll admit, I was too lazy to read the first page. xD
AlexJ Posted January 30, 2013 Posted January 30, 2013 Additionally table locks create heavy CPU usage where as memory usage can still be on normal levels. I highly suspect table locks as he has MyISAM tablesIf table locks increases doesn't it goes under slow queries? So if he monitors slow queries with time of 5sec and if all is good then MySQL is not hogging CPU anymore? Out of curiosity.
GreenLinks Posted January 30, 2013 Posted January 30, 2013 If table locks increases doesn't it goes under slow queries? So if he monitors slow queries with time of 5sec and if all is good then MySQL is not hogging CPU anymore? Out of curiosity. It should list within slow queries
Dhil_ Posted January 30, 2013 Author Posted January 30, 2013 I am pretty confused with the discussion. I do have APC stats : Free: 98.5 MBytes (49.2%) Hits: 39828680 (100.0%) Used: 101.5 MBytes (50.8%) Misses: 889 (0.0%) Grumpy wrote my php might be compiled wrong or have some wrong settings... can u please suggest some way to fetch some infos to you. my full Top top - 09:16:45 up 13 days, 16:32, 1 user, load average: 1.94, 1.97, 1.97 Tasks: 160 total, 5 running, 155 sleeping, 0 stopped, 0 zombie Cpu(s): 48.8%us, 0.6%sy, 0.0%ni, 50.2%id, 0.3%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 6123388k total, 5890728k used, 232660k free, 253788k buffers Swap: 1060248k total, 25688k used, 1034560k free, 4470612k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 15099 www-data 20 0 447m 62m 41m R 100 1.1 3:24.79 apache2 15322 www-data 20 0 447m 59m 38m R 96 1.0 2:56.76 apache2 15100 www-data 20 0 447m 71m 49m R 55 1.2 3:36.53 apache2 15094 www-data 20 0 447m 65m 44m S 8 1.1 3:26.07 apache2 16050 www-data 20 0 441m 56m 39m R 8 0.9 2:21.95 apache2 4941 mysql 20 0 944m 267m 6016 S 6 4.5 34:40.13 mysqld 1 root 20 0 8352 344 312 S 0 0.0 0:02.02 init 2 root 15 -5 0 0 0 S 0 0.0 0:00.00 kthreadd 3 root RT -5 0 0 0 S 0 0.0 0:00.04 migration/0 4 root 15 -5 0 0 0 S 0 0.0 0:00.25 ksoftirqd/0 5 root RT -5 0 0 0 S 0 0.0 0:00.00 watchdog/0 6 root RT -5 0 0 0 S 0 0.0 0:00.11 migration/1 7 root 15 -5 0 0 0 S 0 0.0 0:00.01 ksoftirqd/1 8 root RT -5 0 0 0 S 0 0.0 0:00.00 watchdog/1 9 root RT -5 0 0 0 S 0 0.0 0:00.10 migration/2 10 root 15 -5 0 0 0 S 0 0.0 0:00.00 ksoftirqd/2 11 root RT -5 0 0 0 S 0 0.0 0:00.00 watchdog/2 12 root RT -5 0 0 0 S 0 0.0 0:00.06 migration/3 13 root 15 -5 0 0 0 S 0 0.0 0:00.06 ksoftirqd/3 14 root RT -5 0 0 0 S 0 0.0 0:00.00 watchdog/3 15 root 15 -5 0 0 0 S 0 0.0 0:00.60 events/0 16 root 15 -5 0 0 0 S 0 0.0 0:01.37 events/1 17 root 15 -5 0 0 0 S 0 0.0 0:00.26 events/2 18 root 15 -5 0 0 0 S 0 0.0 0:00.22 events/3 19 root 15 -5 0 0 0 S 0 0.0 0:00.00 cpuset 20 root 15 -5 0 0 0 S 0 0.0 0:00.00 khelper 25 root 15 -5 0 0 0 S 0 0.0 0:00.00 async/mgr 110 root 15 -5 0 0 0 S 0 0.0 0:00.00 kblockd/0 111 root 15 -5 0 0 0 S 0 0.0 0:00.00 kblockd/1 112 root 15 -5 0 0 0 S 0 0.0 0:00.00 kblockd/2 113 root 15 -5 0 0 0 S 0 0.0 0:00.38 kblockd/3 115 root 15 -5 0 0 0 S 0 0.0 0:00.00 kacpid 116 root 15 -5 0 0 0 S 0 0.0 0:00.00 kacpi_notify 117 root 15 -5 0 0 0 S 0 0.0 0:00.00 kacpi_hotplug 186 root 15 -5 0 0 0 S 0 0.0 0:00.00 ata/0 187 root 15 -5 0 0 0 S 0 0.0 0:00.00 ata/1 188 root 15 -5 0 0 0 S 0 0.0 0:00.00 ata/2 189 root 15 -5 0 0 0 S 0 0.0 0:00.00 ata/3 190 root 15 -5 0 0 0 S 0 0.0 0:00.00 ata_aux 191 root 15 -5 0 0 0 S 0 0.0 0:00.00 ksuspend_usbd 196 root 15 -5 0 0 0 S 0 0.0 0:00.00 khubd 199 root 15 -5 0 0 0 S 0 0.0 0:00.00 kseriod 267 root 20 0 0 0 0 S 0 0.0 0:00.00 khungtaskd 270 root 15 -5 0 0 0 S 0 0.0 0:37.77 kswapd0 271 root 15 -5 0 0 0 S 0 0.0 0:00.00 aio/0 272 root 15 -5 0 0 0 S 0 0.0 0:00.00 aio/1 273 root 15 -5 0 0 0 S 0 0.0 0:00.00 aio/2 274 root 15 -5 0 0 0 S 0 0.0 0:00.00 aio/3 275 root 15 -5 0 0 0 S 0 0.0 0:00.00 crypto/0 276 root 15 -5 0 0 0 S 0 0.0 0:00.00 crypto/1 277 root 15 -5 0 0 0 S 0 0.0 0:00.00 crypto/2 278 root 15 -5 0 0 0 S 0 0.0 0:00.00 crypto/3 417 root 15 -5 0 0 0 S 0 0.0 0:00.00 scsi_eh_0 419 root 15 -5 0 0 0 S 0 0.0 0:00.00 scsi_eh_1 I have to admit, the default my.cnf is performing much better than all my optimizations the past many years :tongue: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.51a-24+lenny5-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 427) [--] Data in InnoDB tables: 13M (Tables: 608) [--] Data in MEMORY tables: 0B (Tables: 2) [!!] Total fragmented tables: 47 -------- Performance Metrics ------------------------------------------------- [--] Up for: 20h 33m 46s (4M q [58.923 qps], 394K conn, TX: 66B, RX: 858M) [--] Reads / Writes: 53% / 47% [--] Total buffers: 714.0M global + 2.7M per thread (100 max threads) [OK] Maximum possible memory usage: 989.0M (16% of installed RAM) [OK] Slow queries: 0% (1/4M) [!!] Highest connection usage: 86% (86/100) [OK] Key buffer size / total MyISAM indexes: 512.0M/367.8M [OK] Key buffer hit rate: 99.9% (97M cached / 133K reads) [OK] Query cache efficiency: 73.5% (2M cached / 2M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 2% (1K temp sorts / 56K sorts) [OK] Temporary tables created on disk: 14% (11K on disk / 78K total) [OK] Thread cache hit rate: 99% (307 created / 394K connections) [!!] Table cache hit rate: 0% (64 open / 24K opened) [OK] Open file limit used: 11% (116/1K) [OK] Table locks acquired immediately: 99% (2M immediate / 2M locks) [!!] InnoDB data size / buffer pool: 13.3M/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Reduce or eliminate persistent connections to reduce connection usage Increase table_cache gradually to avoid file descriptor limits Variables to adjust: max_connections (> 100) wait_timeout (< 28800) interactive_timeout (< 28800) table_cache (> 64) innodb_buffer_pool_size (>= 13M)
thompsone Posted January 30, 2013 Posted January 30, 2013 Remove the modifications you've made to apache. Restart the service. Wait. Watch performance.
AlexJ Posted January 30, 2013 Posted January 30, 2013 wait_timeout = 90interactive_timeout = 90connect_timeout = 30table_cache = 1200That would keep mysql connection in control and table cache if optimized to have above 40% hit rate increases performance by far good amount afaik. For some reason with IPB i always tend to get around 30% hit rate. I hardly got above it.
Dhil_ Posted January 30, 2013 Author Posted January 30, 2013 Did you ever check your slow query logs ? Yes ipb support staff checked these things before sending me to this forum wait_timeout = 90 interactive_timeout = 90 connect_timeout = 30 table_cache = 1200 That would keep mysql connection in control and table cache if optimized to have above 40% hit rate increases performance by far good amount afaik. For some reason with IPB i always tend to get around 30% hit rate. I hardly got above it. I will try with the solutions and post result once again thankyou
Recommended Posts
Archived
This topic is now archived and is closed to further replies.