Dump restoration causing server overload?!

[Mikalee]

Hello,

I have been TRYING to transfer to JagPC for a some time now and am still having problems I haven't been able to solve.

When trying to restore the dump file on JagPC (which the support team has been kind enough to do for me using Shell on MySql), the server load jumps through the roof and they have to stop the restoration. I ended up breaking up the dump file into smaller pieces of about 50MB each (about 550mb in total) and it seems all but 2 of the (largest) tables restore fine.

When trying to resore either of these tables (which are the phpbb_posts_text and phpbb_search_wordmatch tables), which are now broken into files of only 25MB, the server load still goes crazy and it must be stopped after only about 10000 records (of 110000).

Does anyone have ANY idea why this is the case and what I can possibly do to fix it? I am getting very very desperate here as our old server is a piece of garbage with terrible service! (www.ipowerweb.com in case anyone is thinking about it - don't!).

[Vin DSL]

Well, there are a couple of things you can do. I'll make it quick, since I'm short on time...

Install phpMyAdmin in your web space...

[Mikalee]

Well, there are a couple of things you can do. I'll make it quick, since I'm short on time...

Install phpMyAdmin in your web space...

Not knowing much about it, is it difficult to configure? Or can I use all the default configurations with the install?

And, wouldn't I run into the same problem even if I did it myself?

Thanks,
Mike

[Mikalee]

Alright, I've just gone ahead and installed phpMyAdmin. I'm trying to find the php.ini file to change the maximum upload size since I read this in the documentation:

[1.16] I cannot upload big dump files (memory, http or timeout problems).
The first things to check (or ask your host provider to check) are the values of upload_max_filesize, memory_limit and post_max_size in the php.ini configuration file.
All of these three settings limit the maximum size of data that can be submitted and handled by PHP. One user also said that post_max_size and memory_limit need to be larger than upload_max_filesize.
If you get a timeout problem, look at the $cfg['UploadDir'] feature.

Excuse my ignorance, but could someone tell me where that file should be? I can't seem to find it anywhere.

Thanks!
Mike

[Mikalee]

After doing a little more research, I basically found out that I don't have access to php.ini, but there may be ways of editing some of the variables using htaccess (I think). So, would anyone know if those variables mentioned above can be changed and how? If not, what's really the point of installing your own phpMyAdmin?

Thanks for any help!

[jason]

Just use this in your .htaccess file:

php_value upload_max_filesize <bytes>

replacing <bytes> with the number of bytes you want the maximum to be. For example, for a 50MB max you would use 52428800 (50MB * 1024Kb/Mb * 1024 bytes/Kb).

Personally, though, I'm not sure that phpMyAdmin is going to help you much if you are having trouble with teh file from the shell, since phpMyAdmin is making the same call to MySQL as the command line tools are. If anything, I think phpMyAdmin will be more of a bottleneck because it has the added overhead of an HTTP-based file upload to deal with as well.

--Jason

[Mikalee]

Just use this in your .htaccess file:

php_value upload_max_filesize <bytes>

replacing <bytes> with the number of bytes you want the maximum to be. For example, for a 50MB max you would use 52428800 (50MB * 1024Kb/Mb * 1024 bytes/Kb).

Where should the .htaccess file be? my root WEB (www/public_html) folder or just the root folder.

Personally, though, I'm not sure that phpMyAdmin is going to help you much if you are having trouble with teh file from the shell, since phpMyAdmin is making the same call to MySQL as the command line tools are. If anything, I think phpMyAdmin will be more of a bottleneck because it has the added overhead of an HTTP-based file upload to deal with as well.

--Jason

Do you have any other comments or suggestions on the original problem (high server load)? Do you have any ideas why it would be doing that and how I could avoid it?

Thanks!
Mike

[Gwaihir]

Where should the .htaccess file be? my root WEB (www/public_html) folder or just the root folder.

It needs to be in a web folder, the one with phpMyAdmin or any one above it in the file three. So, your root web folder will do, but placing it in phpMyAdmin's base folder seems more logical.

That said: I agree with Jason in thinking it will cause the same issues. Perhaps you can find more usefull info on www.mysql.com? Perhaps MySQL has a built in option to limit a certain query's system load to a user definable maximum?

[Vin DSL]

LoL! Every time I view this thread, I should have been somewhere else 5 minutes ago. So, here's another short one...

I assume you are timing out when you try to restore a dump. That can be taken care of by installing phpMyAdmin in your webspace, which you've done, and designating upload and save directories.

Once you have everything setup, just upload the dump file via FTP, or whatever, and restore it from the server, instead of from your PC.

I hope that makes more sense. Gotta run. BBL...

[Mikalee]

Thank you Gwaihir and Vin.

I did everything you mentioned and got it working - technically. However, phpMyAdmin is still crashing after only about 2mb of restoring (whether through the uploaded server directory or my pc). This is crazy, I don't know what to do! I'm desperate enough to have to spend 131231289981237312 hours to split my 550mb dump file to 2mb pieces!!! But, seeing as I would like to have some kind of normal life and actually die before I'm 14980740865438 years old (which is what that many hours would work to! lol), I need a better solution.

Unfortunately for me, I can't find one - anywhere!

Thanks for your help though, I'm sure they'd work for others.

Mike

[Mikalee]

Lovely, now it's crashing at about 700k! Ahhhh

[cornnuts]

Are you on the Ion server?

[Mikalee]

Saturn, I believe (how can I check?)

[Connie]

Saturn, I believe (how can I check?)

I believe you can find that information on the main page in CP.

[Ron]

Mikalee,
The info provided to you so far has been to increase your timeout limit, but from what you were saying, I'm not sure that is your problem. Might be, but doesn't seem like it to me, since it's crashing after only such a short amount of data.

I found this link over at phpBB... maybe it can be of some help to you.
http://www.phpbb.com/kb/article.php?article_id=266
Good luck,
-Ron.