Site is very slow on dedicated hosting - Ceon URI Mapping

[CJPinder]

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.

[conor]

Hi Christian,

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! :)

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.

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!

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! :)

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!

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

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.

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

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! :)

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

[mprough]

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

[g1smd]

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.

[DrByte]

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.

[fawad123]

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.

[fawad123]

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

[conor]

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

[fawad123]

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