Site is very slow on dedicated hosting - Ceon URI Mapping

[fawad123]

@version $Id: init_ceon_uri_mapping.php 392 2009-06-27 18:45:09Z Bob $

[CJPinder]

Okay..try the following...

Edit /includes/init_includes/init_ceon_uri_mapping.php (take a copy of it first just in case).
Starting at line 124 you should see the following code...

$identify_main_page_query = "
    SELECT
        um.language_id,
        um.main_page,
        um.query_string_parameters,
        um.associated_db_id,
        um.alternate_uri,
        um.current_uri,
        um.redirection_type_code
        FROM
             " . TABLE_CEON_URI_MAPPINGS . " um
        WHERE
             um.uri LIKE '" . zen_db_prepare_input($uri_to_match) . "%' AND
             um.uri REGEXP '" . zen_db_prepare_input($pattern) . "'
         ORDER BY
              um.language_id,
              um.current_uri DESC,
              um.date_added DESC;";

You need to add in the line highlighted in RED above. Then upload the changed file to your server.

Hopefully that should speed up the product and category pages.

Regards,
Christian.

[conor]

Hi Christian,

The CEON URI mapping add-on is very bad at doing database lookups

Rather than simply criticising software it would be more useful if you suggested ways to make it better on the software's own thread.

I see this forum post has subsequently been renamed to indicate that Ceon URI Mapping is the problem.

I wouldn't recommend it for anything other than a tiny store

Using it will of course generate more of a load on the site but the fact is that it carries out the minimum number of queries possible.. it has been programmed to offer flexibility and functionality before blind speed as speed can always be improved by using better hardware and software.

fawad123 clearly has a problem with his database software as many people are using Ceon URI Mapping with stores with thousands of products with page times in milliseconds.

I appreciate you are trying to help this person, but slating the software as being "bad" at database lookups and not suitable for anything but tiny stores shows a certain amount of ignorance in my opinion which is not helpful to others.

All the best..

Conor
ceon

[CJPinder]

fawad123 clearly has a problem with his database software as many people are using Ceon URI Mapping with stores with thousands of products with page times in milliseconds.

Can you provide the links to any? Have you personally tested it on a store with thousands of mappings?

I appreciate you are trying to help this person, but slating the software as being "bad" at database lookups and not suitable for anything but tiny stores shows a certain amount of ignorance in my opinion which is not helpful to others.

The CEON URI Mapping add-on does not have any indexes on the ceon_uri_mappings table. It also uses a MySQL REGEXP lookup which is very slow. With hundreds of categories and thousands of products it can get very slow and put a lot of load on the MySQL server.

fawad123's probably is clearly being caused by the URI Mapping add-on. The fixes I have provided will help.

At least I am trying to help. The response you gave in the mapping support thread was overly defensive and dismissive. You refuse to believe there could be a problem with your software before fully investigating the situation.

Christian.

[conor]

Hi,

Can you provide the links to any? Have you personally tested it on a store with thousands of mappings?

Hmm, you seem to be having a bit of an attitude problem here. Why would I make up such a thing? I haven't personally tested it with thousands of mappings, only with about a thousand (and the pages were generated in milliseconds on a very average computer not optimised for webserving), however quite a few people with large stores have got in contact with me directly to talk about the software and none of them had any negative things to say other than about the amount of time it took them to set up the mappings (which is often why they got in contact, understandably! :) ).

The CEON URI Mapping add-on does not have any indexes on the ceon_uri_mappings table.

Yes, that's something I've wondered about previously and was wondering if someone with more expertise in database issues than me might step in with suggestions for indexing. I have not made looking into this a priority and have so far kept it on the backburner as no-one has yet reported any significant slowdowns caused by the software and asked to have it optimised.

I'll be testing your indexing as it is most likely beneficial.. which is why I don't understand why you didn't want to share it on the main thread but instead provide it as part of a solution to a problem on another thread where you publicly deride the software.

It also uses a MySQL REGEXP lookup which is very slow.

Yes, I'm aware of that.. that was a deliberate choice to save having two SQL lookups instead of one. If I disallowed people from having URIs with trailing slashes then the regular expression or additional query wouldn't be necessary. Again, that's a decision I've kept in reserve on the basis of seeing how things worked "in the wild" and, not having had any negative reports as yet, decided to leave it as is. Maybe now that it's finally been mentioned, I'll change that now.. I'll ask for other people's opinions on the main thread.

