Welcome, Guest. Please Login or Register.
September 23, 2017, 08:30:18 PM
Home Help Search Login Register
News: If you are still using YaBB SE, please consider upgrading to SMF as soon as possible.

YaBB SE Community  |  English User Help  |  FAQ  |  Topic: Large message boards and MySQL logs of slow response « previous next »
Pages: 1 [2] 3 4 ... 10 Reply Ignore Print
Author Topic: Large message boards and MySQL logs of slow response  (Read 61582 times)
beneluxrail
Noobie
*
Posts: 17



Re:Large message boards and MySQL logs of slow response
« Reply #15 on: April 11, 2002, 04:05:12 PM »
Reply with quote

My server was so slow that i implemented it on the live dbase. Lost users because of it. Cant believe not a lot of other people had this problem to....
Logged
mediman
Support Team
YaBB God
*****
Posts: 2858



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #16 on: April 11, 2002, 04:07:07 PM »
Reply with quote


Wow way to go!!! This did do the job!!! Perfect!! (Why was this not in the 1.3.0 final release, or am i saying something sily now??)


itīs not done with add indexes!
Logged

mainComm Dev Team
Joseph Fung
Global Moderator
YaBB God
*****
Posts: 4512


Keep smiling: it makes others nervous.


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #17 on: April 11, 2002, 04:48:24 PM »
Reply with quote

Why wasn't it implemented? It was merely one of those things that was overlooked.

feel free to add the indices Hypocrite - it won't hurt.
Logged

AnalogDuck
Jr. Member
**
Posts: 54


I'm a Ducky! Quack quack...


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #18 on: April 11, 2002, 06:13:29 PM »
Reply with quote

mediman, what do you mean precisely by,

Quote
itīs not done with add indexes!


???

I don't get your point.

But don't get me wrong I do understand the myriad of things overlooked in any release.  It's inevitable.

I just hope there's a 1.3.2 coming out at some point.  I've done quite a bit of fixing 1.3.1 stuff at this point.
Logged

-- What does an "analog duck" sound like anyway?  That's the kind of noises I want to make... ;)
mediman
Support Team
YaBB God
*****
Posts: 2858



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #19 on: April 11, 2002, 06:52:29 PM »
Reply with quote


mediman, what do you mean precisely


no, at the moment it will work if you only add this indexes, itīs a organized version of your columns in your tables so that a record will be found only by a look in the index, with the pointer which can found there mysql goes directly to the row! thats speeds up a lot!

but, the queries will not lesser or less complex!
full text search also will not profit !

thats why...
Logged

mainComm Dev Team
Ralph Button (rbutton)
Noobie
*
Posts: 12


Just a guy that is still in love with his wife!


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #20 on: April 11, 2002, 08:19:50 PM »
Reply with quote

I posted my finding on the indexes mostly to add to the information on the forum.  Also as I have about 28,000 posts in 3 months on the forum I manage.  I really had to consider what to do.  By occupation I'm a senior dba for Oracle designing databases for large corporations so figured I have a better view of what to look for in a database than most.  Also as I moved to a dedicated server, for the forum, I was given the "keys" to the server to do whatever I wanted to.  Thus I could trace, turn on logging, etc to see what was happening.  I've always thought the code of the forum was really good but it needed help getting to the data in a timely fashion.  I only wanted to add to the already great job the developers did in creating this software package.  

I also posted the memory settings I adjusted for MySql.  Many will not beable to use them as I would expect your are on shared servers.  BUT those were the key variables I found that affected the performance of the forum software.  The dedicated server I'm running on has 1 gig of memory which allowed for me to adjust the memory settings to the level I did.  Again I do NOT expect that most on their server could set them that high but those, in my view, are some of the key variables in the setting up of MySql database.

Now I can go back to fixing up the forum with new features, looks, etc.  As the users are all happy campers now with the major improvement in speed.  I'm not a programmer but can stumble around in the code to find what I'm looking for.  I can help with databases but not much else!
Logged
mediman
Support Team
YaBB God
*****
Posts: 2858



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #21 on: April 11, 2002, 08:27:28 PM »
Reply with quote

and it was a great help! thx a lot rbutton!

medi
Logged

mainComm Dev Team
Hypocrite
YaBB God
*****
Posts: 692


For To End Yet Again


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #22 on: April 12, 2002, 12:42:06 AM »
Reply with quote

Well, I made the backup and ran the queries. Everything went smoothly and the speed difference is very noticable.
Logged
David
Destroyer Dave
Global Moderator
YaBB God
*****
Posts: 5761


