vb.org Archive
Page 30 of 56 « First 202829 30 313240 Last »
Show 40 post(s) from this thread on one page

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vBulletin 3.7 Add-ons (https://vborg.vbsupport.ru/forumdisplay.php?f=228)
-   -   Miscellaneous Hacks - Yet Another Awards System (https://vborg.vbsupport.ru/showthread.php?t=187600)

Pixel-Peeps 09-29-2008 11:08 PM
Great work this is my fave MOD! !

masterross 09-29-2008 11:10 PM
Quote:
Originally Posted by Xanlamin (Post 1606910)
Originally Posted by masterross https://vborg.vbsupport.ru/external/2011/11/11.gif
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 09-30-2008 04:07 AM
Quote:
Originally Posted by masterross (Post 1633527)
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 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 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 09-30-2008 06:32 PM
Quote:
Originally Posted by masterross (Post 1633527)
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 10-01-2008 10:51 AM
Quote:
Originally Posted by liwo (Post 1634106)
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 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 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 10-01-2008 10:29 PM
Quote:
Originally Posted by liwo (Post 1634934)
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 :confused:


All times are GMT. The time now is 10:09 PM.
Page 30 of 56 « First 202829 30 313240 Last »
Show 40 post(s) from this thread on one page

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01644 seconds
  • Memory Usage 1,767KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (13)bbcode_code_printable
  • (5)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (4)pagenav_pagelink
  • (2)pagenav_pagelinkrel
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (10)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.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/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.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
  • printthread_start
  • pagenav_page
  • pagenav_complete
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete