Will This Trigger Significantly Degrade Performance in MySQL?

mariadbMySQLoptimizationperformancequery-performancetrigger

I have this trigger to (do my best) to prevent brute forcing logins. It has two seperate functions:

  1. If a failed login has been attempted more than 4 times (so read that exactly 5 times) from the same IP address within the last three minutes, that IP_address is added to the blacklisted_ips table. The IP address then has a "cool down" time period specified by a column in the table.

  2. If a failed login has been attempted more than 3 times (so read that exactly 4 times) from the same username within the last two minutes (so IP address agnostic), then the username is added to the blacklisted_usernames table. The username then has a "cool down" time period specified by a column in the table.

My question is, looking at this trigger, it looks performance inefficient to me. Am I correct in thinking that, or is the performance effect negligible?

CREATE TRIGGER prevent_brute_force 
AFTER INSERT ON login_att 
FOR EACH ROW 
BEGIN 
    SELECT ip_add, was_success INTO @ip, @result 
    FROM login_att 
    ORDER BY login_att_id DESC LIMIT 1;

    SELECT ip_add INTO @check_ip 
    FROM login_att 
    WHERE login_att_time > DATE_SUB(NOW(), INTERVAL 3 MINUTE) 
      AND ip_add = @ip 
    HAVING COUNT(ip_add) > 4;

    IF @check_ip IS NOT NULL 
    THEN 
        IF @result = 0
        THEN
            INSERT INTO blacklisted_ips (ip_add) VALUES (@ip); 
        END IF;
    END IF;

    SELECT username_att into @username_att 
    FROM login_att 
    WHERE login_att_time > DATE_SUB(NOW(), INTERVAL 2 MINUTE) 
      AND was_success=0 HAVING COUNT(username_att) > 3;

    IF @username_att IS NOT NULL
    THEN
        INSERT INTO blacklisted_users (username) VALUES (@username_att);
    END IF;
END$$

Best Answer

The best way to evaluate performance is by measuring it- you are the only person that can evaluate that on the real application, or, much better, on a staging environment with a copy of the traffic or an emulation of such.

Hardware, other code running, throughput, how slow are those new queries, amount of data you have, etc. will tell you what you want to know- to the point that the same code can make it or break it under different circumstances.

Having said that, if you just want a high level opinion of the idea will work, without knowing much about your system- I would personally would do this kind of analysis asynchronously based on external logs (or your login_att table), as normally you don't want to punish your normal users if the occurrences are not frequent enough. Also triggers are not my favorite way to handle application logic. However, if you cannot modify the source code, it doesn't add much to the total latency, and you test it and it seem acceptable, go on- but please test it doesn't break your application when there are thousands of parallel attempts per second due to the extra writes.

Related Question