Site is very slow on dedicated hosting - Ceon URI Mapping

[DrByte]

MODERATOR COMMENT
Guys, while there are clearly some strong opinions held here, with strong reasons behind each, and I see that there's an effort here to keep the discussion professional and courteous, I feel the need to put on my moderator hat and point out that it seems fawad123 doesn't speak english as a primarly language ... so some of what he's saying is probably being misinterpreted due to his less-than-ideal choice of words.
Namely, while english-speaking people may take offense at the use of "disgusting", a non-english-speaker may not realize that when using it.
Granted, he did use it several times ... so if he did actually mean it the way it reads, then his comments are in poor taste, and deserving of a request for apology.

In the end, I hope the common goal is a better-working site for fawad123 as far as this mod goes, and a round of improvements to conor's mod as conor has been hoping for, and validation to cjpinder for assisting in identifying specific improvements to aid both.

[fawad123]

Conor, i'm also very professional in dealing and please don't get me wrong. It was all my effort, time and money who made me little hard.
I prefer everyone to use zen-cart with this module but you know what this command can create problem in one server but not all three servers i used. I wish if you could see the performance of all three servers with (enable/disable) Ceon.

I'm not here to critisize your hard work. No doubt you have already done a very good job to help people for their SEOs issues.

I must say "sorry" but you also try to understand that all three servers (shared, VPS, and dedicated) can't be misconfigured at once.

[fawad123]

I didn't jump at the opportunity to spend my limited time checking out your server for free

Conor, i didn't ask you to test my server for free or paid service. I myself can test the server and see the performance.

[fawad123]

Dr. Byte, no doubt english is not my first language and i must apologize for my mistake. You are very professional and you have put lot of effort to keep this forum and reliable and professional place to help people. I admit my mistake in front of those who are very helping to those.

I'm really sorry for either my wrong english or wrong use of words.

[conor]

Hi,

I appreciate your response, thank you for your understanding.

All three servers (shared, VPS, and dedicated) can't be misconfigured at once.

I was under the impression that your problem was a recent occurrence, and that you only experienced a problem when you moved from the VPS to the dedicated server.

If I had have known that you'd been having problems all along then I wouldn't have thought straight off that it was a server issue that I could do nothing about without actually logging into the server etc. and we could have both pursued the issue of inefficient SQL commands.

As it is, Christian has been invaluable in his support, and this has resulted in your site working at the speeds that you and I both expected, and will shortly result in a new version of the software, which apparently looks like it will be hugely faster for anyone with the same size of store as your own.

So thank you Christian for that!

Once Christian (or anyone else who knows about these things - feel free to jump in) gives his advice on the last few questions I brought up I'll be able to implement the improvements in a new version of the software.

I'm sorry that you experienced the problems you have but I think we can all see how that transpired and the main thing is that we've found a problem and a very simple solution.

All the best..

Conor
ceon

[conor]

Hi,

it seems fawad123 doesn't speak english as a primarly language ... so some of what he's saying is probably being misinterpreted due to his less-than-ideal choice of words.
Namely, while english-speaking people may take offense at the use of "disgusting", a non-english-speaker may not realize that when using it.
Granted, he did use it several times ... so if he did actually mean it the way it reads, then his comments are in poor taste, and deserving of a request for apology.

Thanks for the post and the comments, you've explained things very well indeed. I actually thought that fawad123 was from the US but what you've said makes sense.. actually it's quite interesting how this shows how such a simple difference in understanding of a word's cultural meaning can have such an effect!

In the end, I hope the common goal is a better-working site for fawad123 as far as this mod goes, and a round of improvements to conor's mod as conor has been hoping for, and validation to cjpinder for assisting in identifying specific improvements to aid both.

I think everything's resolved now.. and the software will be faster and more efficient as a result. It didn't transpire in an ideal way but we are where we are now!

All the best...

Conor
ceon

[fawad123]

Thank you so much for understanding the situation. I said in the beginning that i can never critisize those who are working to help people. You have done a very good job and you should not be critisized for one line bug fix in thousands of lines of code.

Anywaz, Christian, no doubt, has helped us alot.

Thank you so much for your time and your add-on

Regards,

Fawad

[fawad123]

Conor, it doesn't mean that people living in US can't use the wrong word at wrong place. :)

I live in US :)

I was just little disappointed from your first message on Ceon related thread.

Anywaz, now everything is okay and i'm very much satisfied with the performance of my website.

[conor]

Hi Fawad,

Thank you so much for understanding the situation. I said in the beginning that i can never critisize those who are working to help people. You have done a very good job and you should not be critisized for one line bug fix in thousands of lines of code.

That's good of you to say, thank you. It's not technically a bug but a major inefficiency but regardless, I have to admit to being stunned at the difference that simple change has made on your site. Not least because in earlier versions of the software it did use LIKE instead of REGEXP!

As I said, I'm not an expert in database optimisation by any means, but I thought the simplicity of the regular expression the code uses would have it perform at a similar speed to that of a LIKE command, or if slower, not by the huge factor which it was on a database table of your size. You do indeed learn something new everyday then!

Anywaz, Christian, no doubt, has helped us alot.

Yes indeed. Thanks again Christian!

Thank you so much for your time and your add-on

No problem, I'm glad you like it! Now, time to get off this forum and back to coding.. :)

All the best..

Conor
ceon

[CJPinder]

Hi Conor,

The index you posted.. would it benefit from having the current_uri field indexed or not?

The suggested index I posted earlier was a composite index on
main_page, associated_db_id, language_id and current_uri . The intention being to speed up the lookup queries done in zen_href_link().

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.

Personally, I would say drop support for URIs without a trailing slash. I've tried the mod with 3 different hosts (2 of which were ZC Certified ones) and in each case re-written URIs without a trailing slash caused 404 errors in the apache error logs.

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.

I suspect that the ceon_uri_mappings table has become too big for the MySQL server to cache it in memory. The full table scans may then be causing the whole table to be read from disk every time it is accessed.

It is quite possible for a site on a shared server to run faster than a site on a dedicated server. Most shared servers are big boxes with fast CPUS, lots of memory and fast disks. If the other sites on the server are quiet then you can get good hardware all to yourself. Of course at peak times your site can run very slow.

With a VPS or dedicated server they usually come with a default installation of MySQL which you then have to tune for your specific requirements.

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.

With a 1000 URIs mapped the mapping table would probably be cached in memory so a full table scan would no doubt show the same performance as only 5 mappings.

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.

My guess would be that the mappings table just became too big for the MySQL server to handle the full table scans efficiently.

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

My intention was to help fawad123 with his immediate problem of getting his site working. Once his site was up and running I would of course posted any appropriate suggestions in the URI Mapping Support thread. Unfortunately, things seemed to get a bit out of hand

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 appreciate that it is quite sole destroying when you have worked hard on a piece of software and someone comes a long and criticizes it. I'm sorry if I have caused you any stress and I do appreciate all the effort you put into your software and the support you give.

In my own store we carry about 3000 products and I consider it to be a pretty small independent store. Maybe 'tiny' isn't quite the right word but a store with 1000 products would be quite small, but that is just my opinion.

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?)

Yes, the idea is to use LIKE with a wildcard to get a reduced set of data to then perform the REGEXP on. A REGEXP will usually be at least 10x slower than a LIKE so combining the two gives a good speed up with the same functionality.

Putting an index on the 'uri' field and using one LIKE (or 2 with an OR) may well be even faster but I haven't tested it. The tricky bit would be to get the right index size on 'uri'.

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)?

I'm not sure. Once the index is in use a doubt you would see much improvement.

Regards,
Christian.