The Arcive of Official vBulletin Modifications Site.

Yet Another Awards System · **Released**: 08-08-2008

A lot of you have been waiting for this forever to work 100% with 3.7 due to the new profile tabs...Well wait no longer, here is the new revises version of HacNho's YAAS (Yet Another Awards System).

DESCRIPTION:
This is a Medals/Awards system. Admin can give members awards, and award icons will be displayed in member's profile, posts, as well as in a award list.

FEATURES:
In AdminCP
- Can Create/Edit/Delete/Reorder categories/sub-categories in ACP (with category name and description)
- Can Create/Edit/Delete awards in ACP (with Award Name, Description, Icon URL, Image URL)
- Can Move one (or all) award(s) from one category to another
- Can set some awards un-classified (not displayed for public)
- Can re-order awards in awards showcase
+ Issue awards to members, based on username or userid, with Issue Reason
+ Remove awards from members

In ModCP
+ Issue awards to members, based on username or userid, with Issue Reason
+ Remove awards from members

Front page
- Display awards list in with: Award Name, Description, Icon, Image, and Members who get each award.
- Display awards showcase in profile, with Award information, Issue time and reason
- Display award icons in postbit (showthread, showpost, announcement, private)

CHANGES/MODIFICATION:
- Tables to add: 3 (award, award_user, award_cat)
- Files to add: 4 (/awards.php, /award_request.php, /admincp/award.php, /admincp/award_cat.php)

DIFFICULTY:
Easy
Time: about 5 minutes
Installer is included (1 product XML)

CREDIT:
Idea has been carried on by many people, here are some:
- Lesane for original Award hack for vB2, eventhough I've never use his hack
- AnimeWebby for Awards/ Medals/ Cards Hack [vB3], which I write new code based on his.
- Mac ycl6 for phpBB Medal System for phpBB forum
- Dr Erwin Loh for FORM TO THREAD/ FORUM/ PM/ EMAIL hack
- Cypher for helping with the tabs in 3.7
- liwo for re-writing plugins
- and all others for supports and ideas

And most of all to HacNho

This MOD is 100% Supported.

Authors of YaAS 3.7:
Xanlamin
CypherSTL

Current Release: v3.7.4

Pixel-Peeps · #**292** 09-29-2008, 11:08 PM

Great work this is my fave MOD! !

masterross · #**293** 09-29-2008, 11:10 PM

Quote:

Originally Posted by Xanlamin

Originally Posted by masterross

Hello,

Thanks for 3.7.x release but it still has the same not indexing joins:

this is only for 10min works!

This will be fixed in the next release.

The problem still exist....

Code:

