Break up very large MySQL files

BMW · 08-05-2009, 11:08 PM

I was trying to build a very advanced database for my league. As expected, the MySQL export files quickly ballooned to massive size.

First, the issue was 16MB. The players_at_bat_batting_stats.mysql.sql file grew past 16MB, and my hosting service couldn't import it any longer. There was a file size cap. Also, trying to import too much too fast would often throw random errors because MySQL server was too busy.

So I spent a week or so to write a script to import the files in bite-size segments. Finally, I was successful, and I got past that 16MB limit.

Then comes the 70MB limit!

PHP also bugs out when it runs out of memory. It seems that the amount of data that it can load into memory at once is about 70MB before it throws a fatal error.

I realize that not may people are looking to import files this size. But the few who want to do so may not have the Enterprise-level web hosting that is needed to actually import all this stuff without capping out the server.

Can a feature be added that would simply chop up the files to a certain amount of bytes as they come out of OOTP?

i.e. A 25 MB players_at_bat_batting_stats.mysql.sql could be broken out into:

10MB players_at_bat_batting_stats.mysql.sql_1
10MB players_at_bat_batting_stats.mysql.sql_2
5MB players_at_bat_batting_stats.mysql.sql_3

fhomess · 08-06-2009, 01:34 AM

I've been running into this with my StatsLab mod. Would really be nice if you could optionally create an incremental update for online leagues, too.

BMW · 08-06-2009, 09:38 AM

Essentially, just after a single season worth of data from a normal sized MLB-style league, it's possible to have a file like the "at_bats" exceed a size where it can no longer be imported because of host limitations.

I do consider my host to be pretty decent, so I'm not going to fault them for capping file manipulation at 70MB.

Another Mike D · 08-06-2009, 09:52 AM

That's pretty massive, I don't see why it can't be broken up in to parts. As seasons run, it's just going to balloon to epic proportions. Not sure how this would work into the game, maybe under an advanced tab or something.

jestor92 · 08-07-2009, 01:33 PM

I agree with this dude. It would be a good idea.

wellsoliver · 08-11-2009, 05:30 PM

Most certainly agree- this would be an ideal thing to fix.

08-05-2009, 11:08 PM	#1 (permalink)
BMW All Star Starter Join Date: May 2003 Location: New Jersey Posts: 1,471 Thanks: 125 Thanked 493x in 204 posts	Break up very large MySQL files I was trying to build a very advanced database for my league. As expected, the MySQL export files quickly ballooned to massive size. First, the issue was 16MB. The players_at_bat_batting_stats.mysql.sql file grew past 16MB, and my hosting service couldn't import it any longer. There was a file size cap. Also, trying to import too much too fast would often throw random errors because MySQL server was too busy. So I spent a week or so to write a script to import the files in bite-size segments. Finally, I was successful, and I got past that 16MB limit. Then comes the 70MB limit! PHP also bugs out when it runs out of memory. It seems that the amount of data that it can load into memory at once is about 70MB before it throws a fatal error. I realize that not may people are looking to import files this size. But the few who want to do so may not have the Enterprise-level web hosting that is needed to actually import all this stuff without capping out the server. Can a feature be added that would simply chop up the files to a certain amount of bytes as they come out of OOTP? i.e. A 25 MB players_at_bat_batting_stats.mysql.sql could be broken out into: 10MB players_at_bat_batting_stats.mysql.sql_1 10MB players_at_bat_batting_stats.mysql.sql_2 5MB players_at_bat_batting_stats.mysql.sql_3

08-06-2009, 01:34 AM	#2 (permalink)
fhomess Hall Of Famer Join Date: Nov 2002 Posts: 2,749 Thanks: 15 Thanked 275x in 125 posts	I've been running into this with my StatsLab mod. Would really be nice if you could optionally create an incremental update for online leagues, too. __________________ StatsLab11 - PHP/MySQL based utilities for Online Leagues Other Mods: 19th Century: Schedules, FaceGen BBCards: 1887 Allen & Ginter, 1934 Goudey, 1988 Score, 1996 fhomess, 2005 fhomess FaceGen: 1960-Pres MLB, 32 Colleges, Backgrounds PEBA - Connecticut Nutmeggers

08-06-2009, 09:52 AM	#4 (permalink)
Another Mike D All Star Reserve Join Date: Feb 2002 Location: atl Posts: 832 Thanks: 0 Thanked 2x in 2 posts	That's pretty massive, I don't see why it can't be broken up in to parts. As seasons run, it's just going to balloon to epic proportions. Not sure how this would work into the game, maybe under an advanced tab or something. __________________ San Diego Padres NexGen Baseball League Cleveland Indians United Baseball League Co-commissioner of United Baseball League

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

08-06-2009, 09:38 AM	#3 (permalink)
BMW All Star Starter Join Date: May 2003 Location: New Jersey Posts: 1,471 Thanks: 125 Thanked 493x in 204 posts	Essentially, just after a single season worth of data from a normal sized MLB-style league, it's possible to have a file like the "at_bats" exceed a size where it can no longer be imported because of host limitations. I do consider my host to be pretty decent, so I'm not going to fault them for capping file manipulation at 70MB.

08-07-2009, 01:33 PM	#5 (permalink)
jestor92 Bat Boy Join Date: Mar 2004 Posts: 10 Thanks: 0 Thanked 0x in 0 posts	I agree with this dude. It would be a good idea.

08-11-2009, 05:30 PM	#6 (permalink)
wellsoliver Major Leagues Join Date: Aug 2009 Location: San Diego, CA Posts: 320 Thanks: 15 Thanked 19x in 15 posts	Most certainly agree- this would be an ideal thing to fix.