Tex Killer Posted July 1, 2016 Posted July 1, 2016 Hello! First of all, thank all of you very much for the awesome service! I want to know how big does a database have to be to be considered "too much"? I have a database with a few million entries, and I don't know if I am allowed to try and upload it to HelioHost. If I am allowed, I would be using PostgreSQL, but it would be nice to know if there is any difference between MySQL and PostgreSQL in regards to the resources. I could port the database to MySQL if that would be better. Thanks again!
wolstech Posted July 1, 2016 Posted July 1, 2016 250MB if I remember right...I do know they count towards your 500MB quota, so it's possible that you can just use as much as the space allows. The choice of engine doesn't matter. Pg and My both have the same limits in regards to (over)use and size. I'd actually go for Pg if I had the choice, seeing MySQL has been known to crash.
Tex Killer Posted July 1, 2016 Author Posted July 1, 2016 Yeah, that is why I use PostgreSQL here on HelioHost. I'll experiment a bit and see if the space I have left is enough. Thank you very much!
Tex Killer Posted July 3, 2016 Author Posted July 3, 2016 So, I have a 213 MB SQL dump of my local database, but I don't know how I should go about uploading and importing it.phpPgAdmin doesn't seem to support the execution of compressed sql files, so am I supposed to upload the 213 MB file to it? Wouldn't that fill the empty space I have left, and prevent postgreSQL from importing the dump? Much obliged!
wolstech Posted July 3, 2016 Posted July 3, 2016 Upload, import, then immediately delete the dump file. If I remember right, it should let you go over for a short period, exactly for this reason. Just be sure to get back under 500 ASAP, staying over for too long may get you suspended. I know backups in cpanel will let you go over when you create/restore them there. Not sure if phppgadmin will or not (worst case is it fails with a disk full message). Just try and see If you get suspended for space, just let me know and I'll unsuspend you since I know why you went over.
Tex Killer Posted July 3, 2016 Author Posted July 3, 2016 Well, I would have tried if I could access phpPgAdmin. It has been offline for the last few hours, though. Is the server down or something?
wolstech Posted July 4, 2016 Posted July 4, 2016 Didn't realize you were on johnny...Johnny (or more specifically, Charlie, which hosts Johnny) suffered hardware failure several days back, so yes. No idea if or when it'll be fixed...it's acting like the hard disk died in the server. You can sign up on Stevie if you want (you won't be able to delete the Johnny account, so don't bother trying).
Tex Killer Posted July 4, 2016 Author Posted July 4, 2016 I'm on Stevie, actually.phpPgAdmin is back now, but I'm using a custom php script to load the compressed SQL dump. I had made this script back in 2014 and forgot I had it, lol.The import is almost done, and the space usage on cPanel is almost the same as it was before. Is that usage cached? How long does it take to be updated? Edit: Well, the dump is now completely imported, but the space usage on the cPanel is still the same. I don't think the database size affects that at all.phpPgAdmin is showing a size of 840 MB for the imported database. Is that stored by default in a filesystem that supports transparent compression and has it enabled?
wolstech Posted July 4, 2016 Posted July 4, 2016 I'm honestly not sure what file system we have on the server... An 840MB database should be WAY too large to host here. Cpanel sometimes takes a few hours to update its meter. If its meter hasn't jumped by now, cPanel is probably not counting pg usage.
Tex Killer Posted July 4, 2016 Author Posted July 4, 2016 I should drop the imported database, then? And try to find some other database server to host it? Do you know how reliable phpPgAdmin's estimate of the database size is? I know the SQL file itself doesn't come even close to that number.
wolstech Posted July 4, 2016 Posted July 4, 2016 Yes, I would drop that database and host it elsewhere. It's far too large to host here. I thought the DB was 213MB uncompressed. That size would be fine, but 843MB is not. Very surprised to see it actually import and not get counted as used space though... Out of curiosity, what is that database used for? I don't know of many websites other than gigantic forums that need databases that large.
Tex Killer Posted July 4, 2016 Author Posted July 4, 2016 Database dropped. The uncompressed SQL dump file was 213 MB when exported with the "COPY" option from phpPgAdmin. Using the standard "INSERT" it got to 340 MB instead, also uncompressed. Any of the two options, after compressing using GZip, took about 35 MB. The compressed "INSERT" variant was used on the import proccess. I have no idea why phpPgAdmin said the database was using 840 MB... I though the database itself would weight less than the size of the uncompressed dump files, since it doesn't have to store commands and SQL sintaxes, only the data. Even if the database were to weight 340 MB, I would have cleaned up some space and it would have been fine. That is why I was asking if the database size reported by phpPgAdmin is reliable, since it is much bigger than the dump files. This database has information about political expenses from some of my country's politics (Brazil). This information was collected from open public APIs privided by the government itself, and the database was meant to help us audit it and search for possible irregularities. I'm volunteering my help to a group that does that, and I have recently developed a script to collect this information and store it in the database. I'll try to find some other server to host the database, then. Do you know of any that is also free and would accept this ammount of data? Edit: After trying to figure out where the space was being used, I can see now that the raw data uses no more than 250 MB, but the indexes are quite big. I'll experiment a bit changing the indexes and seeing if I can reduce the database size without affecting performance too much. Maybe I'll manage to fit the database here after all. Thanks!
wolstech Posted July 4, 2016 Posted July 4, 2016 Overhead possibly, but that seems excessive. I'm not too sure what's up with this at this point. I didn't realize the 213MB was an uncompressed dump (I thought it was compressed, which would make 840 reasonable)...the dump as you said is nearly always larger than the DB produces. As for another place to host this, I'm not sure...I've always had mine run here or on a local PC.
Tex Killer Posted July 4, 2016 Author Posted July 4, 2016 I think you didn't see my "Edit" on the previous post (maybe you had the post open before I edited it). It seems the overhead comes mostly from indexes. I'll be experimenting here to see if I can drop some indexes and simplify some others to reduce space usage without affecting performance too much. Thank you very much for your help!
wolstech Posted July 5, 2016 Posted July 5, 2016 Yeah, I missed the edit. Surprising to see indexes eat so much space, but if you can drop some of the indexes to make it fit, that works
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now