Author Archives: Jeanne Boyarsky

tuning jforum for javaranch

Posted on by
2

JavaRanch has been updating our forum software to an extension of JForum.  While testing, Ulf Dittmer noticed that the RSS feeds were incredibly slow – to the point of timing out.  He found that the database query itself was taking so long that the browser gave up.

JavaRanch Moose
Some of the queries built into JForum are written in a way that is fine for forums with a relatively small number of posts – even half a million posts total.  JavaRanch is much larger than that.  We imagine that we are the largest forum to use JForum.  (We have over 400 thousand threads/topics and over 1.7 million posts.)

I enjoy tuning queries, so I started looking at it.  When someone asked me about what I did, I thought it would be nice to blog about my thought process.  I’ve included the before/after queries and execution plans at the bottom so as not to break up the thought process.

1. Run original query at command line in our test environment (which is slightly smaller than the real data set, but close to realistic.)
2. Give up waiting after 5 minutes.
3. Run explain on the original query.
4. Cringe at the heap scan with a relative cost of 16628 – and worse within a nested loop.
5. Wonder why the query needs to do so much processing to get a limited number of rows.  The execution plan shows all that joining occurring on the entire table rather than just the 50 or so rows we care about (via the limit.)
6. Refactor the query to use a subquery to avoid this.  I had used this technique on another similar query the day before and gotten the query down to under 1000 cost units.
7. Run explain again.
8. See cost is 3807.  This is better, but there’s no logical reason it should be four times the cost of the query from yesterday.  Better keep tuning.
9. Hmm.  It’s using the index idx_topics_fp which is on topic_first_post_id.  We have almost 100 forums.  Wouldn’t it be more efficient to include the forum_id in the index.
10. Added index idx_topics_fp_forum on both topic_first_post_id and forum_id.
11. Run explain again.
12. See cost of 978.  Good.  This is about what I got on yesterday’s query.  I guess the built in indexes better matched what I needed on yesterday’s query.
13. Go back to GUI and try URL.  Takes a couple seconds.
14. Commit my changes.  I got a two order of magnitude increase on the query.  Very satisfying.

Original query:

PostModel.selectLatestByForumForRSS = SELECT p.topic_id, p.topic_id, p.post_id, p.forum_id, pt.post_subject AS subject, pt.post_text, p.post_time, p.user_id, u.username, u.user_first_name, u.user_last_name
FROM  jforum_topics t, jforum_posts p, jforum_posts_text pt, jforum_users u
WHERE p.post_id = t.topic_first_post_id
AND p.topic_id = t.topic_id
AND p.user_id = u.user_id
AND p.post_id = pt.post_id
AND p.need_moderate = 0
ORDER BY t.topic_first_post_id DESC

Modified query

PostModel.selectLatestByForumForRSS = SELECT p.topic_id, p.topic_id, p.post_id, p.forum_id, pt.post_subject AS subject, pt.post_text, p.post_time, p.user_id, u.username, u.user_first_name, u.user_last_name
FROM (select topic_first_post_id from jforum_topics where forum_id = ? order by topic_first_post_id desc limit ?) as nested,
jforum_topics t, jforum_posts p, jforum_posts_text pt, jforum_users u
WHERE p.post_id = nested.topic_first_post_id
AND p.topic_id = t.topic_id
AND p.user_id = u.user_id
AND p.post_id = pt.post_id
AND p.need_moderate = 0
ORDER BY t.topic_first_post_id DESC

Original explain

Row #         QUERY PLAN
1       Limit (cost=97566.69..97566.70 rows=1 width=504)
2       -> Sort (cost=97566.69..97566.70 rows=1 width=504)
3       Sort Key: t.topic_id
4       -> Nested Loop (cost=275.61..97566.68 rows=1 width=504)
5       -> Nested Loop (cost=275.61..97560.76 rows=1 width=473)
6       -> Nested Loop (cost=275.61..97556.05 rows=1 width=32)
7       Join Filter: (“inner”.topic_id = “outer”.topic_id)
8       -> Bitmap Heap Scan on jforum_topics t (cost=275.61..16628.72 rows=18174 width=8)
9       Recheck Cond: (forum_id = 1)
10       -> Bitmap Index Scan on idx_topics_forum (cost=0.00..275.61 rows=18174 width=0)
11       Index Cond: (forum_id = 1)
12       -> Index Scan using jforum_posts_pkey on jforum_posts p (cost=0.00..4.44 rows=1 width=24)
13       Index Cond: (p.post_id = “outer”.topic_first_post_id)
14       Filter: (need_moderate = 0)
15       -> Index Scan using jforum_posts_text_pkey on jforum_posts_text pt (cost=0.00..4.70 rows=1 width=449)
16       Index Cond: (“outer”.post_id = pt.post_id)
17       -> Index Scan using jforum_users_pkey on jforum_users u (cost=0.00..5.91 rows=1 width=35)
18       Index Cond: (“outer”.user_id = u.user_id)

