Large message boards and MySQL logs of slow response

[beneluxrail]

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....

[mediman]

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!

[Joseph Fung]

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.

[AnalogDuck]

mediman, what do you mean precisely by,

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.

[mediman]

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...

[Ralph Button (rbutton)]

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!

[mediman]

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

medi

[Hypocrite]

Well, I made the backup and ran the queries. Everything went smoothly and the speed difference is very noticable.

[David]

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.

[Mach8]

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)

[Jan Houtsma]

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

[mediman]

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

Keyname	Unique	Field	Action
PRIMARY	Yes	ID_MSG	Drop
ID_TOPIC	No	ID_TOPIC	Drop

important new index in new sql is postertime

Keyname	Unique	Field	Action
PRIMARY	Yes	ID_MSG	Drop
ID_TOPIC	No	ID_TOPIC	Drop
posterTime	No	posterTime	Drop

medi

[Ben_S]

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

Ta

[Greg Robson]

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

[Mach8]

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).