Haben wir den InnoDB-Buffer richtig gewählt?

Beim letzten Mal haben wir uns ja angesehen, wie wir eine optimale Größe für den innodb_buffer_pool_size berechnen.

Wir können auch noch kontrollieren, ob wir mit unserem prozentualen Aufschlag ein gutes Gefühl hatten.

Prüfe, was du tust

Das Prüfen ist nicht ganz sooooo banal wie das Ermitteln der potentiell optimalen Größe des Bufferpools.

Vorab schon mal das notwendige SQL:

SELECT (PagesData*PageSize)/POWER(1024,2) BufferGenutztMB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

Wir fragen damit ab, wie viele Bufferpool-Seiten wir nutzen – das müssen wir noch in lesbare Werte umrechnen, indem wir die Anzahl der Bufferseiten mit der Seitengröße (in Byte) multiplizieren. Und wir wollen den Schotter in Megabyte haben, da wir in der my.cnf auch mit Megabyte arbeiten (… nicht müssen, aber wir tun es).

Heraus kommt dabei beispielsweise dies hier:

+-----------------+
| BufferGenutztMB |
+-----------------+
|      234.828125 |
+-----------------+
1 row in set (0.00 sec)

Das heißt, es werden 234MB Buffer genutzt. In der my.cnf hatte ich in meinem Fall zuvor 200MB festgelegt.

Abweichung Baby

Tja, irgendwie wird mehr Buffer genutzt, als wir tatsächlich vorgesehen haben.

Leider habe ich darauf keine Antwort gefunden – bestimmt hat das was mit Rundungen oder der Umsetzung auf Seitenbasis zu tun.

Meine PageSize ist 16.384 Byte:

MariaDB [(none)]> SELECT variable_value PageSize
    -> FROM information_schema.global_status
    -> WHERE variable_name='Innodb_page_size';
+----------+
| PageSize |
+----------+
| 16384    |
+----------+
1 row in set (0.00 sec)

Meine Anzahl Pages für den Bufferpool liegt bei 15.034:

MariaDB [(none)]> SELECT variable_value PagesData
    -> FROM information_schema.global_status
    -> WHERE variable_name='Innodb_buffer_pool_pages_data';
+-----------+
| PagesData |
+-----------+
| 15034     |
+-----------+
1 row in set (0.00 sec)

Habt ihr eine Antwort?

Was jemand von euch darauf schon eine Antwort gefunden hat, ich wäre dankbar für eine Mitteilung… oder mache ich einen Denkfehler irgendwo? Wenn ja, auch hier wäre ich für Aufklärung offen 🙂

Optimale Buffersize für InnoDBs

Wenn wir bei einer MySQL-Datenbank die moderne InnoDB-Engine für die Tabellen nutzen, müssen wir uns mit dem Gedanken warmmachen, dass es statt einem Dutzend verschiedener Buffer-Parameter im Prinzip nur noch einen einzigen gibt.

innodb_buffer_pool_size

Dieser Parameter ist der innodb_buffer_pool_size.

Im Prinzip speichert das Datenbanksystem in diesem Buffer die Indizes und Datenseiten der verwendeten Tabellen. Ausnahmen bilden wir immer Datenfelder, die nicht in der Datenseite selbst gespeichert werden – also beispielsweise BLOBs oder andere Konstrukte, die lediglich per ID oder Teildaten in der Datenseite aufgenommen werden.

Da im Zweifel der Speicherzugriff immer viel schneller als der Zugriff auf einer Festplatte ist, sollten wir natürlich dafür sorgen, dass dieser Buffer möglichst eine optimale Größe hat.

Optimale Größe für den Buffer

Was ist denn nun die optimale Größe?

Denken wir luxuriös: Wir wollen einfach alles im Buffer ablegen, was irgendwie sinnvollerweise in den Buffer gepackt werden kann. Also sämtliche Daten- und Index-Informationen.

Wir bekommen wir denn jetzt heraus, wie groß dieses „Alles“ ist – was ja sicherlich als Näherungsgröße für unsere Buffergröße sinnvoll ist?

Das ist ganz einfach – alle Informationen über sämtliche Tabellen hält MySQL im „information_schema“ bereit.

Da müssen wir nur mal nachsehen 🙂

Information_schema befragen

Diese Abfrage ist eigentlich banal:

SELECT CEILING(InnoDB_Alles/POWER(1024,2)) BOAH_ALLES FROM
(SELECT SUM(data_length+index_length) InnoDB_Alles
FROM information_schema.tables WHERE engine='InnoDB') A

Die Antwort kann dann – bitte ein bisschen Geduld aufbringen, die Abfrage dauert – beispielsweise so lauten:

+------------+
| BOAH_ALLES |
+------------+
|        133 |
+------------+
1 row in set (0.58 sec)

