dsheroh has asked for the wisdom of the Perl Monks concerning the following question:

I've run into a... situation... with a web app running off a hosting company's servers. Said hosting company provides users with 100 MySQL databases, each of which can be up to 100M in size. As new features have been added, this app's appetite for data has also increased significantly, to the point of now having one table which is around 120M. This is obviously an issue.

The hosting company has been contacted and they have stated that they provide no options for obtaining larger databases short of migrating everything to a different server.

So, in the interest of avoiding an unneccesary migration, is there any existing module (or well-documented technique that I could turn into a module) which would allow me to combine several of those distinct 100M databases into a virtual n00M database without needing to deal with managing a flock of database handles myself and figuring out which data goes where? (Note that simply putting each table into a separate database is not sufficient, as there's already one >100M table and another is likely to break the 100M barrier within the next several months.)

Replies are listed 'Best First'.
Re: Virtual/distributed database with DBI
by dragonchild (Archbishop) on Nov 12, 2007 at 19:05 UTC
    The most obvious solution is to find a new ISP. There are dozens of ISPs that do what you need with minimal cost.

    As for what you're talking about ... nothing has been written that I know that will partition data across databases. Furthermore, this is a bad idea because you lose the ability to run queries across the partitions. In other words, find a proper solution.

    My criteria for good software:
    1. Does it work?
    2. Can someone else come in, make a change, and be reasonably certain no bugs were introduced?
[reply]
      Yeah, that's pretty much what I expected, but there are others involved who are much-less-than-excited over the idea of migrating to another server, whether with the same provider or not. So I had to check...

      Thanks for the confirmation.

[reply]
Re: Virtual/distributed database with DBI
by moritz (Cardinal) on Nov 12, 2007 at 19:25 UTC
    I haven't heard of an existing solution, and I don't think it's worth the effort.

    Go to a decent hosting company instead, and get a database that fits your needs. That's much cheaper than staying with a crappy solution that will break every now and then and will cost too much developer's time.

[reply]
Re: Virtual/distributed database with DBI
by Cody Pendant (Prior) on Nov 12, 2007 at 21:45 UTC
    What everyone else says about the hosting company.

    Why 100MB? What's special about that number? Why should it even be a problem? MySQL databases can be gigabytes in size without encountering problems. 100MB sounds like a completely arbitrary number.


    Nobody says perl looks like line-noise any more
    kids today don't know what line-noise IS ...
[reply]

      And everyone knows no ISP or hosting provider ever sets any sort of arbitrary resource limits on anything . . . (sorry, didn't mean to drip that much sarcasm all over there)

      Hosting companies are contracting to provide a certain service. In order to do so they have to budget X worth of cpu/disk/network bandwidth/sysadmin overhead for each customer. While MySQL in and of itself on a dedicated machine serving a single customer may be perfectly capable of handling much larger databases, caveat emptor if a hosting company's offering that level of service on a shared hosting platform.

      When things slow to a crawl or run out of space because they've oversubscribed their infrastructure you'll wish you'd gone with a more clued hosting company that either charged you more (because they're spending more on beefier hardware with fewer customers per each) or with a dedicated server that you're not sharing with everyone else and their dog's multi-gigabyte databases.

      We're looking for a Perl and Database Developer for Corporate Investments Group.

[reply]
        That is all absolutely true, however... this particular case involves a hosting company that offers 100 x 100M databases, with no option to reallocate them as, say, 10 x 1G databases (or even 1 x 1G database, if you want to make the argument that it's easier to find a box with 100M available than one with 1G). Even if you're running them from a managed dedicated server. AFAICT, the only way they'll let you get a database over 100M is if you're on an unmanaged dedicated server, in which case they charge the same rates to provide less service.

        So, yeah, I'd say it's completely arbitrary...

[reply]
Re: Virtual/distributed database with DBI
by perlfan (Parson) on Nov 13, 2007 at 14:55 UTC
    It will introduce some latency, but if you can't move your app off this server why not move you database to a server that gives you space to work? Your db does /not/ have to be on the same box as your app these days ;).

    On the otherhand, think of creative solutions around it - is your db size limited to 100mb, or is that your quota? Low volume sites can get by with sqlite, which is simply a local file in your home directory.

    That said, I've never thought about how to manage distributed dbs. Assuming there is a single, centralized app using them, the problem is actually a pretty simple one since you really only have to maintain state knowledge at a single point. The trouble comes in when you want to start doing fancy relational things with the data, since you are in effect breaking the link among related tables that containing them in the same db provides.

    A brute force solution would have you keeping only a single table (or part of a single table) on each db (provided they don't need more than 100mb each). This would render the "relational" part of the RDBMS mostly ineffective, unless you included small look up tables on each of these single table dbs (or had a single db for each look up table). In the end, trying to make things relational would force you to reimplement a lot of this functionality in your app (poorly).
[reply]

Back to Seekers of Perl Wisdom