Делимся тестами

K

kenny

Гость
#1
И так сегодня суббота и на работе делать нечего мягко говоря.
Я создал эту тему для сравнения форков mysql.
Сейчас наиболее популярны две MariaDB и Percona. Опешу ситуацию, в фирме в которой я руковожу отделом своеобразный симбиоз Mysql и MSsql, почему так получилось, да все потому что что мой предшественник был виндузятником и то ли не хватало знаний, то ли не было желание. Но как говорится что есть то есть.
Бд Mysql примерно 12 гигов
БД Mssql около 10.
Вот сегодня и решил заморочится и протестировать Percona, вот теперь и посмотрим действительно она так хороша или нет.
Все тастировалось на виртуальной машине под управлением xen, на гипервизоре установлена freebsd 8
Код:
testers# uname -anr
FreeBSD testers.simvol.local 8.2-RELEASE FreeBSD 8.2-RELEASE #0: Fri Feb 18 02:24:46 UTC 2011    root@almeida.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC  i386
testers#
Как видно из вывода, ядро не собиралось да и тюнинг не производился. Что ж скачал перкону Percona-Server-5.5.15-rel21.0.tar.gz
Сервер из себя представляет 4 виртуальных проца и 1 Гб оперативы :)
И так.
Создаем бд, заливаем 1млн. строк.
real 2m53.465s
user 0m0.976s
sys 0m1.934s
теперь окончательный лог
Код:
SELECT * FROM tests WHERE id = FLOOR(RAND()*1000000)
Benchmark
        Average number of seconds to run all queries: 0.737 seconds
        Minimum number of seconds to run all queries: 0.648 seconds
        Maximum number of seconds to run all queries: 0.814 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

SELECT * FROM tests WHERE index_field = FLOOR(RAND()*1000000)
Benchmark
        Average number of seconds to run all queries: 0.740 seconds
        Minimum number of seconds to run all queries: 0.711 seconds
        Maximum number of seconds to run all queries: 0.789 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

SELECT * FROM tests WHERE noindex = FLOOR(RAND()*1000000)
Benchmark
        Average number of seconds to run all queries: 0.746 seconds
        Minimum number of seconds to run all queries: 0.675 seconds
        Maximum number of seconds to run all queries: 0.781 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

UPDATE tests SET noindex = FLOOR(RAND()*1000000)  WHERE id = FLOOR(RAND()*1000000)
Benchmark
        Average number of seconds to run all queries: 3.129 seconds
        Minimum number of seconds to run all queries: 3.112 seconds
        Maximum number of seconds to run all queries: 3.162 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

UPDATE tests SET noindex = FLOOR(RAND()*1000000) WHERE index_field = FLOOR(RAND()*1000000)
Benchmark
        Average number of seconds to run all queries: 3.147 seconds
        Minimum number of seconds to run all queries: 3.117 seconds
        Maximum number of seconds to run all queries: 3.186 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1
UPDATE tests SET noindex = FLOOR(RAND()*1000000) WHERE noindex = FLOOR(RAND()*1000000)
Benchmark
        Average number of seconds to run all queries: 3.142 seconds
        Minimum number of seconds to run all queries: 3.117 seconds
        Maximum number of seconds to run all queries: 3.179 seconds
        Number of clients running queries: 10
        Average number of queries per client: 1

Benchmark
        Average number of seconds to run all queries: 0.002 seconds
        Minimum number of seconds to run all queries: 0.001 seconds
        Maximum number of seconds to run all queries: 0.006 seconds
        Number of clients running queries: 10
        Average number of queries per client: 0

Benchmark
        Average number of seconds to run all queries: 0.002 seconds
        Minimum number of seconds to run all queries: 0.001 seconds
        Maximum number of seconds to run all queries: 0.003 seconds
        Number of clients running queries: 10
        Average number of queries per client: 0
Вывод топа при диком инсерте


UPD
Код:
MySQL 5.5.15            uptime 0 1:7:18        Sat Sep 24 11:46:25 2011

__ Key _________________________________________________________________
Buffer used        0 of  8.00M  %Used:  0.00
  Current    944.00k            %Usage:  11.52
Write hit      0.00%
Read hit        0.00%

__ Questions ___________________________________________________________
Total          61.23k    15.2/s
  DMS          59.34k    14.7/s  %Total:  96.92
  COM_QUIT      1.66k    0.4/s            2.70
  Com_            309    0.1/s            0.50
  -Unknown        77    0.0/s            0.13
Slow 10 s          0      0/s            0.00  %DMS:  0.00  Log: OFF
DMS            59.34k    14.7/s          96.92
  INSERT      58.01k    14.4/s          94.74        97.76
  SELECT          718    0.2/s            1.17          1.21
  UPDATE          610    0.2/s            1.00          1.03
  DELETE            4    0.0/s            0.01          0.01
  REPLACE          0      0/s            0.00          0.00
Com_              309    0.1/s            0.50
  drop_table      81    0.0/s            0.13
  set_option      52    0.0/s            0.08
  create_tabl      51    0.0/s            0.08

