LogNotes

#ok Для нагрузочного тестирования MariaDB

CREATE DATABASE test_load;
USE test_load;

CREATE TABLE orders (
  id INT AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  product_id INT NOT NULL,
  quantity INT NOT NULL,
  price DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX(user_id),
  INDEX(product_id)
);


DELIMITER //

CREATE PROCEDURE fill_orders(IN total INT)
BEGIN
  DECLARE i INT DEFAULT 1;
  WHILE i <= total DO
    INSERT INTO orders (user_id, product_id, quantity, price)
    VALUES (
      FLOOR(1 + RAND()*1000),        
      FLOOR(1 + RAND()*500),         
      FLOOR(1 + RAND()*10),          
      ROUND(RAND()*100, 2)           
    );
    SET i = i + 1;
  END WHILE;
END
 //
DELIMITER ;



DELIMITER //

CREATE PROCEDURE run_load_test(IN iterations INT)
BEGIN
  DECLARE i INT DEFAULT 0;
  WHILE i < iterations DO
    
    SELECT user_id, SUM(quantity*price) AS total_spent
    FROM orders
    WHERE created_at > NOW() - INTERVAL 1 DAY
    GROUP BY user_id
    ORDER BY total_spent DESC
    LIMIT 10;
    
    DO SLEEP(0.01);

    SET i = i + 1;
  END WHILE;
END

 //
DELIMITER ;

=============
CALL fill_orders(100000);
CALL run_load_test(1000);
# mysql -u root -e "USE test_load; CALL run_load_test(1000);"