miniBB ® 

miniBB

®
Support Forums
  
 | Start | Register | Search | Statistics | File Bank | Manual |
Specific miniBB Support Forums / Specific /  
 

Database performance optimizing

 
Author Vodicka
Partaker
#1 | Posted: 16 Oct 2009 11:38 
Hi there!

I run www.omlazeni.cz

The database size is 300MB, we have 650 000 posts, 12 500 topics and 17 000 registered users.

We have just moved to VPS and I think of dedicated server so the computing power is not or will not be problem, also we have plenty of disk space available.

Still, I realized it is high time we thought about some database performace optimizing.

I dont understand databases, but I have read something so these are my thoughts:

- Index over post_text might improve search
- Multiple indexes confuse me. I dont know why they are there. Let me quote phpMyAdmin:

"The following indexes appear to be equal and one of them should be removed: topic_last_post_id, topic_last_post_id_2"


I seek help in two forms:

1. Free tips of how layman might make some quick fixes. (Like "Yes, remove the multiple indexes, nothing bad can happen and it will improve performance a bit.")
2. Mayby paid help from team (Paul?), if there is some real room for database performance improvement.

I forgot to mention that the performance for normal users is quite OK, but for admin and mods it is sometimes painstakingly slow - when we move or delete topics or posts...

Author Paul
Lead Developer 
#2 | Posted: 19 Oct 2009 04:49 
Multiple indexes were dropped in one of the earlier miniBB releases, you may follow updates history to get a precise version. They are left by mistake and one of them should be removed. Though this is related only to topic_id or post_id indexes as I remember. topic_last_post_id_2 doesn't belong to miniBB. I don't know where it comes from.

The command for dropping an index is quite simple, and it should be run from the SQL command window:

drop index INDEX_NAME on TABLE_NAME;
Regarding post_text index - you probably mean FULLTEXT index. It has no effect with miniBB. FULLTEXT is still a very experimental index on mySQL side, and it probably will work only with English encoding on full capacity. I never checked it with non-English texts. Its algorithm also pretty differs from what miniBB search provides.

We agree that on large databases miniBB search may be not sufficient at the moment, but it appears more limitation of mySQL and how it allows to search within a database by a free text. If you want full power on text search, it's better to use some external engine like Google's search or something similar. Searching within tons of text is not the task for a small software.

You can't improve anything with moving or deleting of posts. When you delete just one message, there are at least 5 other operations like UPDATE involved. UPDATE or INSERT operations are most heavy for mySQL. In you feel mySQL works slow, you need a server dedicated for mySQL only.

Author Vodicka
Partaker
#3 | Posted: 19 Oct 2009 06:26 
Thank you Paul!

Author tom322
Active Member
#4 | Posted: 19 Oct 2009 13:45 
Vodicka:
The database size is 300MB, we have 650 000 posts, 12 500 topics and 17 000 registered users.
Did you run this all on a shared host? If so, I must say the host was very good ;)

Author Vodicka
Partaker
#5 | Posted: 19 Oct 2009 16:54 
tom322:
I must say the host was very good ;)
I will tell them...

Specific miniBB Support Forums / Specific /
 Database performance optimizing
 Share Topic's Link

Your Reply Click this icon to move up to the quoted message


  ?
Post as a Guest, leaving the Password field blank. You could also enter a Guest name, if it's not taken by a member yet. Sign-in and post at once, or just sign-in, bypassing the message's text.


Before posting, make sure your message is compliant with forum rules; otherwise it could be locked or removed with no explanation.

 

 
miniBB Support Forums Powered by Forum Software miniBB ® Home  Features  Requirements  Demo  Download  Showcase  Gallery of Arts
Compiler  Premium Extensions  Premium Support  License  Contact Us
Check out the Captcha add-on: protect your miniBB-forums from the automated spam and flood.


  ⇑