Explain on modified query
Row #         QUERY PLAN
1       Sort (cost=3807.84..3807.94 rows=40 width=504)
2       Sort Key: t.topic_id
3       -> Nested Loop (cost=0.00..3806.78 rows=40 width=504)
4       -> Nested Loop (cost=0.00..3579.24 rows=40 width=500)
5       -> Nested Loop (cost=0.00..3342.41 rows=40 width=469)
6       -> Nested Loop (cost=0.00..3164.39 rows=40 width=453)
7       -> Limit (cost=0.00..2975.39 rows=40 width=4)
8       -> Index Scan Backward using idx_topics_fp on jforum_topics (cost=0.00..1351866.51 rows=18174 width=4)
9       Filter: (forum_id = 1)
10       -> Index Scan using jforum_posts_text_pkey on jforum_posts_text pt (cost=0.00..4.70 rows=1 width=449)
11       Index Cond: (pt.post_id = “outer”.topic_first_post_id)
12       -> Index Scan using jforum_posts_pkey on jforum_posts p (cost=0.00..4.44 rows=1 width=24)
13       Index Cond: (p.post_id = “outer”.topic_first_post_id)
14       Filter: (need_moderate = 0)
15       -> Index Scan using jforum_users_pkey on jforum_users u (cost=0.00..5.91 rows=1 width=35)
16       Index Cond: (“outer”.user_id = u.user_id)
17       -> Index Scan using jforum_topics_pkey on jforum_topics t (cost=0.00..5.68 rows=1 width=4)
18       Index Cond: (“outer”.topic_id = t.topic_id)

Explain after index
Row #         QUERY PLAN
1       Sort (cost=978.89..978.99 rows=40 width=504)
2       Sort Key: t.topic_id
3       -> Nested Loop (cost=0.00..977.83 rows=40 width=504)
4       -> Nested Loop (cost=0.00..741.01 rows=40 width=473)
5       -> Nested Loop (cost=0.00..513.47 rows=40 width=469)
6       -> Nested Loop (cost=0.00..335.45 rows=40 width=453)
7       -> Limit (cost=0.00..146.44 rows=40 width=4)
8       -> Index Scan Backward using idx_topics_fp_forum on jforum_topics (cost=0.00..66535.36 rows=18174 width=4)
9       Index Cond: (forum_id = 1)
10       -> Index Scan using jforum_posts_text_pkey on jforum_posts_text pt (cost=0.00..4.70 rows=1 width=449)
11       Index Cond: (pt.post_id = “outer”.topic_first_post_id)
12       -> Index Scan using jforum_posts_pkey on jforum_posts p (cost=0.00..4.44 rows=1 width=24)
13       Index Cond: (p.post_id = “outer”.topic_first_post_id)
14       Filter: (need_moderate = 0)
15       -> Index Scan using jforum_topics_pkey on jforum_topics t (cost=0.00..5.68 rows=1 width=4)
16       Index Cond: (“outer”.topic_id = t.topic_id)
17       -> Index Scan using jforum_users_pkey on jforum_users u (cost=0.00..5.91 rows=1 width=35)
18       Index Cond: (“outer”.user_id = u.user_id)

postgresql explain

Posted on by
Reply

I had an opportunity to do some tuning on postgresql and was pleasantly surprised at how smoothly it went.

The first thing I did was try to run an “explain” on the query under discussion.  (Explain is a tabular or graphical view of the detailed steps the database uses to execute your query.  By knowing what path it will take and what tables/indexes it will look at, you can tune your query appropriately.) Knowing this works differently in different databases, I looked up what to do.  Here are the steps:

To run explain at the command line:
1) Type “explain” followed by your query.  For example “explain select * from table”.

That’s right – one step!

To run explain graphically:
1) Install pgadmin if you haven’t already
2) Type query into editor
3) Choose query –> explain
This shows the graphical view of the query.  Clicking on the data output tab shws the text view generated by the command line.

Now it may have changed since then, but I needed to create a separate table the last time I ran an explain in Oracle.  This was extra steps that I have to look up each time.  db2 had a good graphical explain built into the tool.

