Miscellaneous Hacks - vB Global Translator - Multiply your indexed pages & put search traffic on autopilot

**Text Removed**

[racale]

done as funeral now when I click on the flags to me that this

https://vborg.vbsupport.ru/showpost....&postcount=294

Alter table wt_cache_short collate utf8_bin; ALTER TABLE wt_cache_short CHANGE tl tl VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL; ALTER TABLE wt_cache_short CHANGE originaltext originaltext VARCHAR( 50 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL; ALTER TABLE wt_cache_short CHANGE translated translated VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL; Alter table wt_cache_medium collate utf8_bin; ALTER TABLE wt_cache_medium CHANGE tl tl VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL; ALTER TABLE wt_cache_medium CHANGE originaltext originaltext VARCHAR( 255 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL; ALTER TABLE wt_cache_medium CHANGE translated translated VARCHAR( 1000 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL; Alter table wt_cache collate utf8_bin; ALTER TABLE wt_cache CHANGE tl tl VARCHAR( 10 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL; ALTER TABLE wt_cache CHANGE translated translated TEXT CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL; ALTER TABLE wt_cache CHANGE translated translated VARCHAR( 65000 ) CHARACTER SET utf8 COLLATE utf8_bin NULL DEFAULT NULL; CREATE TABLE saver ( id INT, tl VARCHAR(10), originaltext VARCHAR(65000) ) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE TABLE cleaner ( id INT ) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci; delete from cleaner; delete from saver; insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache group by originaltext,tl having count(*) > 1); insert into cleaner (SELECT cache.id from saver, wt_cache cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id); DELETE FROM wt_cache USING wt_cache INNER JOIN cleaner ON wt_cache.id = cleaner.id; delete from cleaner; delete from saver; insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_short group by originaltext,tl having count(*) > 1); insert into cleaner (SELECT cache.id from saver, wt_cache_short cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id); DELETE FROM wt_cache_short USING wt_cache_short INNER JOIN cleaner ON wt_cache_short.id = cleaner.id; delete from cleaner; delete from saver; insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_medium group by originaltext,tl having count(*) > 1); insert into cleaner (SELECT cache.id from saver, wt_cache_medium cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id); DELETE FROM wt_cache_medium USING wt_cache_medium INNER JOIN cleaner ON wt_cache_medium.id = cleaner.id; OPTIMIZE TABLE wt_cache, wt_cache_medium, wt_cache_short; alter table wt_cache_short drop index originaltext; create UNIQUE INDEX originaltext on wt_cache_short (originaltext, tl); alter table wt_cache_medium drop index originaltext; create UNIQUE INDEX originaltext on wt_cache_medium (originaltext, tl);



What should I do thanks

good job at all

[imported_silkroad]

Originally Posted by Dave Hybrid

Are you sure your not comparing a page never cached to one that is cached vs a normal page?

Yes, I am sure I am comparing cached pages, because I know how this product works (except for the developer details of the database cache design), and of course I know the difference of discussing performance of a page that is in database v. one that has not yet been translated.

I don't wish to get on your bad side, so I'll not comment further

Recently I worked directly with Amazon AWS on a CloudFront/S3 performance issue regarding "before" and "after" results for a website moving to Amazon AWS. We published the results.

In addition, I have a lot of experience tuning MySQL for our site, and can see numerous issues in the database performance before and after mods are installed. For example, a mod that uses blobs in the MySQL db table cannot be tmp cached to memory and must be tmp cached to disk, lowering performance .... etc etc.

As I mentioned, I want to stay on your good side, so I'll not comment further

[Dave Hybrid]

Originally Posted by imported_silkroad

Yes, I am sure I am comparing cached pages, because I know how this product works (except for the developer details of the database cache design), and of course I know the difference of discussing performance of a page that is in database v. one that has not yet been translated.

I don't wish to get on your bad side, so I'll not comment further

Recently I worked directly with Amazon AWS on a CloudFront/S3 performance issue regarding "before" and "after" results for a website moving to Amazon AWS. We published the results.

In addition, I have a lot of experience tuning MySQL for our site, and can see numerous issues in the database performance before and after mods are installed. For example, a mod that uses blobs in the MySQL db table cannot be cached and must be written to disk, lowering performance

As I mentioned, I want to stay on your good side, so I'll not comment further

I certainly wasnt trying to insult you, many do not understand how it works.

If you have any code suggestions I'd be happy to test them out. Thanks.

[imported_silkroad]

Originally Posted by Dave Hybrid

If you have any code suggestions I'd be happy to test them out. Thanks.

One of the things I noticed was that this mod uses blobs in the dB. After installing, my MySQL tuning scripts starting complaining that a high percentage of temporary tables were being written to disk v. memory. I doubled the size of both the heap and tmp table cache and it did not help giving the message (attached in the screen shot).

Note: I just rebooted MySQL, so the number of temp tables written to disk increases the longer this mod runs.

Cheers.

[Dave Hybrid]

@NLP-er - What's your opinion on this?

@silkroad - He's the database guy ;P

[imported_silkroad]

On a related note, I ran a number of comparisons similar to below(directly on the server where the db is located):

time curl http://URL1 > /dev/null
time curl http://URL1?hl=flag > /dev/null

(after the initlal translation of course)

My early results show that the translated files take about 40 - 50% more time to load than the untranslated files, FYI.

On average we would see around 170ms for a typical "before" time and around 240 ms for a typical "after" time.

(and the file size change did not account for the difference).

Just FYI.

[imported_silkroad]

PS: I have not drawn any conclusions, I just was commenting that I have noticed some differences in performance (before/after), so please don't be unhappy with me. Thanks.

[Dave Hybrid]

The thing is, this stuff cost money to develop and I dont see a few milliseconds as good value for money. What I'm saying is if you want to try to further the DB development then be my guest, but I am happy with it's current state and wont be myself.

Not being funny just being straight with you, I'm not a DB coder so can't comment in depth or do this myself.

[imported_silkroad]

Originally Posted by Dave Hybrid

The thing is, this stuff cost money to develop and I dont see a few milliseconds as good value for money. What I'm saying is if you want to try to further the DB development then be my guest, but I am happy with it's current state and wont be myself.

Not being funny just being straight with you, I'm not a DB coder so can't comment in depth or do this myself.

Hi Dave!

I agree, the most important performance stat, of course, is the increase of search engine referral traffic over time. We are completely "wired" to record any changes, so I am looking forward to reporting the results with some nice graphs :-)

[NLP-er]

Originally Posted by imported_silkroad

OK, I'll put that aside and change the subject.

Let's discuss Google indexing and translateflags.php.

If we assume that Google has limited resources to index a site, and that GoogleBot does not distinguish between the various language flags, then this seems to imply that it will be more efficient, and generate more search traffic, in the beginning of the translation process, to only expose the top ten or so Internet-language flags.

The reason for this is that if Google indexes all translation languages equally, Google will index languages with very small Internet-populations with the same priority of languages with high Internet populations.

So, unless I am wrong, which I could be it seems to indicate that to optimize search referral results, in the first month or two, a site should expose only the top 10 or so flags (from an Internet-usage perspective) and not expose the minor languages.

Is this how you see things as well?

I don't know google spiders algorithms, but if you will not expose some languages google definitively will not visit it