[--] Up for: 16m 54s (56K q [55.506 qps], 2K conn, TX: 2B, RX: 14M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 602.0M global + 5.7M per thread (250 max threads)
[OK] Maximum possible memory usage: 2.0G (49% of installed RAM)
[OK] Slow queries: 0% (0/56K)
[OK] Highest usage of available connections: 3% (8/250)
[OK] Key buffer size / total MyISAM indexes: 64.0M/178.0M
[OK] Key buffer hit rate: 99.5% (2M cached / 14K reads)
[OK] Query cache efficiency: 59.5% (28K cached / 47K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[!!] Joins performed without indexes: 353
[OK] Temporary tables created on disk: 5% (98 on disk / 1K total)
[OK] Thread cache hit rate: 99% (8 created / 2K connections)
[OK] Table cache hit rate: 95% (238 open / 248 opened)
[OK] Open file limit used: 4% (431/10K)
[OK] Table locks acquired immediately: 99% (45K immediate / 45K locks)

CypherSTL · #**294** 09-30-2008, 04:07 AM

Quote:

Originally Posted by masterross

The problem still exist....

Code:

[--] Up for: 16m 54s (56K q [55.506 qps], 2K conn, TX: 2B, RX: 14M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 602.0M global + 5.7M per thread (250 max threads)
[OK] Maximum possible memory usage: 2.0G (49% of installed RAM)
[OK] Slow queries: 0% (0/56K)
[OK] Highest usage of available connections: 3% (8/250)
[OK] Key buffer size / total MyISAM indexes: 64.0M/178.0M
[OK] Key buffer hit rate: 99.5% (2M cached / 14K reads)
[OK] Query cache efficiency: 59.5% (28K cached / 47K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[!!] Joins performed without indexes: 353
[OK] Temporary tables created on disk: 5% (98 on disk / 1K total)
[OK] Thread cache hit rate: 99% (8 created / 2K connections)
[OK] Table cache hit rate: 95% (238 open / 248 opened)
[OK] Open file limit used: 4% (431/10K)
[OK] Table locks acquired immediately: 99% (45K immediate / 45K locks)

Where are you seeing that at?

masterross · #**295** 09-30-2008, 08:44 AM

u can see in any any mysql stats.
but I suggest using:
http://wiki.mysqltuner.com/MySQLTuner

for better visualization

regards

CypherSTL · #**296** 09-30-2008, 10:39 AM

Code:

-------- Performance Metrics -------------------------------------------------
[--] Up for: 103d 6h 0m 40s (6M q [0.772 qps], 501K conn, TX: 295B, RX: 1B)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 34.0M global + 2.7M per thread (100 max threads)
[OK] Maximum possible memory usage: 729.8M (36% of installed RAM)
[OK] Slow queries: 0% (0/1K)
[OK] Highest usage of available connections: 3% (3/100)
[OK] Key buffer size / total MyISAM indexes: 200.0M/11.0M
[!!] Key buffer hit rate: 22.1% (10K cached / 8K reads)
[!!] Query cache efficiency: 14.3% (27 cached / 189 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 30 sorts)
[OK] Temporary tables created on disk: 4% (3 on disk / 63 total)
[OK] Thread cache hit rate: 94% (3 created / 59 connections)
[OK] Table cache hit rate: 39% (865 open / 2K opened)
[OK] Open file limit used: 44% (1K/3K)
[OK] Table locks acquired immediately: 100% (1K immediate / 1K locks)
[OK] InnoDB data size / buffer pool: 12.4M/24.0M

I don't see that here.

liwo · #**297** 09-30-2008, 06:32 PM

Quote:

Originally Posted by masterross

The problem still exist....

Code:

[--] Up for: 16m 54s (56K q [55.506 qps], 2K conn, TX: 2B, RX: 14M)
[--] Reads / Writes: 83% / 17%
[--] Total buffers: 602.0M global + 5.7M per thread (250 max threads)
[OK] Maximum possible memory usage: 2.0G (49% of installed RAM)
[OK] Slow queries: 0% (0/56K)
[OK] Highest usage of available connections: 3% (8/250)
[OK] Key buffer size / total MyISAM indexes: 64.0M/178.0M
[OK] Key buffer hit rate: 99.5% (2M cached / 14K reads)
[OK] Query cache efficiency: 59.5% (28K cached / 47K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4K sorts)
[!!] Joins performed without indexes: 353
[OK] Temporary tables created on disk: 5% (98 on disk / 1K total)
[OK] Thread cache hit rate: 99% (8 created / 2K connections)
[OK] Table cache hit rate: 95% (238 open / 248 opened)
[OK] Open file limit used: 4% (431/10K)
[OK] Table locks acquired immediately: 99% (45K immediate / 45K locks)

That means that there are some indexes missing for a table that is used in a join. I had a quick look at the tables and think I found the missing indexes.

So please do a backup of your database and run the following MySQL querys (adding a table prefix as necessary):

Code:

ALTER TABLE `award_user` ADD INDEX `award_id` ( `award_id` )

Code:

ALTER TABLE `award_user` ADD INDEX `userid` ( `userid` )

These two statements create the probably missing indexes.

Code:

OPTIMIZE TABLE `award_user`

This statement actually fills the newly created indexes with the necessary data.

After doing this, check if the problem persists. If I guessed right it doesn't.

masterross · #**298** 10-01-2008, 10:51 AM

Quote:

Originally Posted by liwo

That means that there are some indexes missing for a table that is used in a join. I had a quick look at the tables and think I found the missing indexes.

So please do a backup of your database and run the following MySQL querys (adding a table prefix as necessary):

Code:

ALTER TABLE `award_user` ADD INDEX `award_id` ( `award_id` )

Code:

ALTER TABLE `award_user` ADD INDEX `userid` ( `userid` )

These two statements create the probably missing indexes.

Code:

OPTIMIZE TABLE `award_user`

This statement actually fills the newly created indexes with the necessary data.

After doing this, check if the problem persists. If I guessed right it doesn't.

Thanks bro!
Now it's much better!
But some indexes are still missing:

Code:

[--] Up for: 6h 1m 4s (2M q [112.642 qps], 106K conn, TX: 3B, RX: 552M)
[--] Reads / Writes: 82% / 18%
[--] Total buffers: 602.0M global + 5.7M per thread (250 max threads)
[OK] Maximum possible memory usage: 2.0G (49% of installed RAM)
[OK] Slow queries: 0% (0/2M)
[OK] Highest usage of available connections: 9% (24/250)
[OK] Key buffer size / total MyISAM indexes: 64.0M/175.9M
[OK] Key buffer hit rate: 100.0% (118M cached / 46K reads)
[OK] Query cache efficiency: 66.6% (1M cached / 2M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (103 temp sorts / 140K sorts)
[!!] Joins performed without indexes: 132
[OK] Temporary tables created on disk: 7% (3K on disk / 49K total)
[OK] Thread cache hit rate: 99% (24 created / 106K connections)
[OK] Table cache hit rate: 84% (347 open / 413 opened)
[OK] Open file limit used: 5% (539/10K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)

liwo · #**299** 10-01-2008, 07:09 PM

I found another one that should propably be added:

Code:

ALTER TABLE `award` ADD INDEX `award_cat_id` ( `award_cat_id` )

Again, optimize table to fill the index:

Code:

OPTIMIZE TABLE `award`

That's all I spotted now. To further track down the issue I would suggest to turn on slow query log on the mysql server and enable the option "log-queries-not-using-indexes", which logs the querys to the slow query log. If you are on shared hosting, ask your provider to enable these options for you (normally they do such things as you are about to take load off their server

). If you run your own server I hope you know how to do this :P (if not, have a look at the documentation on mysql.com, search for the options and add them to my.cnf)

Medar · #**300** 10-01-2008, 08:35 PM

Question on this mod...I have a previous version installed but am about to drop this one in place and upgrade. Mine is also fairly modified to suit our needs. One thing I am working on is creating a page that would pull users based on their group, display their registered date, and display their awards. IE -

Person 1 . . . . . 08/08/2008 . . . . . AWARD 1 - AWARD 2 - AWARD 5
Person 3 . . . . . 08/12/2008 . . . . . AWARD 1
Person 4 . . . . . 08/15/2008 . . . . . AWARD 2 - AWARD 5
Person 9 . . . . . 08/18/2008 . . . . . AWARD 1 - AWARD 2 - AWARD 5

I have the page already showing everything but the awards...and just started looking at the code for that. Fairly heavy SQL query...anyone have any quick idea on how that would look?

masterross · #**301** 10-01-2008, 10:29 PM

Quote:

Originally Posted by liwo

To further track down the issue I would suggest to turn on slow query log on the mysql server and enable the option "log-queries-not-using-indexes", which logs the querys to the slow query log.

Hi,

Thank for the advice!
I've enabled slow query log with log-queries-not-using-indexes but how to find out where are the missing indexes?
It store almost all queries

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.06106 seconds Memory Usage 2,336KB Queries Executed 26 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (13)bbcode_code (5)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)modsystem_post (1)navbar (6)navbar_link (120)option (1)pagenav (1)pagenav_curpage (4)pagenav_pagelink (2)pagenav_pagelinkrel (11)post_thanks_box (11)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (11)post_thanks_postbit_info (10)postbit (11)postbit_onlinestatus (11)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!