Tag Archives: sql

more postgres tuning in jforum

Posted on by
Reply

A teammate installed a new feature on CodeRanch JForum that uses a 4,515,409 row table. When dealing with over a million rows, scans become a huge performance drain. To the point where one query was slow but real usages with many at the same time brought down the app. The reason why the query was slow was interesting so I asked him if I could blog about it.

The original query

select countryCode, countryName, region, city
     from ip2location
     where 540815125 >= low and 540815125 <= high;

Running this through explain says it uses the index with a cost of:

Bitmap Heap Scan on ip2location (cost=5949.66..54170.71 rows=219870 width=32)

That’s a really high cost explain plan.

My first thought was to change it to:

explain  select countryCode, countryName, region, city
     from ip2location
     where 540815125 >= low and 540815125 <= high;

Which has a much better explain plan of

Index Scan using ip2l_low_asc_idx on ip2location (cost=0.00..8.77 rows=1 width=32)

The reason is that in the first query, postgres needs to scan the large index from the beginning until it hits the low value. In the second, I gave it permission to start really close to the target row. I subtracted 1000 but that was arbitrary. It just needs to high enough to be in the vicinity of the row without missing out on any data.

My approach also makes the lookup time consistent. It is always looking through 1000 worth of index. (Which is always less than 1000 rows given the bunching of low to high.) The original is immediate through a full index scan.

Then the original teammate switched it to:

select countryCode, countryName, region, city
     from ip2location
     where  low =
           (select max(low)
            from   ip2location
            where 540815125 >= low)

This has the same explain cost as the hacky one and is clearer.  Same logic though – it doesn’t require scanning/returning extra results.

sql query optimization – why temp tables can help

Posted on by
Reply

Last month, I migrated the jforum.net forum data to a coderanch jforum forum.  I had a requirement/goal to update the links in our forum so they work rather than point to the jforum broken links.

First I created the mapping.  (lesson: store this data as you migrate so you don’t have to do it later.)  I wound up mapping on subject lines and dates.  Luckily the threads were migrated in numeric order so I could fill in the gaps.  But that wasn’t interesting enough to blog about.  What was interesting enough to blog about was the SQL queries to update the database.

My goal

I wanted to make the changes entirely through the database – no Java code.  I also wanted to avoid postgres stored procedures because I encountered some time sinks last time I wrote a postgres stored procedure.  I am happy to say I achieved my goal.

Step 1 – Analysis

Noted that I need to update 375 rows.  Too many to do by hand.  There are just under 5000 posts in the jforum forum.  Which means therea rea t most 5000 search/replace strings to check.  This doesn’t seem bad for a computer.  Once I know the SQL, I can write code to generate 5000 of them using my local mappings and then run the SQL script on the server.

select * from jforum_posts_text where post_text like '%http://www.jforum.net/posts/list/%'

Step 2 – Horrible performing but functional query

It’s got to work before you can tune it.  My first attempt was:

explain update jforum_posts_text set post_text=replace(post_text, 'http://www.jforum.net/posts/list/5.page','http://www.coderanch.com/t/574339 ')  where post_text like '%http://www.jforum.net/posts/list/5.page%';

The query plan was:

Seq Scan on jforum_posts_text (cost=0.00..132971.82 rows=1 width=459) 
Filter: (post_text ~~ '%http://www.jforum.net/posts/list/5.page%'::text)

5000 of those is going to take over an hour of hitting the database hard. Not good. I could run it over the weekend when volumes are need be, but I can do better than that.

Step 3 – Trying to use an index

I know that most (95% maybe) of the updates are in the JForum forum.  We had a few “legacy” links to jforum.net in other forums, but not a lot.  I then tried adding a condition on forum id

explain update jforum_posts_text set post_text=replace(post_text, 'http://www.jforum.net/posts/list/5.page','http://www.coderanch.com/t/574339 ')  where post_text like '%http://www.jforum.net/posts/list/5.page%' and post_id in (select post_id from jforum_posts where forum_id = 95);

The query plan was:

Nested Loop (cost=22133.48..97281.40 rows=1 width=459)
HashAggregate (cost=22133.48..22263.46 rows=12998 width=4)
Bitmap Heap Scan on jforum_posts (cost=245.18..22100.99 rows=12998 width=4)
Recheck Cond: (forum_id = 95)
Bitmap Index Scan on idx_posts_forum (cost=0.00..241.93 rows=12998 width=0)
Index Cond: (forum_id = 95)
Index Scan using jforum_posts_text_pkey on jforum_posts_text (cost=0.00..5.76 rows=1 width=459)
Index Cond: (jforum_posts_text.post_id = jforum_posts.post_id)
Filter: (jforum_posts_text.post_text ~~ '%http://www.jforum.net/posts/list/5.page%'::text)

Well, it is using the indexes now.  But it is only about a 30% drop in cost for the worst case. On an untuned complex query, I usually see at least an order of magnitude performance jump on my initial tuning.

Step 4 – time for a temp table

Most of the work is finding the 375 rows that need updating in a table with 1,793,111 rows.  And it has to happen for each of the 5000 times I run the query.

