ShopNix

Tagebuch eines Shops auf Basis von osCommerce

Posts Tagged ‘SQL

Zeichensätze im Shop

leave a comment »


Inzwischen ist der internationale Zeichensatz utf8 schon fast Standard. Wer Shops in verschiedenen Sprachen betreut, wird daher über kurz oder lang darauf umstellen um Konvertierungen zwischen den verschiedenen Zeichensätzen künftig zu vermeiden.

MySQL bietet dazu eine Vielzahl von Parametern, deren Auswirkungen bedacht sein wollen. Die Parametern sind in Systemvariablen abgelegt und können vom Client abgefragt werden.

SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

Diese Parameter können teilweise auch vom Client per SQL geändert werden und ihr Zusammenspiel ist nicht nur für die Ausgabe wesentlich, sondern auch für die Sortierfolge oder die Suche nach Strings mit dem Schlüsselwort LIKE. In Abhängigkeit von den Einstellungen konvertiert MySQL ggfls. die Ausgabe. Hat man alles richtig gemacht, sieht nicht nur die Ausgabe sauber aus, sondern eine Suche nach einem Suchbegriff mit Umlaut bringt, unabhängig von der Groß- / Kleinschreibung, alle Ergebnisse.

Für meinen VARIO-Helicopter Shop zog ich zunächst einen Dump und kodierte ihn mit dem Linux-Tool recode von iso-8859-15 nach utf8 um. Anschließend fügte ich folgende Zeilen ein:

ALTER DATABASE `3_vario-es` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
set names 'utf8';
set character set 'utf8';

und las den Dump in die Datenbank ein. Um auch die Clientverbindung zum Shop richtig einzustellen, fügte ich anschließend in shop/includes/functions/database.php ab Zeile 22 zwei zusätzliche Zeilen ein:

    if ($$link) {
    	mysql_select_db($database);
// ShopNix.b 
    	mysql_query("set names 'utf8'");
    	mysql_query("set character set 'utf8'");
// ShopNix.e
    }

Aufpassen: Die Funktion muß natürlich auch im Admin-Bereich angepasst werden!

Die sprachabhängigen Dateien habe ich ebenfalls mit recode umkodiert und in der jeweiligen Führungsdatei (z.B. german.php) die Konstanten für die Kodierung angepasst. Aufpassen: Der W3C-Standard will die Schreibweise „utf-8“ für die HTML-Header, MySQL braucht „utf8“!

Zum Umkodieren mit PHP5 muss (unter Debian) das Paket php5-recode installiert sein. Hier ein Beispiel des Funktionsaufrufs: mysql_real_escape_string(recode_string('ISO-8859-1..UTF-8', $row['categories_name'])) im Zusammenhang mit einem Transfer von der alten Datenbank mit ISO-8859-1 Kodierung zur aktuellen Datenbank mit UTF-8.

Advertisements

Written by spessart

11. November 2009 at 09:31

xt:Commerce – Performance

leave a comment »


Nach Einbau eines Moduls für suchmaschinenfreundliche URL brach die Performance des Systems erneut zusammen. Es wird nichts über bleiben, als mit Hilfe der Logfiles die SQL-Queries zu analysieren und nach und nach zu optimieren. MySQL hat, wie jede brauchbare Datenbank, einige Instrumente um ungünstig formulierte Queries aufzustöbern. Im ersten Schritt schalte ich in der Konfiguration (Debian: /etc/mysql/my.cnf) das Protokoll für langsame Queries ein. Für mich sind die folgenden Parameter geeignet:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 2
log-queries-not-using-indexes

Ich protokolliere also alle Queries, deren Ausführung länger als 2 Sekunden dauert, und solche die keine Indizes verwenden, in einem eigenen Log-File. Ein Beispiel eines solchen Queries:

select distinct p.products_id, p.products_price, p.products_tax_class_id,
  p.products_image, pd.products_name
from  products p, products_description pd, products_to_categories p2c, categories c
where p.products_status = '1' and c.categories_status = '1'
  and p.products_ordered > 0
  and p.products_id = pd.products_id
  and pd.language_id = '2'
  and p.products_id = p2c.products_id
  and p2c.categories_id = c.categories_id
  and '424' in (c.categories_id, c.parent_id)
order by p.products_ordered desc limit 10;

