Haneev webdesign

Database drivers vergeleken: PDO, mysqli en mysql

Geschreven op 28 February 2009 door Han. Categorie: Databases. Reacties: 368

De laatste tijd ben ik veel bezig met performance en databases. Over query snelheid heb ik het al gehad, maar ik kon geen fatsoenlijke test vinden voor het verschil in de drivers van mysql. De driver die de query's naar de database stuurt. Men zegt dat mysql traag is en dat pdo veel sneller is, en dat mysqli niet uitmaakt vergeleken met mysql. Het wordt aangeraden om PDO te gebruiken, omdat het een goede database driver is met prepared statements en men raadt mysql af omdat die dat dus niet heeft. PDO en mysqli hebben zo'n beetje dezelfde functionaliteit alleen pdo is iets flexibeler met switchen tussen databases (postgresql en mysql) maar dat heeft geen direct voordeel omdat de sql query's voor postgresql toch anders zijn dan die van mysql.

De test

Ik gebruik dezelfde database set als de vorige post. Voor deze test maak ik gebruik van table_1 (innodb). Ik voer één query 100 keer uit en meet daarvan de totale tijd op. En dat per driver, de snelheid van prepared statements laat ik nu buiten beschouwing.

Resultaten

PDO is de snelste daarna mysqli en als laatste mysql. Zoals je ziet liggen de scores heel erg dicht bij elkaar. Significant is er dan ook geen echte winnaar. Toch kan je hieruit opmaken dat mysql wel de langzaamste is. Voor als je echt performance wilt en elk onderdeel wilt optimaliseren, zou ik toch pdo gebruiken. Ook omdat het flexibel werkt en veilig is als je prepared statements gebruikt.

Mysql heeft zijn tijd gehad en zal ook worden verwijderd bij PHP6. Mysqli is daarentegen een goede vervanger, maar als je nog eens van plan bent over te stappen op een andere database taal zou ik toch pdo gebruiken omdat de structuur van query's uitvoeren (niet de sql) hetzelfde is.

Lees verder ...

InnoDB vs MyISAM

Geschreven op 23 February 2009 door Han. Categorie: Databases. Reacties: 561

Een benchmark van de twee meest gebruikte 'storage engines' oftewel opslag manieren. InnoDB en MyISAM worden veel gebruikt, MyISAM omdat het in het algemeen snel is en InnoDB voor zijn transacties en relaties, maar is InnoDB langzamer dan MyISAM? Moet de oude MyISAM het afleggen tegen de nieuwere InnoDB? Daar krijg je straks antwoord op.

Voor de snelle lezers onder ons: de resultaten en conclusies

Wat ging ik testen?

Ik wilde graag testen wat nu de verschillen waren tussen myisam en innodb van "select" query, en ook een "join" query. Ook wilde ik weten hoe ze reageren op de "load data local into" query. Omdat ik elke keer ook de tabel moest legen heb ik gelijk ook maar de "truncate" en "delete" query meegenomen en de verschillen daartussen.

Gebruikte versies en testomgeving

De test heb ik laten plaatsvinden met twee connecties tegelijk naar twee aparte databases, één voor myisam en de ander natuurlijk voor innodb. De tijd die elke query nodig had om uit te voeren werd gemeten en tussen elke query die wordt uitgevoerd zit een wachttijd van 1 sec, in de helft van de testen is de wachttijd 0. Het gemiddelde daarvan zijn de resulaten. Elk query gedeelte werd 100 keer uitgevoerd, en het gemiddelde per query uitgerekend. Voor elke query wordt ook het flushcommando uitgevoerd.

Gebruikte tabellen en querys:

mysql:

1
2
3
4
56
7
8
9
1011
12
13
14
1516
17
18
19
2021
22
23
24
2526
27
28
29
3031
32
33
34
3536
37
38
39
4041
42
43
44
4546
47
48
49
5051