__ SELECT and Sort _____________________________________________________
Scan              730    0.2/s %SELECT: 101.67
Range              0      0/s            0.00
Full join          3    0.0/s            0.42
Range check        0      0/s            0.00
Full rng join      0      0/s            0.00
Sort scan          4    0.0/s
Sort range          0      0/s
Sort mrg pass      0      0/s

__ Table Locks _________________________________________________________
Waited              0      0/s  %Total:  0.00
Immediate      59.35k    14.7/s

__ Tables ______________________________________________________________
Open              36 of  400    %Cache:  9.00
Opened            137    0.0/s
__ Connections _________________________________________________________
Max used          12 of  151      %Max:  7.95
Total          1.66k    0.4/s

__ Created Temp ________________________________________________________
Disk table        43    0.0/s
Table            283    0.1/s    Size:  16.0M
File                5    0.0/s

__ Threads _____________________________________________________________
Running            1 of    1
Cached              0 of    0      %Hit:  0.06
Created        1.66k    0.4/s
Slow                0      0/s

__ Aborted _____________________________________________________________
Clients            1    0.0/s
Connects            1    0.0/s

__ Bytes _______________________________________________________________
Sent            3.52M  872.1/s
Received      39.64M    9.8k/s

__ InnoDB Buffer Pool __________________________________________________
Usage          32.78M of 127.98M  %Used:  25.61
Read hit      100.00%
Pages
  Free          6.09k            %Total:  74.39
  Data          2.10k                    25.60 %Drty:  0.00
  Misc              1                      0.01
  Latched                                  0.00
Reads        198.45M  49.1k/s
  From file      148    0.0/s            0.00
  Ahead Rnd                0/s
  Ahead Sql                0/s
Writes          1.01M  250.9/s
Flushes        13.43k    3.3/s
Wait Free          0      0/s
_ InnoDB Lock _________________________________________________________
Waits            540    0.1/s
Current            0
Time acquiring
  Total        927887 ms
  Average        1718 ms
  Max            3265 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads          158    0.0/s
  Writes      59.37k    14.7/s
  fsync        58.57k    14.5/s
  Pending
    Reads          0
    Writes          0
    fsync          0

Pages
  Created      1.95k    0.5/s
  Read            147    0.0/s
  Written      6.72k    1.7/s

Rows
  Deleted          6    0.0/s
  Inserted    289.34k    71.7/s
  Read        324.13M  80.3k/s
  Updated        164    0.0/s
Еще немного тестов
Собственно сами плагины
Код:
+--------------------------------+----------------+---------------+--------------------+---------------------+----------------+------------------------+---------------+-------------------------------------+----------------+-------------+
| PLUGIN_NAME                    | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE        | PLUGIN_TYPE_VERSION | PLUGIN_LIBRARY | PLUGIN_LIBRARY_VERSION | PLUGIN_AUTHOR | PLUGIN_DESCRIPTION                  | PLUGIN_LICENSE | LOAD_OPTION |
+--------------------------------+----------------+---------------+--------------------+---------------------+----------------+------------------------+---------------+-------------------------------------+----------------+-------------+
| INNODB_RSEG                    | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB rollback segment information | GPL            | ON          |
| INNODB_SYS_TABLES              | 1.1            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB SYS_TABLES                  | GPL            | ON          |
| INNODB_SYS_TABLESTATS          | 1.1            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB SYS_TABLESTATS              | GPL            | ON          |
| INNODB_SYS_INDEXES            | 1.1            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB SYS_INDEXES                  | GPL            | ON          |
| INNODB_SYS_COLUMNS            | 1.1            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB SYS_COLUMNS                  | GPL            | ON          |
| INNODB_SYS_FIELDS              | 1.1            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB SYS_FIELDS                  | GPL            | ON          |
| INNODB_SYS_FOREIGN            | 1.1            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB SYS_FOREIGN                  | GPL            | ON          |
| INNODB_SYS_FOREIGN_COLS        | 1.1            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB SYS_FOREIGN_COLS            | GPL            | ON          |
| INNODB_SYS_STATS              | 1.1            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | XtraDB SYS_STATS table              | GPL            | ON          |
| INNODB_TABLE_STATS            | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB table statistics in memory  | GPL            | ON          |
| INNODB_INDEX_STATS            | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB index statistics in memory  | GPL            | ON          |
| INNODB_BUFFER_POOL_PAGES      | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB buffer pool pages            | GPL            | ON          |
| INNODB_BUFFER_POOL_PAGES_INDEX | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB buffer pool index pages      | GPL            | ON          |
| INNODB_BUFFER_POOL_PAGES_BLOB  | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | InnoDB buffer pool blob pages      | GPL            | ON          |
| XTRADB_ADMIN_COMMAND          | 1.0            | ACTIVE        | INFORMATION SCHEMA | 50515.0            | NULL          | NULL                  | Percona      | XtraDB specific command acceptor    | GPL            | ON          |
+--------------------------------+----------------+---------------+--------------------+---------------------+----------------+------------------------+---------------+-------------------------------------+----------------+-------------+
 

dimka3210

Пользователь
#3
Было бы неплохо увидеть именно сравнение, так как видя результаты одной БД, невозможно гадать о времени выполнения другой. Цифры в студию. :)