oli's blog

Advanced MySQL DBA Workshop - draft

Sat, 2010-09-11 15:13 — oli
Taxonomy upgrade extras: 
mysql
dba
workshop
course

With a partner we are planning and Advanced MySQL DBA workshop. As the name says it should be a workshop. Its planned duration is 2 days. So within 2 days we can cover 4 to 8 topics more deeply.

Who is corrupting InnoDB blocks?

Tue, 2010-08-10 18:01 — oli
Taxonomy upgrade extras: 

Because I have seen this week again a similar situation I thought about what could cause such InnoDB block corruptions. I came to the following evil-doers:

FromDual references

Mon, 2010-08-02 12:35 — oli
Taxonomy upgrade extras: 

What customers say about FromDual

Translate...
Wir sind mit der Funktionalität und der Einführung sehr zufrieden. Das gesamte Cluster läuft absolut stabil. Dies ist ein erheblicher Vorteil für unseren Kunden denn wir können unsere Dienstleistungen unterbrechungsfrei anbieten. Wir werden Galera bei weiteren Projekten einsetzen und bestehende Master/Slave-Anwendungen umstellen.
A. Rempening, Datenbank-Administrator, KiKxxl GmbH
 
Neben den fachlich sehr fundierten Tipps und Diskussionen hat mich die unkomplizierte und effiziente Art und Weise von FromDual beeindruckt! Das waren wertvolle Tage und meine Erwartungen wurden sogar übertroffen!
T. Schneider, Leiter IT, Mespas AG
 
Wir fühlen uns beim FromDual Support sehr gut betreut :)
Andreas Rose, DBA bei der AVM GmbH in Berlin

Case study / Referenzbericht

KiKxxl Gmbh: Hochverfügbare Kommunikationsservices auf Basis der MySQL Datenbank und Galera Cluster (Highly available communication services based on a MySQL database and Galera Cluster) (PDF, 420 kbyte)

CREATE ALGORITHM = TEMPTABLE VIEW

Fri, 2010-06-11 10:34 — oli
Taxonomy upgrade extras: 

To find out more about the VIEW’s behaviour I was playing around with the ALGORITHM = TEMPTABLE feature [1]. It was also related to a customers question of how VIEW’s effect performance of queries.

FlexViews - Materialized Views for MySQL

Thu, 2010-05-06 21:45 — oli
Taxonomy upgrade extras: 

Justin Swanhart from Percona just poked me an told me about FlexViews, a package for Materialized Views he built, based on my ideas. Please have also a look there, possibly it can help you any further…

FromDual becomes Open Database Alliance (ODBA) Silver Partner

Sun, 2010-04-25 14:36 — oli
Taxonomy upgrade extras: 
mysql
consulting
open database alliance
partner
mysql-consulting

Uster, Switzerland – April 26, 2010 – FromDual has signed the Service Provider Partnership Agreement of the Open Database Alliance (ODBA).

Is RethinkDB a possible solution for RRD SE?

Fri, 2010-04-02 08:44 — oli
Taxonomy upgrade extras: 

When I dug through RethinkDB white papers I have seen that they make use of round-robin mechanisms.

MySQL and MariaDB Configuration File template (my.cnf/my.ini)

Mon, 2010-03-15 22:53 — oli
Taxonomy upgrade extras: 
configuration
sample
my.cnf
my.ini
template

The MySQL and MariaDB default configuration is not very great for production use. Some of the default values in my.cnf should be changed when you need it for business critical applications.

MySQL High-Availability (HA) solutions

Mon, 2010-03-15 22:25 — oli
Taxonomy upgrade extras: 
high availability
ha
drbd
architecture
heartbeat

The following solutions are used with MySQL:

The MySQL CSV Storage Engine

Mon, 2010-03-15 20:53 — oli
Taxonomy upgrade extras: 
storage engine
csv

A little summary about the MySQL CSV table engine:

  • The SQL-Demo script (930 byte) for the following article.
  • The CSV converter to convert normal CSV files into a CSV format which is for MySQL acceptable.

Caution: Use on your own risk!

MySQL Cluster analysis for foodmart

Mon, 2010-03-15 20:19 — oli
Taxonomy upgrade extras: 
mysql cluster
memory
sizing
foodmart

This is an automated analysis of the DBI:mysql:database=foodmart;host=localhost database for migration into MySQL Cluster. No warranty is made to the accuracy of the information.

ndb_size.pl report for database foodmart (21 tables)

Mon, 2010-03-15 20:08 — oli
Taxonomy upgrade extras: 

Connected to: DBI:mysql:database=foodmart;host=localhost

