Win a copy of Node.js Design Patterns: Design and implement production-grade Node.js applications using proven patterns and techniques this week in the Server-Side JavaScript and NodeJS forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Rob Spoor
  • Tim Cooke
  • Junilu Lacar
Sheriffs:
  • Henry Wong
  • Liutauras Vilda
  • Jeanne Boyarsky
Saloon Keepers:
  • Jesse Silverman
  • Tim Holloway
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
Bartenders:
  • Al Hobbs
  • Mikalai Zaikin
  • Piet Souris

indexTable space utilization and maintenance?

 
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi. I'm new to this forum and JForum in general. I've searched for a forum topic similar to my question below, but couldn't find one. Please point me in the direction of the solution if this has been previously asked.

I'm a recently new hire as a database engineer in a software development company that has integrated JForum into their product offering. Reviewing some of our slow running application queries, many of them point to JForum. Which makes me question whether JForum is properly installed/configured and if there is any tweaking or maintenance that can be done to improve performance. Without any useful JForum documentation I'm resorting to posting my question(s) here.

We've integrated JForum into our Java/Grails application utilizing a MySQL database on the back end. In the past year on this installation, we've initiated 22 forums, that hosts 12,875 topics and a total of 66,460 posts. This doesn't seem to excessive to me. Our overall JForum database size is 11.3Gb of which the indexTable comprises approx. 11.2Gb ofindexTable space utilization and maintenance? that space (+99% of total used space). This table has more than 18 million rows which seems excessive based upon the overall size of the database and the number of posts. One of the simple queries that was identified as slow executing against the JForum database is "select name_ from indexTable where deleted_ = 1". I don't have any explanation as to why this query would be executed from within JForum or our application, but the slow query log this past week identified this query having been executed 19 times this past week for an average execution time of more than 125 seconds. The "deleted_" column is not indexed. I considered adding an index to it to improve this query, but thought I would check with others to determine if some other action should first be taken against this table or updating our installation configuration.

Thanks in advance for all responses and advice. Let me know if you need more information regarding our setup.

 
David Warton
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
.... Or to simplify things, can the rows in indexTable where deleted_ = 1 be deleted? This will reduce the number of rows in this table significantly.
 
Sheriff
Posts: 10445
227
IntelliJ IDE Ubuntu
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
David, welcome to CodeRanch!

That's the first time I'm hearing about a table named indexTable. Are you sure it's part of core JForum and not something your application might have added? What does its schema look like?
 
David Warton
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for your response.

Finally heard back from our development team that this is a table that they've added in order for JForum to function properly within our environment.
 
You can thank my dental hygienist for my untimely aliveness. So tiny:
Building a Better World in your Backyard by Paul Wheaton and Shawn Klassen-Koop
https://coderanch.com/wiki/718759/books/Building-World-Backyard-Paul-Wheaton
reply
    Bookmark Topic Watch Topic
  • New Topic