[hadi_php]

Well, i got stuck with mysql. For my site load balance i need to use file system database to reduce pressure of mysql. So i need to make some portion into xml. But i got stuck again. I just need to know where to start for use a xml file as my db. Thanks in advance.

[RudiVisser]

Look into the SimpleXML classes for the XML side.

But to be honest, I don't think it will reduce much load.. You'll just be offloading calls to PHP rather than MySQL, and since it's on the filesystem you'll put more load on that.

I'd suggest optimising the queries you are running on the database over changing the method.

This post has been edited by RudiVisser: 29 April 2010 - 03:22 PM

[hadi_php]

yes, there are several quires like mysql join query with 3 or more table table. do you let me know that how to optimize them ... instead of join query...

[RudiVisser]

Well if you post your queries I'm sure we can take a look at them!

[hadi_php]

like

select a.brand_name,a.des,a.price,b.name,b.pub,c.c_name,c.result from one a, two b, three c where b.id=a.id=c.parent_id AND b.id=1 AND state=1 ORDER by b.ordering DESC LIMIT $st,$per_page 

i have this type of query twice time and also other single query...

[RudiVisser]

You should try doing proper joins.. Such as:

SELECT
	a.brand_name,
	a.des,
	a.price,
	b.name,
	b.pub,
	c.c_name,
	c.result
FROM three c
WHERE b.id = 1
	AND state = 1
LEFT JOIN one a ON a.id = c.parent_id
LEFT JOIN two b ON b.id = c.parent_id
ORDER BY b.ordering DESC
LIMIT $st, $per_page

If that doesn't work try INNER JOIN, I suck with joins

[AdaHacker]

RudiVisser, on 29 April 2010 - 11:39 AM, said:

But to be honest, I don't think it will reduce much load.. You'll just be offloading calls to PHP rather than MySQL, and since it's on the filesystem you'll put more load on that.

I second that. If you're having trouble scaling MySQL, switching to XML files is just trading one problem for another, probably much worse problem. Databases are designed to scale - XML files are not. Your time would be better spent on some basic database optimization.

Start by isolating the really slow queries - MySQL's slow query logging is very handy for this. Then, use the EXPLAIN statement to look at the execution plans for those queries. Make sure that they're actually using the correct indexes and check that they're not generating temporary tables on disk. After that, you should look into caching the results of your more frequent queries using something like Memcache or APC. The MySQL query cache might also be helpful, depending on your scenario. Effective caching can make the difference between a site that's running smoothly and one that's about to fall over.

RudiVisser, on 01 May 2010 - 05:16 AM, said:

If that doesn't work try INNER JOIN, I suck with joins

Since you mentioned it.... The JOINs need to be part of the FROM clause. Putting them in the WHERE clause like you have is syntactically incorrect. Also, the comma-join in the original query is semantically equivalent to an inner join, not a left join. It should be more like this:

SELECT
	a.brand_name,
	a.des,
	a.price,
	b.name,
	b.pub,
	c.c_name,
	c.result
FROM three c
JOIN one a ON a.id = c.parent_id
JOIN two b ON b.id = c.parent_id
WHERE b.id = 1
	AND state = 1
ORDER BY b.ordering DESC
LIMIT $st, $per_page

However, you are right about using an explicit JOIN in the FROM clause rather than the comma-join. MySQL's query optimizer isn't very smart. I've seen a couple of cases where it doesn't correctly optimize a comma-join as a join, but rather builds the entire Cartesian product of the join tables and then filters on that. Not that that's necessarily happening here, but it's at least worth checking the query plan.

[hadi_php]

yah, left join is much quicker and good than normal join....

So you are telling that do not go with xml. Continue with mysql with optimized query and use cache system?

[RudiVisser]

@AdaHacker - Thanks for the explanation.. Multi-Table queries were always my weak point

@hadi_php - Yes, don't go with XML at all. To be honest you probably don't even need a query cache system, but it will always help. In addition to optimising the queries you should also try to reduce the amount of queries you're running in the app.. That will speed it up too!

You could even store results of all queries (that you need data from multiple times) in a class/array and you'll be able to access them all the time.

Back in the day I used my own query function that would store queries and their results, then when I tried to run them again it would just return the results as it was a duplicate query.. If your web-app is pretty big, you could just use something like that

[AdaHacker]

RudiVisser, on 02 May 2010 - 03:42 AM, said:

You could even store results of all queries (that you need data from multiple times) in a class/array and you'll be able to access them all the time.

Honestly, I wouldn't even bother with something like that. If the problem is actually the load on the servers, and not just painfully bad queries, then an actual caching system is going to get you way more mileage for the same amount of effort. And if you expect traffic to grow, you will need it sooner or later anyway, so why not start now?

Just stop and think about it. Storing query results in arrays, etc. is nice, but how much does it reduce the database load? By a few queries per request? That's great if you're running the same query several times, but those arrays don't persist across requests - you still need to run them all at least once per page load.

Compare that to, say, APC, which will persist across requests. The nice thing about APC is that it's easy to install, requires no additional services or servers, and is ridiculously easy to implement in your code. You would write something like this:

# Retreive the query result from cache.
$result = apc_fetch('someKey');
# If the result is not in cache, run your query and store it.
if ($result === false) {
   # Run query here and fetch all your rows into a variable called $result
   # Store the data in cache with an expiration time of 5 minutes.
   apc_store('someKey', $result, 300)
}

Since APC stores data across requests, instead of running your query once per page load, you're running it once every five minutes. For any significant amount of traffic, that's a huge savings. Naturally, you will want to vary the cache TTL depending on your needs, but with enough traffic, even caching for a minute or two can save you a significant amount of DB load. And for some queries, you might get away with caching the results for a long time if the data doesn't get updated often.

[Fratyr]

Try redis database. key=value one.

[RudiVisser]

AdaHacker, on 02 May 2010 - 12:59 PM, said:

Honestly, I wouldn't even bother with something like that. If the problem is actually the load on the servers, and not just painfully bad queries, then an actual caching system is going to get you way more mileage for the same amount of effort. And if you expect traffic to grow, you will need it sooner or later anyway, so why not start now?

Of course - Different circumstances require different things. My issue was never with server load, more like just reducing the sheer query count. I wrote a tonne of SERIOUSLY BAD code back in the day, needed to optimise it. This is where the query "caching" system came in, it was running the same query in several points as it required the same data in a lot of places and wasn't reusing the results.. This was more of a preliminary fix

[hadi_php]

have you got any tutorial about APC , memcache...? so that i can choose which one easier for me

[AdaHacker]

hadi_php, on 03 May 2010 - 01:08 AM, said:

have you got any tutorial about APC , memcache...? so that i can choose which one easier for me

No, I don't. The APIs are really simple, though. You can probably get a good idea just from the documentation. Each one only has about 5 functions that are directly relevant to caching data.

However, I can tell you right now that APC is definitely much easier in terms of setup. Memcache is a distributed caching system, so it requires a separate service and can pool across multiple servers. This can improve cache efficiency for larger server pools (you only have to cache data once across the server farm, rather than once per server), but it also introduces network latency. APC, on the other hand, is all on the local machine and doesn't require anything special in terms of configuration. You pretty much just install the extension, restart the web server, and you're good to go. (Of course, you may want to tweak the settings a but, but it's not required.) If the extension is packaged by your OS distributor, you could probably get it up and running on a test system within 5 minutes and start experimenting.

If you're not sure what caching system is most appropriate, you could write yourself a custom caching class to wrap the calls. That way, you could switch between APC/Memcache/whatever on the back end without having to change your application code.