Including information for versions: 4.1, 5.0, 5.1

MySQL Architectures Overview

Mon, 2010-03-15 19:00 — oli
Taxonomy upgrade extras: 
mysql
architecture

Database Architecture & Design

Application logic in the middle tier?

Reduce money spent on software licenses by moving logic to the middle tier. This means moving stored procedures into the application server or web server layer where it is much cheaper to scale out. Reducing or eliminating business logic running in the DB may reduce Database CPU utilisation and hence save support costs for the DB Portability is key! [1]

What's going on when MySQL does operations on Partitions

Mon, 2010-03-15 16:50 — oli
Taxonomy upgrade extras: 
operations
partition

Following question came up recently: What happens if you drop or add a partition of an existing already partitioned table with ALTER TABLE? Will it be copied or will just the single partition be dropped and added? How are the index(es) rebuild after such a drop/add?

Operating System Signals on different platforms

Mon, 2010-03-15 16:21 — oli
Taxonomy upgrade extras: 
operating system
signal
platform
kill
shell> kill -l

Linux

 1) SIGHUP 2) SIGINT 3) SIGQUIT 4) SIGILL
 5) SIGTRAP 6) SIGABRT 7) SIGBUS 8) SIGFPE
 9) SIGKILL10) SIGUSR111) SIGSEGV12) SIGUSR2
13) SIGPIPE14) SIGALRM15) SIGTERM16) SIGSTKFLT
17) SIGCHLD18) SIGCONT19) SIGSTOP20) SIGTSTP
21) SIGTTIN22) SIGTTOU23) SIGURG24) SIGXCPU
25) SIGXFSZ26) SIGVTALRM27) SIGPROF28) SIGWINCH
29) SIGIO30) SIGPWR31) SIGSYS
34) SIGRTMIN35) SIGRTMIN+136) SIGRTMIN+2
37) SIGRTMIN+338) SIGRTMIN+439) SIGRTMIN+540) SIGRTMIN+6
41) SIGRTMIN+742) SIGRTMIN+843) SIGRTMIN+944) SIGRTMIN+10
45) SIGRTMIN+1146) SIGRTMIN+1247) SIGRTMIN+1348) SIGRTMIN+14
49) SIGRTMIN+1550) SIGRTMAX-1451) SIGRTMAX-1352) SIGRTMAX-12
53) SIGRTMAX-1154) SIGRTMAX-1055) SIGRTMAX-956) SIGRTMAX-8
57) SIGRTMAX-758) SIGRTMAX-659) SIGRTMAX-560) SIGRTMAX-4
61) SIGRTMAX-362) SIGRTMAX-263) SIGRTMAX-164) SIGRTMAX

Solaris 10 (x86, Sparc)

 1) SIGHUP 2) SIGINT 3) SIGQUIT 4) SIGILL
 5) SIGTRAP 6) SIGABRT 7) SIGEMT 8) SIGFPE
 9) SIGKILL10) SIGBUS11) SIGSEGV12) SIGSYS
13) SIGPIPE14) SIGALRM15) SIGTERM16) SIGUSR1
17) SIGUSR218) SIGCHLD19) SIGPWR20) SIGWINCH
21) SIGURG22) SIGIO23) SIGSTOP24) SIGTSTP
25) SIGCONT26) SIGTTIN27) SIGTTOU28) SIGVTALRM
29) SIGPROF30) SIGXCPU31) SIGXFSZ32) SIGWAITING
33) SIGLWP34) SIGFREEZE35) SIGTHAW36) SIGCANCEL
37) SIGLOST
41) SIGRTMIN42) SIGRTMIN+143) SIGRTMIN+244) SIGRTMIN+3
45) SIGRTMAX-346) SIGRTMAX-247) SIGRTMAX-148) SIGRTMAX

AIX

 1) HUP 2) INT 3) QUIT 4) ILL
 5) TRAP 6) LOST 7) EMT 8) FPE
 9) KILL10) BUS11) SEGV12) SYS
13) PIPE14) ALRM15) TERM16) URG
17) STOP18) TSTP19) CONT20) CHLD
21) TTIN22) TTOU23) IO24) XCPU
25) XFSZ26) bad trap27) MSG28) WINCH
29) PWR30) USR131) USR232) PROF
33) DANGER34) VTALRM35) MIGRATE36) PRE
37) bad trap38) bad trap39) bad trap40) bad trap
41) bad trap42) bad trap43) bad trap44) bad trap
45) bad trap46) bad trap47) bad trap48) bad trap
49) bad trap50) bad trap51) bad trap52) bad trap
53) bad trap54) bad trap55) bad trap56) bad trap
57) bad trap58) bad trap59) bad trap60) GRANT
61) RETRACT62) SOUND63) SAK

