ASTRAPI Posted September 28, 2010 Share Posted September 28, 2010 Hello My forum is very slow (not all day) and here i have some info if you can help (I thought it was from search as i don't use sphinx yet but i think is not): 1)My server: # 2x Intel Xeon Quad 5405 # 12 GB DDR2 RAM # 4Xsas Disks (RAID 10) # 1.000 Mbit port Cpanel server. Ipb 2.3.x 100.000 users 1.000.000 posts 2)my.cnf: [mysqld] skip-innodb skip-networking skip-locking local-infile=0 set-variable=max_connections= 270 wait_timeout = 30 table_cache = 3000 thread_cache_size = 4 key_buffer_size = 512M query_cache_size = 128M query_cache_limit = 128M thread_cache_size = 40 join_buffer_size = 4M sort_buffer_size = 12M read_rnd_buffer_size = 12M max_allowed_packet = 32M max_heap_table_size = 1G tmp_table_size = 1G thread_concurrency = 16 low_priority_updates = 1 concurrent_insert = 2 3)mysqltuner.pl: MySQL version 5.0.91-community x86_64 -------- Performance Metrics ------------------------------------------------- [--] Up for: 6h 9m 13s (2M q [124.678 qps], 175K conn, TX: 143B, RX: 1B) [--] Reads / Writes: 70% / 30% [--] Total buffers: 1.6G global + 28.4M per thread (270 max threads) [OK] Maximum possible memory usage: 9.1G (77% of installed RAM) [OK] Slow queries: 0% (679/2M) [OK] Highest usage of available connections: 37% (102/270) [OK] Key buffer size / total MyISAM indexes: 512.0M/446.6M [OK] Key buffer hit rate: 100.0% (1B cached / 102K reads) [OK] Query cache efficiency: 43.7% (852K cached / 1M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (4 temp sorts / 23K sorts) [!!] Joins performed without indexes: 1516 [OK] Temporary tables created on disk: 1% (63 on disk / 3K total) [OK] Thread cache hit rate: 97% (4K created / 175K connections) [!!] Table cache hit rate: 17% (517 open / 2K opened) [OK] Open file limit used: 10% (661/6K) [OK] Table locks acquired immediately: 97% (1M immediate / 1M locks) -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Increase table_cache gradually to avoid file descriptor limits Variables to adjust: join_buffer_size (> 4.0M, or always use indexes with joins) table_cache (> 3000) 4)tuning-primer.sh: SLOW QUERIES The slow query log is NOT enabled. Current long_query_time = 10 sec. You have 679 out of 2768963 that take longer than 10 sec. to complete Your long_query_time seems to be fine Avg. qps = 124 Total Questions = 2768936 Threads Connected = 36 JOINS Current join_buffer_size = 4.00 M You have had 1531 queries where a join could not use an index properly join_buffer_size >= 4 M This is not advised You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. TABLE LOCKING Current Lock Wait ratio = 1 : 37 5)top: top - 19:49:56 up 6:12, 1 user, load average: 4.18, 4.10, 3.74 Tasks: 248 total, 3 running, 244 sleeping, 0 stopped, 1 zombie Cpu(s): 26.3%us, 8.2%sy, 0.1%ni, 64.7%id, 0.2%wa, 0.0%hi, 0.4%si, 0.0%st Mem: 12306248k total, 4511880k used, 7794368k free, 213048k buffers Swap: 15623172k total, 0k used, 15623172k free, 1718148k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 2770 mysql 15 0 862m 364m 3284 S 342.4 3.0 570:33.72 mysqld 12872 cpuser 15 0 105m 25m 5820 S 70.4 0.2 0:13.96 php5 10675 cpuser 15 0 120m 39m 5840 S 66.5 0.3 0:23.16 php5 14870 cpuser 15 0 118m 36m 5404 S 50.9 0.3 0:09.02 php5 11075 cpuser 16 0 104m 24m 5852 S 15.7 0.2 0:21.03 php5 11065 cpuser 16 0 111m 30m 5412 R 11.7 0.3 0:13.55 php5 12914 cpuser 16 0 109m 28m 5424 R 9.8 0.2 0:16.52 php5 9698 cpuser 16 0 105m 25m 5848 S 2.0 0.2 0:24.27 php5 15190 nobody 19 0 336m 8732 1988 S 2.0 0.1 0:02.24 httpd 16960 nobody 18 0 398m 7116 1980 S 2.0 0.1 0:01.07 httpd 17007 nobody 18 0 399m 8336 1988 S 2.0 0.1 0:01.05 httpd 17678 root 15 0 12872 1112 716 R 2.0 0.0 0:00.01 top 1 root 15 0 10352 696 580 S 0.0 0.0 0:02.58 init 2 root RT -5 0 0 0 S 0.0 0.0 0:00.26 migration/0 3 root 34 19 0 0 0 S 0.0 0.0 0:00.01 ksoftirqd/0 4 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/0 5 root RT -5 0 0 0 S 0.0 0.0 0:02.18 migration/1 6 root 34 19 0 0 0 S 0.0 0.0 0:00.31 ksoftirqd/1 7 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/1 8 root RT -5 0 0 0 S 0.0 0.0 0:00.04 migration/2 9 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/2 10 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/2 11 root RT -5 0 0 0 S 0.0 0.0 0:00.01 migration/3 12 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/3 13 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/3 14 root RT -5 0 0 0 S 0.0 0.0 0:00.22 migration/4 15 root 34 19 0 0 0 S 0.0 0.0 0:00.01 ksoftirqd/4 16 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/4 17 root RT -5 0 0 0 S 0.0 0.0 0:03.58 migration/5 18 root 34 19 0 0 0 S 0.0 0.0 0:00.01 ksoftirqd/5 19 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/5 20 root RT -5 0 0 0 S 0.0 0.0 0:00.01 migration/6 21 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/6 22 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/6 23 root RT -5 0 0 0 S 0.0 0.0 0:00.00 migration/7 24 root 34 19 0 0 0 S 0.0 0.0 0:00.00 ksoftirqd/7 25 root RT -5 0 0 0 S 0.0 0.0 0:00.00 watchdog/7 26 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/0 27 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/1 28 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/2 29 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/3 30 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/4 31 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/5 32 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 events/6 33 root 10 -5 0 0 0 S 0.0 0.0 0:00.05 events/7 34 root 10 -5 0 0 0 S 0.0 0.0 0:00.00 khelper 135 root 20 -5 0 0 0 S 0.0 0.0 0:00.00 kthread 146 root 10 -5 0 0 0 S 0.0 0.0 0:00.04 kblockd/0 147 root 10 -5 0 0 0 S 0.0 0.0 0:00.68 kblockd/1 148 root 10 -5 0 0 0 S 0.0 0.0 0:00.03 kblockd/2 149 root 10 -5 0 0 0 S 0.0 0.0 0:00.05 kblockd/3 150 root 10 -5 0 0 0 S 0.0 0.0 0:00.03 kblockd/4 6)httpd -v: Server version: Apache/2.2.14 (Unix) 7)php -v PHP 5.2.12 (cli) (built: Feb 6 2010 20:06:41) Zend Engine v2.2.0, Copyright © 1998-2009 Zend Technologies --> (i think i don't need this) 8)php.ini: ;;;;;;;;;;;;;;;;;;; ; Resource Limits ; ;;;;;;;;;;;;;;;;;;; max_execution_time = 30 ; Maximum execution time of each script, in seconds max_input_time = 60 ; Maximum amount of time each script may spend parsing request data memory_limit = 256M ; Maximum amount of memory a script may consume (32MB) 9)FCGI Server API CGI/FastCGI 10)RAID10? How can i test this to be sure that is raid 10 as in the datacenter info panel it says Raid 1 but i was ask for raid 10 and i am not sure if it is a typo or not... free -m : total used free shared buffers cached Mem: 12017 4551 7466 0 208 1682 -/+ buffers/cache: 2660 9357 Swap: 15257 0 15257 vmstat: procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------ r b swpd free buff cache si so bi bo in cs us sy id wa st 5 0 0 7609604 214000 1723612 0 0 8 69 47 40 26 9 65 0 0 kernel: Linux 2.6.18-194.11.4.el5 iostat: avg-cpu: %user %nice %system %iowait %steal %idle 26.33 0.08 8.72 0.23 0.00 64.64 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn sda 87.40 129.82 1095.99 2927058 24711096 sda1 1.41 6.83 35.45 153978 799392 sda2 3.32 28.34 146.51 638882 3303312 sda3 78.72 87.58 736.27 1974594 16600536 sda4 0.00 0.00 0.00 8 0 sda5 0.00 0.05 0.00 1166 0 sda6 0.09 0.16 1.49 3615 33560 sda7 3.85 6.83 176.27 154087 3974296 mysqladmin processlist : | Id | User | Host | db | Command | Time | State | Info | +--------+----------------+-----------+----------------+---------+------+--------------+------------------------------------------------------------------------------------------------------+ | 178823 | cpuser_cpusera | localhost | usercp_mydatab | Query | 27 | Locked | UPDATE ibf_posts SET post_thanks='5874,3830,7777,5641,9461,7439,9850,10659,14007,16051,10688,17912,1 | | 178867 | cpuser_cpusera | localhost | usercp_mydatab | Query | 22 | Locked | UPDATE ibf_posts SET post_thanks='6053,376,2435,533,7832,2242,6387,6939,8000,9163,13,9566,9523,11594 | | 178870 | cpuser_cpusera | localhost | usercp_mydatab | Query | 22 | Locked | INSERT INTO ibf_posts (author_id,use_sig,use_emo,ip_address,post_date,icon_id,post,author_name,topic | | 178881 | cpuser_cpusera | localhost | usercp_mydatab | Query | 21 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=144046 | | 178884 | cpuser_cpusera | localhost | usercp_mydatab | Query | 21 | Locked | UPDATE ibf_posts SET post_thanks='10252,63550,17086,24883,30518,51413,57935,11976,44185,39402,80061, | | 178885 | cpuser_cpusera | localhost | usercp_mydatab | Query | 21 | Locked | UPDATE ibf_posts SET post_thanks='5,35194,49457,5972,55552,83132,19672,65706,62578,3708,21663,27991, | | 178892 | cpuser_cpusera | localhost | usercp_mydatab | Query | 20 | Locked | UPDATE ibf_posts SET post_thanks='13250,17183,16510,981,19668,10791,3552,17943,11119,24857,1851,1185 | | 178902 | cpuser_cpusera | localhost | usercp_mydatab | Query | 18 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=100227 | | 178906 | cpuser_cpusera | localhost | usercp_mydatab | Query | 18 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=143867 | | 178910 | cpuser_cpusera | localhost | usercp_mydatab | Query | 17 | Locked | UPDATE ibf_posts SET post_thanks='72252,48529,43959,10336,20248,45977,27716,15620,63391,38085,72021, | | 178912 | cpuser_cpusera | localhost | usercp_mydatab | Query | 17 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=137560 | | 178913 | cpuser_cpusera | localhost | usercp_mydatab | Query | 17 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=60592 | | 178914 | cpuser_cpusera | localhost | usercp_mydatab | Query | 16 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=3995 | | 178916 | cpuser_cpusera | localhost | usercp_mydatab | Query | 16 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=144778 | | 178917 | cpuser_cpusera | localhost | usercp_mydatab | Query | 16 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=132086 | | 178928 | cpuser_cpusera | localhost | usercp_mydatab | Query | 15 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=143546 | | 178931 | cpuser_cpusera | localhost | usercp_mydatab | Query | 15 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=144226 | | 178941 | cpuser_cpusera | localhost | usercp_mydatab | Query | 14 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=105947 | | 178948 | cpuser_cpusera | localhost | usercp_mydatab | Query | 14 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=143514 | | 178950 | cpuser_cpusera | localhost | usercp_mydatab | Query | 13 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=82640 | | 178952 | cpuser_cpusera | localhost | usercp_mydatab | Query | 13 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=120186 | | 178954 | cpuser_cpusera | localhost | usercp_mydatab | Query | 13 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=111497 | | 178956 | cpuser_cpusera | localhost | usercp_mydatab | Query | 13 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=143683 | | 178973 | cpuser_cpusera | localhost | usercp_mydatab | Query | 12 | Locked | UPDATE ibf_topics SET poll_state=0,last_vote=0,total_votes=0 WHERE tid=146712 | | 178979 | cpuser_cpusera | localhost | usercp_mydatab | Query | 11 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=143460 | | 178983 | cpuser_cpusera | localhost | usercp_mydatab | Query | 12 | Locked | UPDATE ibf_posts SET post_thanks='69977,68409,10625,13347,96441,84288,6846,99892,31947,11173,42780,5 | | 178991 | cpuser_cpusera | localhost | usercp_mydatab | Query | 11 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=142416 | | 178992 | cpuser_cpusera | localhost | usercp_mydatab | Query | 11 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=146584 | | 178995 | cpuser_cpusera | localhost | usercp_mydatab | Query | 11 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=84227 | | 178997 | cpuser_cpusera | localhost | usercp_mydatab | Query | 11 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=143204 | | 178998 | cpuser_cpusera | localhost | usercp_mydatab | Query | 11 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=38121 | | 178999 | cpuser_cpusera | localhost | usercp_mydatab | Query | 11 | Locked | UPDATE ibf_posts SET post_thanks='13271,15219,24406,24439,128,63277,2896,26073,68937,45395,78617,305 | | 179011 | cpuser_cpusera | localhost | usercp_mydatab | Query | 10 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=62092 | | 179013 | cpuser_cpusera | localhost | usercp_mydatab | Query | 10 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=143460 | | 179014 | cpuser_cpusera | localhost | usercp_mydatab | Query | 10 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=143460 | | 179015 | cpuser_cpusera | localhost | usercp_mydatab | Query | 10 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=143460 | | 179022 | cpuser_cpusera | localhost | usercp_mydatab | Query | 10 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=111417 | | 179023 | cpuser_cpusera | localhost | usercp_mydatab | Query | 9 | Sending data | SELECT p.pid, p.queued, t.approved, t.forum_id FROM ibf_posts p LEFT JOIN ibf_t | | 179030 | cpuser_cpusera | localhost | usercp_mydatab | Query | 9 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=142214 | | 179034 | cpuser_cpusera | localhost | usercp_mydatab | Query | 9 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=48316 | | 179038 | cpuser_cpusera | localhost | usercp_mydatab | Query | 9 | Locked | UPDATE ibf_posts SET post_thanks='39232,62673,43360,61427,64233,51635,64840,61032,17842,45183,33583, | | 179039 | cpuser_cpusera | localhost | usercp_mydatab | Query | 9 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=111497 | | 179046 | cpuser_cpusera | localhost | usercp_mydatab | Query | 8 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=60592 | | 179048 | cpuser_cpusera | localhost | usercp_mydatab | Query | 8 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=117576 | | 179059 | cpuser_cpusera | localhost | usercp_mydatab | Query | 7 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=144505 | | 179061 | cpuser_cpusera | localhost | usercp_mydatab | Query | 7 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=146075 | | 179071 | cpuser_cpusera | localhost | usercp_mydatab | Query | 6 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=146424 | | 179072 | cpuser_cpusera | localhost | usercp_mydatab | Query | 5 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=59245 | | 179074 | cpuser_cpusera | localhost | usercp_mydatab | Query | 5 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=145810 | | 179076 | cpuser_cpusera | localhost | usercp_mydatab | Query | 5 | Sending data | SELECT p.pid, p.queued, t.approved, t.forum_id FROM ibf_posts p LEFT JOIN ibf_t | | 179079 | cpuser_cpusera | localhost | usercp_mydatab | Query | 5 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=139434 | | 179082 | cpuser_cpusera | localhost | usercp_mydatab | Query | 5 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=52413 | | 179090 | cpuser_cpusera | localhost | usercp_mydatab | Query | 4 | Locked | UPDATE ibf_posts SET post_thanks='75801,50690,41233,14819,16294,78522,26325,33772,69820,69178,66905, | | 179091 | cpuser_cpusera | localhost | usercp_mydatab | Query | 4 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=119486 | | 179095 | cpuser_cpusera | localhost | usercp_mydatab | Query | 4 | Locked | UPDATE ibf_posts SET post_thanks='66606,54286,88012,59033,8215,69447,59689,27902,31827,19999,7136,43 | | 179097 | cpuser_cpusera | localhost | usercp_mydatab | Query | 4 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=33619 | | 179099 | cpuser_cpusera | localhost | usercp_mydatab | Query | 4 | Locked | UPDATE ibf_posts SET post_thanks='48000,77907,30431,35142,79372,2990,35723,5944,11247,39372,58691,59 | | 179100 | cpuser_cpusera | localhost | usercp_mydatab | Query | 3 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=144849 | | 179108 | cpuser_cpusera | localhost | usercp_mydatab | Query | 3 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=38121 | | 179110 | cpuser_cpusera | localhost | usercp_mydatab | Query | 3 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=62092 | | 179112 | cpuser_cpusera | localhost | usercp_mydatab | Query | 3 | Locked | UPDATE ibf_posts SET post_thanks='5929,5821,443,9850,18105,26875,23946,19112,44342,29887,41606,3794, | | 179115 | cpuser_cpusera | localhost | usercp_mydatab | Query | 2 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=118327 | | 179119 | cpuser_cpusera | localhost | usercp_mydatab | Query | 2 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=144823 | | 179120 | cpuser_cpusera | localhost | usercp_mydatab | Query | 2 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=145810 | | 179126 | cpuser_cpusera | localhost | usercp_mydatab | Query | 1 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=146597 | | 179127 | cpuser_cpusera | localhost | usercp_mydatab | Query | 1 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=113690 | | 179135 | cpuser_cpusera | localhost | usercp_mydatab | Query | 0 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=33002 | | 179136 | cpuser_cpusera | localhost | usercp_mydatab | Query | 0 | Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=40058 | | 179137 | root | localhost | | Query | 0 | | show processlist mysqladmin processlist |wc -l : 41 netstat -an |grep :80 |wc -l : 4844 Server load : 3 to 4 Note also that i use a shoutbox with refresh every 30 seconds and flood control to 5. That's all :) And it seems that when i look on a topic with many replies inside then the forum is slow and some times i got this: 500 Internal Server Error Can anyone help me on this? I was numbers in front of every command and question so you can comment more easy. I was try in the past the xcache but i had many problems with the shoutbox and stats and topics.A user was register and the counter was not increase or on some users never saw that user and on topics when i was press back and then reload i was not able to view the topic at all...... I was thinking for the disks I/O ... I was thinking also to use Innodb but i am afraid as i was use latin1 collation and utf8 on the ACP to view them without any problems. I have also in ram only the sessions table. Many thoughts but no solution :( Thank you Link to comment Share on other sites More sharing options...
giggsey Posted September 28, 2010 Share Posted September 28, 2010 I wasn't having SQL issues as such, but I believe Apache issues with a certain large site. The power of your box is more than powerful enough. How many active users do you have online at once? You can try sphinx, but do your members do much searching? Plus, a server load of 4 is fine for a 4 processor machine, as the load figure is the number of processes waiting for CPU, averaged and some other stuff. Link to comment Share on other sites More sharing options...
ASTRAPI Posted September 28, 2010 Author Share Posted September 28, 2010 Members per minute 280. And no they are not searching a lot. It seems that the problem is that sql tables are locking... Some apache stats: Figures in parentheses refer to the 7-day period ending Sep 10 2010 at 3:00 PM. Successful requests: 9,650,494 (27,286) Average successful requests per day: 33,172 (3,897) Successful requests for pages: 76,760 (906) Average successful requests for pages per day: 263 (129) Failed requests: 445,566 (194) Redirected requests: 15,912 (139) Distinct files requested: 1,297,168 (3,320) Distinct hosts served: 64,200 (619) Data transferred: 60.15 gigabytes (233.08 megabytes) Average data transferred per day: 211.74 megabytes (33.30 megabytes) Link to comment Share on other sites More sharing options...
Elad Nava Posted September 28, 2010 Share Posted September 28, 2010 Isn't that caused by too many open connections to MySQL tables? Maybe code was edited and MySQL connection isn't being closed? Link to comment Share on other sites More sharing options...
AlexJ Posted September 28, 2010 Share Posted September 28, 2010 From your query locks, it looks like locks are coming from Thank Mod. Try to disable it and see how it goes. Link to comment Share on other sites More sharing options...
ASTRAPI Posted September 29, 2010 Author Share Posted September 29, 2010 Isn't that caused by too many open connections to MySQL tables? Maybe code was edited and MySQL connection isn't being closed? Maybe i don't know how to check it and what to do :(From your query locks, it looks like locks are coming from Thank Mod. Try to disable it and see how it goes. Yes but not for all... Locked | UPDATE ibf_topics SET views=views+1 WHERE tid=132086 | This is not i think from the thanks mod.... Link to comment Share on other sites More sharing options...
AlexJ Posted September 29, 2010 Share Posted September 29, 2010 Can you post full queries from slow query log? Edit: Few queries also show up in Google search. ibf_topics SET views=views+1 http://www.webhostingtalk.com/showthread.php?t=470752 http://forums.cpanel.net/f5/invision-power-board-taking-over-server-37192.html Link to comment Share on other sites More sharing options...
ASTRAPI Posted September 29, 2010 Author Share Posted September 29, 2010 Can you post full queries from slow query log? Not at the moment...Edit: Few queries also show up in Google search. ibf_topics SET views=views+1 How can i stop this? I am using a robots.txt :User-agent: * Crawl-Delay: 20 Disallow: /admin/ Disallow: /cache/ It doesn't work? Here is my htaccess also: RewriteEngine on <IfModule mod_expires.c> <FilesMatch "\.(gif|jpg|jpeg|png|swf|css|js|html?|xml|txt)$"> ExpiresActive On ExpiresDefault "access plus 10 years" </FilesMatch> </IfModule> ## <IfModule mod_rewrite.c> RewriteCond %{REQUEST_FILENAME} !-f RewriteCond %{REQUEST_FILENAME} !-d RewriteRule ^(.*\.(js|css))$ smartoptimizer/?$1 <IfModule mod_expires.c> RewriteCond %{REQUEST_FILENAME} -f RewriteRule ^(.*\.(js|css|html?|xml|txt))$ smartoptimizer/?$1 </IfModule> <IfModule !mod_expires.c> RewriteCond %{REQUEST_FILENAME} -f RewriteRule ^(.*\.(gif|jpg|jpeg|png|swf|css|js|html?|xml|txt))$ smartoptimizer/?$1 </IfModule> ## </IfModule> <FilesMatch "\.(gif|jpg|jpeg|png|swf|css|js|html?|xml|txt)$"> FileETag none </FilesMatch> ## RewriteEngine on <Files ~ "^\.ht"> Order allow,deny Deny from all Satisfy All </Files> <Files "*.shtml"> Allow from All </Files> ## RewriteEngine On RewriteCond %{REQUEST_METHOD} HEAD RewriteRule .* - [F] # ServerSignature Off # RewriteCond %{HTTP_REFERER} !^http://mydomain.com/.*$ [NC] RewriteCond %{HTTP_REFERER} !^http://mydomain.com$ [NC] RewriteCond %{HTTP_REFERER} !^http://www.mydomain.com/.*$ [NC] RewriteCond %{HTTP_REFERER} !^http://www.mydomain.com$ [NC] RewriteRule .*\.(jpg|jpeg|gif|png|bmp)$ - [F,NC] Link to comment Share on other sites More sharing options...
mat206 Posted September 30, 2010 Share Posted September 30, 2010 Looks like mysql is eating up a ton of CPU.. might be busy waiting because of the table locks. Try converting the ibf_topics table to innodb as the storage engine. My thoughts behind this would be since MyISAM (default) uses table level locking, if you are hitting ibf_topics with tons of concurrent update requests no other query will be able to access any other row until those update queries are finished. Innodb would allow row level locking instead, which should push the number of concurrent requests you can service a bit higher. If it doesn't work, you can always change back to MyISAM. Link to comment Share on other sites More sharing options...
ASTRAPI Posted September 30, 2010 Author Share Posted September 30, 2010 Ok how can i change this to innodb and how can i change it then back to myisam? Also what about the ibf_posts ? And how can i fix the problem with google as AlexJ mention? Thank you Link to comment Share on other sites More sharing options...
AlexJ Posted September 30, 2010 Share Posted September 30, 2010 Try this settings: thread_cache_size = 192 I don't know why you need:join_buffer_size = 4M sort_buffer_size = 12M read_rnd_buffer_size = 12M Isn't 12M too large? Also, I see you mentioned you are using 2.3.x version o0Ipb 2.3.x Link to comment Share on other sites More sharing options...
stoo2000 Posted September 30, 2010 Share Posted September 30, 2010 Simple things first ;) You seem to be saying that the problem is updating the topic views, since it's locking the table. Try changing this setting 'Update Topic Views Immediately' to no. Link to comment Share on other sites More sharing options...
ASTRAPI Posted October 2, 2010 Author Share Posted October 2, 2010 @ stoo2000 If i set it to update every 30 minutes the 29 minutes it will be fine and then it will lock again if it will run all of them.... Ok how can i change this to innodb and how can i change it then back to myisam? And how can i fix the problem with google as AlexJ mention about Google? Thank you Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.