I'm not a llama!


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #23 on: April 12, 2002, 01:00:02 AM »
Reply with quote

I did it too and it worked.  Can't see any difference since the board I did it to has one member and one post.
Logged

Mach8
Sweetie
Beta Tester
YaBB God
*****
Posts: 1218



Re:Large message boards and MySQL logs of slow response
« Reply #24 on: April 12, 2002, 05:33:03 AM »
Reply with quote

Just made the appropriate changes and in my opinion the board is a lot faster (it was MUCH slower than 1.1.0 before, now it's about the same speed). Just got to see what my forum members think (who were complaining about the slow speed) :-\
Logged
Jan Houtsma
Noobie
*
Posts: 31



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #25 on: April 13, 2002, 05:15:22 PM »
Reply with quote


I have created the following indexes and find that I have a 7X improvment in response on the forum!!!!!
Also MySQL no longer has listed 8,000 slow queries PER DAY - I'm now down to 2 queries in the slow query log.

alter table topics add key (isSticky);
...


I did not issue any of these lines above in my board which is up to date to the latest version including Jeff's bug fix download. When i look at the tables with phpMyAdmin all these keys are already there....
Maybe i do not understand the this post. Maybe someone can explain?
Thanks,

Jan
Logged
mediman
Support Team
YaBB God
*****
Posts: 2858



WWW
Re:Large message boards and MySQL logs of slow response
« Reply #26 on: April 13, 2002, 05:35:59 PM »
Reply with quote

maybe jeff or another dev updatet the *.sql ???
newer mysql versions add automaticaly indeces if you use CREATE TABLE (but not this one!)

but look here

old yabbse db table messages

KeynameUniqueFieldAction
PRIMARYYesID_MSGDrop
ID_TOPICNoID_TOPICDrop

important new index in new sql is postertime

KeynameUniqueFieldAction
PRIMARYYesID_MSGDrop
ID_TOPICNoID_TOPICDrop
posterTimeNoposterTimeDrop

medi
« Last Edit: April 13, 2002, 05:38:52 PM by mediman » Logged

mainComm Dev Team
Ben_S
Disciple of Joe
Support Team
YaBB God
*****
Posts: 1586


I Love YaBB SE!


WWW
Re:Large message boards and MySQL logs of slow response
« Reply #27 on: April 13, 2002, 06:10:41 PM »
Reply with quote

Done it to my 36687 post board and it seems to have sped it up a bit :)

Ta
Logged
Greg Robson
Training to be like Joseph
YaBB SE Developer
YaBB God
*****
Posts: 1459


Hello!


WWW
Re:I really should of looked at this board before...
« Reply #28 on: April 14, 2002, 04:01:53 AM »
Reply with quote

As the subject says - I've come a bit late to this conversation.

Yes the indexes are bound to speed things up and I'm very impressed with speed differences peopel have experienced. As the self nominated database designer I can tell you that I have been spending many hours looking at the DB schema. I have found the following:

1. It lacks indexes
2. Some of the numeric fields are way to big (I don't think it's realistic to expect 4,200,000,000 users - do you?)
3. It lacks normalisation (Polls needs splitting to enable the changing of the number of items easily).

I'm only half way through a fairly thorough analysis and I'm beginning to work on a new schema. Whoever writes the convertor will probably have several headaches before they finish but the end result will be a much faster YaBB  :) :) :)

Hope that explains the situation a bit more :)
Logged

You can't have everything... where would you put it? -- Steve Wright
Mach8
Sweetie
Beta Tester
YaBB God
*****
Posts: 1218



Re:Large message boards and MySQL logs of slow response
« Reply #29 on: April 14, 2002, 07:13:16 AM »
Reply with quote

In addition to the database changes listed above, I also took out MostMaster's image resizer from the forum code. I don't need this particular function on my forum, and it has greatly increased in speed, especially on threads with many posts (and many sigs).
Logged
Pages: 1 [2] 3 4 ... 10 Reply Ignore Print 
YaBB SE Community  |  English User Help  |  FAQ  |  Topic: Large message boards and MySQL logs of slow response « previous next »
 


Powered by MySQL Powered by PHP YaBB SE Community | Powered by YaBB SE
Đ 2001-2003, YaBB SE Dev Team. All Rights Reserved.
SMF 2.0.12 | SMF © 2016, Simple Machines
Valid XHTML 1.0! Valid CSS!
Page created in 0.044 seconds with 19 queries.