performance degrades. why???

[Stanislav Arkhipov]

hello

just trying mysql for some sort of analytical processing.

mysql> status
--------------
C:\Program Files\MySQL\MySQL Server 5.5\bin\mysql.exe Ver 14.14 Distrib 5.5.10, for Win64 (x86

Connection id: 14
Current database:
Current user: root@localhost
SSL: Not in use
Using delimiter: ;
Server version: 5.5.10 MySQL Community Server (GPL)

AMD Phenom, 8GB RAM. test database takes about 500MB.

I have the procedure (all extra code removed for testing):

BEGIN
  DECLARE dc          DATE;
  declare c, length INT;
  DECLARE i           VARCHAR(40);
  DECLARE tmp1, tmp2  FLOAT;

    SET c = iterations;
    set length=DATEDIFF(d2, d1) - lt;
    SELECT prod INTO i FROM filtersku WHERE id = 1;
    START TRANSACTION;
    WHILE c > 0
    DO
      set dc=d1;
      -- SET dc = d1 + INTERVAL CEIL(length * RAND()) DAY;
      SELECT
        CAST(COALESCE(SUM(qty) / COALESCE(s, 1), 0) AS DECIMAL(10, 0)) into tmp1
      FROM
        moves m
        LEFT OUTER JOIN season sea
          ON (MONTH(m.tdate) = sea.mn AND m.prod = sea.product)
      WHERE
        m.prod = i AND
        m.moving_type = 3
        AND m.tdate BETWEEN dc AND (dc + INTERVAL (lt - 1) DAY);
      SET c = c - 1;
    END WHILE;
    COMMIT;
END

check it several times:

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (0.53 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (1.11 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (1.61 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (2.11 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (2.62 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (3.18 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (3.93 sec)

mysql> \r
Connection id: 28
Current database: *** NONE ***

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (0.53 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (1.09 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (1.61 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (2.11 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (2.64 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (3.18 sec)

mysql> call td_auto.gen_histogram_1sku_fake('2008-01-01','2011-04-30',3,3000);
Query OK, 0 rows affected (3.92 sec)

note execution time raises within same connection.

1. what's the reason of such behavior?
2. what could i do to prevent it? have to run SP about 1e5 times...


ini:
character-set-server=utf8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=10
query_cache_size=0
query_cache_type = 0
table_open_cache=256
tmp_table_size=60M
thread_cache_size=8

innodb_additional_mem_pool_size=8M
innodb_flush_log_at_trx_commit=0
innodb_log_buffer_size=8M
innodb_buffer_pool_size=600M
innodb_log_file_size=120M
innodb_thread_concurrency=10
innodb_write_io_threads = 8
innodb_read_io_threads = 8