Reduce mysql load on the server

to save memory

[jake66]

My entire site is composed of at least a few queries. Some pages more than others. Right now, mysql is the biggest memory hog of all services on the server.

My question is:
Can I lessen the amount of memory mysql absorbs? Or will the memory simply be used up more and more with the larger a database gets and I will just need to purchase a larger hosting package to accompany the usage?

Is there a way to do something to the queries or the way output is displayed to save on memory?

I am using mysql 5 with php 5.

At present I am experiencing no time-outs, lag or errors.. I would simply like to stop it before it starts though. :)

[coopster]

need to purchase a larger hosting package to accompany the usage

Are you on your own server, or shared hosting?

On most shared hosting I have found that developers do not optimize their queries. Most often this is because they truly have no idea what they are doing. I'm not talking about you, I'm talking about every other user of the shared hosting provider that is on the same box as you.

The best thing you can do is analyze your queries and monitor them for performance. Often, a mere index will turn memory usage around quite abruptly. If you still see high memory usage from your database you may want to consider a dedicated server.

[venti]

You might also look at tuning your mysql server settings so that mysql has the proper amount of resources to be performing the tasks. The mysqlperformanceblog is a good resource.

And as coopster said, always always always tune your queries. Look for using temp, using where, using filesort, etc... Also, ensure you have indexes that are being used on each particular query.

Last but not least, look into caching solutions like memcache, query cache, and array caches for the queries that can't be tuned any more but that are used frequently.

[jake66]

I am on a VPS and I have indexes on every table in the database, the tables that are called the most via the php scripts.

Whereabouts do I monitor the sql usage and using temp?

Just about every query has a "where" in it, some of them scripts that I wrote myself.

My host suggested using SSH to read usage, but I am not entirely sure on how to read the results.. it looks like a bunch of running programs with several different numbers and without a lot of explanation to me.

Also, ensure you have indexes that are being used on each particular query.

Is this something I have to add to the query?
For indexes within the database, I open phpmyadmin and set indexes to ID#s (if the largest script that uses that table has a where id='id#')