Connection pool, performance tests

[ghost]

I would like to make a question regarding performance

Below a small "demo" file that is used to measure execution time. I want to update a sequence with nextvval 100K times.

CREATE SEQUENCE  "TEST_SEQ"  
    MINVALUE 1 
    MAXVALUE 10000000000
    INCREMENT BY 1 
    START WITH 1 
    CACHE 200 
    NOORDER  
    CYCLE;

The sequence is created with the above script, and using the workbench like this

declare local_1 INT;
BEGIN

  FOR i IN 1..100000 LOOP
    select TEST_SEQ.nextval into local_1 from dual;
  END LOOP;

END;
/

it takes less than 2 seconds for the counter to reach 100K.

However the following code wants more than 7 seconds to reach 10K (an order of magnitude less). I am wondering if there is something better I can do, or if I miss something. Is it possible using the library to reach better number, ie 2K-3K per second?

In order to compile please use g++ t_main.cpp -std=c++11 -lboost_system -lboost_thread -lodpic -ldl

I used a simple thread pool from boost.

#include <boost/array.hpp>
#include <iostream>
#include <boost/asio/io_service.hpp>
#include <boost/bind.hpp>
#include <boost/thread/thread.hpp>
#include <boost/chrono/thread_clock.hpp>
#include <memory>
#include <chrono>

dpiContext *context;
dpiPool *pool;

int ShowError(dpiContext *context)
{
    return 1;
}

int icalc(int x) {

    dpiConn *conn;
    dpiStmt *stmt;
    uint32_t numQueryColumns;

    const char *sql = "select TEST_SEQ.nextval from dual";

    if (dpiPool_acquireConnection(pool, NULL, 0, NULL, 0, NULL, &conn) < 0 )
        return ShowError(context);

    if (dpiConn_prepareStmt(conn, 0, sql, strlen(sql), NULL, 0, &stmt) < 0)
        return ShowError(context);

    if ( dpiStmt_execute(stmt, DPI_MODE_EXEC_DEFAULT, &numQueryColumns) < 0)
        return ShowError(context);

    if ( dpiStmt_release(stmt) < 0 )
        return ShowError(context);

    if( conn ) {
            dpiConn_release(conn) ;
            return 0; //"Hello world";
    } else {
        return 1; //"err";
    }

}

int main(int argc, char** argv)
{

    dpiErrorInfo errorInfo;
    //dpiContext *context;

    if (dpiContext_create(DPI_MAJOR_VERSION, DPI_MINOR_VERSION, &context, &errorInfo) < 0) {
        return -1;
    }

    dpiCommonCreateParams commonParams;
    dpiPoolCreateParams createParams;

    //dpiPool *pool;

    if (dpiContext_initCommonCreateParams(context, &commonParams) < 0) {
        std::cout << "cannot init common params" << std::endl;
        return 1;
    }
    commonParams.createMode = DPI_MODE_CREATE_THREADED;

    if (dpiContext_initPoolCreateParams(context, &createParams) < 0) 
    {
        std::cout << "cannot init the params" << std::endl;
        return 1; //dpiTestCase_setFailedFromError(testCase);
    }
    createParams.minSessions = 15; //MINSESSIONS;
    createParams.maxSessions = 20; //MAXSESSIONS;
    createParams.sessionIncrement = 1; //SESSINCREMENT;

    if (dpiPool_create(context, "DB", strlen("DB"),
            "DB", strlen("DB"), "1.7.7.1:1521/DEV",
            strlen("10.70.7.146:1521/L10DEV"), &commonParams, &createParams,  &pool) < 0)
        return 1; 

    if (dpiPool_setGetMode(pool, DPI_MODE_POOL_GET_WAIT) < 0)
        return 1;     

    int maxthreads = 50;
    boost::asio::io_service ioService;
    std::unique_ptr<boost::asio::io_service::work> work( new boost::asio::io_service::work(ioService));
    boost::thread_group threadpool; //pool
    std::cout << "thread starts with max threads: : " << maxthreads << std::endl;
    for (std::size_t i = 0; i < maxthreads; ++i)
        threadpool.create_thread(boost::bind(&boost::asio::io_service::run, &ioService));

    using namespace boost::chrono;

    //thread_clock::time_point start = thread_clock::now();

    std::chrono::steady_clock::time_point begin = std::chrono::steady_clock::now();
    for(int i = 0 ; i < 10000; i++) 
        ioService.post(boost::bind(icalc, 42));

    work.reset();
    threadpool.join_all();
    ioService.stop();
    std::chrono::steady_clock::time_point end= std::chrono::steady_clock::now();

    std::cout << "Time difference = " << std::chrono::duration_cast<std::chrono::seconds>(end - begin).count() <<std::endl;

    return 0;
}

Do we misuse the library somehow?

