Large store and high database load

[Guest]

Hey Everybody,

 

I am running a site using osc and I've started to hit some walls on the load that the database can handle. Currently the site gets about 4000 unique visitors a day and each visitor averages 6 pages. This generates approximately 28046 page views per day. My store has over 30k products spread across about 400 categories. Using some mysql load tools (mtop, mysqlreport, munin) , the site is generally around 50-75 queries per second.

 

So I have done some work in reducing the load. My customer wants to have the category counts on. I know that this is a HUGE load, massive number of queries. So I resolved this by using pear cache_lite and updating the left side nav daily. When it does generate the category navigation there are approximately 1500-2000 queries. However, because these are highly optimized they go through very quickly.

 

I have the module installed that shows the queries and how long they've taken. This of course provides invaluable information. I generally look at the page_parse_time_log and see pages that are loading slowly, and then figure out which queries could be optimized. When looking at queries, if they are faster than .1 of a second I don't generally worry about those. This has gotten me to where I am today.

 

What I've done is put multi-master replication in place. This works fairly well and gives you about 40% fewer queries per database. Because it has to replicate the data modification statements (DMS), those double. But select statements half per db. I had to make some code changes to deal with the incrementing values of orders so that they don't jump, same with products_id. Those are the only places that I can think of off hand that I cared that the numbers stayed in a single incrementing fashion. Since the site gets between 50-100 orders per day, the chance of a collision across the actual order didn't seem great enough for me to worry about that yet. I am probably going to switch to a time based order number in the future to completely eliminate this issue.

 

However what I have run into is two tables colliding on a fairly consistent basis. The cache and sessions table end up in a sort of collision loop. I don't know the cause of it yet - I don't know what the situation is that actually creates the collision and I'm not sure that I can watch it for every user and see how they interact with the site. My solution thus far is to run: slave stop; truncate sessions; truncate cache; slave start; on both databases and replication will catch up and work fine. The downside to this is that all users loose their cache and are required to log back in. Not ideal.

 

What ultimately brings me to needs more power for the site is the search engine. The way the search on the site works requires a table scan because it does a wild card search. This cannot be indexed. Even the CSR's that work for the company just do product searches all day when customers call in. They search for products_id, manufacturers, mfr part numbers, descriptions, etc. They just do searches instead of navigating. Same with the customers, there are approximately 400 searches done per day. Ultimately about 25% of the queries on the site require a table scan. When the load is high on the site (usually from about 12pm-6pm EST) the page load times can go up over 20 seconds. And there doesn't seem anyway to resolve this. The first query does the count of results, second query displays the results.

 

Does anybody have any thoughts or ideas around this topic?