Not optimal sql query when using jdbc_page_size

[pdorofiejczyk]

I have prepared following logstash config

input {
 jdbc {
   jdbc_driver_library => "mysql-connector-java-5.1.39-bin.jar"
   jdbc_driver_class => "com.mysql.jdbc.Driver"
   jdbc_connection_string => "jdbc:mysql://dbhost:3306/mydb"
   jdbc_user => "user"
   jdbc_password => "password"
   statement => "SELECT * FROM test"
   jdbc_paging_enabled => "true"
   jdbc_page_size => "10000"
 }
}
output {
  stdout {  codec => json_lines  }
}

When I run logstash and check SHOW PROCESSLIST; on my db, I see following sql statements:

SELECT * FROM (SELECT * FROM test) as j LIMIT 10000 OFFSET 10000

This query reads all rows from table on each page (a few milions rows in my case) so this paging strategy is not optimal. It should do queries presented below.

SELECT * FROM test LIMIT 10000 OFFSET 10000

[axhiao]

And it is too slow! Is there a proper way to perform sql "select * from tbl" when the amount of table is large?

[catinred2]

is there any workaround for this?

[axhiao]

There is a solution for this:
jdbc_connection_string => "jdbc:mysql://localhost:3306/mydb?useCursorFetch=true"
jdbc_fetch_size => 50000

[pdorofiejczyk]

useCursorFetch will use server-side cursors, which in MySQL are implemented using temporary tables

[jbboehr]

In MySQL, a table like this:

CREATE TABLE `videos` (
  `video_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`video_id`),
  KEY `ts` (`ts`)
) ENGINE=InnoDB

the optimal queries should be, for count and fetch respectively:

SELECT COUNT(ts) FROM videos WHERE ts >= :sql_last_value LIMIT 1

SELECT * FROM videos WHERE ts >= :sql_last_value ORDER BY ts ASC LIMIT 1000

Is there any way to achieve this? I can add a limit to the statement clause, but then the count will be wrong and presumably stop import after one iteration. I'd like to avoid high offsets and large temporary tables.

[jbboehr]

After poking around the issues (#200), it appears the count query is only used for debug. In that case, the latter query of mine should partially work; it would be preferable if it continued to fetch rows until exhausting the input.

[shavo007]

@axhiao we found this issue also. Posted topic here: https://discuss.elastic.co/t/logstash-jdbc-plugin/84874

We have a large dataset.

What you recommend to performance tune?

[izhigalko]

@jbboehr count query also used in Sequel pager
This part with pages need to be reqriten imho. We can use algorithm with limit/offset query by page size while rows fetched. This will work without count query.

[jbboehr]

@izhigalko In MySQL at least, queries with high offsets are slow. If possible, it's better to page using a where on an indexed column.

mysql> select video_id from videos force index (ts) order by ts asc limit 3665722, 1;
1 row in set (1.15 sec)

mysql> explain select video_id from videos order by ts asc limit 3665722, 1;
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | videos | index | NULL          | ts   | 4       | NULL | 3472469 | Using index |
+----+-------------+--------+-------+---------------+------+---------+------+---------+-------------+

mysql> select * from videos force index (ts) order by ts asc limit 3665722, 1;
1 row in set (11.72 sec)

mysql> explain select * from videos order by ts asc limit 3665722, 1;
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows    | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+
|  1 | SIMPLE      | videos | ALL  | NULL          | NULL | NULL    | NULL | 3472472 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+---------+----------------+

mysql> select * from videos where ts >= '2017-05-12 17:45:20' order by ts asc limit 1;
1 row in set (0.00 sec)

mysql> explain select * from videos where ts >= '2017-05-12 17:45:20' order by ts asc limit 1;
+----+-------------+--------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+---------------+------+---------+------+------+-----------------------+
|  1 | SIMPLE      | videos | range | ts            | ts   | 4       | NULL | 1127 | Using index condition |
+----+-------------+--------+-------+---------------+------+---------+------+------+-----------------------+

[jbboehr]

The solution I'm going with for now is to use a higher limit without paging and just wait for the scheduler.

[chaodhib]

Here is the cause of the issue:

Like explained here, Sequel (the library used to interact with the DB) do not parse the raw SQL that is provided to it. So it cannot add a LIMIT or an OFFSET clause to the statement. The only thing it can and does do is run the statement provided by the user as a subquery.

Imo, the solution to our problem is simple but lower level: take care of the pagination ourselves (by adding manually the LIMIT and OFFSET clauses to the statement and running the statement in a loop).

[jbboehr]

Disclaimer: We use MySQL, so this may only apply to that.

I'm not sure you're replying to my comment or not, but my suggestion is to have an option to not use offset for paging at all. Each subsequent page should be fetched using the previous :sql_last_value until their are no more records remaining.

Using an offset of 1m involves a key or table scan of 1m rows, whereas using an indexed where involves a simple btree lookup. Note that, in the former case, the last iteration will involve a full table or index scan (assuming MySQL doesn't pull of some dirty tricks, although I'm pretty sure it doesn't).

I've mostly set this up already in my own configuration, the only issue is each page has to wait for the next cron iteration. Because of this, I've set the cron to once per 15 seconds with a high limit clause, but it should be faster to import if it automatically fetches each page after the previous is done.

If the cron is set to once per second and it takes more than one second to import, does it ignore the next iteration or double up (parallel or serial)?

[chaodhib]

@jbboehr I was not replying specifically to your comment. I was explaining the root cause of the issue mentioned by OP. I also opened a PR to solve that issue.

However, I implemented what you described here. I needed to migrate several tables with millions of rows from MySQL to ES. Using this approach, I noticed a performance increase by several order of magnitude. I will try to clean up the code and make it mergeable shortly.

Basically, I would first run a query that would give me the highest id of the rows in my table SELECT MAX(item.id) FROM Items item. Then I would run the query SELECT item.* FROM Items item WHERE item.id > offset AND item.id <= offset + @jdbc_page_size with an increasing offset until I reach the highest id previously mentioned.

I'm wandering if this approach would be suitable for databases other than MySQL though. Also, I'm wandering if this approach would be suitable when the tracking column is a timestamp.