MySQL server sizing

NetHosted Customer Joined: 29 Aug 2006 Posts: 4

Hi

I am thinking about developing a product that tracks users on a web site, logging their ip addresses, their geographic location from the ip address and some other parameters.

The logging table has 17 columns so is not too wide. Each entry has to be looked up against a geographical lookup table that currently contains 88,000 rows (though this query currently on takes 0.05 seconds and so can be run on data logging rather than reporting).

The reporting would be fairly basic, though there has to be query activity to find instances of ip addresses across customer records.

I am thinking about hosting this product rather than releasing it as stand alone code that users install themselves.

The feasibility of this will depend on the potential size of the databases (s).

Each monitored web site could generate 100,000 log entries. Over time, the application could be logging users on 10,000 sites, though this would, of course, be a gradual take up.

I am currently running a trial version of the system that has 196,000 logged entries and occupies 27Mb which include 6Mb for the index.

This raises some questions:

Is the potential database size feasible or would it be too difficult to manage, backup, run on sane levels of server hardware?

Is MySQL up to this size of database? Would PostgreSQL be a better choice?

Would more than one server be required ie run the index on one box and the other database functions on the other?

Would it be better to create a database per customer (customers may have more than one monitored website) or to run one very long table?

Obviously I'd be looking for a dedicated server for this from the set go. Would the enterprise server be up to this? or could I start with a lower hardware level and upgrade as required?

Can NetHosted handle this level of database driven application?

Are there other things I should consider at this stage? (database replication to a hot standby server may be one)

Sorry about all the questions, but I have to see if this is a feasible project.

Thanks
Chris

NetHosted Staff Joined: 22 Mar 2004 Posts: 5625

Chris,

That's quite a post, I hope I cover everything.

Anything is feasible, I wouldn't worry too much about the database size (in the sense of storage).

I'm not up with the current status of these two projects. To the best of my knowledge MySQL is still considered the faster of the two in most aspects but I'd strongly advise posting your specs to the two project's mailing lists and find out what they recommend. You're going to find the experts there obviously so they will be able to advise with more certainty than I can. I would suggest finding out the queries/second of your current setup and extrapolating that result, that should be a useful bit of info for them to help them talk feasibility/hardware.

Well you're looking at the choice between 10,000 databases or 10,000 entries in a table. It depends how you are coding your project as to which would give better performance.

For a project such as this you're looking to spend your money on disk speed, processor power and RAM. You'll notice the benefits of faster disk speed more than anything on this I would suggest (as long as you don't short change the processor/RAM clearly). I would suggest RAID10 if you're looking for performance with the added redundancy (think of it as RAID0 with the benefits of RAID1). Of course you can always opt for RAID05 or 50 if you prefer the RAID5 route.

Replication on a system that is reliant on SQL is a smart move.

We can cope with such a setup but this really is a custom job, implying custom pricing.

This project could be started on a lesser spec server(s) but the solution I suggest above is the best for the project later down the line (10,000 users).

Hope that helps,

Andrew

NetHosted Customer Joined: 29 Aug 2006 Posts: 4

Hi Andrew

Many thanks for your considered reply

NetHosted Staff Joined: 22 Mar 2004 Posts: 5625

Chris,

Your existing server sounds like a good spec to start the project with, it should be able to cope with pretty decent amounts of growth before we'd need to unleash the beast that is the setup I suggested if the project really took off.

Yes it'd be possible to migrate to a higher end solution if needs be

Thanks,

Andrew

NetHosted Customer Joined: 29 Aug 2006 Posts: 4