OpenBSD

 1) HUP 2) INT 3) QUIT 4) ILL
 5) TRAP 6) ABRT 7) EMT 8) FPE
 9) KILL10) BUS11) SEGV12) SYS
13) PIPE14) ALRM15) TERM16) URG
17) STOP18) TSTP19) CONT20) CHLD
21) TTIN22) TTOU23) IO24) XCPU
25) XFSZ26) VTALRM27) PROF28) WINCH
29) INFO30) USR131) USR2

HP-UX

 1) HUP 2) INT 3) QUIT 4) ILL
 5) TRAP 6) ABRT 7) EMT 8) FPE
 9) KILL10) BUS11) SEGV12) SYS
13) PIPE14) ALRM15) TERM16) USR1
17) USR218) CHLD19) PWR20) VTALRM
21) PROF22) IO23) WINCH24) STOP
25) TSTP26) CONT27) TTIN28) TTOU
29) URG30) LOST31) RESERVED32) ?
33) XCPU34) XFSZ35) ?36) ?
37) RTMIN38) RTMIN+139) RTMIN+240) RTMIN+3
41) RTMAX-342) RTMAX-243) RTMAX-144) RTMAX

Quick links to the MySQL documentation

Mon, 2010-03-15 15:50 — oli
Taxonomy upgrade extras: 
documentation
general query log

MySQL Statement Syntax

Data Definition Language (DDL) Statements

ALTER DATABASEALTER EVENTALTER LOGFILE GROUPALTER FUNCTION
ALTER PROCEDUREALTER SERVERALTER TABLEALTER TABLESPACE
ALTER VIEWCREATE DATABASECREATE EVENTCREATE FUNCTION
CREATE INDEXCREATE LOGFILE GROUPCREATE PROCEDURECREATE FUNCTION
CREATE SERVERCREATE TABLECREATE TABLESPACECREATE TRIGGER
CREATE VIEWDROP DATABASEDROP EVENTDROP FUNCTION
DROP INDEXDROP LOGFILE GROUPDROP PROCEDUREDROP FUNCTION
DROP SERVERDROP TABLEDROP TABLESPACEDROP TRIGGER
DROP VIEWRENAME DATABASERENAME TABLE

Data Manipulation Language (DML) Statements

CALLDELETEDOHANDLER
INSERTLOAD DATA INFILEREPLACESELECT
TRUNCATE TABLEUPDATE

MySQL Utility Statements

DESCRIBEEXPLAINHELPUSE

MySQL Transactional and Locking Statements

START TRANSACTIONCOMMITROLLBACKSAVEPOINT
ROLLBACK TO SAVEPOINTLOCK TABLESUNLOCK TABLESSET TRANSACTION
XA Transactions

Account Management Statements

CREATE USERDROP USERGRANTRENAME USER
REVOKESET PASSWORD

Table Maintenance Statements

ANALYZE TABLEBACKUP TABLECHECK TABLECHECKSUM TABLE
OPTIMIZE TABLEREPAIR TABLERESTORE TABLE

Plugin and User-Defined Function Statements

CREATE FUNCTIONDROP FUNCTIONINSTALL PLUGINUNINSTALL PLUGIN

SHOW Statements

SHOW AUTHORS

MySQL Cluster overview

Mon, 2010-03-15 14:19 — oli
Taxonomy upgrade extras: 
mysql cluster

This is a chaotic collection of my MySQL Cluster experience…

MySQL Cluster memory sizing

Mon, 2010-03-15 13:53 — oli
Taxonomy upgrade extras: 
mysql cluster
memory
sizing

MySQL Cluster is pretty fast. The reason for this is, that it is completely memory based. Nowadays memory is still, in contrary to disk, limited to your systems. Thus, before installing a MySQL Cluster you have to calculate the amount of memory you need.

MySQL hints

Mon, 2010-03-15 13:38 — oli
Taxonomy upgrade extras: 
mysql
migration
innodb
hint
primary key
index
prefixed index
sequence
temporary
keyword
auto_increment
memory table

Table of Contents

DBA wisdoms

Mon, 2010-03-15 13:29 — oli
Taxonomy upgrade extras: 
backup
dba
tuning
database administrator

Controlling developers is like herding cats.

Kevin Loney, Oracle DBA Handbook

Oh no, it's not. It's much harder than that!

Pages

Subscribe to RSS - oli's blog