Im MySQL-Client führe ich dieses Query mit dem vorgestellten Schlüsselwort EXPLAIN aus und erhalte als Ergebnis unter anderem die Zahl der sequentiell abgearbeiteten Datensätze. In meinem Beispiel sind das 1786 Datensätze. Davon werden 1780 Sätze verworfen, denn mein Query liefert unterm Strich nur 6 Datensätze zurück. Ändere ich die vorletzte Zeile meines Queries in:

  and (c.categories_id = '424' or c.parent_id = '424')

kann der Optimizer auf Primärschlüssel zurückgreifen und das Query wird performant abgearbeitet. Damit ist zwar die technische Lösung gefunden, aber leider noch lange nicht implementiert. Diese Methode zeigt mir nämlich nicht, wo das Query in der Software vorkommt. Da die Queries in der Shop-Software dynamisch zusammengebaut werden, kann ich auch nicht ohne weiteres nach einer Zeichenkette suchen. Es ist eben wie so oft: Vor den Preis haben die Götter den Schweiß gesetzt.

Dieses spezielle Query fand ich in templates/XXX/source/boxes/best_sellers.php, und stoße damit auf eine erhebliche Schwäche des vielgerühmten Templatesystems. Offensichtlich gelang es nicht, Code und Design zu trennen, denn das Query ist im „Design“ enthalten.

Written by spessart

18. April 2009 at 21:14

Veröffentlicht in Performance, xtCommerce

Tagged with , , ,

xt:Commerce – MySQL 5.0 – Performance

leave a comment »


Die Performance von xt:Commerce ist generell schlechter als die von osCommerce. Das ist zum einen zwangsläufig so, weil mehr Features eben auch mehr Aufwand bedeuten. Ein Teil der Probleme liegt allerdings auch in der kleinen Installationsbasis, die zwangsläufig zu geringerem Feedback führt.

Einen Teil der Probleme kann man durch Tuning der Datenbank reduzieren. Sehr hilfreich ist dazu das Script tuning-primer.sh das z.B. mit wget http://www.day32.com/MySQL/tuning-primer.sh erhältlich ist. Unter Debian ist das Paket bc Voraussetzung für das Script.

Entsprechend der Empfehlungen des Scripts habe ich die Parameter table_cache, read_buffer und join_buffer_size angehoben.

Anschließend habe ich eine Meldung im Supportforum abgesetzt und erhielt postwendend Antwort:

ALTER TABLE `shipping_status` ADD INDEX ( `language_id` );
ALTER TABLE `products` ADD INDEX ( `products_startpage` );
ALTER TABLE `products_to_categories` ADD INDEX ( `categories_id` );
ALTER TABLE `orders_products` ADD INDEX ( `orders_id` , `products_id` );
ALTER TABLE `zones_to_geo_zones` ADD INDEX ( `geo_zone_id` );
ALTER TABLE `tax_rates` ADD INDEX ( `tax_zone_id` );
ALTER TABLE `products` ADD INDEX ( `manufacturers_id` );

Damit reduziert sich das Problem (zumindest auf den ersten Blick) ganz erheblich.

Auf Grund meiner Auswertungen werde ich wohl noch einige Schlüssel hinzufügen. Dabei ist zu beachten, daß diese Indizes natürlich auch von der installierten Version und eventuellen Zusatzmodulen abhängen.

alter table whos_online add index (session_id);
alter table whos_online add index (time_last_click);
alter table specials add index (expires_date);
alter table products add index (session_id);
alter table personal_offers_by_customers_status_1 add index (products_id);
alter table products_description add index (language_id);
alter table customers add index (customers_status);
alter table products add index (products_ordered);
alter table specials add index (products_id);
alter table orders add index (orders_status);
alter table customers add index (customers_email_address);
alter table search_keywords add index (search_text);
alter table products_xsell add index (products_id);
alter table products_xsell add index (products_xsell_grp_name_id, products_id);
alter table content_manager add index (language_id, content_group);

Um Notwendigkeit und Wirkung eines Schlüssels zu testen, betrachte ich zunächst die Queries, die in mysql-slow.log protokolliert werden und kopiere sie mit dem vorangestellten Schlüsselwort „explain“ in den MySQL-Client zur Ausführung. Dann lege ich den oder die Schlüssel an, von denen ich mir eine Performancesteigerung erwarte. Anschließend lasse ich mir wieder mit explain die Auswirkungen anzeigen. Wenn ein Schlüssel keinen Vorteil bringt, lösche ich ihn sofort wieder, denn auch ein Index zuviel ist nicht gut für die Gesamtleistung des Systems.

Written by spessart

30. März 2009 at 16:15

Veröffentlicht in osCommerce, Performance, xtCommerce

Tagged with , ,