With hundreds of categories and thousands of products it can get very slow and put a lot of load on the MySQL server.

That's indeed possible but as of yet I haven't had any reports of such a situation occurring which can actually be attributed to the software itself.

fawad123's probably is clearly being caused by the URI Mapping add-on. The fixes I have provided will help.

It is my opinion that you are both mistaking correlation for causation.. clearly there is a dramatic difference on this person's site when URI Mapping is switched on rather than when it isn't on but the difference is so huge that it indicates another problem, most likely with the server setup itself. If the site works significantly faster on a virtual server rather than a dedicated server then that's quite a big indication that there's a fundamental problem with the dedicated server itself. It would appear that Ceon URI Mappings is simply showing up an underlying problem, which is unfortunately something I can't help with without logging into the server etc.

At least I am trying to help. The response you gave in the mapping support thread was overly defensive and dismissive.

I'm having chemo at the minute and was able to work for just 3 hours this week. My time is at a huge premium so I'm trying to spend as little time as possible on these forums and to instead work on actual coding. I recognised that there must be a problem with this person's server and that's not something I can help with so I said so. I realise it appears dismissive and apologise if it came across that way but when you've only a small number hours in front of a computer in a week you'll find that you too would make your replies as short/quick as possible.

You refuse to believe there could be a problem with your software before fully investigating the situation.

You've misread the intentions behind my replies and this is probably why your attitude towards me/the software is somewhat negative. I absolutely don't ignore anyone's comments and blindingly believe that the software is not to be at fault.. in fact, I've been highly curious as to how the software would perform on sites with many thousands of products. As I say above though, people with such sites who have been in contact with me haven't had anything negative to say and I judged this person to have something fundamentally wrong with their server which is something I don't have the time to help with/investigate.

All the best..

Conor
ceon

[CJPinder]

Conor,

The problem is that the URI Mapping add-on is doing full table scans on every query. This is due to the lack of indexes. It also uses REGEXP which means a very slow full table scan.

When an index was added to the ceon_uri_mappings table the query time for the homepage dropped dramatically.

The product and category pages are still slow because of the full table scan using REGEXP. The additional of a LIKE with wildcard that I suggested should hopefully speed that up. We won't know for sure until fadwad123 tests it and reports back.

Using to LIKEs without wildcards and with an index is also an option. It was going to be my second suggestion after I had heard back about using the additional LIKE with wildcard.

Now, it may be that on a fast server with lots of memory and a well tuned MySQL server the full table scans won't show as an issue. However, they are an issue and they are unnecessary. It may be that fadwad123's MySQL server is not fully tuned and that is showing up the problem with the Mapping Add-On. Tuning the server may mask the problem but the problem will still remain.

I provided my suggested fixes in this thread for continuity.

I'm sorry if you take offence that I believe that your add-on is only suitable for small stores. However, while it continues to do full table scans and use slow queries I will stick with that opinion.

I have nothing personal against you Conor. I try not to form opinions about people I have never met. I wish you well with your treatment and hope you get well soon.

Regards,
Christian.

[conor]

Hi,

The problem is that the URI Mapping add-on is doing full table scans on every query. This is due to the lack of indexes.

Yes, I always thought indexes would be a good idea to speed things up and as I said I was expecting that someone with more database expertise would contribute their thoughts eventually. The fact that it has taken several years for anyone to say anything made this remain a low priority for me to look into.. with no complaints about speed came no motivation to add the indexes.

The index you posted.. would it benefit from having the current_uri field indexed or not? I don't know enough about these things to make that decision and don't have the time to try out various options to see what's best so if you know about these things your advice would be appreciated.

It also uses REGEXP which means a very slow full table scan.

I don't think it means a very slow table scan, but it certainly will result in a slower table scan. I decided that since it was such a simple regular expression (a text string with one single optional character at the end) that a query using it as opposed to two LIKES would be probably quicker. I didn't do any tests to see if that is true, again as I'm not an expert in this field and have limited time.

When an index was added to the ceon_uri_mappings table the query time for the homepage dropped dramatically.

That's exactly what would be expected of course but as no-one has previously mentioned any speed issues I never added the index.. which I was taking as the original speed of the queries being sufficiently fast anyway that people didn't feel the need for an increase.

