Yesterday I posted some demonstrations of the impressive power of indexes in relational databases for data retrieval even when those tables get massive in size. That resulted in a few comments on Twitter and on the post itself regarding the other half of the "equation", namely, that my test was potentially biased in favour of query performance because I loaded the entire table before I added the indexes.
This means the indexes are nicely compacted with no "fragmentation". This is a valid point but as I responded on Twitter, when you trying to do a weekend query test with a 2billion row table, then pre-loading the data makes a lot of sense unless you want to lose most of your weekend waiting for the table to be populated with single row inserts 
In particular, given that on my podcast recently I discussed why it can be good to have a mindset of "Every index is a bug"
https://open.spotify.com/episode/1X2UUIqJv5jLsPxKri7fSz
then pre-loading a table, and indexing it to the hilt might seem somewhat hypocritical
So to make sure I'm not hiding/missing any issues or distorting the test, let us now consider the performance of getting the data into this table in a way that we would expect from a real world application, ie, taking clicks from a large user base. Using the same population script, I'll add a predicate to load only 1/3rd of the data (~670million rows) and then I'll explore loading additional data in a real world scenario, ie, single row inserts from concurrent sessions.
SQL> create table user_likes ( 2 user_id int not null, 3 post_id int not null, 4 dt date default sysdate not null 5 ) ; Table created. SQL> insert /*+ APPEND */ into user_likes 2 with 3 u as ( 4 select /*+ materialize */ rownum user_id from dual 5 connect by level <= 200000 6 order by dbms_random.value ), 7 p as ( 8 select /*+ materialize */ rownum post_id from dual 9 connect by level <= 10000 10 order by dbms_random.value ) 11 select user_id, post_id, sysdate 12 from u,p 13 where mod(user_id+post_id,3) = 0; 666666667 rows created.
I'm going to index the tables slightly differently, ie, as non-unique, because that will make my benchmark code easier because I'm not going to have to worry about duplicates - I can just generate a random user and a random post and just slam a row in there. If anything, this approach should worsen my benchmark results because I'll have greater likelihood of hitting pre-existing index data and needing to split leaf and/or branch blocks.
SQL> create index user_likes_ix1 on user_likes ( user_id, post_id ); Index created. SQL> create index user_likes_ix2 on user_likes ( post_id, user_id ); Index created.
To benchmark concurrent application workload, I'm going to use a benchmark mechanism you may have seen previously from my session on physical design I did earlier this year. (You can see that video at the bottom of this post). I have a table called RESULTS which not only will store the benchmark results, it will serve as a semaphore mechanism to ensure all concurrent threads of my application are launched at the same time.
SQL> create table results as select rownum-1 seed, 0 tps, 0 ela, systimestamp st, systimestamp en from dual 2 connect by level <= 6; Table created.
Now my benchmark has two phases, an INIT phase will pre-calculates the random data to be inserted. I do this because I don't want the cost of generating randomised data to distort the benchmark result. (In a real application, users of course know what posts they want to click "Like" on!). Then the RUN section of the code will
- wait to get access to the RESULTS table (our semaphore)
- perform 1,000,000 random single "Like" requests (ie, an insert into our USER_LIKES table), each as a single transaction
- then store the throughput at the end of the run
SQL> create or replace 2 package benchmark is 3 iter int := 1000000; -- new likes to post 4 5 idx int; 6 seed int; 7 l_start timestamp; 8 9 type numlist is table of number 10 index by pls_integer; 11 12 l_user numlist; 13 l_post numlist; 14 15 procedure init(p_seed int); 16 procedure run(p_seed int); 17 end; 18 / Package created. SQL> SQL> create or replace 2 package body benchmark is 3 4 procedure init(p_seed int) is 5 begin 6 seed := p_seed; 7 dbms_random.seed(p_seed); 8 9 select mod(rownum,200000)+1 bulk collect into l_user 10 from dual connect by level <= iter 11 order by dbms_random.value; 12 13 select mod(rownum,10000)+1 bulk collect into l_post 14 from dual connect by level <= iter 15 order by dbms_random.value; 16 17 end; 18 19 procedure run(p_seed int) is 20 delt number; 21 begin 22 l_start := systimestamp; 23 for i in 1 .. iter 24 loop 25 insert into user_likes (user_Id, post_id) 26 values (l_user(i), l_post(i)); 27 commit; 28 end loop; 29 delt := extract(second from (systimestamp-l_start)) + 60*extract(minute from (systimestamp-l_start)); 30 update results 31 set tps = round(iter / delt,1), 32 ela = delt, 33 st = l_start, 34 en = systimestamp 35 where seed = p_seed; 36 commit; 37 end; 38 39 end; 40 / Package body created. SQL> SQL> lock table results in exclusive mode; Table(s) Locked.
Because my PC has 8 cores (I told you it was old ) I'll run 6 of these concurrently, leaving 2 cores for the database and OS to do their thing.
So we'll launch the 6 concurrent SQL sessions slamming this table, totalling 6million transactions and see what results we get.
-- -- launch 6 new sessions that will wait on my commit -- SQL> host start sql_plus @benchmark.sql 0 SQL> host start sql_plus @benchmark.sql 1 SQL> host start sql_plus @benchmark.sql 2 SQL> host start sql_plus @benchmark.sql 3 SQL> host start sql_plus @benchmark.sql 4 SQL> host start sql_plus @benchmark.sql 5 SQL> commit; Commit complete. --- now we wait SQL> select min(ela), max(ela), avg(ela) from results; MIN(ELA) MAX(ELA) AVG(ELA) ---------- ---------- ---------- 589.9 608.4 593.8 SQL> select sum(tps) from results; SUM(TPS) ---------- 10103.1
I think that's a pretty impressive result. I can achieve about 10,000 transactions (ie, "Likes") per second. For context, I read recently that VISA normally performs 4,000 transactions per second world-wide. I can't vouch for the validity of that statement, but if true, I think that speaks volumes for the performance the benchmark is getting here.
And just in case you're thinking I'm gaming the system here, here's the memory allocation for my database on this PC
SQL> show sga Total System Global Area 3221225240 bytes Fixed Size 9273112 bytes Variable Size 889192448 bytes Database Buffers 2298478592 bytes Redo Buffers 24281088 bytes
Yes, only 3G of RAM in total allocated! The PC has 32G of RAM but since I run a lot of databases on this box, I'm need to be frugal on the memory that each one gets
I want to stress - I've deliberately not exploited any of the "advanced" features of the database to gain a performance boost here. If you take a look at the video from my channel at the tail of this post, I talk about several mechanisms that could be used to perhaps bump up this performance by as much as 300%. But even without using any of those techniques, it is fair to say that a database novice, who merely creates a table, adds indexes as per their anticipated query requirements, and wants to scale up to 2 billion rows, can still get 10,000 transactions per second on modest hardware
But now that I've smashed in 6million rows, the question remains - what damage have I done the indexes? Do database indexes get stale? Do my previous query performance tests still hold up?
Lets repeat the test with a slight modification to take into the account that we now have a variable number of rows for each user and/or post data in the table. (Once again you can just skip to just the bits in blue if you don't want death-from-code)
SQL> set serverout on SQL> declare 2 type numlist is table of number index by pls_integer; 3 iter int := 50000; 4 u numlist; 5 p numlist; 6 res numlist; 7 s1 timestamp; 8 s2 timestamp; 9 s3 timestamp; 10 delt number; 11 lo number := 999999999; 12 hi number := 0; 13 cnt int; 14 begin 15 select trunc(dbms_random.value(1,200000) ) 16 bulk collect into u 17 from dual 18 connect by level <= iter; 19 20 select trunc(dbms_random.value(1,10000) ) 21 bulk collect into p 22 from dual 23 connect by level <= iter; 24 25 s1 := localtimestamp; 26 for i in 1 .. iter 27 loop 28 s2 := systimestamp; 29 select user_id 30 bulk collect into res 31 from user_likes 32 where user_id = u(i); 33 delt := extract(second from localtimestamp - s2); 34 if delt < lo then lo := delt; end if; 35 if delt > hi then hi := delt; cnt := res.count; end if; 36 end loop; 37 delt := extract(second from localtimestamp - s1) + 60*extract(minute from localtimestamp - s1); 38 39 dbms_output.put_line(iter||' executions: All posts for nominated user'); 40 dbms_output.put_line('====================================='); 41 dbms_output.put_line('Total: '||delt); 42 dbms_output.put_line('Avg: '||(delt/iter)); 43 dbms_output.put_line('Low: '||lo); 44 dbms_output.put_line('Hi: '||hi||' (for '||cnt||' rows returned)'); 45 46 lo := 999999999; 47 hi := 0; 48 s1 := localtimestamp; 49 for i in 1 .. iter 50 loop 51 s2 := systimestamp; 52 select user_id 53 bulk collect into res 54 from user_likes 55 where post_id = p(i); 56 delt := extract(second from localtimestamp - s2); 57 if delt < lo then lo := delt; end if; 58 if delt > hi then hi := delt; cnt := res.count; end if; 59 end loop; 60 delt := extract(second from localtimestamp - s1) + 60*extract(minute from localtimestamp - s1); 61 62 dbms_output.put_line(iter||' executions: All users for nominated post'); 63 dbms_output.put_line('====================================='); 64 dbms_output.put_line('Total: '||delt); 65 dbms_output.put_line('Avg: '||(delt/iter)); 66 dbms_output.put_line('Low: '||lo); 67 dbms_output.put_line('Hi: '||hi||' (for '||cnt||' rows returned)'); 68 69 lo := 999999999; 70 hi := 0; 71 s1 := localtimestamp; 72 for i in 1 .. iter 73 loop 74 s2 := systimestamp; 75 select user_id 76 bulk collect into res 77 from user_likes 78 where post_id = p(i) 79 and user_id = u(i); 80 delt := extract(second from localtimestamp - s2); 81 if delt < lo then lo := delt; end if; 82 if delt > hi then hi := delt; end if; 83 end loop; 84 delt := extract(second from localtimestamp - s1) + 60*extract(minute from localtimestamp - s1); 85 86 dbms_output.put_line(iter||' executions: Lookup (probably) single post for a nominated user'); 87 dbms_output.put_line('======================================='); 88 dbms_output.put_line('Total: '||delt); 89 dbms_output.put_line('Avg: '||(delt/iter)); 90 dbms_output.put_line('Low: '||lo); 91 dbms_output.put_line('Hi: '||hi); 92 93 end; 94 / 50000 executions: All posts for nominated user ===================================== Total: 101.196 Avg: .00202392 Low: 0 Hi: .097 (for 3334 rows returned) 50000 executions: All users for nominated post ===================================== Total: 1454.985 Avg: .0290997 Low: .007 Hi: .885 (for 66851 rows returned) 50000 executions: Lookup (probably) single post for a nominated user ======================================= Total: 11.196 Avg: .00022392 Low: 0 Hi: .011 PL/SQL procedure successfully completed. As you can see, for the small lookups, we're still in the single digit millisecond region for performance, and for the worst case scenario (getting thousands of rows for a single post) we're averaging around 30ms. This is actually better than the original benchmark, because I'm dealing with ~700million rows instead of 2billion. (Don't flame me for using less rows - I want to have some weekend free time )
Finally, if you're thinking "What if there were multiple queries going on, what about the read contention?" then I can't comment for other database engines, but its pretty much a non-issue with Oracle because we don't ever do read locks. In saying this I'm not trying to throw shade on other relational databases here. Whilst I think that Oracle is the best of them, you should be able to get nearly the same performance here with any of the major players - SQL Server, DB2, Postgres, MySQL etc).
What I am saying is that ... relational databases are insanely good. So next time you stumble across a blog out there on the interwebs which talks about how relational database can't scale
then maybe point them this way and hopefully they'll get a better understanding of why relational database have been so successful for decades.
If you are on Oracle, and want to see how you might be able to take 10,000 transactions per second to maybe 30,000 per second, then check out my physical design video below.
WordPress.com
No comments:
Post a Comment
Note: Only a member of this blog may post a comment.