[Sguil-devel] MySQL prepared statements

[David W. <dwi...@mi...>]

Hey gang,

A little while ago I mentioned the idea of using prepared SQL statements 
instead of constantly running mysql_query() in op_sguil.c.  A prepared 
statement would allow MySQL to parse, compile, and optimize a query one 
time, and execute it many times.  This *may* have an incredible benefit 
to op_sguil.c's repetitive inserts.  You would run mysql_prepare() for a 
handful of SQL statements in an initialization function of some sort 
(sgDbConnect() maybe?), one for each insertion, and then just run 
mysql_execute() for each insertion.

http://www.mysql.com/doc/en/C_API_Prepared_statements.html
http://www.mysql.com/doc/en/C_API_Prepared_statement_function_overview.html
http://www.mysql.com/doc/en/mysql_execute.html

Here's a choice quote:

"Another advantage of prepared statements is that it uses a binary 
protocol that makes data transfer between client and server more 
efficient. Prepared statements also can support input and output binding 
for multiple query execution. "

If I'm reading that right, then the curent use of mysql_query() may be 
the source of much of the lag that I notice in insertions across links 
with multiple hops.  It's quite possible that mysql_query() requires 
significantly more network protocol overhead that leaves it very 
susceptible to latency.  Currently, insertion into the EVENT table takes 
tenths or hundredths of a second typically when done from a test sensor 
just a couple hops away, versus when it is on the same network as 
sguild/mysqld, in which case it takes a few thousandths of a second.  It 
seems like the EVENT table inserts are much more susceptible to this 
problem than the insertions into the packet header tables, payload 
table, or even the communications with sguild for the realtime alerts.

This change could make a drastic difference in my scalability problems.

-Dave