Tada – wir wissen jetzt, dass wir 133MB Daten- und Indexinformationen in unserer Datenbank haben.

Fertig… oder?

Nun. Jetzt haben wir erst mal die Info, was wir an Nettodaten so in unseren Tabellen liegen haben, die per InnoDB-Engine verwaltet werden.

Aber das reicht natürlich noch nicht – wir machen mit den Daten ja auch was, also stellen Abfragen beliebiger Komplexität.

Update: Es ist auch nicht zu vergessen, dass nicht alle Tabellen mit der InnoDB-Engine betrieben werden können. Sämtliche Systemtabellen, dazu gehören auch die Information-Schemas, laufen mit myISAM oder anderen Engines. Die brauchen ebenfalls Buffer – die aber werden zwar vom Hauptspeicher abgezogen, werden aber über die übliche Verdächtigen wie key_buffer_size und so geregelt.

Und für diese Abfragen braucht die Datenbank auch immer mal ein bisschen Platz – für Zwischentabellen oder sonstige Infos.

Überschlägig und je nach Anwendungsfall dürfen wir davon ausgehen, dass 60 bis 80% der Nettodaten noch mal an Bufferbedarf oben drauf kommen – wir können auch 200% Aufschlag nehmen, wenn wir genug Speicher haben. Aber wir sollten ein bisschen vernünftig sein, vor allem dann, wenn auf demselben Rechner noch anderes Zeug läuft – was ja ebenfalls Ressourcen braucht, nech.

Bufferaufschlag

Ich für meinen Fall nehme einen Aufschlag von 60% an. Jetzt können wir natürlich per Taschenrechner die 133MB einfach mit 1,6 multiplizieren und bekommen unseren „endgültigen“ Buffer.

Aber bekanntlich bin ich faul und überlasse alles dem Rechner, was geht:

SELECT CEILING(InnoDB_Alles*1.6/POWER(1024,2)) BOAH_ALLES FROM
(SELECT SUM(data_length+index_length) InnoDB_Alles
FROM information_schema.tables WHERE engine='InnoDB') A

Ergebnis:

+------------+
| BOAH_ALLES |
+------------+
|        213 |
+------------+
1 row in set (0.58 sec)

Tada – also ändern wir doch ein wenig die my.cnf und stellen einen Buffer ein 🙂

 

undefined access to undefined

Ich steh ja so wirklich und ganz und gar auf vielsagende Fehlermeldungen. Echt jetzt. Wer möchte nicht, dass man sofort weiß, was das Problem ist.

undefined access http://www.yellowstoneaussies.de/infos/undefined

Fehlersuche

Tja. Wo fängt man an, wenn man so einen Sondermüll in seinen Serverlogs findet?

Am Besten mal dort, wo es immer so ein bisschen stinkt. Im Javascript-Bereich. Und siehe…

Netzwerkanalyse ist dein Freund

Da fällt uns sofort etwas ins Auge, was uns eine Spur zu scheinen ist:

the undefined
the undefined

Wer zum Geier sucht denn da was unter undefined? Und wieso?

Gucken wir mal, wie die Parameter dieser Anfrage aussehen – ist ja bestimmt wieder ein Ajax-Request…

really undefined
really undefined

Öhö. Ok, Verdacht auf das Übliche zu schieben, war wohl zu Unrecht.

Dann schauen wir uns die Kopfzeilen doch noch mal genauer an…

dgd
dgd

Was ist denn „dgd_scrollbox„?

Scrollen wir doch mal einen halben Kilometer in der Netzwerkanalyse nach oben und schauen uns jedes Javascript an, was das Netzwerk passiert hat (Javascript hat man grundsätzlich immer erst mal im Verdacht bei sowas).

And in the tate – there it is

dgdgdg can't you see
dgdgdg can’t you see

Da ist was mit DGD! Ha.

Suchen wir doch einfach mal nach Ajax… und wir werden fündig in einem noch völlig unklaren Zusammenhang – aber kurz nach Ablaufen des Scripts schlagen haufenweise Antworten aus dem Backend ein, die Bilder für ein Grid-Layout darstellen.

Geistesblitz

Analyse wird hier dann abgebrochen, weil wir einen Verdacht haben. Könnte es vielleicht sein, dass da ein Bild nicht gefunden wird?

Jaaaaa jetzt fragt man sich, woher diese Eingebung gekommen ist. Keine Ahnung. Kam einfach. Kennt ihr doch sicher, irgendwie „ahnt“ man was.

Und – wenn ich die Augen vorher für die Realität geöffnet hätte, wäre mir das wohl schon vorher aufgefallen – was sehen meine mittlerweile genervten und müden Augen:

white planes
white planes

Das weiße Loch da mittendrin gehört doch da nicht hin… oder… habe ich einfach kein Sinn für Ästhetik?

