Caching Queries

Status
Not open for further replies.
grandcartel

grandcartel

Grand Cartel Properties
Jan 28, 2007
55
1
0
Queens, NYC
So I'm working on a lyrics site, and I figured in order to lower the load a bit, I'd need to cache queries. Maybe I'm jumping far too ahead here, but I figured I might as well look into it.

Anyway, what caching solutions would you guys suggest? I took a look at memcache, but my site isn't OOP, so I figured it's not right for me.
 


grandcartel said:
So I'm working on a lyrics site, and I figured in order to lower the load a bit, I'd need to cache queries. Maybe I'm jumping far too ahead here, but I figured I might as well look into it.
Click to expand...

Here's a bunch of questions to your questions.

Are you talking about caching database queries or http queries?
What language are you using to develop your site?
Will you be creating enough of a load that you will need to worry about this? Don't waste time now if this will never be a problem.

Where is the time being spent when you build a page? You might need to run some tests and benchmarks to find where you are running into problems. If your database queries are taking a long time, look at writing more efficient queries and adding indexes. You'll be suprised at how much that can help.

If you are spending more time building your page (looping through DB results) then some kind of html page caching will be handy. Most web language frameworks (php, perl, .NET) already have a library out there to help with this.


grandcartel said:
Anyway, what caching solutions would you guys suggest? I took a look at memcache, but my site isn't OOP, so I figured it's not right for me.
Click to expand...

Memcache is not OOP, it's basically a simple key/value database, plus there are several language bindings for it. Do you know what you want to cache?
 
jover said:
Are you talking about caching database queries or http queries? What language are you using to develop your site?
Will you be creating enough of a load that you will need to worry about this? Don't waste time now if this will never be a problem.
Click to expand...
I'm talking about database queries. I'm programming the website in PHP. The database will be slammed once the site begins entering SEs and such.
Where is the time being spent when you build a page? You might need to run some tests and benchmarks to find where you are running into problems. If your database queries are taking a long time, look at writing more efficient queries and adding indexes. You'll be suprised at how much that can help.
Click to expand...
I started working on programming the backend and I have indexes/primary keys all set up done, but load times are still pretty high and overhead seems to be an issue here. Now, I'm no database guru, but I'm trying to make it as efficient as possible by joining tables and such.

Can you reccomend any libraries for this? I don't mind exploring different options as far as storing methods since I don't really even know what's out there.
 
grandcartel said:
Can you reccomend any libraries for this? I don't mind exploring different options as far as storing methods since I don't really even know what's out there.
Click to expand...

adodb is one I've used, it has a query cache option and is easy to use. I've played around with the caching part some, but never used it in production.

How many queries are you executing per page?
If you are using mysql, do you use the explain command to see what your query does?
 
If you dont want to cache just queries: jpcache is good (see google)

Otherwise:
Use serialize and unserialize and store the result somewhere.
 
What database are you using?
Are you on a shared or dedicated server?
How big is your database?
How much ram does your server have?

You left out too many variables to get a really good response.

I can't image why it would take long at all. Are you doing a lot of JOINs in a particular query? If you are on a shared server and using mysql more than likely it's highly optimized and already caching the queries. If you are on a dedicated server, using mysql and can edit the conf files check out the mysql query cache:

MySQL AB :: MySQL 5.0 Reference Manual :: 5.14 The MySQL Query Cache

Use something like phpmyadmin to run your queries and it will tell you how long it takes to execute. If your seeing slow page loads maybe the problem isn't in the query but something else.

It's possible that your primary keys are right or you have indexes setup wrong. Again if you are using mysql use the EXPLAIN to see how the query is being executed. I would bet that the indexs are set right. Are you using integer values for your keys?

MySQL AB :: MySQL 5.0 Reference Manual :: 7.2.1 Optimizing Queries with EXPLAIN
 
Grant29 said:
Are you on a shared or dedicated server?
How big is your database?
How much ram does your server have?
Click to expand...
Currently running on a shared account but I can't recall the other two bits
You left out too many variables to get a really good response.

I can't image why it would take long at all. Are you doing a lot of JOINs in a particular query? If you are on a shared server and using mysql more than likely it's highly optimized and already caching the queries. If you are on a dedicated server, using mysql and can edit the conf files check out the mysql query cache:

