Mysql limitations

[Anon]

Hi,

I am running php and mysql together on a machine with 700 mghz, 512MB Ram..

I am using mysql to keep daily statistics on all pages on our website.

We get around 200,000 uniques daily, so as you can imagine this has been near impossible to do so far with mysql effectively.

My question is this..
the max_connections setting for mysql,we have at 1000.. and at peak times it still cannot handle things even with this extreme measure.

Is there a way of getting more from mysql here?
Or can someone recommend a database that would work well with php scripts, but would much more capable of handling a higher number of concurrent connections..
??

Any advice would be greatly appreciated.

Thank you.

[[deleted]]

You're not up against MySQL's limitations, you are battling bad server setup.

MySQL requires at least 2mByte per connection, but you have only 512MB.

512MB/1000=500kBbyte per process that's only a quarter of the bare minimum.
Your spends all it's time swapping to disk, which means queries take a lot longer, which means the MySQL connections are in use for way too long, which looks like you are short on connections. But increasing the number of connections only makes the problem worse.

I suggest you start by adding 1000*2MB=2GByte of memory for the connections, and about 1GByte for cache, make it an even 4GByte total (to give the OS and Filesystem some space)

While you're spending money, I'd also recommend getting a SCSI or IDE RAID array to speed up disk-access.

[Anon]

I'm interested in what you're doing with the data AFTER it's stuffed into MySQL. How are you analyzing it? Have you written a stats package?

I'm struggling with site traffic analysis right now, and everything I'm looking at is going to cost $50K to $120K per year (25-30 million pageviews per month, thousands of domains to analyze). At those price levels I start thinking about building and not buying. :-)

[[deleted]]

Statis aren't that complex, they just demand a lot from your database.

If you use PostgreSQL you can do stats quite simply with a few clever queries (so clever that MySQL doesn't understand them :-) )

[[deleted]]

Vincent, do you have an example of that? I'd love to see it. I'm going to start doing some side-by-side comparison in-house, since we're building a lot of future-apps right now.

[[deleted]]

Examples of what, stats or queries that MYSQL doesn't understand? :-)

[[deleted]]

Haha. grin The clever queries you were talking about...