Skip navigation links

MySQL Forums :: Performance :: performance degrades. why???


Advanced Search

performance degrades. why???
Posted by: Stanislav Arkhipov ()
Date: June 10, 2011 05:43AM

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

Options: ReplyQuote


Subject Views Written By Posted
performance degrades. why??? 1103 Stanislav Arkhipov 06/10/2011 05:43AM
Re: performance degrades. why??? 553 Rick James 06/11/2011 09:07AM
Re: performance degrades. why??? 410 Stanislav Arkhipov 06/14/2011 01:43AM
[solved]: performance degrades. why??? 417 Stanislav Arkhipov 06/16/2011 01:10AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.