WAYYYY too many slow SQL queries, killing SQL server.

uninstalled

When this error happens?

Once pages are cached, users shouldn't notice much difference in load times.

Was your mysql server getting killed once you installed this on the forum linked to in your sig?

It doesn't matter at this point, I have no intention of reinstalling this mod.
Your mod caused hundreds of slow sql queries, which, of course, caused massive problems with the server.

So the SQL am i adding that to the current database that my forum uses?

the initial caching of a page is slow, we found 3 - 10 seconds. After that we found pages were generated almost as fast as standard pages eg; not noticeable when browsing.

You only have a hand full of posts. If it caused, as you put it "massive problems" i'd say you need to find a decent host or get a more powerful server. Maybe you could post some detail about the "massive problems" so dave or his off sider could comment?

It's not the host, or the server that is causing the problem, the mod is.
Stop trying to make assumptions about that which you know nothing of.

Prior to installing this mod, the forum used 1-5 mysql processes constantly. SINCE using the mod (before removing it), the forum jumped from 1-5 mysql processes constantly to 50-70. There is no reason for this except the mod. Once the mod was disabled and removed? Back to 1-5 mysql processes.

This mod handles sql VERY poorly and causes issues. It's not the server's fault the mod is slow, it's the mod's fault. It's not the server's fault the mod handles queries slowly, it's the mod's fault. It's not the server's fault the mod takes forever to work, it's the mod's fault.

Stop trying to blame things which you know nothing of. The server wasn't under load at all, in fact, it was just fine. The mod makes entirely too many slow queries, end of story.

I've installed this on 3 forums so far, one has 7 million+ posts, the other 3.5million and the smallest 1.1 million. 1,500+ active user sessions are common on all of them.

MySQL usage obversely increased, there were no noticeable effects on server performance from a users POV. Average load floats between 1 and 2 on all of our servers, none of them have "massive problems".

You have 2,300 posts in the forum linked in your sig. You must be able to see why I suggested server or host maybe not up to scratch. You inital post was vauge so I went for the low fruit ;)

The mod isn't perfect, but it does a reasonable job. It's very easy to install and once translated pages are cached, they load fast enough for a user not to notice a speed difference between translated and non-translated pages.

regards

One of our forum members has a lot of experience in this area. I will ask for his suggestions and get back to you on this.

As for Japanese, so far we have found the Yahoo translation service much better than Google's service. However, I have no idea about the API service.

Wen i use Save the plugin and activate.

When you post lines of code errors, it is a bit "meaningless" if you don't also post the exact version of vB you are running, etc.

You are posting an "error" from a function outside of this mod. What is the calling function?

I'm at this part now


Could someone past what this line is supposed too look like after the edit

(Deleted by imported_silkroad)

OBTW: Is the duplicate entry in the sitemap error by Google still unresolved?

There is no any slow query :) There is no any joins. Each query takes data only from 1 table. Each select takes indexed data, so answer is immediate :) Learn more about DB :)

You now such word as: hypocrite? Did you even look ad this mod DB structure? Did you even look at this mod SQL queries?

As I wrote - there is no any slow query. Queries are instant, cause each data in where cause is indexed and data is taken without any joins. This is fastest kind of queries in any DB engine :)

So take advice of your own and:
Stop trying to blame things which you know nothing of

:D

Make sure you copied all plugin content and you set appropriate hook.

Coment the line:
:)

Still have no idea :) As I wrote sitemap is not generated by this mod, but by vbSEO Sitemap Generator, so question should be for their staff. I don't care enough to ask them ;)

Wow, you really are clueless, aren't you?
Debugging PROVES that this mod caused the slow queries.

Previous to your mod:
Forum has 1-5 queries constantly running through mysql. Yeah, that's all, just 1-5, and that's normal and natural

Your mod installed:
Within 24 hours of having your mod installed, that 1-5 queries ran up to 50+, ALL with excessive time .

