Drupal 7: Performance auf SQL-Server

veröffentlicht von whpelz am Do., 18.02.2016 - 13:11

Performance-Tuning für SQL-Server

Drupal ist in der Standard-Installation mit aktiviertem Localization client auf einem MS-SQL-Server deutlich langsamer als auf einer MySQL-Datenbank.
Die Tabelle locale_sources wird bei jedem Page-Load mehrfach gelesen, da hier nach Übersetzungen gesucht wird. Für den SQL-Server muss für diese Tabelle eine Indizierung nachgebaut werden, wie sie für MySQL existiert.

Ausgangspunkt:

In der MySQL-DB ist auf die Spalte der zu übersetzenden Textstrings (source) ein Index gesetzt (MYSQL-COMMENTS der Lesbarkeit halber weggelassen):

MySQL:

CREATE TABLE `locales_source` (
  `lid` int(11) NOT NULL AUTO_INCREMENT,
  `location` longtext,
  `textgroup` varchar(255) NOT NULL DEFAULT 'default',
  `source` blob NOT NULL,
  `context` varchar(255) NOT NULL DEFAULT '',
  `version` varchar(20) NOT NULL DEFAULT 'none',
  PRIMARY KEY (`lid`),
  KEY `source_context` (`source`(30),`context`)
) ENGINE=InnoDB AUTO_INCREMENT=15808 DEFAULT CHARSET=utf8;

 

SQL-Server:

CREATE TABLE [dbo].[locales_source](
  [lid] [int] IDENTITY(1,1) NOT NULL,
  [location] [nvarchar](max) NULL,
  [textgroup] [nvarchar](255) NOT NULL CONSTRAINT [locales_source_textgroup_df] DEFAULT ('default'),
  [source] [nvarchar](max) NOT NULL,
  [context] [nvarchar](255) NOT NULL CONSTRAINT
  [locales_source_context_df] DEFAULT (''),
  [version] [nvarchar](20) NOT NULL CONSTRAINT [locales_source_version_df] DEFAULT ('none'),
CONSTRAINT [locales_source_pkey] PRIMARY KEY CLUSTERED
([lid] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY])
ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

In der Standard-Konfiguration, wie sie durch den Acquia-Windows Webplatform Installer hingestellt wird, ist kein Index diesbezüglich vorhanden.
Beim SQL-Server gibt es grundsätzlich keine Indizes, die man per Substring auf große Spalten wie blob oder nvarchar(max) setzen kann.

Lösung:

Es wird der linke Teilstring der großen Spalte separat in einer eigenen Spalte abgelegt, auf die dann ein Index gesetzt werden kann.

ALTER TABLE dbo.locales_source ADD
source_left30 AS CAST(LEFT(source, 30) AS NVARCHAR(30)) PERSISTED;

Als computed field wird der Inhalt durch die Datenbank automatisch geführt.
Als persisted wird der Inhalt permant in der Tabelle gespeichert und nicht nur beim SELECT errechnet.

Dadurch läßt sich auf die Spalte ein Index definieren:

CREATE INDEX ix_source_left30_context ON dbo.locales_source (source_left30,context);

Jetzt muss noch auf Drupal-Seite dafür gesorgt werden, dass die neue Spalte auch bei dem entsprechenden Select angesprochen wird.
Hierzu muss die nachfolgende Abfrage im Drupal-Core geändert werden in modules\locale\locale.module

Orginal:

$translation = db_query("SELECT s.lid, t.translation, s.version FROM {locales_source} s \
  LEFT JOIN {locales_target} t ON s.lid = t.lid AND t.language = :language \ 
  WHERE s.source = :source AND s.context = :context AND s.textgroup = 'default'", array(
    ':language' => $langcode,
    ':source' => $string,
    ':context' => (string) $context,
))->fetchObject();

geändert:

// 2016.02.16  whpelz Computer Manufaktur Patch source_left30 f. SQL-Server
  $translation = db_query("SELECT s.lid, t.translation, s.version FROM {locales_source} s \
  LEFT JOIN {locales_target} t ON s.lid = t.lid AND t.language = :language \
  WHERE s.source = :source AND s.source_left30 = :left30 AND s.context = :context \
  AND s.textgroup = 'default'", array(
    ':language' => $langcode,
    ':source' => $string,
    ':left30' => substr($string,0,30),
    ':context' => (string) $context,
))->fetchObject();

Und wenn man nach einem Core-Update vergißt den Patch nachzuführen wird das System einfach wieder langsam ...

 

 

Neuen Kommentar hinzufügen