Site is very slow on dedicated hosting - Ceon URI Mapping

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

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.

Hi Fawad,

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.

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!

fawad123:

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

Yes indeed. Thanks again Christian!

fawad123:

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

Hi Conor,

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().

conor:

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.

conor:

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.

conor:

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.

conor:

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.

conor:

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

conor:

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. :wacko:

conor:

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'.

conor:

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.

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.

It is good to see that your site is now working at a reasonable speed. Thank you for the kind words. Glad I could be of assistance.

Regards,
Christian.

Hi Christian,

CJPinder:

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().

Yes, sorry, for some reason when I read it I didn't see the current_uri field and I thought it should be included so was asking you as much. Since it was there all along I have no idea how I missed it!

That was the index I'd thought about adding so it's good to see that you think it's a good index to use as well, maybe I should have just gone with my instinct two years ago when I first created the software.. ah well, I've just wasted some CPU time on thousands of sites! :)

CJPinder:

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.

That's very strange. Any rewrite rule that covers a URI removes that URI from the webserver error process unless the software itself generates a 404 header. If you could get in contact privately via this link with access details for one of the sites that had a problem I can take a look at the logs and htaccess file and see if I can find out why that is happening.

The vast majority of people using the module use URIs with no trailing slash. Actually, I'm not aware yet of anyone who does add a trailing slash, which is why I was thinking removing that functionality altogether (except for the root page of course as that is required by the HTTP spec) might be beneficial.. it would mean being able to use LIKE once more without a wildcard.

CJPinder:

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.

Ahh, that sounds very plausible indead. When Fawad posted his problem my thought was that maybe the connection the MySQL server was being dropped after each query and that the reconnection time overhead was responsible for the delays but I couldn't understand why that would be.

I didn't realise that MySQL by default caches the database table in memory, I imagined it would read it from disk and then cache a table as it was used, so all operations would always take place in a similar fashion. It's good to learn I got that wrong, so I won't make the same mistake again!

CJPinder:

It is quite possible for a site on a shared server to run faster than a site on a dedicated server.

I'm well aware of that.. and of the exact reasons you wrote about.. I had been under the mistaken impression though that the previous VPS/shared server used was performing with site pages loading instantly and therefore that pages taking 16 seconds was a slowdown of possibly 16-100 times, which of course is a factor too big even for the difference between a cheap dedicated server and a VPS/shared server which had access for the necessary time to top rate hardware. Anyway.. that's all historical information for us now! :)

CJPinder:

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

That certainly sounds like it!

CJPinder:

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.

Thanks for the nice comments.. hopefully with the addition of the index and the change to focussing on LIKE in the next version you might not consider the software to be "bad at database lookups" and only for tiny stores anymore? ;)

CJPinder:

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. :wacko:

Yeah, small rather than tiny. Tiny is our store, it has 7 products! :)

CJPinder:

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.

As I was saying yesterday, I really have learnt something useful here.. I never thought a simple regular expression would be so inefficient.. whenever I changed the LIKE to REGEXP (a year or so back I think) I didn't see any noticeable change on my test server and heard nothing bad from anyone else so I thought that the MySQL developers must have optimised the software well enough for simple expressions (as I know in general regular expressions should be used as a last resort due the speed penalty imposed by the use of the reg exp engine), obviously that's not remotely true! Doubt Ill ever use a REGEXP in SQL again if I can avoid it! :)

CJPinder:

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'.

I've just opened my New Riders MySQL book for the first time in about 5 years.. I think it's probably a good idea for me to create a test URI mapping database table of about 30000 records (or any amount that won't fit/be cached in memory) and try out a few options to see which is fastest.. I'll try to get time to do that later in the week and will include the quickest/most efficient index structure overall in a new version of the software.

Thanks for taking the time to provide your feedback, it's greatly appreciated.

All the best..

Conor
ceon

I am posting this in an honest effort to help the poster...

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.
These are NOT for SEO, they are useful for usability. In fact, the issue of comparative load speed negates even that as speed is for SEO.

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

Issues remaining is still in part your webhost, as these parse times are somewhat slow.

Parse Time: 1.083 - Number of Queries: 322 - Query Time: 0.25248613789368

PageSpeed score 66/100

Still to be corrected for improvement:

1. 105 Errors, 10 warning(s)
2. Image optimization - This one for example (Header_down.jpg) is 975.99kb, and is loading but does not appear in the loaded page
3. Coded links within your pages to non www pages causing additional host lookups
4. Loading 7 individual stylesheets
5. Sloppy CSS which results in very large file sizes, optimize these
6. Unoptimized JS (categories_css.js), which is also loading in the body rather that the <head>

While rewriting your urls does not help you rank, speed does.... and it is a growing part of Google algorithm.

~Melanie

Regarding trailing slashes and their omission from URLs:

The trailing slash denotes a folder URL, one that will serve the folder index.

URLs for HTML pages do not have a trailing slash, and do not have to have an extension.

This is mentioned in the HTTP specs.

For "pages" it is also common for "with slash" URL requests to be redirected to "without slash" to prevent Duplicate Content issues.

For "folders", the server's own inbuilt default redirect acts to add the slash on when a folder name is requested without the slash.

g1smd:

For "folders", the server's own inbuilt default redirect acts to add the slash on when a folder name is requested without the slash.... unless the server's config has been altered. Some server configs do not follow the default described.

Thank you so much for the comments given by all the experts here.
Today, i have come across a new problem related with either 'cache' or session'. As soon as i go for checkout after adding product in the cart, the message "Your shopping cart is empty" displays.

I have gone through different threads related with this issue but none of them helped me out.

Note:
I have already applied 'fix_cache_key.php' and it has updated the path in "Session Directory". Further if i apply, it shows "No change requires".

Please suggest me what else i can do to resolve this issue.

mprough, you have given wonderful analysis. I'll definitely going to fix the warnings and errors.

Hi Fawad,

How are you?

I sent you a PM about checking out various options for quicker database access with your URI mappings but haven't heard from you.. just checking if you got it okay? (I know I don't always get the notifications for various reasons so thought it best to check).

All the best..

Conor
ceon

I'm really sorry for late response. Actually i missed that PM. Can you please send that again, if you don't mind?