Jump to content

Database locks into sleep command


Recommended Posts

although it was not reported not being able to reproduce it as a bug but ive now tested on 3 servers, two servers that has installed by the datacenter, softlayer.com and singlehop and my main server located in lithuania.
The server from softlayer and singlehop are using a default mysql and apache installation so nothing has been customized, no apache mod installed or anything

those 3 servers use mysql 5.5.14, apache 2.2.20

I did a fresh ipb and ipdownload install on those two servers but i still get the sleep issue.

When someone is downloading a file from ipdownload, the entire forum will stop responding. Only when you finished/stopped downloading will the forum respond again

According to the mysql processlist:
kill 484457 database localhost forum Sleep 30 --- ---

appears the connection to the database have not been processing successfully and stays locked until the download has been finished

Link to comment
Share on other sites

can anyone try this out on mysql 5.5?
also the whole database lock it causing mysql consuming the cpu 100% this is only when theres more than 5 people downloading from ipdownload


before that i had vbulletin with downloadsII and i had people downloading 100 files at same time and mysql never went anythingbelow 50%

Link to comment
Share on other sites

once ipdownloads starts the transfer, its pretty much done.... you issue is with mysql/apache/server imo not the board.

to test this give 5 people static links to a file and see what happens when they download, see if it produces the same results

Link to comment
Share on other sites

wow this makes me regret going with ipb, first ipb staff said this is a serve config, ok ill accept that, i hired one sysadmin to check, and he said its a bad mysql coding and nothing he can do with the server as its a coding issue, ok then i asked a second sysadmin and he said the exact same thing, this is not a server issue as theres nothing wrong with the server config along with mysql.
ok then i thought ipb staff said since theres nothing wrong with their own ipbmarket i then asked a THIRD SYSADMIN and he said the same thing, there was nothing in the server config that actually prevent the sleep command.
Asking 3 different sysadmin and they all said this is a coding issue.

Really what should i do now?????

Link to comment
Share on other sites

Typically system admins are not php/mysql coders.... so you will need to take what some of them say with a grain of salt when it comes to coding of php/mysql.

I worked with you on this for a long time and proved to you the issue all points to mysql 5.5 and your configuration of it. The answer and solution is to use mysql 5.1 at this point and be done with it.

Edit your config in custombuild and change it to 5.1 and rebuild it.

Link to comment
Share on other sites

not really, another member steve moore who has 5.1 also has his forum frozen whenever someone download from ipdownload. so highly doubt thats a mysql verion issue

http://community.invisionpower.com/tracker/issue-33209-forum-database-put-into-sleep-command-during-download/

Link to comment
Share on other sites

We use this and some of the clients do not have any issues,

Have you tried the skip-locking method, Another option maybe that the mysql is having to many connections and could cause it to stall, Could be something deepeer but end of the day it's only PHP what send the command to the mysql, It could be an issue with IPB but that could be sorted by a few server config changes.

To be perfect honest it's impossible to say much futher without knowing what processes are running and whats being said by /var/log/messages.

Edit: Non of the config's has timestamp on do they ?

Link to comment
Share on other sites

the error logs shows nothing relevent to this issue no

nope my forum still freezes after restarting mysql, heres my cnf
[mysqld]
#skip-networking
#bind-address=127.0.0.1
max_allowed_packet=256M
join_buffer_size=32M
tmp_table_size=128M
wait_timeout = 3600
skip-external-locking

key_buffer_size=512M
character-set-server=utf8
max_connections=900

key_buffer = 36M
myisam_sort_buffer_size = 64M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 8000
thread_cache_size = 512
interactive_timeout = 25
max_connect_errors = 10
query_cache_limit = 8M
query_cache_size = 128M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
range_alloc_block_size = 4096
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
#max_write_lock_count = 4

[mysqld_safe]
open_files_limit = 20192

[mysqldump]
#quick
max_allowed_packet = 64M

[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

Link to comment
Share on other sites

Now, Take no offence but thats some big *** configuration for mysql, I maybe wrong and you have some whopping server but, If your not a webhost then you should have nothing like that.

Give me some information about your server.

OS
CPU Cores,
Memory
Disc size / Speed.

....................

Anyway change and remove what I say below, But backup the file you currently have

Once done save and restart your mysql server.

[mysqld]


skip-innodb


max_allowed_packet=16M


join_buffer_size=2M


tmp_table_size=128M


wait_timeout = 100


skip-locking


key_buffer_size=512M


character-set-server=utf8


max_connections=900


key_buffer = 128M


myisam_sort_buffer_size = 64M


read_buffer_size = 2M


sort_buffer_size = 1M


table_cache = 1024


thread_cache_size = 128


interactive_timeout = 100


max_connect_errors = 20


query_cache_limit = 2M


query_cache_size = 128M


query_cache_type = 1


query_prealloc_size = 262144


query_alloc_block_size = 65536


range_alloc_block_size = 4096


transaction_alloc_block_size = 8192


transaction_prealloc_size = 4096


default-storage-engine = MyISAM



[mysqld_safe]


open_files_limit = 4086



[myisamchk]


key_buffer = 64M


sort_buffer = 64M


read_buffer = 16M


write_buffer = 16M




The main ones I changed are:

max_allowed_packet=256M 16M is just fine, Not sure why someone sends packets that big is beyond me.
interactive_timeout = 25 , Need minimum 70 for IPB.
join_buffer_size=32M Leave this set to 2, Not sure if you made a typo error there.
key_buffer = 36M 64MB for every 1GB of RAM, I cannot see your dedicated box only having 512MB ?
skip-external-locking , skip-locking is what your after.
#bind-address=127.0.0.1 It's commented also thats default anyway so not needed.

If you need futher help and my suggestions did not fix it then do not hesitate to contact me. We can fix this guarenteed, Only thing we cannot do is hardware :smile:
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...