Personally I always want things to work as flexibly as possible first, then as efficiently as possible second and in this case an index will definitely give extra efficiency without impinging upon the flexibility/functionality of the software so it's definitely a good addition. As I said, I've been waiting for someone with more expertise than me in this area to comment so I'll be interested to hear your thoughts about the question I've asked above.

The product and category pages are still slow because of the full table scan using REGEXP. The additional of a LIKE with wildcard that I suggested should hopefully speed that up. We won't know for sure until fadwad123 tests it and reports back.

A LIKE with a wildcard won't work as it will mean that categories will be matched instead of subcategories as the category's URI is a subset of the subcategory's URI. As I was saying, the one REGEXP could be changed into two LIKES but I'd have imagined that that would be slower. I don't know though. The other option I talked about previously is simply to drop support for storing URIs with a slash at the end in the DB, that way a single LIKE could be used.

Using to LIKEs without wildcards and with an index is also an option. It was going to be my second suggestion after I had heard back about using the additional LIKE with wildcard.

Sorry you've already said what I just said.. I mustn't have read the post properly before starting to comment on a point by point basis.. that's that rushing things thing again! :|

Now, it may be that on a fast server with lots of memory and a well tuned MySQL server the full table scans won't show as an issue. However, they are an issue and they are unnecessary. It may be that fadwad123's MySQL server is not fully tuned and that is showing up the problem with the Mapping Add-On. Tuning the server may mask the problem but the problem will still remain.

I do agree that making things as efficient as possible is very important (again as long as that's not at the cost of functionality) but I don't see how this one server can be so slow when no-one else has reported problems and the person himself said that his site was faster on a shared server.

When I tested a site with almost 1000 products mapped recently it was on a desktop Pentium D PC with the standard versions of Apache for windoze, PHP5.3 and MySQL 5.1.. there was no noticeable difference between the site with 1000 mappings and a similar one which only had 5 mappings, so for a site with a few thousand more mappings to take 16 seconds to build a page just sounds like the server itself must have a problem. Which is why I suggested loking at SQL connections etc.

Certainly making URI Mapping more efficient is great and should definitely be done but I still don't think it could be the main problem this person is experiencing. The factor their site is slowed down is just too huge.

I provided my suggested fixes in this thread for continuity.

I only encountered this thread because its title had been changed to mention Ceon URI Mapping, I would never have seen your suggestions otherwise.

I'm sorry if you take offence that I believe that your add-on is only suitable for small stores. However, while it continues to do full table scans and use slow queries I will stick with that opinion.

It uses one single query with a REGEXP for each page on a site that is loaded. It then uses an additional lookup or two for each link being built for a Zen Cart page using the zen_href_link function.

The REGEXP can be changed to a LIKE OR LIKE but I can't see any way anyone could write URI mapping software to use fewer queries without sacrificing functionality. How can URI Mapping software not carry out a table scan?! (Indexing aside, which as you se I totally agree the software should have).

As this is the first person to mention speed being an issue it is indeed disappointing to hear someone else advise others that the software is "unsuitable" for anything but "tiny" sites. How do you define a "tiny" store? I personally wouldn't call a store with 1000 products in it tiny and that is handled with ease by the module.

I have nothing personal against you Conor.

Thanks, I'm glad to hear that.. your last post sounded like you thought I was lying when I said the software is used without issue on sites with thousands of mappings so it kind of gave the impression that you had a problem with me. I definitely am open to hearing how it can be made better and am glad we've got that cleared up! :)

Thanks also for the nice comments, I'm getting better thankfully, it's simply frustrating to have so little time day to day, there's never enough time even when days are 24 hours! :)

All the best..

Conor
ceon

[conor]

Hi Christian,

A LIKE with a wildcard won't work as it will mean that categories will be matched instead of subcategories as the category's URI is a subset of the subcategory's URI.

I just noticed that your LIKE statement uses an AND before the REGEXP is called.. that's an interesting change that looks like it would provide speed benefits (not testing the expression until the simpler LIKE matched?) without sacrificing the ability for people to use URIs which end in slashes (how useful that is I don't know though and maybe just to make things easier overall I should drop that support).

In the zen_href_link() URI lookups I'd been thinking about adding a "LIMIT 1" to the end.. would that cause the SQL software to stop once it had found a match (and therefore minimise the query time further)?

All the best..

Conor
ceon

