Loading data from POST requests into MySQL

vaibhav.pawar · 2016-10-02T03:28:19+00:00

On Friday, 30 September 2016 02:44:29 UTC+3, Bjørnar Ness wrote:Yeah.. I can ofcorse, but perhaps look at this as a feature request instead, then: bloc...

Loading data from POST requests into MySQL

vaibhav.pawar

Hi,

I have set up a site using Openresty and MySQL using lua-resty-mysql. The site will receive about 30 million POST requests a day. I want to load the data from each such request into MySQL database.

The data posted will be in the following format -

[
{"TRANSID":"14507226013384557","HEADER":"1234","RESPONSE":"OK","TIMESTAMP":"1450791482"},
{"TRANSID":"14507226013383534","HEADER":"1235","RESPONSE":"OK","TIMESTAMP":"1450791482"},
{"TRANSID":"14507226013385581","HEADER":"1236","RESPONSE":"OK","TIMESTAMP":"1450791482"}
]

The table has TRANSID, HEADER, RESPONSE and TIMESTAMP columns. The above data should insert 3 records into my table with the columns taking appropriate values.

What is the best way to do this using lua-resty-mysql? I need an optimized solution as the number of requests is huge.

Thanks,
Vaibhav

rpaprocki

Hi,

On Sat, Oct 1, 2016 at 12:28 PM, <vaibha...@loylty.com> wrote:

Hi,

I have set up a site using Openresty and MySQL using lua-resty-mysql. The site will receive about 30 million POST requests a day. I want to load the data from each such request into MySQL database.

The data posted will be in the following format -

[
{"TRANSID":"14507226013384557","HEADER":"1234","RESPONSE":"OK","TIMESTAMP":"1450791482"},
{"TRANSID":"14507226013383534","HEADER":"1235","RESPONSE":"OK","TIMESTAMP":"1450791482"},
{"TRANSID":"14507226013385581","HEADER":"1236","RESPONSE":"OK","TIMESTAMP":"1450791482"}
]

The table has TRANSID, HEADER, RESPONSE and TIMESTAMP columns. The above data should insert 3 records into my table with the columns taking appropriate values.

What is the best way to do this using lua-resty-mysql? I need an optimized solution as the number of requests is huge.

The most efficient way to insert a large number of values into a MySQL database is to write multiple rows' worth of data in a single transaction. So it would make sense to buffer the data to be sent to the MySQL server and create a single large INSERT query, rather than calling INSERT for each HTTP request handled by OpenResty. This data can then be used to build a SQL transaction string and flush'ed out via a single call to send_query(), either on a timer via recurring calls to ngx.timer.at(), or after a certain number or size of messages have been buffered. Have a look at lua-resty-logger-socket for a similar buffer + flush implementation. Your buffer/send strategy may also depend on several factors such as the max_allowed_packet value configured on the MySQL server.

Also, a few generic notes:

- Use the TCP keepalive pool functionality available in lua-resty-mysql instead of calling close() for each connection.

- Your DBA will have a better idea about this, but consider INSERT DELAYED statements if your write workload becomes problematic

- String concatentation becomes expensive, so consider using temp tables and string.join() to build the actual query string to be sent to MySQL

Good look with it!

vaibhav.pawar

Robert, thank you for the suggestions. Very useful.

On Monday, October 3, 2016 at 8:43:07 PM UTC+5:30, rpaprocki wrote:

Hi,

On Sat, Oct 1, 2016 at 12:28 PM, <vaibha...@loylty.com> wrote:
Hi,

I have set up a site using Openresty and MySQL using lua-resty-mysql. The site will receive about 30 million POST requests a day. I want to load the data from each such request into MySQL database.

The data posted will be in the following format -

[
{"TRANSID":"14507226013384557","HEADER":"1234","RESPONSE":"OK","TIMESTAMP":"1450791482"},
{"TRANSID":"14507226013383534","HEADER":"1235","RESPONSE":"OK","TIMESTAMP":"1450791482"},
{"TRANSID":"14507226013385581","HEADER":"1236","RESPONSE":"OK","TIMESTAMP":"1450791482"}
]

The table has TRANSID, HEADER, RESPONSE and TIMESTAMP columns. The above data should insert 3 records into my table with the columns taking appropriate values.

What is the best way to do this using lua-resty-mysql? I need an optimized solution as the number of requests is huge.

The most efficient way to insert a large number of values into a MySQL database is to write multiple rows' worth of data in a single transaction. So it would make sense to buffer the data to be sent to the MySQL server and create a single large INSERT query, rather than calling INSERT for each HTTP request handled by OpenResty. This data can then be used to build a SQL transaction string and flush'ed out via a single call to send_query(), either on a timer via recurring calls to ngx.timer.at(), or after a certain number or size of messages have been buffered. Have a look at lua-resty-logger-socket for a similar buffer + flush implementation. Your buffer/send strategy may also depend on several factors such as the max_allowed_packet value configured on the MySQL server.

Also, a few generic notes:

- Use the TCP keepalive pool functionality available in lua-resty-mysql instead of calling close() for each connection.
- Your DBA will have a better idea about this, but consider INSERT DELAYED statements if your write workload becomes problematic
- String concatentation becomes expensive, so consider using temp tables and string.join() to build the actual query string to be sent to MySQL

Good look with it!