Reducing MySQL resource use

[gerardg]

I've recently had a site suspended on a shared server for MySQL using too many resources. Over the past two weeks I've worked on optimizing my queries - most use 1-2 tables (3 max) and are pretty basic; Almost all are selects, a few updates, and a rare insert. I've also gone over my database making sure all columns queried/joined against have indexes. Don't know what else I can do. FYI - I'm getting 10-13K uniques/day, ~100K page views, using pconnects. The admin emailed me a process list and MySQL had 43(!) processes open. Would there be any problems on a dedicated server?

Thanks!

[[deleted]]

Hum... only 43 and he's complaining? giggle Is he running it on a 486 machine or something? 🙂

Pconnect does NOT help to keep the connections to a minimum. In fact, it will keep more connections open for longer periods of time.
Sacrifice the 'speed' of pconnect and start using normal connect again, that will leave the unused connections free for other users.

[gerardg]

Is there really any performance loss for using regular connects? I remember reading somewhere that it really wasn't noticeable.

[[deleted]]

Pconnect keeps connections open in a pool, so if you use pconnect and there is an open connection available, then you get a connection identifier from the pool.
If you use regular connect, then PHP has to actively build a new connection to the server every time. This can take several milliseconds depending on the speed and resources of your machines.
Not really a problem on low-traffic sites, but on high-traffic sites every millisecond counts.