Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

ive got log parser on perl and it uses mysql insert or update command on every line. Like:

Log line 1: 2012-12-01 12:12 [perld] Hello world #1
....
Log line N: 2012-12-01 12:NN [perld] Hello world #N

it parses this lines and get "timestamp" and "#N" and inserts them into mysql db (parses line with perl::tail function).

Ive got about 100 lines/second so there would be about 100 inserts ti mysql.

Is there any solution/algorithm to optimize that? or maybe i should use something like buffer with insert?

share|improve this question

3 Answers 3

up vote 1 down vote accepted

The are several points you can check. You can try stored procedures (prepared statements). This can be implemented using dbi->prepare method in perl. This is generally faster than executing the same query multiple times as you need to send only the parameters.

One thing you can also try is to combine multiple insert statments into one and execute them as single statement.

Also, you can try MyISAM if you are not already using it. It is faster than InnoDB. You can try it if you don't use transactions.

share|improve this answer
    
there's a difference between stored procedures and prepared statements. The latter is a good idea if the OP isn't already using them. –  Tom Newton Jan 6 '12 at 9:27
    
Yes, you are right. I did not recall the right term so I mentioned both :) –  Khaled Jan 6 '12 at 9:31
    
So if i use "prepare" for insert and there is some UPDATE between them? I should use Trigger? –  MealstroM Jan 6 '12 at 9:51

Check the indexes on your table - if you have ones you don't need your queries may be taking a longer time than needed.

Also, are you sure your sql is what is taking the time - as Khaled says, you might benefit from pre-preparing the query, but there may be other places where things are slow - for example, you say you parse a date - maybe done easily with Date::Manip - but not quickly! Make sure you know where needs optimising before you start :)

share|improve this answer

MySQL supports multiline insert, like this:

INSERT INTO sometable (field1, field2) VALUES
(1, 2),
(2, 3),
(3, 4);

etc. You can do one insert per second after parsing all the lines for that second or (better) allow for the threshold to be set at runtime which will allow you to figure out the setting performance-wise by experimenting.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.