Need help tables locking on a huge server

ASTRAPI · September 28, 2010

giggsey · 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.

ASTRAPI · 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)

Elad Nava · 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?

AlexJ · 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.

ASTRAPI · 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....

AlexJ · 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

ASTRAPI · 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]

mat206 · 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.

ASTRAPI · 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

AlexJ · 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 o0

Ipb 2.3.x

stoo2000 · 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.

ASTRAPI · 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

Need help tables locking on a huge server

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Archived

Recently Browsing 0 members

Upcoming Events