Dedicated Server Quits --> Too many mysql connections i believe

Hey guys

I have a huge database, hundreds of thousands of records that are large themselves.

What happens is after a while, a site that should take 1 second to load takes up to 30 seconds.

I tried optimizing database, i tried optimized my.conf, i tried buying more ram, but it still keeps maxing out. I'm really at a point of desperation where I hate working online. Pleasee give me some pointers.

Thanks

 

I would need to know the specs of the server, try contacting your host though they can always help.

 

It's Intel(R) Xeon(TM) CPU 3.00GHz, cache size: 2048 KB
1gb just for me and 2gb burstable

Current Memory Usage

total used free shared buffers cached
Mem: 12273644 12198536 75108 0 199692 5567352
-/+ buffers/cache: 6431492 5842152
Swap: 16779884 41140 16738744
Total: 29053528 12239676 16813852

 

"huge database, hundreds of thousands of records" - friggin hillarious. when you get up to a 4 gig db, then... you may say huge

so, when you say you optimized what did you do? Did you the add the appropriate indexes? Indexes make the world of diferences, please verify that all tables (except mapping tables) have primary keys, and any joined tables have foriegn keys setup with indexes setup.

 

Hey Database is over 15gb as an approximation. Everything is grabbed from database. I will make sure each has an index and all that stuff.

 

What is your max_connnections set to in your my.cnf file?

 

You NEEEEEEED a dedicated server of your own, no two ways about it, a vps doesn't have what you need here.

 

I think you are taking the wrong approach. The trick to having good database performance is simply not to use the database : cache everything that can be cached. You can cache :
- whole pages if they don't change during every load
- section of pages
- objects (PHP for example has a networked memory-cache extension that can make objects persistent)
- SQL query results

For the "too many connections" you can also do lazy-loading of connectionss (ie: you only connect to the database at the first query, not at the beginning of the script, and you disconnect as soon as you are done with your last query)

There's basically an unlimited amount of optimizations that can be done before having to buy more hardware.

 

Yes your so right.
I went ahead and just got some server analyst to do the work for me. So far its running lot better..

A vps is good enough, the cpu load wasnt being reach.

 

Maybe not today, but what about when the other sites on your vps are busy, 15gigs is too much data to serve from a database with only a portion of a server.