What surprised me here was that I figured out postgresql’s explain much faster than Oracle’s.  Namely because it was so simple!  For the command line version, there is only one step – and it’s not one I am likely to forget.

It’s always nice when software works in such an intutive manner.

what is mentoring?

Posted on by
Reply

What is mentoring?  Listening?  Advising?  Helping?  I was looking for the definition of mentoring today and came across some interesting things.

dictionary.com lists two definitions:

1, a wise and trusted counselor or teacher.

2. an influential senior sponsor or supporter

These are both correct, but not very helpful to me.  Sometime later I came across an article that really expresses what I was looking for.

Mentoring is about one person helping another to achieve something. More specifically, something that is important to them. It is about giving help and support in a non-threatening way, in a manner that the recipient will appreciate and value and that will empower them to move forward with confidence towards what they want to achieve. Mentoring is also concerned with creating an informal environment in which one person can feel encouraged to discuss their needs and circumstances openly and in confidence with another person who is in a position to be of positive help to them

Parts of this initial paragraph really jump out at me.  I’ve listed the relevant keywords and phrases here with my thoughts on them from both being a formal/informal mentor/mentee:

  • helping – Why am I not surprised the first verb in the description is so critical?  Mentoring really does boil down to helping someone else.  The mentee is still responsible for themselves and everything pertaining to the situation.  Whereas the mentor gives advice/opinions/guidance to help the person.
  • achieve something – The goal might or might not be known to the mentee.  Sometimes there is a specific issue that one wants to discuss.   In that case there is a pretty clear goal.  Sometimes it’s just to hear advice on what is coming.  This is kind of vague.  For example, technical people are known for needing to improve their soft skills.  Yet we don’t tend to seek out advice on the topic.  A good mentor will bring it up anyway helping the person at least realize there is an opportunity out there.
  • non-threatening way – This should go without saying.  The idea of mentoring is to be guidance not “do this or else.”
  • appreciate and value – I found this phrase particularly interesting.  Usually I appreciate and value my mentor’s advice right away.  However sometime it takes time to sink in.  I received some advice related to answering questions about a year out of college.  About two years later, I told the person giving the advice that it finally clicked and now I understand what he was talking about!  At the time I did appreciate that the advice was given, but I wasn’t at the point yet where it could be useful.
  • empower and move forward with confidence – The mentee really is responsible for themselves and the mentor is just trying to help that person succeed.
  • discuss needs and circumstances openly and in confidence – One needs to know what’s going on to provide useful help and advice.  Yet often when we need advice, it’s because something problematic is going on or our innermost desires conflict with others.  Not the easiest thing to talk about in general.  Especially for technical people like us who are more comfortable with computers and logic.  Being assured of confidence allows one to “just talk” as if one is talking to a friend.  Sometimes that means your mentor (or one of your mentors) should be someone you know well or someone you don’t work with directly.
  • informal environment – Some environments are more informal than others.  Part of managing someone involves mentoring and providing career guidance.  This type of mentoring is extremely valuable because a manager knows more about a person’s job than anyone else.  It’s also valuable to have someone to talk to who is NOT your direct manager.  For one thing, it’s uncomfortable discussing things directly relating to your manager with your manager.  For another, no matter how much you trust your manager, there’s still a nagging feeling that the person is your manager which affects openness.  It’s hard to present an idea you haven’t thought through directly to your manager.  It’s also useful discussing things with someone else to gather more points of view.  This “other person” to talk to doesn’t need to be formally named a mentor.
  • in a position to be of positive help – There are multiple levels of help.  Sometimes a person just needs someone talk to.  Expressing ideas to someone else helps clear ones head and make connections between ideas.  This is the really basic level of help.  At higher levels of help, it advances it to making suggestions and asking questions to help the person think about solutions and next steps.

Formal mentoring tends to be easier to visualize.  As an example of informal mentoring, I talked to a collegue at JavaRanch (where I volunteer, not my “real” job) about something this weekend.  This is someone who I really respect – especially when it comes to process and team dynamics.  And with mentoring!  He asked me a bunch of questions to understand both the scenario and what I was thinking/what I wanted.  He also asked some higher level questions.  This was the most valueable thing for me.  If I had talked to someone I work with, this question would have been unlikely to come up because everyone would be thinking within the job and taking certain things for granted.  Now this is the only time I asked this particular person for advice (that I can recall.)  But I’ve absorbed so much from him over the years.  I think this shows that there is informal mentoring (“can I talk to you about this one thing” vs just learning from watching/listening to someone who doesn’t even realize they are mentoring.)