CREATE TABLE IF NOT EXISTS `multitotaal` (
  `id` int(11) unsigned NOT NULL,
  `type` tinyint(2) NOT NULL,
  `datum` date NOT NULL,
  `van` smallint(5) NOT NULL,  `naar` smallint(5) NOT NULL,
  `name` varchar(200) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`,`type`)
) ENGINE=[MyISAM | InnoDB] DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 -- --------------------------------------------------------
 
--
-- Tabel structuur voor tabel `multi_[1-6]`
-- 
CREATE TABLE IF NOT EXISTS `multi_[1-6]` (
  `id` int(11) unsigned NOT NULL,
  `datum` date NOT NULL,
  `van` smallint(5) NOT NULL,  `naar` smallint(5) NOT NULL,
  `name` varchar(200) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=[MyISAM | InnoDB] DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 --
-- Tabel structuur voor tabel `table_1`
--
 
CREATE TABLE IF NOT EXISTS `table_1` (  `id` int(11) NOT NULL,
  `vId` int(11) NOT NULL,
  `name` varchar(200) collate utf8_unicode_ci NOT NULL,
  `number` varchar(200) collate utf8_unicode_ci NOT NULL,
  `type` tinyint(1) NOT NULL,  PRIMARY KEY  (`id`),
  KEY `vId` (`vId`)
) ENGINE=[MyISAM | InnoDB] DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
 
-- -------------------------------------------------------- 
--
-- Tabel structuur voor tabel `table_2`
--
 CREATE TABLE IF NOT EXISTS `table_2` (
  `vId` int(11) NOT NULL,
  `OverigName` varchar(200) collate utf8_unicode_ci NOT NULL,
  `ExtraInfo` varchar(100) collate utf8_unicode_ci NOT NULL,
  PRIMARY KEY  (`vId`)) ENGINE=[MyISAM | InnoDB] DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

Dit zijn de tabellen die ik gebruikt hebt voor beide engines. ENGINE=[MyISAM | InnoDB] hiermee bedoel ik natuurlijk myisam of innodb, hetzelfde geldt voor [1-6]. Ik heb namelijk 6 tabellen multi_1 tot multi_6.

table_1 = een tabel met exact 1 miljoen verschillende regels.

table_2 = een tabel waarvan table_2.vId gekoppeld is aan table_1.vId (ongeveer 100 waardes)

multi_[1-6] = een tabel met allemaal exact 9501 regels.

multiTotaal = multi_[1-6] allemaal in 1 tabel.

Gebruikte query's:

mysql:

1
2
3
4
56
7
8
9
1011

-- Voor de "delete" query
DELETE FROM [table]
TRUNCATE TABLE [table]
LOAD DATA LOCAL INFILE '[file]' INTO TABLE [table] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
-- Select gedeelteSELECT * FROM table_1 WHERE vId = '[rand]';
SELECT * FROM table_1 t INNER JOIN table_2 m ON m.vId = t.vId WHERE t.vId = '[rand]'
SELECT * FROM multiTotaal WHERE type = [rand-n]  OR type = [rand-n] OR type = [rand-n]
SELECT * FROM [table-n] x INNER JOIN [table-n] y ON y.id = x.id INNER JOIN [table-n] z ON z.id = x.id
-- Niet vergeten, de flush :)FLUSH TABLE [table]

Korte uitleg van [([a-z-]+)] gedeelte.

[table] staat voor een willekeurige tabel [file] voor een bestand met komma gescheiden waardes voor snel importeren. [rand] staat voor een random getal tussen 1-6 voor kiezen van de tabel. [rand-n] staat voor 3 willekeurige tabellen van multi_n.

Conclusie

De resultaten zijn aardig van wat ik er van verwacht had! Alleen wat me toch wel opviel is dat InnoDB met de grote select query toch sneller is. Eigenlijk kunnen we zeggen dat InnoDB de grote winnar is, maar voordat we dat doen ga ik elke grafiek even bijlangs om te verklaren waarom het zo geworden is.

Opmerking achteraf

De tabel groottes verschillen nogal, waar innodb 66 MB nodig heeft voor 1 x 1 miljoen en 6 x 10.000 heeft myisam 'maar' 46 MB nodig. Innodb heeft meer ruimte nodig voor zijn indexes.

InnoDB is de grote winnaar!

InnoDB is goed in wat hij moet doen, namelijk relaties en indexes. Myisam is snel in grote bulk informatie waar weinig mee gechekt moet worden. Wil je grote controle over je database kies dan voor innodb. Wil je snelheid met toevoegen en verwijderen kies dan voor myisam, maar toch is InnoDB de grote winnaar!

Voor op- en aanmerkingen, reageer dan hieronder.

Lees verder ...

1

Ga naar Archief