It is a little bit critical for us, since we want to decide if we will use oracle for a high performance server.

Thank you very much in advance.

[cjbj]

What are you trying to find out and what operations are really important to you?

The code (which I haven't gone right through) tests some distinct areas. For example it queries from a sequence - is that something you plan to do? Have you tried querying different rows of a large table? Why not set the pool min & max to the same values (& increment to 0) so the sessions are pre-created? How did you decide on the pool settings vs the number of threads? Are you testing your network - the PL/SQL code doesn't do any network traffic, so the overheads are different? Will you only be doing one statement for every connection acquire/release - that may not reflect some real-world (or best practice) scenarios? If pure speed is important, why use not use OCI directly?

[anthony-tuininga]

I tried running your code. On my machine, which is by no means a fast machine, I am getting a full 100,000 iterations in about 7-8 seconds. The first bit of that time (1/2 a second or so) is for creating the pool and pre-creating the connections. My database is local (on the same machine as the code that is running). Is that true of your case as well? Running remotely will definitely slow things down! As Chris mentioned, running on the server is not the same thing as running on a client -- so the performance I am seeing with the session pool is pretty reasonable I would think.

I played a little with the number of threads. On my machine at least reducing the number of threads to 15 improved throughput -- and there was less startup time. But that is highly dependent on your situation.

[ghost]

Hello,

What I am trying to achieve here is to use a sequence in order to implement a counter. Multiple http clients use a web service and we want to increment a counter atomically under some circumstances. I don't know if this is the best way to increment a counter in oracle. In case you have something else in mind, it would be great if you could share it.

So we need some real world performance, as your link indicates below. We need at least 3K/sec counter increments.

The idea to have static pool with min = max = some value, sounds OK. I will try it immediately.

I have already used OCCI but the performance was not what I want it, so I come up with this implementation (odpi), assuming that my OCCI client code was not good. However I realized that the performance is almost similar either with OCCI or with odpi.

it is interesting that you mentioned PL/SQL. Would it better instead of having the select statement on the client c++ code to invoke a PL/SQL function that does the same thing?

What is important for us is to really ensure that either with OCCI or with ofpi we can do >2K increments per second.

On Fri, 7 Jul 2017 at 05:34, Christopher Jones notifications@github.com wrote:

What are you trying to find out and what operations are really important to you?

The code (which I haven't gone right through) tests some distinct areas. For example it queries from a sequence - is that something you plan to do? Have you tried querying different rows of a large table? Why not set the pool min & max to the same values (& increment to 0) so the sessions are pre-created http://docs.oracle.com/cd/E82638_01/JJUCP/optimizing-real-world-performance.htm#JJUCP-GUID-BC09F045-5D80-4AF5-93F5-FEF0531E0E1D? How did you decide on the pool settings vs the number of threads? Are you testing your network - the PL/SQL code doesn't do any network traffic, so the overheads are different? Will you only be doing one statement for every connection acquire/release - that may not reflect some real-world (or best practice) scenarios? If pure speed is important, why use not use OCI directly?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/oracle/odpi/issues/15#issuecomment-313571228, or mute the thread https://github.com/notifications/unsubscribe-auth/AajvyfkxmrzA-gwoarnnp4y8Ed8lzGmIks5sLZkagaJpZM4OQBgL .

[anthony-tuininga]

With your code on my machine I was getting 12,500 increments/second. So I think that qualifies. :-)

[anthony-tuininga]

But is that all you are trying to do with the database?

[ghost]

Hello , I really appreciate the fact that you are responding to my question.

No, we have a lot of tables that support our service and oracle of course qualifies.

But as I have explained above, I wanted to find out if oracle offers a solution for the counter increment problem. We need a way to increment a value really fast.

On Fri, 7 Jul 2017 at 06:06, Anthony Tuininga notifications@github.com wrote:

But is that all you are trying to do with the database?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/oracle/odpi/issues/15#issuecomment-313575368, or mute the thread https://github.com/notifications/unsubscribe-auth/AajvyT5BOzSQe8ADPsM5Z9T62U9uOW4wks5sLaDCgaJpZM4OQBgL .

[ghost]

What is the proper way to do this?

Select for update?

On Fri, 7 Jul 2017 at 06:13, ellampros . el.lampros@gmail.com wrote:

Hello , I really appreciate the fact that you are responding to my question.

No, we have a lot of tables that support our service and oracle of course qualifies.

But as I have explained above, I wanted to find out if oracle offers a solution for the counter increment problem. We need a way to increment a value really fast.

On Fri, 7 Jul 2017 at 06:06, Anthony Tuininga notifications@github.com wrote:

But is that all you are trying to do with the database?

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/oracle/odpi/issues/15#issuecomment-313575368, or mute the thread https://github.com/notifications/unsubscribe-auth/AajvyT5BOzSQe8ADPsM5Z9T62U9uOW4wks5sLaDCgaJpZM4OQBgL .

[anthony-tuininga]

What is the proper way to do this?

If all you want is the counter to be incremented, then the select statement is the fastest; but if you have other things going on at the same time, putting it into a PL/SQL procedure makes the most sense. You want to reduce the number of round trips, especially if you are across a network and the database isn't local to your application.

[cjbj]

You are straying into DB performance questions. I'll see if I can get someone with wider performance tuning skills to comment on sequence usage.

Do you have any other requirements for the 'counter'? Don't forget that sequences (specially with caching) may end up with number range 'gaps'.

A customer OCI program will like be faster than OCCI or ODPI-C.

[ghost]

You mean the select on the sequence and not the "for update"

On Fri, 7 Jul 2017 at 06:17, Anthony Tuininga notifications@github.com wrote:

What is the proper way to do this?

If all you want is the counter to be incremented, then the select statement is the fastest; but if you have other things going on at the same time, putting it into a PL/SQL procedure makes the most sense. You want to reduce the number of round trips, especially if you are across a network and the database isn't local to your application.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/oracle/odpi/issues/15#issuecomment-313576666, or mute the thread https://github.com/notifications/unsubscribe-auth/Aajvybb5hvneV6LdOAE1uw93osMO9iA_ks5sLaNfgaJpZM4OQBgL .

[anthony-tuininga]

You mean the select on the sequence and not the "for update"

Yes. The "for update" clause is when you intend to update the table or view you are querying -- which you definitely don't want to do in this case!

[ghost]

Christopher,

The atomic counter increment is the hot issue right now.

As I said the performance requirements are not extra ordinary, so I would like to resolve this in Oracle.

It would be great if a "counter expert" could help

On Fri, 7 Jul 2017 at 06:23, ellampros . el.lampros@gmail.com wrote:

You mean the select on the sequence and not the "for update"

On Fri, 7 Jul 2017 at 06:17, Anthony Tuininga notifications@github.com wrote:

What is the proper way to do this?

If all you want is the counter to be incremented, then the select statement is the fastest; but if you have other things going on at the same time, putting it into a PL/SQL procedure makes the most sense. You want to reduce the number of round trips, especially if you are across a network and the database isn't local to your application.

— You are receiving this because you authored the thread. Reply to this email directly, view it on GitHub https://github.com/oracle/odpi/issues/15#issuecomment-313576666, or mute the thread https://github.com/notifications/unsubscribe-auth/Aajvybb5hvneV6LdOAE1uw93osMO9iA_ks5sLaNfgaJpZM4OQBgL .

[cjbj]

@leflabs define 'atomic'. Are 'gaps' OK?

[ghost]

I have read about those gaps.

If the performance Is ok with "no cache" in the sequence, I will use no cache.

On the other hand if the counter can reach 1 million, then 200 gaps are OK.

If the counter can reach just 5 hits, then no gaps are allowed.

On Fri, 7 Jul 2017 at 06:31, Christopher Jones notifications@github.com wrote:

@leflabs https://github.com/leflabs define 'atomic'. Are 'gaps' OK?

— You are receiving this because you were mentioned.

Reply to this email directly, view it on GitHub https://github.com/oracle/odpi/issues/15#issuecomment-313578203, or mute the thread https://github.com/notifications/unsubscribe-auth/AajvyW15Hvj3pO985QkZoTiFjad_joTCks5sLaacgaJpZM4OQBgL .

[ghost]

@anthony-tuininga I downloaded a VM from oracle with the database on, installed some dev tools and I compiled the demo cpp file in the same machine with the DB. It is true that it is much much faster (with CACHE "on").

However since we have opened the discussion here, I would be glad to know if this the canonical/normal way to deal with counters in Oracle Database.

[cjbj]

I got some comments. In summary, revisit your design and think about what you want the numbers for.

Are you going to use them to insert into the DB? Then consider using PL/SQL for all that logic.

Generating unique, possibly monotonically increasing, values is non-trivial, and if they are needed very fast and with high concurrency, they should always be generated as close as possible to their actual use. So if they are needed ONLY on the application server side, don't ask the database to generate them.

Or maybe you could create batches of numbers on each application server.

A lot depends on your goals, and on potential architectures (do you have multiple 'mid tiers', aka ODPI-C apps?)

The existing sample benchmark code doesn't really capture any of this, so it doesn't prove much.

[ghost]

@cjbj and @anthony-tuininga thanks very much for this thread. I will probably re visit my design.

Regards.

[cjbj]

@leflabs let us know what you decide.

[anthony-tuininga]

I'll close this since the question has been answered but feel free to create a new issue if a new question pops up! Thanks.