AFTER your mod removed
Forum has 1-5 queries constantly running through mysql. Yeah, that's all, just 1-5, and that's normal and natural


Don't even start with me and "that which you do not know". I develop and debug php/sql software for a living, it's what I do. I know full well how to develop, test, and work with software. In this case, the cause of the slow queries is quite evident. Just because you don't want to admit your product has this problem doesn't mean it's not so.

This product has nothing but slow queries. 10s response times? Yeah right, try 10 minutes, IF you're lucky.

Are we saying the mod is not workable in any shape or form?

Nope, some guy with a tiny little forum posts vague replies. Which frankly, are no help to anyone. If I had a 3,000 post forum that suffered "massive problems" after installing this mod, I to would be extremely angry at my host or server administator. His attitude is perfectlly understandable ;)

If your website has appeal to people who's primary language isn't English, I reckon you'd be mad not to give it a go. Most of the people how have posted in this thread seem to be happy to share information or help each other.

Oh! You should care ;)

Here is the reason why:

Google Sitemap Update Frequency and Sitemap Stats

Proper site maps make a huge difference when indexing :D

LOL :D You are funny guy :) Especially with this "Don't even start with me". You have very huuuuuuuuuuge ego.

I was never discuss number of queries with you :) Keep focused! Or you don't have to because you are debug php/sql software for a living :D

You still didn't tell do you even know what kind of queries are in this mod. Can you show me which query is such slow for you and what is the time of that query? Or just debugging php/sql software for a living you don't have to identify the problem? :D

Are you able to point at least 1 slow query o master of masters who debug php/sql for a living?

Thanks for laugh which you gave me :D :up:

Good point - thanks :up: :)

Thanks. Yea, the problem is that there is no other way (that we know of) to inform GoogleBot of the update frequencies, it is really inefficient to have Google just crawling for the sake of it without any "brains", especially for large forums.

Here is my other suggestions / recommendation for the future:

• Using mod_rewrite, rewrite /forum/....html?hl=foo to be either /forum/foo/...html or /forum/....-foo.html

Modify vBSEO sitemap to change the way they have suggested indexing the flags:

• Do not sitemap index like this:

Index like this (keep original content in tact):

and then do an entire language as a group:
Repeat.

The reason for this is that it is easier to manage the (long term) indexing process if the language links are all nice and neat in a few sequencial sitemap files v. interwoven around each link. (The "interwoven process" in a nightmare, quite frankly).

Sorry, but the solution offered by the vBSEO team, while better than nothing, is not very manageable for large boards if you care about the convergence of Googles index with your sitemap. (Everyone should care if they care about SEO).

We track this on a weekly basis and have currently have nearly 92% convergence. Google does not keep dumping and recrawling, etc. (as it currently happening with the translated links) because there is no update frequency associated with the hl=foo links :erm:

Cheers.

Actually, that is not true. We run this mod on a large board and have benchmarked it.

Yes, it adds a lot of time (slow queries) when a page is first translated; but after it is translated (and snug-as-a-bug-in-a-rug in the dB), it adds less than 100ms to a showthread query.

Naturally, since it must go across the network to sing the Google translation API song, that query will be very slow.

It's OK to beat up on these guys, since the code is free and you don't seem to understand it yet. ;-) They are used to it, but don't expect flowery replies when you beat them up ;)

I think they have done an outstanding job, and have donated one, of perhaps many, donations to these unsung heros of increased web traffic :-) In addition, I nominated this the "MOTM"! It is an amazing mod, and hopefully, will get better over time, if folks don't beat up the coders and chase them away!

That is also our experience with it :)

As I understood wolfstream, he was talking about DB SQL queries.

As you just wrote when page is first time translated long translation time is caused by http queries (requests) to Google for translation, not by SQL queries to DB (which are instant) :)

We are not able to make Google faster ;)

Also - thanks again for donation ;) :up:

After working with this mod for three weeks, I have dropped the Google Sitemap method suggested by the vBSEO team. That method, well intended, was a quick "kludge" which was not optimal for this type of application.