I decided to use a temporary table so I could run the expensive part once.

create table jeanne_test as select * from jforum_posts_text where post_text like '%http://www.jforum.net/posts/list/%';

explain update jforum_posts_text set post_text=replace(post_text, 'http://www.jforum.net/posts/list/5.page','http://www.coderanch.com/t/574339 ')  where post_id in (select post_id from jeanne_test where post_text like '%http://www.jforum.net/posts/list/5.page%');

Now I’m doing the expensive part once.  It still takes a couple seconds to do the first part.  But the second part – the update I’m running 5000 times – drops the query plan to

Nested Loop (cost=13.50..21.99 rows=1 width=459)
HashAggregate (cost=13.50..13.51 rows=1 width=4)
Seq Scan on jeanne_test (cost=0.00..13.50 rows=1 width=4)
Filter: (post_text ~~ '%http://www.jforum.net/posts/list/5.page%'::text)
Index Scan using jforum_posts_text_pkey on jforum_posts_text (cost=0.00..8.46 rows=1 width=459)
Index Cond: (jforum_posts_text.post_id = jeanne_test.post_id

Nice.  Running the script with the 5000 update statements only took a few seconds.

Conclusion

Database tuning is fun.  Explain is your friend.  As are different approaches.  And for those who aren’t doing match, the performance jump was 3-4 orders of magnitude.

Which Database to Start With?

Posted on by
14

When people ask me how to learn to use a database or how to write SQL queries, I tell them to pick a database system and immerse themselves in it. In fact that advice goes for a lot of software technologies: just immerse yourself in a language, as programming tutorials are easy to come by these days. On the other hand, when people ask me which database software to use, I tend to give pause. Most of the time, I recommend MySQL for beginners since it tends to be the most light-weight system to install and use, but I know it’s not often the easiest to understand. With the advent of new light-weight database editions of often heavier products, perhaps it’s time I reconsider the issue.

1. MySQL: Free, lightweight, and readily available

MySQL stands out as the easiest for users to start with, in part because most people can get access to a MySQL database without having to setup anything. Most, if not all, hosting companies that offer database support do so in the form of a MySQL database. The only disadvantage with hosting solutions is that users lose the ability to run local applications on the database, often relying on phpMyAdmin for all database changes. I recommend anyone serious about learning MySQL download and install it themselves, as there are plenty of installation platforms supported.

The good: Free. Easy to download and/or find an existing database to work with. Somewhat easy to install. Lots of free tools available. Good documentation.
The bad: If the installation or auto-configuration breaks, user is left spending hours diagnosing the problems. The MySQL GUI tools, while nice, have to be downloaded separately from the server. Limited support. Clustering and support of large transaction systems is not uncommon. Also, it can be buggy and unpredictable at times, as I’ve seen in practice.

2. Oracle: Heavy and Powerful

Oracle is one of the oldest database systems and stands out as a powerhouse among databases given its vast support for advanced clustering, memory management, and query optimization. If you need something robust, powerful, and able to support millions or billions of transactions a day, it’s the best there is. Oracle needs to be licensed for a production environment, although developers can download a free limited-use version which is good for building an application.

The good: Powerful. Can do some really cool things for those that appreciate it. Extremely scalable.
The bad: Often large and time-consuming installation. Least user friendly of all the database systems, although it’s gotten better over the last few years. Not free. Not a wide variety of tools, free or otherwise, to manipulate the database.

3. Microsoft SQL Server: Easy to use administration interface, often powerful

Microsoft SQL Server has matured greatly over the last 10 years into a decent rival of Oracle. I like MS SQL Server in that it hides a lot of the underlying configuration information from the user. On the other hand, I dislike MS SQL server in that it hides a lot of the underlying configuration information from the user. Double-edged sword, I know. Like Oracle, you need a license if you want to use it in a production environment.

The good: Easy to set up new databases and administer them. Best for those who have no idea how to administer a database. New express editions can be used for free.
The bad: Over-simplifies a lot for advanced users, making it harder to optimize. Not free. Developer edition has nominal cost, although it probably should be free.

Other Databases

This article is not meant to be the end-all for database software discussion, but a beginning guide of the big three database systems for those who are not well-versed in the area. To cover every possible database software, such as PostgreSQL or DB2, as well as countless others, would take a book or two. Most students starting out just need to find a single database and start ‘playing’ with it until they get the hang of it, rather than an exhaustive discussion of which database is best.

Non-standard Databases

Some of you may be more familiar with embedded databases such HSQLDB, SQLite, or Derby than the ones I have mentioned. Rarely do I see beginners using embedded databases, so perhaps I’ll write an article about such systems down the road. Also, I have not purposely not mentioned Microsoft Access as a learning database, simply because I don’t consider it standard database software, but rather a glorified Excel spreadsheet. Most of teaching someone how to use a regular database after using Access, is convincing them all databases are not like Access.

My favorite database? If I’m teaching or writing a relatively simple web-application, MySQL. If someone else is paying for the license and the application is large enough, Oracle.