[fawad123]

First of all i'm very thankful to "CJPinder" that your fixes helped me alot and now the final fix is really helpful. Now i'm getting this time.

Parse Time: 1.697 - Number of Queries: 353 - Query Time: 0.27653534675598

This is appreciable. I must say thanks to CJPinder because he has saved my time and money.

Now i'm getting to 'Conor'.

Conor, i'm not a person who wants to critisize for your bug in the add on (SEO Ceon). It is good but let me tell you the exact story i faced since i switched to dedicated server.

I was working fine on shared hosting but when i noticed problem with google indexing i decided to shift to VPS because i was getting around 12 seconds on average to open a single page.

I shifted my entire side to VPS with 1 GB Ram and 15 BG Hard disk on CentOS. The best time i got was 33 seconds to open the front page.

Then me and my team decided to shift to Dedicated server (Intel Pentium Dual Core 2.8 GHz, 2 GB RAM and 500 GB Hard disk with 7 mbps link). The best time i got was 33 seconds to open the front page. I talked with their support team and they said that your SQL queries are causing this delay. I logged into the Linux Shell and gave a command 'top'. On the other page i opened the webpage on dedicated server and started browsing the pages, the SQL process was talking 300 % CPU time on such a good dedicated server.

It is my opinion that you are both mistaking correlation for causation.. clearly there is a dramatic difference on this person's site when URI Mapping is switched on rather than when it isn't on but the difference is so huge that it indicates another problem, most likely with the server setup itself. If the site works significantly faster on a virtual server rather than a dedicated server then that's quite a big indication that there's a fundamental problem with the dedicated server itself. It would appear that Ceon URI Mappings is simply showing up an underlying problem, which is unfortunately something I can't help with without logging into the server etc.

Conor, now you'll realize the hardware details of my servers. For your information i'm currently testing my Google ajax scripting on servers and they responding in the best time. These scripts are quite heavier than your Ceon. But please don't get me wrong i can send you the script for testing too. I just wrote this sentence because servers have no problem in SQL or PHP or anything.

Now after fixes my site is working at best speed.

you can also try http://www.essaysbundle.com (with fixes)
http://www.solutioninn.com (without fixes, just front page fixed)

Coron, if you see when i started this thread it was around 3 weeks of time. It took my 3 weeks to find the problem and during this time i have lost $150 on VPS and dedicated server.

I hope you can realize the situation i faced during this problem.
CJPinder is life saver at this time whereas you rejected my reply when i posted on your forum. It was really disgusting. You are blaming a helpful person for 'attitude' when he is helping without any problem and you are not helping when you know the core of the add-on. This is really disgusting Mr. Conor.

fawad123 clearly has a problem with his database software as many people are using Ceon URI Mapping with stores with thousands of products with page times in milliseconds.

My products are 24000, now i invite you to see both the websites as mentioned above and check for your mistake in Ceon.

[conor]

Hi,

Coron, if you see when i started this thread it was around 3 weeks of time. It took my 3 weeks to find the problem and during this time i have lost $150 on VPS and dedicated server.

I'm glad you've got things working as desired. No-one else has ever had the same problem you had so I don't know why your server had such problems with the REGEXP command.

If you properly read my responses to Christian you realise where I was coming from.

It was really disgusting.

I tell you what... How's about you spend weeks writing some software and giving it away and supporting it for free, then get sick and start having chemo and then I'll e-mail you and ask you to help me with a problem with the software, a problem which you genuinely think is being caused by my server and not by the software, and see if you'd write "I don't think the software is the problem as no-one else has ever had the same problem, so I can't help you. You should check out your server configuration..".

Would you then expect me to write back calling that action "disgusting"?

I don't think that's a very respectable thing to do?!

If I had more than a few waking hours daily I'd be more open to tracing problems, even if I thought they weren't to do directly with the software, but I think most people will understand why, under the circumstances, I didn't jump at the opportunity to spend my limited time checking out your server for free.

You are blaming a helpful person for 'attitude' when he is helping without any problem

No, if you read my posts you'll see that I've already said that Christian had given me the impression that he thought I was lying to protect the software's reputation when that wasn't the case. I consider the matter resolved now.

you are not helping when you know the core of the add-on. This is really disgusting Mr. Conor.

I hope you'll apologise for your misunderstanding of the situation.

All the best..

Conor
ceon