Falls sich jemand über das „oh oh da stimmt was mit SSL nicht“-Symbol in der Adressleiste wundert: So sieht das aus, wenn eine Seite das erste Mal nach dem Cachelöschen angezeigt wird. Ihr solltet die Seiten so niemals sehen.

Hintergrund ist, dass wir die Seiten beim erstmaligen Abruf sofort Netto anzeigen ohne die durch die Parserorgie diverser Funktionen zu jagen. Mein nächtlicher Precacher (Stichwort „Wartungsscript“) kümmert sich darum üblicherweise, wenn ich nicht grad untertägig eine Seite aus dem Cache ballern muss/möchte.

Realitätsabgleich

Looken wir doch mal im Backend nach.

Und was fällt uns da mit der Tür ins Haus?

beitragsbild not found
beitragsbild not found

Und da wir von vorherigen Recherchen wissen, dass ein Postgrid mit Bildanzeige irgendwoher sein Bild beziehen möchte, sind wir uns sicher, dass das das Corpus Delicti ist. Also setzen wir das doch mal.

suffkopp
suffkopp

Dann testen wir noch mal die Seite…

suffkopp in da info
suffkopp in da info

Und im Log… nada 🙂

Naja. Doch mehr als „nada“ – aber „undefined“ ist weg.

Fazit

Manchmal muss man sich echt einen Knoten denken und die Spurensuche aufgeben und sich einfach auf das Gefühl verlassen – dann findet man die Lösung schon.

Deshalb ist es so wichtig, dass wir stundenlang am Rechner hocken und uns die Nächte um die Ohren hauen, ohne, dass man „was sieht“ (wie meine Frau so schön sagt zum Thema nutzloses Herumgammeln).

Da habt ihr noch ein Argument, wieso das, was wir tun, gut & richtig ist.

Gern geschehen 🙂

 

WordPress-Postings in zufälliger Reihenfolge

Wenn ein Blog eine Menge Artikel hat, kann es passieren, dass die älteren Beiträge so tief im Archiv versunken liegen, dass der interessierte Leser die kaum noch zur Kenntnis nehmen kann.

Das ist gerade bei Artikeln, die eher zeitlos sind, schon ein richtiges Problem. Mein Blogprojekt „orga-dich“ hatte genau dieses Problem.

Zufällige Reihenfolge

WordPress ermittelt die Postings, die in der Blogroll, also in der Liste der Beiträge, angezeigt werden, über ein wp_query-Objekt. Davon gibt es erfahrungsgemäß mehrere innerhalb eines Blogs, beispielsweise zum Auslesen aller Menüseiten oder innerhalb bestimmter Seitenelemente, die ebenfalls Artikel oder Seiten enthalten sollen.

Aber es gibt auch eine „Master-Query“, die für die Hauptseite zuständig ist. Und eine Query kann auch Parameter haben, die wir übersteuern können…

Oha, functions.php, ick hör dir trapsen

Innerhalb eines WordPress-Monsters können wir uns mit sogenannten Hooks an verschiedenen Stellen der Verarbeitung „beteiligen“.

Und einer davon ist der „posts_orderby„-Hook: Über den können wir zusätzliche Parameter für die Sortierung an das Query-Objekt mitgeben – also frisch ran ans Werk.

Captain Hook

Einen filter zu einem hook zu schreiben ist banal:

add_filter( 'posts_orderby', 'zufaellige_postings' );

Damit haken wir uns in den „posts_orderby„-Hook ein und geben bekannt, dass die PHP-Function „zufaellige_postings“ ausgeführt werden soll, sobald dieser Hook/diese Action ausgeführt wird.

Diese Funktion sieht für unseren Bedarf auch noch recht übersichtlich aus:

function zufaellige_postings( $orderby ) {
   if( is_front_page() ) {
      $orderby = 'RAND';
      return $orderby;
   }
}

Das „is_front_page()“ sorgt dafür, dass wir nur die Query beackern, die für die Anzeige der Startseite mit der Beitragsliste zuständig ist. Wir wollen ja nicht sämtliche DatenbankPost-Queries zufällig gestalten 🙂

That’s it – oder?

Hm – im Prinzip war es das. Aber wir haben ein Problem. Startseiten enthalten ja nicht grundsätzlich alle Postings des Blogs. Sondern immer nur eine Auswahl, der Rest möchte über eine Seitenwahl ausgewählt werden, die im Fachjargon „Pagination“ heißt:

pagination
pagination

Also dieser Bereich mit den Zahlen (1 2 … 4) und Pfeilen. Damit navigiert der geneigte Leser zwischen den vielen hunderttausend Postings eines Blogs.

Wenn wir unsere Zufallsmethode jetzt live schalten, wird auch alles schön gewürfelt – leider aber auf jeder Seite von Neuem! Das kann ja nicht gewollt sein…