What I have done is easy and requires a small bit of manual labor and goes something like this:

1. Copy the *xml.gz files from ./vbseo_sitemap/data to another directory, for example FORUMROOT/es for Spanish, FORUMROOT/ja for Japanese, FORUMROOT/zh-CN for Chinese etc.
   
2. Unzip the files and use sed to add the ?hl=ja (or whatever flag you want to do) to each URL in the Sitemap. This takes about 10 seconds.
   
3. Update sitemap_index.xml.gz the same way, or use VI, etc.
   
4. Submit this Sitemap to Google.
   
5. Copy the first one you did and repeat for as many languages as you wish.

This method has many advantages.

First of all you have a completely different sitemap of your entire site for each language. So easy to submit to language specific search engines. Also, you can easily track the indexing progress for each Sitemap. This is much easier to manage and much cleaner, IMHO.

Of course, this method takes a bit of work when your need to update your language Sitemaps, but if you have a large board, this will get you indexed nicely in a well organized way. You can add the newer links after a high percentage of the legacy links are archived (in a few months).

We added the top 10 languages to Google Webmaster Tools, each with its own Sitemap, so where we originally had one big sitemap with nearly 396K URLs, we now have a total of around 4,750K URLs total in 11 Sitemaps. So far, Google is happy :-)

With this simple method, you can see the index progress on each language. You can submit your Sitemaps to language specific search engines. You can manage the update frequency on the translated URLs differently than your main site. You can also avoid any potential problems with your main sitemap.

(See attachment)

Enjoy and Good Luck!

I forgot to add, for the next "trick" I will write the URLs in the new Sitemaps as

versus

etc....

and then add a very simple mod_rewrite rule to rewrite FORUMROOT/flags/blahblah.... to FORUMROOT/blahblah?hl=flag

:-)

Great idea! keep in mind this will effect paths to css and images on some forums.

Note:

We are definately seeing a sort of "Google penalty" for using ?hl=flag (duplicate content, it seems -- at least in the Sitemaps)

As Google indexes the various language sitemaps, it is subtracting indexed links from the main sitemap ... so I will need to rewrite the URLs sooner-than-later !

I think it was temporary Google problem - I change nothing and right now my sitemap is clear of duplicate content errors :) Just checked.

<font color="Red">EDIT: DOES NOT WORK YET. DO NOT USE THE 'BRAINSTORMING' IDEA HERE</font>. - imported_silkroad

Cheers NLP-er and Thanks.

I think we will vBSEO rewrite, something like:

'^(.+?)\.html\?hl=(.+?)$' => '$2/$1.html'

Do you think it this work?

Or maybe just manually change the sitmaps (as above #591) and try:

'^ja/(.+?)\.html$' => '$1.html?hl=ja'
'^ru/(.+?)\.html$' => '$1.html?hl=ru'
'^ko/(.+?)\.html$' => '$1.html?hl=ko'

yadda, yadda, yadda ..

Maybe?

just installed this...works great and easy setup too

thanks ;)

Did you add this to your custom rewrite rules and custom redirects?

I checked first one '^(.+?)\.html\?hl=(.+?)$' => '$2/$1.html' and it crashed my forum :D So I commented it.

The best way would be to redirect internally URLs like country/rest to rest?hl=country.
Internally I mean without changing URL in browser (without sending header).
And redirect URLs like rest?hl=country to country/rest with 301 header.

It would be best because old, already indexed addresses will work. Redirect will made reindexing faster (I think :)) and you avoid possibility of duplicate content penalty if same content is available in booth URL's. In same time it would be good for this mod because no changes would be required at all. Unfortunately I'm not expert in .htaccess file or vbSEO custom rewrite rules, so I don't know does it is possible. For sure it is possible to redirect one address to other, but can it be done internally?...

Sorry about that!

I tried something similar in .htaccess after posting above and it crashed here too.... driving the load average of the server to outer space :o

More later ..... :confused: