# Logstash jdbc plugin internals **URL:** \ **Category:** Logstash\ **Created:** [June 4, 2020, 11:22am UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-internals/235743 "2020-06-04T11:22:51Z")\ **Posts on this page:** 6\ **Page:** 1
**Author:** ![luis\_g](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luis_g/32/58236_2.png) [@luis\_g](https://discuss.elastic.co/u/luis_g)\ **Post date:** [June 4, 2020, 11:22am UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-internals/235743/1 "2020-06-04T11:22:51Z")
Hi, I have some questions regarding the internals of logstash when using the jdbc input plugin. I would like to mention that my current logstash configuration is working properly, but I want to know how the jdbc\_page\_size and the :sql\_last\_value work together... Say, for instance, that in my logstash pipeline I have the following configuration: ```auto statement => select * from table t where t.id > :sql_last_value order by t.id jdbc_paging_enabled => true jdbc_page_size => 10000 use_column_value => true tracking_column => "id" ``` In the documentation it is stated that jdbc\_paging\_enabled causes the sql statement to be broken in multiple queries... Say I have 100.000 records in the DB, how would logstash execute the statements? 1st: select \* from table t where t.id \> 0 order by t.id FETCH NEXT 10000 ROWS ONLY; (set the last id to :sql\_last\_value. eg. 10000) 2nd: select \* from table t where t.id \> 10000 order by t.id FETCH NEXT 10000 ROWS ONLY; (set the last id to :sql\_last\_value. eg. 20000) 3nd: select \* from table t where t.id \> 20000 order by t.id FETCH NEXT 10000 ROWS ONLY; (set the last id to :sql\_last\_value. eg. 30000) etc Is this accurate? In the source code ([https://github.com/chaodhib/logstash-input-jdbc/blob/4e8d1d7fbfd01fec5840530222f982b306e8bb22/lib/logstash/plugin\_mixins/jdbc.rb](https://github.com/chaodhib/logstash-input-jdbc/blob/4e8d1d7fbfd01fec5840530222f982b306e8bb22/lib/logstash/plugin_mixins/jdbc.rb)) it is written: "This will cause a sql statement to be broken up into multiple queries. Each query will use limits and offsets to collectively retrieve the full result-set." In Oracle there is no limit or offset so how does it work? It uses FETCH like I mentioned before or ROWNUM? when is the value of :sql\_last\_value written to the metadata file? ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [June 4, 2020, 1:17pm UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-internals/235743/2 "2020-06-04T13:17:57Z")
> [@luis\_g](#): > > when is the value of :sql\_last\_value written to the metadata file? [Every time](https://github.com/logstash-plugins/logstash-input-jdbc/blob/0cee881dcdae5ba75716dacab54aba8da13086b6/lib/logstash/inputs/jdbc.rb#L318) a statement is executed. ---
**Author:** ![luis\_g](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luis_g/32/58236_2.png) [@luis\_g](https://discuss.elastic.co/u/luis_g)\ **Post date:** [June 4, 2020, 2:12pm UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-internals/235743/3 "2020-06-04T14:12:27Z")
> [@Badger](#): > > [Every time](https://github.com/logstash-plugins/logstash-input-jdbc/blob/0cee881dcdae5ba75716dacab54aba8da13086b6/lib/logstash/inputs/jdbc.rb#L318) a statement is executed. you mean the statement defined in the jdbc input plugin, so: statement =\> select \* from table t where t.id \> :sql\_last\_value order by t.id or after each one of the statements that are internally created from using jdbc\_paging\_enabled=true? ---
**Author:** ![Badger](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/badger/32/25190_2.png) [@Badger](https://discuss.elastic.co/u/Badger)\ **Post date:** [June 4, 2020, 2:47pm UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-internals/235743/4 "2020-06-04T14:47:00Z")
My reading is that paging is internal to the mixin statement handler, so it is once for the statement defined in the input option. ---
**Author:** ![luis\_g](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/luis_g/32/58236_2.png) [@luis\_g](https://discuss.elastic.co/u/luis_g)\ **Post date:** [June 8, 2020, 1:07pm UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-internals/235743/5 "2020-06-08T13:07:30Z")
Because I dont really know how the sql statement is perfomed under the hood, I ended up changing the query for something like: `statement => select * from (select * from table t where t.id > :sql_last_value order by t.id) where rownum < 250k` In this case the queries to the DB are much faster and I end not really needing jdbc\_paging\_enabled =\> true. I get less indexed documents per minute but its good enough. Also, this way the metadata file is written after 250K indexed documents instead of after the entirety of the records have been indexed. ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2020, 1:07pm UTC](https://discuss.elastic.co/t/logstash-jdbc-plugin-internals/235743/6 "2020-07-06T13:07:35Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.