Navazuji ne svůj článek MS SQL 2005 versus MySql 5.1, kde jsem porovnával oba databázové systémy. Nakonec jsem se rozhodl přeci jen provést porovnání rychlosti. Nejde o žádný
promyšlený benchmark, ale pouze o jednoduchý test. Vytvářím v něm tabulku s 10
mil. záznamů a dvě menší tabulky, které jsou na tuto napojeny. Testuji dotaz
pomocí INNER JOIN, který by měl výkon zjistit.Nejprve jsem vytvořil testovací data v MS SQL pomocí této procedury:
USE [TestRychlosti]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[VytvorData]
AS
BEGIN
SET NOCOUNT ON;
declare @max_radku int;
set @max_radku = 10000000;
declare @start_datum datetime;
set @start_datum='2000-01-01';
declare @cislo int;
declare @datum datetime;
declare @k int;
print 'vytvoreni tabulky table_zaklad';
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_zaklad]') AND type in (N'U'))
DROP TABLE [dbo].[table_zaklad];
CREATE TABLE [dbo].[table_zaklad](
[cislo] [int] IDENTITY(1,1),
[datum] [datetime] NULL
);
-- Naplneni tabulky table_zaklad
set @k=0;
while @k < @max_radku begin INSERT INTO [TestRychlosti].[dbo].[table_zaklad] ([datum]) VALUES (dateadd(hh,@k,@start_datum)); set @k=@k+1; end print 'vytvoreni tabulky table_datum'; IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_datum]') AND type in (N'U')) DROP TABLE [dbo].[table_datum]; CREATE TABLE [dbo].[table_datum]( [cislo] [int] IDENTITY(1,1), [datum] [datetime] NOT NULL ); -- deklaruji kurzor declare table_zaklad_c cursor FORWARD_ONLY READ_ONLY for select cislo,datum from table_zaklad; open table_zaklad_c; fetch next from table_zaklad_c into @cislo, @datum; while @@FETCH_STATUS = 0 begin if (rand()*10)>7
begin
INSERT INTO [TestRychlosti].[dbo].[table_datum] ([datum]) VALUES (@datum);
-- print 'cislo: ' + str(@cislo);
end
fetch next from table_zaklad_c into @cislo, @datum;
end
-- odstranuji kurzor
close table_zaklad_c;
deallocate table_zaklad_c;
print 'vytvoreni tabulky table_cislo';
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[table_cislo]') AND type in (N'U'))
DROP TABLE [dbo].[table_cislo];
CREATE TABLE [dbo].[table_cislo](
[cislo] [int] not null,
[datum] [datetime] not NULL
);
-- deklaruji kurzor
declare table_cislo_c cursor
FORWARD_ONLY READ_ONLY for select cislo,datum from table_zaklad;
open table_cislo_c;
fetch next from table_cislo_c into @cislo, @datum;
while @@FETCH_STATUS = 0
begin
if (rand()*10)>9
begin
INSERT INTO [TestRychlosti].[dbo].[table_cislo] ([cislo],[datum]) VALUES (@cislo,@datum);
-- print 'cislo: ' + str(@cislo);
end
fetch next from table_cislo_c into @cislo, @datum;
end
print 'odstranuji kurzor 3';
close table_cislo_c;
deallocate table_cislo_c;
print 'pridavam index table_zaklad';
CREATE NONCLUSTERED INDEX IX_table_zaklad ON dbo.table_zaklad (cislo,datum)
print 'pridavam index tabulce table_cislo';
ALTER TABLE dbo.table_cislo ADD CONSTRAINT PK_table_cislo PRIMARY KEY CLUSTERED
(cislo) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
print 'pridavam index tabulce table_datum';
ALTER TABLE dbo.table_datum ADD CONSTRAINT PK_table_datum PRIMARY KEY CLUSTERED
(datum) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
END
Během zpracování této procedury MS SQL vytěžoval procesor cca na 25 procent a maximum použité RAM pro vlastní SQL server bylo 795 MB. SQL Server Management Studio (smsee.exe) vkládání dat nejvíce zatěžovalo a zabíralo více jak 1,5 GB RAM. Délka zpracování trvala přibližně 110 minut, ale s počítačem jsem během toho normálně pracoval. Potom jsem provedl testovací dotazy v MS SQL. Každý testovací dotaz byl proveden 5x a výsledek je průměr z těchto jednotlivých výsledků. Po té, abych nemusel proceduru přeprogramovat pro MySql, jsem testovací data vyexportoval z MS SQL do CVS souborů pomocí utility bcp.exe. Do MySql jsem data naimportovalpomocí SQLyog. Ručně jsem potom vytvořil stejné indexy jako v MS SQL:
CREATE TABLE `table_zaklad` (
`cislo` int(11) DEFAULT NULL,
`datum` datetime DEFAULT NULL,
KEY `cislodatum` (`cislo`,`datum`)
) ENGINE=InnoDB/MyISAM
CREATE TABLE `table_cislo` (
`cislo` int(11) NOT NULL DEFAULT '0',
`datum` datetime DEFAULT NULL,
PRIMARY KEY (`cislo`)
) ENGINE=InnoDB/MyISAM
CREATE TABLE `table_datum` (
`cislo` int(11) DEFAULT NULL,
`datum` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
PRIMARY KEY (`datum`)
) ENGINE=InnoDB/MyISAM
Velkým problémem byly MySql typy tabulek. InoDB tabulky fungovali správně. Chtěl jsem ovšem také otestovat tabulky MyISAM, které by podle mě dostupných informací měly být rychlejší. Sice mají některá omezení, ale ty s testem nesouvisí. Měl jsem ale velké potíže data naimportovat do MyISAM tabulek. Import sice nehlásil žádnou chybu, ale data v tabulce nebyla. Občas došlo k zahlášení chyby konzistence tabulky a opravy nepomáhaly. Zkoušel jsem i změnit typ tabulek z InnoDB na MyISAM, ale i v tomto případě došlo ke ztrátě dat. Nakonec jsem přišel na to, že neplechu dělá SQLyog verze 7.0 CE. Stačilo data v SQLyog vyexportovat a potom naimportovat pomocí MySql řádkového klienta.
Nastavení MySql je 500 MB Key buffer, 2 MB Sort buffer size , Query cache limit 1 MB a Cache size 15 MB.
| Počet záznamů |
| table_zaklad |
10 000 000 |
++++++++++ |
| table_datum |
2 999 882 |
+++ |
| table_cislo |
1 001 260 |
+ |
| Velikost dat na disku (MB) |
| MS SQL 2005 Express (mdf+ldf) |
647 |
| MS SQL 2008 Express (mdf+ldf) |
841 |
| MySql 5.1 InnoDB (podle Schema Tables) |
754 |
| MySql 5.1 MyISAM (myi+myd+frm) |
433 |
Při testování na MySql jsem narazil na problém s cache. Nastavil jsem u MySql maximální použití RAM 500 MB pro index block. První dotaz se vždy vykonával několik sekund, i několik desítek. Každý další trval ovšem méně jak 1 ms. Tedy čím více dotazů bych prováděl, tím by pro MySql byl lepší průměr. Nakonec jsem to vyřešil tak, že jsem před každou sadou
testů provedl restart databáze a to jak u MySql, tak u MS SQL. Prováděl jsem přímo restart příslušné služby. Po restartu jsem vždy provedl Select 1 a Select 2 (viz dále), pak jsem opět provedl restart služby. Není to asi ideální způsob testování, ale na lepší jsem nepřišel.
Pokud jsem prováděl testy bez restartu služby, ukazovaly výsledky u druhého a dalších dotazů MySql 0 ms a MS SQL 3-4 sekundy. V tomto případě ale samozřejmě záleží na nastavení cache.
Vlastní testování proběhlo pomocí dvou dotazů (select). U dotazu Select 2 některé indexy úmyslně neexistují. Je zajímavé, že s touto situací se lépe vyrovnal MS SQL 2005.
Select 1: select count(*) as sel1 from table_zaklad tz inner join table_datum td on tz.datum = td.datum inner join
table_cislo tc on tz.cislo = tc.cislo (vrátilo 300794)
Select 2: select count(*) as sel2 from table_cislo tc inner join table_datum td on tc.cislo=td.cislo inner join table_zaklad tz on td.datum = tz.datum (vrátilo 300551)
Poznámky k testování:
- 1x mi spadl instalační program MS SQL a musel jsem instalaci provést od začátku znovu.
- Při generování testovacích dat, spadl celý MS SQL server. Nestačil ani jeho restart. Služba sice běžela, ale nedalo se k ní připojit. Z hlediska efektivity jsem zvolil pro Microsoft produkty oblíbenou strategii: MS SQL jsem reinstaloval. Jaké bylo moje překvapení, když ani potom se k serveru pomocí Management studia nedalo připojit. Chybu
jsem vůbec nepochopil. Nakonec jsem zvolil připojení přes shared memory, místo default a připojit se podařilo. Asi byla chyba mezi klávesnicí a židlí, ale kde to nevím.
| Výsledky v sekundách |
|
|
Select 1 |
Select 2 |
Koeficinet |
| MS SQL 2005 Express |
70,71 |
65,78 |
1,04 |
| MS SQL 2008 Express |
109,09 |
92,21 |
1,54 |
| MySql 5.1 InnoDB |
34,14 |
104,78 |
1,06 |
| MySql 5.1 MyISAM |
44,89 |
85,73 |
1,00 |
Výsledky mě příliš nepřekvapili. Databáze MySql a MS SQL 2005 jsou srovnatelné. Pocitově mi více vyhovuje MySql, ale jde skutečně pouze o pocit. Technicky lze srovnatelně nasadit obě řešení. Hardware počítače na kterém jsem testoval je CPU Intel Core 2 1.80 GHz, 2 GB RAM, MS Windows XP prof. Vlastní hardware ovšem nepovažuji za důležitý,
podstatný je rozdíl mezi databázemi, v tomto případě ovšem není významný. Koeficient, který je počítán podle nejlepšího výsledku, kterého dosáhlo MySql MyIsam, ukazuje, že s výjimkou MS SQL 2008 jsou všechny databáze plně srovnatelné.
MS SQL 2008
Během přípravy testování Microsoft uvolnil novou verzi svého SQL serveru, tentokrát s označením 2008. Neodolal jsem a provedl testování i této verze. Bohužel podle mého testování MS SQL 2008 Express podává ze všech testovaných produktů nejhorší výsledky. MS SQL 2008 je tedy dalším novým produktem Microsoftu, který by zasloužil dopracovat. Narážím tím na významné chyby v .NET 3.5 SP1.
Závěr
Je jasné, že test, který jsem prováděl je nutné brát s velkou rezervou. Databáze byly poměrně malé; po každém testu jsem musel restartovat proces a tím jsem vyřadil všechny cache a také nejsem žádný databázový guru. Pro mne má tento test jeden důležitý výsledek: MySql je plně srovnatelnou a použitelnou databází stejně jako MS SQL a MS SQL 2008 je pomalejší proti předchozí verzi. Zda je toto zpomaléní pro provoz významné, to se neodvažuji rozsoudit.
Záznamů
