Jump to content

Need help tables locking on a huge server


Recommended Posts

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

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

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

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

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

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

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

@ 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

Archived

This topic is now archived and is closed to further replies.

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...