Database locks into sleep command

[sasiko]

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

[Rhett]

I can't duplicate this running mysql 5.1.x I would look to 5.5 as your issue perhaps... I sent you a link to test as well.

[sasiko]

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%

[Rhett]

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

[sasiko]

yeah i did mention that too, i gave them the link and the board loads normal.. so its an issue related with mysql connection not proccessing successfully,

[Rhett]

It would be interesting to know if anyone else here is using ip.downloads with mysql 5.5 and using large files that could try and duplicate this.

[sasiko]

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?????

[Rhett]

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.

[sasiko]

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/

[Rhett]

Your choice..... It will be interesting if you ever track it down.

[Gary.]

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 ?

[sasiko]

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

[Gary.]

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:

[sasiko]

seems Gary cant seem to fix this issue :(

heres something interesting, i use firefox and chrome, both logged in under the same username, i download a file from chrome, and the forum stop responding at all, while on firefox it loads just fine.