Schöne Lösung aus dem Netz der Netze

Und tatsächlich, Ehre wem Ehre gebührt, ein User mit dem klangvollen Namen „hlashbrooke“ (Hugh Lashbrooke) hat auf GitHub eine passende Lösung gefunden.

Er speichert eine zufällig generierte Seed-Zahl in einer Session-Variable und übergibt sie immer erneut an das Query-Objekt als „rand„-Parameter. Dadurch beginnt der Zufallsgenerator immer wieder beim selben Ausgangswert (= Seed) und generiert dieselben Zufallszahlen.

Wie wir wissen, generieren übliche Algorithmen keine ernsthaft zufälligen Zahlenfolgen – sie brauchen einen Ausgangspunkt (den sog. „Seed“) und wenn dieser gleich ist, wird die Reihenfolge der „zufälligen“ Zahlen auch immer gleich sein.

Auf diese Weise wird bei einem seitenweisen Abruf der Postings die einmal erstellte Reihenfolge beibehalten – und liefert entsprechend nicht für jede Seite neue, zufällige Postings sondern liefert die Postings in einer zufälligen Folge innerhalb der gesamten Query.

So sieht das Spaß dann aus:

session_start();

add_filter( 'posts_orderby', 'randomise_with_pagination' );
function randomise_with_pagination( $orderby ) {

	if( is_front_page() ) {

	  	// Reset seed on load of initial archive page
		if( ! get_query_var( 'paged' ) || get_query_var( 'paged' ) == 0 || get_query_var( 'paged' ) == 1 ) {
			if( isset( $_SESSION['seed'] ) ) {
				unset( $_SESSION['seed'] );
			}
		}
	
		// Get seed from session variable if it exists
		$seed = false;
		if( isset( $_SESSION['seed'] ) ) {
			$seed = $_SESSION['seed'];
		}
	
	    	// Set new seed if none exists
	    	if ( ! $seed ) {
	      		$seed = rand();
	      		$_SESSION['seed'] = $seed;
	    	}
	
	    	// Update ORDER BY clause to use seed
	    	$orderby = 'RAND(' . $seed . ')';
	}

	return $orderby;
}
?>

Leider geil – und funktioniert super! Und ist auch angemessen dokumentiert, danke liebe(r) Hugh Lashbrooke (da sind noch mehr coole Sachen zu finden).

So sieht es dann aus

Im Ergebnis kommt das schon ganz nice.

orga-dich postings
orga-dich postings

SSL Verschlüsselung aktivieren – Fehlschlag

Wie es halt so läuft, ich möchte den nerdporn-blog auch per https abrufbar machen – dazu braucht es dann ein Zertifikat.

Let’s Encrypt It

Die Webseite der Yellowstones hat seit mehreren Wochen eine passende Absicherung bekommen – zusammen mit einem globalen Zusammenbruch unseres Rankings, dass sich erst nach gut einer Woche wieder erholt hatte. Bis Google eben mitbekommen wurde, dass das nur ein klitzekleiner Umzug auf einen anderen Prefix gewesen ist – „mitbekommen wurde“, weil ich über drei Ecken Google die Umleitungen von der „alten“ http-Domain auf die „neue“ https-Domain beibringen musste. Offenkundig und entgegen aller menschlichen Denkweisen sind „http://“ und „https://“ zwei völlig verschiedene Dinge 🙁

Wie dem auch sei. Ich möchte jetzt ein weiteres Zertifikat bei Let’s Encrypt anfordern und zwar über das dazu gehörende Interface unserer Synology. Da gibt es einen netten „Assistenten“, der sich um alles kümmert.

Leider schade

Tja. Leider möchte mir diesmal Let’s Encrypt ums Verreckten noch eins kein weiteres Zertifikat ausstellen. Ständig irgendwelche Fehler beim acme-Abruf – und keine Hilfe oder ein paar mehr näheres Infos weit und breit.

acme
acme

Auch in einem anderen Log tauchen nur Fehler auf, zu denen ich noch keine funktionierende Lösung finden konnte:

let's encrypt part 2
let’s encrypt part 2

Noch ohne Lösung

Diesmal kein sehr hilfreicher Beitrag: Ich habe noch keine Lösung dafür gefunden… das erste Zertifikat ging ohne Probleme aber das zweite trotz aller Iterationen diverser Dinge (Domainnamen, Anmelde-Emailadresse, Portfreigaben/-weiterleitungen etc. pp.) will es einfach nicht klappen… selbst die Fehlermeldungen sind sich nicht einig, die wechseln auch immer wieder mal.

Bisschen frustrierend…

Nachtrag

Öhmmm… peinlich… geht alles, man muss nur mal RTFM ernstnehmen…