miniBB ® miniBB®
miniBB Support Forums
 | Forums | Register | Reply | Search | Statistics | Manual |
Specific miniBB Support Forums / Specific /   

Database performance optimizing

Author Vodicka
Registered
#1 | Posted: 16 Oct 2009 11:38 | Edited by: Vodicka 
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 | Edited by: Paul 
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
Registered
#3 | Posted: 19 Oct 2009 06:26 
Thank you Paul!

Author tom322
Registered
#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
Registered
#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 Top

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

 Short link for this topic:

 ?
Only registered users are allowed to post here. Please, enter your username/password details upon posting a message, or register first.


Before posting, make sure your message is compliant with our forum posting rules. If not, it may be locked or deleted with no explanation.
 
miniBB Support Forums Powered by Forum Software miniBB ® Features  Requirements  Demo  Download  Showcase  Gallery of Arts
Compiler  Premium Extensions  Premium Support  License  Contacts
Check out the Captcha add-on: protect your miniBB-forums from the automated spam and flood.
Captcha Addon for miniBB