Re: Database Optimization

nospamm...@gmail.com wrote:
>
> I'm looking for a way to improve dramatically the performance of my PHP
> application. The application is getting slow as it is taking more load.
> It is performing a very high number of queries to a database, and I
> believe that this is taking up most of the ressources.
>
> I'm trying to figure out how I could cache the content of the database
> and prevent that many queries to be performed.
>
> What I would like to do is cache all the content of the database in
> memory, so that I could access it directly through my PHP application
> without querying the database and saving precious ressources.
>
> The database is quite small, 15 - 20 mB and it's size is constant (it
> does not get bigger over time). 92% of the queries are SELECT, only 8
> percents are UPDATE, DELETE and INSERT.

It sounds like you could improve you performance by using query
caching and/or better indexing... Read "High Performance MySQL"
by Jeremy Zawodny; it should give you some ideas...

> So, my question is, is it possible and recommandable to place 20mB of
> data in shared memory in order to prevent queries to the database? (all
> updates, deletes and inserts are performed both in the database as well
> as in memory)

Yes, it is possible. MySQL supports HEAP tables that are stored in
memory. But you still need to figure out a way to save those tables
on the hard drive, because HEAP tables disappear when MySQL server
stops or reboots.

> Or would I be better to place a copy of the database on a ramdrive?

Again, you can do that, but you still need to make sure your database
is synchronized to a hard drive somewhere...

> Other info:
> I have a server which runs both the PHP application and the MySQL
> database. It has 1GB of RAM. The database receives 250 queries / sec.

It appears a very manageable load... How many concurrent connections
are you handling?

Cheers,
NC
Received on Tue Oct 18 02:24:08 2005