MySQL AB :: MySQL 5.0 Reference Manual :: 5.14 The MySQL Query Cache

Use something like phpmyadmin to run your queries and it will tell you how long it takes to execute. If your seeing slow page loads maybe the problem isn't in the query but something else.

It's possible that your primary keys are right or you have indexes setup wrong. Again if you are using mysql use the EXPLAIN to see how the query is being executed. I would bet that the indexs are set right. Are you using integer values for your keys?

MySQL AB :: MySQL 5.0 Reference Manual :: 7.2.1 Optimizing Queries with EXPLAIN
Click to expand...
There are many rows (something like 400k+) that go through a loop, and so the only thing I'm trying to do is lesser the load on the server. Loading the page doesn't take much time at all, but thats just for me. I would imagine that with simultaneous users, it could be stressful on the server.

I'll mess around with jcache, looks interesting :)
 
400k rows is not that much. I have databases in the 10Gig range that only take a fraction of a second to look up data. More than likely your problem is your database structure. You'd be best off to post the table setups and the mysql query.

Check out the mysql mailing list too, those guys give fast answers that work great. They will want to see your table structures and query.
 
As long as your indexes are correctly built and the column cardinality is high, i don't believe you should have a performance issue. If you are getting large result sets, then a cache could help, provided the same result sets are accessed over and over again. However, if you're just bringing back a small set of rows, you probably won't gain too much.

Keep in mind that mySQL is also trying to use its own caching to help speed up your queries. As long as the mySQL server is not overloaded, you should be fine.
 
WTF is a "column cardinality" ... sounds kinky, I might wanna try it.

::emp::
 
stanley said:
is it possible to cache mysql queries and update the cache every 24 hours or so? (cron job maybe?)
Click to expand...

The way the query cache works in MySQL is that the cache becomes invalid when the table changes. A new cache is then created when the query comes up again. Wash, rinse, repeat.

Zen
 
emp said:
WTF is a "column cardinality" ... sounds kinky, I might wanna try it.

::emp::
Click to expand...

It refers to the number of unique values for a given column. Say i had 1 million rows in a table and a column named 'PersonCount'. If the value of PersonCount was always 3, 4, or 5, then I would have a cardinality of 3. A low cardinality causes indexes to be less performant. In the above example, if the values were equally distributed (333,333 rows of each value), than an index could only be used to narrow the potential matching rows to 333,333. However, if there were 20000 potential values for PersonCount, then the index would perform much faster. For performance, the closer the ratio (column cardinality/totalRows) is to 1, the more efficient the queries will run provided that the specified column is indexed, and the index is being used in the query.
 
stanley said:
is it possible to cache mysql queries and update the cache every 24 hours or so? (cron job maybe?)
Click to expand...

It is very possible with a rather short php script
Code: 
<?php
$query_s = "SELECT id, foo FROM bar";
$query = $sql->query($query_s);
while($result = $query->fetch_array(MYSQL_ASSOC))
    $temp['id'] = $result['foo'];

$serlialized = serialized($temp);

$sql->query("INSERT INTO query_cache SET query='".$query_s."', data='".$serialized."' ");

?>

Above is using mysqli btw, you then do the opposite to get the data back, pretty self explanitory really :)

Hope this helps
 
grandcartel said:
There are many rows (something like 400k+) that go through a loop, and so the only thing I'm trying to do is lesser the load on the server. Loading the page doesn't take much time at all, but thats just for me. I would imagine that with simultaneous users, it could be stressful on the server.

I'll mess around with jcache, looks interesting :)
Click to expand...
Ive benchmarked, 1000 loop through a large (in bytes) array isnt the fastest thing in the world, so a 400k one isnt going to be fast. Might just be a
Sounds like the programming you are doing is rather inefficent, note i run a database with 30 gig of data being accessed with hundreds of simultaneous connections... 29 million rows and its extremely fast... also selecting 1k rows. So you really shouldnt have an issue unless you've done a simple mistake!

An explain would help us help you :)
 
Status
Not open for further replies.
Top Bottom
Back