You are here: Home / 2009 / Archives for March 2009

A beginners look at Drizzle – Getting around with SHOW

by

Assuming you have successfully compiled Drizzle, and you are ready to start for the first time, here are some beginner differences with those familiar with the current MySQL 5.1 GA version. Note: drizzle is actually an original fork of the 6.0.x codebase however the code looks very little like mysql anymore.

Getting started differences

The first default MySQL installation step scripts/install_mysql_db is gone, and there is no replacement. The ‘mysql’ schema no longer exists. Talking about schemas, ‘test’ is also gone by default.

While a bin/drizzled_safe exists to start drizzle, this presently doesn’t operate as expected. You should start drizzle with sbin/drizzled &

They still have an empty password for ‘root’. Would be nice if it was at least a default like an Oracle installation, and a password became a mandatory requirement. Speaking of passwords, trying ‘drizzle -uroot -pxxxx’ will result in an unexpected error.

$ bin/drizzle -uroot -pxxx

ERROR:
Non-integer value supplied for port.  If you are trying to enter a password please use --password instead.

Taking a look around at some common mysql commands you may be familiar with.

Information Schema

drizzle> show schemas;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.00 sec)
drizzle> use information_schema;
drizzle> show tables;
+---------------------------------------+
| Tables_in_information_schema          |
+---------------------------------------+
| CHARACTER_SETS                        |
| COLLATIONS                            |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS                               |
| GLOBAL_STATUS                         |
| GLOBAL_VARIABLES                      |
| KEY_COLUMN_USAGE                      |
| PLUGINS                               |
| PROCESSLIST                           |
| REFERENTIAL_CONSTRAINTS               |
| SCHEMATA                              |
| SESSION_STATUS                        |
| SESSION_VARIABLES                     |
| STATISTICS                            |
| TABLES                                |
| TABLE_CONSTRAINTS                     |
| INNODB_CMP                            |
| INNODB_CMP_RESET                      |
| INNODB_CMPMEM                         |
| INNODB_CMPMEM_RESET                   |
| INNODB_LOCK_WAITS                     |
| INNODB_LOCKS                          |
| INNODB_TRX                            |
+---------------------------------------+
23 rows in set (0.00 sec)

Gone are COLUMN_PRIVILEGES ENGINES EVENTS FILES KEY_COLUMN_USAGE PARTITIONS PROFILING ROUTINES SCHEMA_PRIVILEGES TABLE_PRIVILEGES TRIGGERS USER_PRIVILEGES VIEWS. With MySQL privileges and a number of 5.x features removed, that’s expected. I was surprised to see ENGINES gone. More on that later.

SIDE NOTE: Disappointed to see (0.00 sec) still around. I would have expected the client interface could have had an overhaul for timing output, and report say 0.00 milliseconds.

Variables

drizzle> show variables
+----------------------------------------+---------------------------------------------+
| Variable_name                          | Value                                       |
+----------------------------------------+---------------------------------------------+
| archive_aio                            | OFF                                         |
..
| innodb_change_buffering                | inserts                                     |
| innodb_file_format                     | Antelope                                    |
| innodb_file_format_check               | Antelope                                    |
| innodb_replication_delay               | 0                                           |
| innodb_stats_on_metadata               | ON                                          |
| innodb_stats_sample_pages              | 8                                           |
| innodb_strict_mode                     | OFF                                         |
| innodb_use_sys_malloc                  | ON                                          |
| innodb_version                         | 1.0.3                                       |
...
| logging_query_enable                   | OFF                                         |
| logging_query_filename                 |                                             |
| logging_query_threshold_big_examined   | 0                                           |
| logging_query_threshold_big_resultset  | 0                                           |
| logging_query_threshold_slow           | 0                                           |
| logging_syslog_enable                  | OFF                                         |
| logging_syslog_facility                | local0                                      |
| logging_syslog_ident                   | drizzled                                    |
| logging_syslog_priority                | info                                        |
| logging_syslog_threshold_big_examined  | 0                                           |
| logging_syslog_threshold_big_resultset | 0                                           |
| logging_syslog_threshold_slow          | 0                                           |
...
| multi_thread_max_threads               | 2048                                        |
| myisam_block_size                      | 1024                                        |
| optimizer_switch                       |                                             |
| optimizer_use_mrr                      | force                                       |
| pool_of_threads_size                   | 8                                           |
| replicator_directory                   |                                             |
| replicator_enabled                     | OFF                                         |

161 rows in set (0.01 sec)

MySQL 5.1 has some 265 variables values, so huge changes here. The big sections gone are around CHARACTER SETS, SSL, REPLICATION, QUERY_CACHE, LOG, FULL TEXT. The above list shows new variables, some of these are Innodb plugin related.

Status

drizzle> show status
...
112 rows in set (0.01 sec)

MySQL 5.1 has some 290 status and Drizzle currently 112. Same suspects, REPLICATION, SSL, QUERY_CACHE, a lot of COM_ commands, all INNODB related status and THREADS_CREATED and THREADS_RUNNING. Nothing new from Drizzle.

Engines

drizzle> show engines;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'engines' at line 1
drizzle> show plugins;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'plugins' at line 1

An initial surprise here, but knowing that all work is converting to plugins, you found this all in INFORMATION_SCHEMA.PLUGINS

drizzle> select * from plugins;
+---------------------+----------------+---------------+--------------------+----------------+--------------------------------------+--------------------------------------------------------------------------+----------------+
| PLUGIN_NAME         | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_TYPE        | PLUGIN_LIBRARY | PLUGIN_AUTHOR                        | PLUGIN_DESCRIPTION                                                       | PLUGIN_LICENSE |
+---------------------+----------------+---------------+--------------------+----------------+--------------------------------------+--------------------------------------------------------------------------+----------------+
| binlog              | 1.0            | ACTIVE        | STORAGE ENGINE     | NULL           | MySQL AB                             | This is a pseudo storage engine to represent the binlog in a transaction | GPL            |
| compress            | 1.0            | ACTIVE        | UDF                | NULL           | Stewart Smith                        | UDF for compress()                                                       | GPL            |
| crc32               | 1.0            | ACTIVE        | UDF                | NULL           | Stewart Smith                        | UDF for computing CRC32                                                  | GPL            |
| logging_query       | 0.2            | ACTIVE        | LOGGER             | NULL           | Mark Atwood  mark @fallenpegasus.com  | Log queries to a CSV file                                                | GPL            |
| logging_syslog      | 0.2            | ACTIVE        | LOGGER             | NULL           | Mark Atwood  mark @fallenpegasus.com  | Log to syslog                                                            | GPL            |
| multi_thread        | 0.1            | ACTIVE        | SCHEDULING         | NULL           | Brian Aker                           | One Thread Per Session Scheduler                                         | GPL            |
| pool_of_threads     | 0.1            | ACTIVE        | SCHEDULING         | NULL           | Brian Aker                           | Pool of Threads Scheduler                                                | GPL            |
| replicator          | 0.1            | ACTIVE        | REPLICATOR         | NULL           | Brian Aker                           | Basic replication module                                                 | GPL            |
| signal_handler      | 0.1            | ACTIVE        | DAEMON             | NULL           | Brian Aker                           | Default Signal Handler                                                   | GPL            |
| single_thread       | 0.1            | ACTIVE        | SCHEDULING         | NULL           | Brian Aker                           | Single Thread Scheduler                                                  | GPL            |
| uncompressed_length | 1.0            | ACTIVE        | UDF                | NULL           | Stewart Smith                        | UDF for compress()                                                       | GPL            |
| uncompress          | 1.0            | ACTIVE        | UDF                | NULL           | Stewart Smith                        | UDF for compress()                                                       | GPL            |
| ARCHIVE             | 3.5            | ACTIVE        | STORAGE ENGINE     | NULL           | Brian Aker, MySQL AB                 | Archive storage engine                                                   | GPL            |
| CSV                 | 1.0            | ACTIVE        | STORAGE ENGINE     | NULL           | Brian Aker, MySQL AB                 | CSV storage engine                                                       | GPL            |
| MEMORY              | 1.0            | ACTIVE        | STORAGE ENGINE     | NULL           | MySQL AB                             | Hash based, stored in memory, useful for temporary tables                | GPL            |
| InnoDB              | 1.0.1          | ACTIVE        | STORAGE ENGINE     | NULL           | Innobase Oy                          | Supports transactions, row-level locking, and foreign keys               | GPL            |
| INNODB_TRX          | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | InnoDB transactions                                                      | GPL            |
| INNODB_LOCKS        | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | InnoDB conflicting locks                                                 | GPL            |
| INNODB_LOCK_WAITS   | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | InnoDB which lock is blocking which                                      | GPL            |
| INNODB_CMP          | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | Statistics for the InnoDB compression                                    | GPL            |
| INNODB_CMP_RESET    | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | Statistics for the InnoDB compression; reset cumulated counts            | GPL            |
| INNODB_CMPMEM       | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | Statistics for the InnoDB compressed buffer pool                         | GPL            |
| INNODB_CMPMEM_RESET | 1.0.2          | ACTIVE        | INFORMATION SCHEMA | NULL           | Innobase Oy                          | Statistics for the InnoDB compressed buffer pool; reset cumulated counts | GPL            |
| MyISAM              | 1.0            | ACTIVE        | STORAGE ENGINE     | NULL           | MySQL AB                             | Default engine as of MySQL 3.23 with great performance                   | GPL            |
+---------------------+----------------+---------------+--------------------+----------------+--------------------------------------+--------------------------------------------------------------------------+----------------+
24 rows in set (0.00 sec)

This is where we start to see some significant differences. Storage engines are now all plugins. We see some entire new features for SCHEDULING, LOGGING and REPLICATOR.

drizzle> select plugin_type,count(*) from information_schema.plugins group by plugin_type order by 2 desc;
+--------------------+----------+
| plugin_type        | count(*) |
+--------------------+----------+
| INFORMATION SCHEMA |        7 |
| STORAGE ENGINE     |        6 |
| UDF                |        4 |
| SCHEDULING         |        3 |
| LOGGER             |        2 |
| REPLICATOR         |        1 |
| DAEMON             |        1 |
+--------------------+----------+
7 rows in set (0.00 sec)

SHOW Commands

Drizzle supports the following SHOW Commands.

SHOW COLUMNS FROM information_schema.tables;
SHOW CREATE DATABASE information_schema;
SHOW DATABASES;
SHOW ENGINE INNODB STATUS;
SHOW ERRORS;
SHOW INDEX FROM information_schema.tables;
SHOW OPEN TABLES;
SHOW PROCESSLIST;
SHOW STATUS;
SHOW TABLE STATUS;
SHOW TABLES;
SHOW VARIABLES;
SHOW WARNINGS;
SHOW CREATE SCHEMA;
SHOW SCHEMAS;

The following are no longer valid.

AUTHORS, CHARACTER SET, COLLATION, CONTRIBUTORS, EVENT event_name, FUNCTION func_name, PROCEDURE proc_name, TRIGGER trigger_name, VIEW view_name, ENGINES, EVENTS, FUNCTION CODE func_name, FUNCTION STATUS, GRANTS FOR , INNODB STATUS, PLUGINS, PROCEDURE CODE proc_name, PROCEDURE STATUS, PRIVILEGES, PROFILE, PROFILES, SCHEDULER STATUS, TRIGGERS, BINARY LOGS, MASTER LOGS, BINLOG EVENTS, MASTER STATUS, SLAVE HOSTS, SLAVE STATUS,

A Drizzle update – Running version 2009.03.970-development

by

I’ve not looked at compiling and running Drizzle on my server for the past four weeks. Well overdue time for a check and see how it’s going. I saw in today’s planet.mysql.com by Eric Day a new dependency is needed. libdrizzle 0.2.0 now in Drizzle is now required, so I started there.

cd ~/bzr
bzr branch lp:libdrizzle
cd libdrizzle
./config/autorun.sh
./configure
make
sudo make install

No problems there, also documented at the Drizzle Wiki. Great to see the docs up to date. I see my old work on starting the compiling page still relevant. Tested on CentOS 5 and Mac OS/X 10.5

Compiling drizzle was not much more difficult.

cd ~/bzr/drizzle
bzr update
make distclean
./config/autorun.sh
./configure --prefix=/home/drizzle/deploy
make
make install

The problems happened when I started drizzle. Initially I was using bin/drizzled_safe, but it was recommended via IRC#drizzle I stick with sbin/drizzled

sbin/drizzled &
error while loading shared libraries: libprotobuf.so.2: cannot open shared object file: No such file or directory

An investigation of Google Proto Buffers.

$ protoc --version
libprotoc 2.0.2

I see that protobuf 2.0.3 is now available, but this was not the problem.

I got around the problem by specifying the current library path:

$ LD_LIBRARY_PATH=/usr/local/lib sbin/drizzled &

I corrected this problem by adding /usr/local/lib to the default ld path, both the libdrizzle and libprotobuf libs are located there.

$ echo "/usr/local/lib" > /etc/ld.so.conf.d/drizzle.conf
$ ldconfig
$ ls -l /usr/local/lib
total 37240
-rw-r--r-- 1 root root  1194602 Mar 31 17:42 libdrizzle.a
-rwxr-xr-x 1 root root      940 Mar 31 17:42 libdrizzle.la
lrwxrwxrwx 1 root root       19 Mar 31 17:42 libdrizzle.so -> libdrizzle.so.0.0.2
lrwxrwxrwx 1 root root       19 Mar 31 17:42 libdrizzle.so.0 -> libdrizzle.so.0.0.2
-rwxr-xr-x 1 root root  1117979 Mar 31 17:42 libdrizzle.so.0.0.2
-rw-r--r-- 1 root root 12199302 Nov 30 23:32 libprotobuf.a
-rwxr-xr-x 1 root root      836 Nov 30 23:32 libprotobuf.la
lrwxrwxrwx 1 root root       20 Nov 30 23:32 libprotobuf.so -> libprotobuf.so.2.0.0
lrwxrwxrwx 1 root root       20 Aug 27  2008 libprotobuf.so.0 -> libprotobuf.so.0.0.0
-rwxr-xr-x 1 root root  5027949 Aug 27  2008 libprotobuf.so.0.0.0
lrwxrwxrwx 1 root root       20 Nov 30 23:32 libprotobuf.so.2 -> libprotobuf.so.2.0.0
-rwxr-xr-x 1 root root  5586965 Nov 30 23:32 libprotobuf.so.2.0.0
-rw-r--r-- 1 root root  9264068 Nov 30 23:32 libprotoc.a
-rwxr-xr-x 1 root root      852 Nov 30 23:32 libprotoc.la
lrwxrwxrwx 1 root root       18 Nov 30 23:32 libprotoc.so -> libprotoc.so.0.0.0
lrwxrwxrwx 1 root root       18 Nov 30 23:32 libprotoc.so.0 -> libprotoc.so.0.0.0
-rwxr-xr-x 1 root root  3645396 Nov 30 23:32 libprotoc.so.0.0.0
drwxr-xr-x 2 root root     4096 Mar 31 17:42 pkgconfig

Starting

$ sbin/drizzled &
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins.
090331 18:38:08  InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
090331 18:38:08  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
090331 18:38:08 InnoDB Plugin 1.0.3 started; log sequence number 46419
sbin/drizzled: ready for connections.
Version: '2009.03.970-development'  socket: ''  port: 4427  Source distribution

Verifying

$ bin/drizzle -uroot
Welcome to the Drizzle client..  Commands end with ; or g.
Your Drizzle connection id is 1
Server version: 2009.03.970-development Source distribution
Type 'help;' or 'h' for help. Type 'c' to clear the buffer.
drizzle> select version();
+-------------------------+
| version()               |
+-------------------------+
| 2009.03.970-development |
+-------------------------+
1 row in set (0.00 sec)
drizzle> exit

Sweet! Now to try some testing & benchmarking before the barrage of conferences next month, 2009 MySQL Camp, Percona Performance Conference and MySQL Conference & Expo.

I’m going to check out The Juice Database Benchmark next as a more realistic benchmark to DBT2 and sysbench.

Extending vmplot

by

Taking the work already done with vmplot.sh, a useful tool for MySQL performance tuning by Yves and Matt at BigDBAHead, and in true Open Source fashion I’ve enhanced and modified for my own purposes.

These changes include:

  • Error checking for ‘gnuplot’ command on the system
  • Eliminate the first row of sample data, as this is often not a complete sample for the vmstat duration.
  • Created a HTML output file for easy browser viewing
  • Changed Memory scale values from Kilobytes to Megabytes
  • Resizing png’s for optimal 1024×768 display output (2 per row)

Download vmplot

And I get:

So more specifics of what I did.

Install gnuplot.

$ yum install gnuplot

Create vmstat sample file.

vmstat 1 100 > vmstat.out

Generate output graphs (need to work out those warnings)

 ./vmplot -i vmstat.out -o tmp
Warning: empty y range [0:0], adjusting to [-1:1]
Warning: empty y range [0:0], adjusting to [0:1]

And then I can view via a browser, in this case http://localhost/tmp/vmplot.htm

Some worthwhile references include Gnuplot Reference Manual, Gnuplot Examples and Gnuplot in Action

Identifying resource bottlenecks – CPU

by

One of the first steps when addressing a MySQL performance tuning problem is to perform a system audit of the physical hardware resources, then identify any obvious bottlenecks in these resources.

When dealing with CPU, a quick audit should include identifying the number of CPU cores your server has, and the types of these cores. The key file on Linux systems is /proc/cpuinfo.

Number of cores can be found via the command cat /proc/cpuinfo | grep “^processor” | wc -l

You need to look more closely at the file to determine the type of CPU (e.g. below the model name shows Intel(R) Xeon(R) CPU X3220 @ 2.40GHz. The combination of knowing the number of processors (cores) listed and physical id and siblings helps identify how many CPUs and how many cores per CPU exist.

$ cat /proc/cpuinfo
processor	: 0
vendor_id	: GenuineIntel
cpu family	: 6
model		: 15
model name	: Intel(R) Xeon(R) CPU           X3220  @ 2.40GHz
stepping	: 11
cpu MHz		: 2394.051
cache size	: 4096 KB
physical id	: 0
siblings	: 4
core id		: 0
cpu cores	: 4
fpu		: yes
fpu_exception	: yes
cpuid level	: 10
wp		: yes
flags		: fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm syscall nx lm constant_tsc pni monitor ds_cpl vmx est tm2 cx16 xtpr lahf_lm
bogomips	: 4789.96
clflush size	: 64
cache_alignment	: 64
address sizes	: 36 bits physical, 48 bits virtual
power management:

...

Other commands that help with identifying CPU/cores include mpstat and top.

$ mpstat -P ALL 5

11:43:43 AM  CPU   %user   %nice    %sys %iowait    %irq   %soft  %steal   %idle    intr/s
11:43:48 AM  all    0.00    0.00    0.00    0.00    0.05    0.00    0.00   99.95   1033.00
11:43:48 AM    0    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00   1000.40
11:43:48 AM    1    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00      0.00
11:43:48 AM    2    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00     31.40
11:43:48 AM    3    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00      1.00

$ top
0

top - 11:42:09 up 36 days, 13:17,  2 users,  load average: 0.20, 0.24, 0.25
Tasks: 133 total,   1 running, 132 sleeping,   0 stopped,   0 zombie
Cpu0  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu1  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu2  :  0.0%us,  0.0%sy,  0.0%ni,100.0%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Cpu3  :  0.0%us,  0.3%sy,  0.0%ni, 99.7%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   4050776k total,  3825584k used,   225192k free,   397580k buffers
Swap:  1052248k total,      128k used,  1052120k free,  2302408k cached

You can easily identify a CPU bottleneck using the vmstat command.

The following shows an idle system.

$ vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 0  0    128 234088 409632 2474372    0    0     0     0 1081  198  0  0 100  0  0
 0  0    128 234088 409632 2474396    0    0     0     0 1003   59  0  0 100  0  0
 0  0    128 234088 409636 2474392    0    0     0   100 1085  209  0  0 100  0  0
 0  0    128 233836 409636 2474396    0    0     0     0 1014  184  3  0 97  0  0
 0  0    128 233284 409636 2474396    0    0     0     0 1182  435  2  0 98  0  0
 0  0    128 233176 409636 2474396    0    0     0     0 1024  104  1  0 99  0  0
 0  0    128 233176 409636 2474396    0    0     0     0 1079  195  0  0 100  0  0
 1  0    128 233168 409644 2474396    0    0     0   232 1021  188  3  0 97  0  0
 0  0    128 233176 409644 2474396    0    0     0     0 1111  213  2  0 98  0  0
 0  0    128 233176 409644 2474396    0    0     0     0 1005   60  0  0 100  0  0

The key columns (from the man page are)

CPU – These are percentages of total CPU time.

  • us: Time spent running non-kernel code. (user time, including nice time)
  • sy: Time spent running kernel code. (system time)
  • id: Time spent idle. Prior to Linux 2.5.41, this includes IO-wait time.
  • wa: Time spent waiting for IO. Prior to Linux 2.5.41, included in idle.
  • st: Time stolen from a virtual machine. Prior to Linux 2.6.11, unknown.

    • Procs

  • r: The number of processes waiting for run time.

NOTE: The columns of vmstat may vary between different Linux Operating Systems.

If you system is CPU Bound then you will observe this. Look at id,us,sy,r

$ vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 3  0    128 275684 397176 2300672    0    0     0     0 1118  427 74  2 25  0  0
 3  0    128 217404 397176 2300672    0    0     0     0 1017  138 74  1 25  0  0
 6  0    128 239584 397176 2300672    0    0     0     0 1086  350 93  2  5  0  0
 4  0    128 269468 397176 2300672    0    0     0     0 1005  229 98  2  0  0  0
 4  0    128 217636 397180 2300668    0    0     0   168 1087  251 99  2  0  0  0
 4  0    128 240576 397180 2300668    0    0     0     0 1006  182 99  2  0  0  0
 4  0    128 270708 397180 2300668    0    0     0     0 1079  338 98  2  0  0  0
 4  0    128 218752 397180 2300684    0    0     0     0 1005  106 99  1  0  0  0
 4  0    128 226316 397180 2300684    0    0     0     0 1077  308 98  2  0  0  0
 4  0    128 198664 397184 2300680    0    0     0    76 1010  250 99  1  0  0  0
 4  0    128 179444 397184 2300680    0    0     0     0 1077  238 100  0  0  0  0
 4  0    128 185396 397184 2300688    0    0     0     0 1006  210 99  1  0  0  0
 4  0    128 199408 397184 2300688    0    0     0     0 1079  336 99  1  0  0  0

You should also be wary of a Single CPU Bound process. This is why knowing the number of cores is important. In this example, one CPU is bound.

$ vmstat 1
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st

 0  0    128  99592 412544 2477580    0    0     0     0 1017   89  0  0 100  0  0
 0  0    128  99592 412544 2477580    0    0     0     0 1090  222  0  0 100  0  0
 0  0    128  99592 412544 2477580    0    0     0     0 1019   98  0  0 100  0  0
 1  0    128  99592 412544 2477580    0    0     0     0 1096  347 14  0 86  0  0
 1  0    128  99592 412548 2477576    0    0     0    84 1030  194 25  0 75  0  0
 1  0    128  99592 412548 2477576    0    0     0     0 1094  300 25  0 75  0  0
 1  0    128  99592 412548 2477580    0    0     0     0 1012   76 25  0 75  0  0
 1  0    128  99592 412548 2477580    0    0     0     0 1096  318 25  0 75  0  0
 1  0    128  73192 412548 2477580    0    0     0     0 1039  273 29  0 70  0  0
 1  0    128  77284 412556 2477572    0    0     0   268 1122  373 25  1 75  0  0
 2  0    128  83592 412556 2477584    0    0     0     0 1036  374 27  1 72  0  0
 0  0    128  56220 412564 2477576    0    0     0   172 1017   84  7  0 94  0  0
 0  0    128  56220 412564 2477576    0    0     0     0 1078  192  0  0 100  0  0

$ mpstat -P ALL 1
12:15:55 PM  CPU   %user   %nice    %sys %iowait    %irq   %soft  %steal   %idle    intr/s
12:15:56 PM  all   25.00    0.00    0.00    0.00    0.00    0.00    0.00   75.00   1072.00
12:15:56 PM    0    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00   1001.00
12:15:56 PM    1    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00      0.00
12:15:56 PM    2    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00     62.00
12:15:56 PM    3  100.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00      9.00

12:15:56 PM  CPU   %user   %nice    %sys %iowait    %irq   %soft  %steal   %idle    intr/s
12:15:57 PM  all   25.00    0.00    0.00    0.00    0.00    0.00    0.00   75.00   1021.00
12:15:57 PM    0    0.00    0.00    0.00    0.00    0.00    1.00    0.00   99.00   1001.00
12:15:57 PM    1    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00      0.00
12:15:57 PM    2    0.00    0.00    0.00    0.00    0.00    0.00    0.00  100.00     18.00
12:15:57 PM    3  100.00    0.00    0.00    0.00    0.00    0.00    0.00    0.00      2.00

I will be detailing identifying bottlenecks of Memory, Disk and Network in future posts. You can also find out more at the MySQL User Conference “Monitoring 101 – Simple stuff to save your bacon” session.

Two *FREE* MySQL Conferences Next Month

by

The annual MySQL Conference & Expo will be held in this year on April 20-23 in Santa Clara, California with a double twist.

Not one, but *two* FREE additional MySQL Conferences are running at the same time, in the same hotel. If you on the west coast you can effectively get a free conference with many MySQL experts speaking at them. I am speaking at all three on three different topics.

The first announcement was the 2009 MySQL Camp organized by Sheeri K. Cabral – The She-BA in line with the O’Reilly approach of having a smaller un-conference within a conference such as with Web 2.0 NY last year.

However the big news was the Percona Performance Conference which was to be held in an adjacent location at the same time, is available now within the same hotel, the Hyatt Regency. In some regards, the Schedule is a better lineup then the official conference.

Percona is a major player in providing services to the MySQL eco-system, the failure to include Percona speakers such as Baron Schwartz was seen as a bizarre move. As a member of the MySQL Conference review committee, Percona submitted a number of presentations, and a number received high scores, more then sufficient in comparison to others accepted.

The MySQL eco-system is at a very fragile junction point, there are numerous independent versions, patches and forks now available, and the recent decision by Sun Microsystems, the trademark owner of the ‘mysql’ term to request Google to enforce trademark usage within Google Ad Words did not seem a productive move for a open source produce. Read more at
Hurting the little guy and MySQL Banned In Google Adword Campaigns. Combined with recent news that MySQL, may now become part of IBM, greater uncertainty may prevail.

These conferences will be unique opportunity to see and hear hopefully more details of what’s happening in the MySQL World.

Twitter Tips

by

I have in the past questioned the value of Twitter as an effective business tool, but it continues to defy the trend of inability to bridge the business gap with social media.

Even with still continual growth problems (at least it’s not down as much) Twitter is everywhere I go, see or do. You see it at business events, business cards, meetups even on CNN Headline News. There are so many various differ twitter sites, applications, widgets etc, I’m surprised there isn’t a twitter index just of the twitter related sites.

I have now incorporated Twitter into my professional site and I’m using this micro-blogging approach more to share my professional skills and interests to my growing band of followers. I don’t expect to make the Twitter top list which is headed by CNN Breaking News with 667,353 followers.

Even Lance Armstrong (who rates 9th) used Twitter for press releases this week of his injuries.

For more reading check out How Twitter Makes You A Better Writer and 27 Twitter Applications Your Small Business Can Use Today.

I was surprised to see How to get a job by blogging: Tips for a setting up the kind of professional blog that will get you hired, barely mention Twitter.

Now be sure to add a background appropriate to your Twitter. This one is wicked.

Classic quotes – Community One East

by

The CommunityOne East 2009 conference has finished up. There were a few classic statements made by the speakers during the day. They included.

“We have a community reception, that’s a long way to say free beer.”

“Google is the dial tone of the Internet, if it’s not there people start freaking out.”

“I am an insom-maniac, a late night hacker.”

“Having a successful catastrophic – Achieving your marketing goals, and your site crashes due to the load.”

“Ruby is a beautiful expressive fun language.”

(talking about cloud providers) — “Lock-in, it’s like marriage, it’s not necessarily a bad thing.”

“It wasn’t a red carpet, but it was carpeted.”

Event: CommunityOne East in New York, NY.
Article Author: Ronald Bradford

Priceless Monty

by

While working with Monty Widenius on a bug I reported in MariaDB I was surprised to not get an IRC response for a few minutes. When committed Monty can identify, create a workaround, and patch a problem in code before you have time to read all the responses he also types. See my Monty’s Monument “Passion is a timeless wisdom” comment. One thing I forgot to say in that entry was, while 9pm in New york, it was at last 3am-5am Monty time.

We all love memorable quotes, so here is one.

“sorry, machine died; First time in years” 

[9:40pm] montywi: then just try compiling mysqld.o, no need to wait for everything else...
....
[9:58pm] rbradfor: make is clean.
[9:59pm] montywi: sorry, machine died;  First time in years
[10:00pm] rbradfor: laughs out load, a priceless monty quote, love it.
...

Your Code, Your Community, Your Cloud… Project Kenai

by

Following the opening keynote announcement about Kenai I ventured into a talk on Project Kenai.

With today’s economy, the drive is towards efficiency is certainly a key consideration, it was quoted that dedicated hosting servers only run at 30% efficiency.

An overview again of Cloud Computing

  • Economics – Pay as you go,
  • Developer Centric – rapid self provisioning, api-driven, faster deployment
  • Flexibility – standard services, elastic, on demand, multi-tenant

Types of Clouds

  • Public – pay as you go, multi-tenant application and services
  • Private – Cloud computing model run within a company’s own data center
  • Mixed – Mixed user of public and private clouds according to applications

SmugMug was referenced as a Mixed Cloud example.

Cloud Layers

  • Infrastructure as a Services – Basic storage and computer capabilities offer as a service (eg. AWS)
  • Platform as a Service – Developer platform with build-in services. e.g. Google App Engine
  • Software as Service – applications offered on demand over the network e.g salesforce.com

Some issues raised about this layers included.

  • IaaS issues include Service Level, Privacy, Security, Cost of Exit
  • PaaS interesting point, one that is the bane of MySQL performance tuning, that is instrumentation
  • SaaS nothing you need to download, you take the pieces you need, interact with the cloud. More services simply like doing your Tax online.

Sun offers Project Kenai as well as Zembly.

Project Kenai

  • A platform and ecosystem for developers.
  • Freely host open source projects and code.
  • Connect, community, collaborate and Code with peers
  • Eventually easily deploy application/services to “clouds”

Kenai Features

  • Code Repository with SVN, Mercurial, or an external repository
  • Issue tracking with bugzilla, jira
  • collaboration tools such as wiki, forums, mailing lists
  • document hosting
  • your profile
  • administrative role

Within Kenai you can open up to 5 open source projects and various metrics of the respositories, issue trackers, wiki etc.

The benefits were given as the features are integrated into your project, not distributed across different sites. Agile development within the project sees a release every 2 weeks. Integration with NetBeans and Eclipse is underway.

Kenai is targeted as being the core of the next generation of Sun’s collaboration tools. However when I asked for more details about uptake in Sun, it’s only a request, not a requirement for internal teams.

The API’s for the Sun Cloud are at http://kenai.com/projects/suncloudapis.

Event: CommunityOne East in New York, NY.
Presenter: Tori Wieldt, Sun Microsystems
Article Author: Ronald Bradford

Everybody is talking About Clouds

by

From the opening keynote at CommunityOne East we begin with Everybody is talking About Clouds.

It’s difficult to get a good definition, the opening cloud definition today was Software/Platform/Storage/Database/Infrastructure as a service. Grid Computing, Visualization, Utility Computing, Application Hosting. Basically all the buzz words we currently know.

Cloud computing has the ideals of truly bringing a freedom of choice. For inside or outside of an enterprise, the lower the barrier, time and cost into freedom of choice give opportunities including:

  • Self-service provisioning
  • Scale up, Scale down.
  • Pay for only what you use.

Sun’s Vision has existed since 1984 with “The NETWORK is the Computer”.

Today, Sun’s View includes Many Clouds, Public and Private, Tuned up for different application needs, geographical, political, with a goal of being Open and Compatible.

How do we think into the future for developing and deploying into the cloud? The answer given today was, The Sun Open Cloud Platform which includes the set of core technologies, API’s and protocols that Sun hopes to see uptake among many different providers.

The Sun Cloud Platform

  • Products and Technologies – VirtualBox, Sun xVM, Q-Laser, MySQL
  • Expertise and Services
  • Partners – Zmanda, Rightscale, Kickapps
  • Open Communities – Glashfish, Java, Open Office, Zfs, Netbeans, Eucalyptus

The Sun Cloud includes:

  • Compute Service
  • Storage Service
  • Virtual Data Center
  • Open API – Public, RESTful, Java, Python, Ruby

The public API has been released today and is available under Kenai. It includes two key points:

  • Everything is a resource http GET, POST, PUT etc
  • A single starting point, other URI’s are discoverable.

What was initially showed was CLI interface exmaples, great to see this still is common, a demonstration using drag and drop via a web interface was also given, showing a load balanced, multi-teired, multi server environment. This was started and tested during the presentation.

Then Using Cyberduck (a WebDAV client on Mac OS/X) and being able to access the storage component at storage.network.com directly, then from Open Office you now get options to Get/Save to Cloud ( using TwoGuys.com, Virtual Data Center example document).

Seamless integration between the tools, and the service. That was impressive.

More information at sun.com/cloud. You can get more details also at the Sun Microsystems Unveils Open Cloud PlatformOfficial Press Release.

Event: CommunityOne East in New York, NY.
Article Author: Ronald Bradford

CommunityOne East – An open developer conference

by

With an opening video from thru-you.com – an individual taking random you-tube video and producing video mashup’s, the CommunityOne East conference in New York, NY beings.

The opening introduction was by Chief Sustainability Officer Dave Douglas. Interesting job title.

His initial discussion was around what is the relationship between technology and society. A plug for his upcoming book “Citizen Engineer” – The responsibilities of a 21st Century Engineer. He quotes “Crisis loves an Innovation” by Jonathan Schwartz, and extends with “Crisis loves a Community”.

He asks us to consider the wider community ecosystem such as schools, towns, governments, NGO’s etc with our usage and knowledge of technology.

Event: CommunityOne East in New York, NY.
Author: Ronald Bradford

Hurting the little guy?

by

Today I come back from the dentist, if that wasn’t bad enough news, I get an email from Google AdWords titled Your Google AdWords Approval Status.

In the email, all my AdWords campaigns are now disapproved, because of:

SUGGESTIONS:
-> Ad Content: Please remove the following trademark from your ad:
mysql.

Yeah right. I can’t put the word ‘MySQL’ in my ads. How are people to now find me? It would appear that many ads have been pulled not just mine. Is this a proactive measure by Google? is this a complaint from the MySQL trademark holder Sun Microsystems?

I’d like any comment, feedback or suggestions on how one can proceed here.

It reminds me of the days CentOS advertised itself as an “Open source provider of a popular North American Operating System”, or something of that nature.

Understanding the various MySQL Products & Variants

by

The MySQL marketplace today is far more complex then simply choosing between a particular version of MySQL that Sun/MySQL produces.
The MySQL server product in general is released under the GNU General Public License (GPL) v2, however you should carefully review the MySQL Legal Policies as a number of exceptions and different license agreements operate for companion tools such as MySQL Cluster, MySQL client libraries and documentation for example.

Looking into the MySQL ecosystem for products, I’ve produced the following categories:

  • Sun/MySQL Official Products
    • MySQL Versions
  • MySQL Variants
    • Community
    • Enterprise
  • MySQL Plugins
  • MySQL Patches
  • MySQL Alternatives

Why does such a diversification occur?  I attribute this to three primary causes:

  • The GPL license by nature allows for an organization to take the product, modify it and use it for their specific needs. They can also provide these patches under GPL for others to use and incorporate. While this has occurred for example  Google , FaceBook, eBay , Proven Scaling and Percona to name a few, Sun/MySQL has elected not to undertake any proactive process of incorporating these in any timely fashion.
  • The policy of Sun/MySQL to allow for contributions was so strict, and combined with a properietory Version Control System BitKeeper you had to purchase, there was little incentive for community contributions in relation to so many other open source projects
  • The Sun/MySQL management and decision makers didn’t listen to the community and paying customers, and over the past 3-5 years the product life cycle, features, release schedule and quality can be questioned.

Sun/MySQL Official Products

Sun/MySQL holds the license to the MySQL server products. They release official binaries and the source code (due to GPL).  Even within MySQL, there are several products that differ subtly and to the untrained eye it can be confusing to understand and determine what is best. Your can download from www.mysql.com the following versions:

  • MySQL Server 5.1  GA
  • MySQL Community Server 5.0 GA
  • MySQL Enterprise Server 5.0 GA
  • MySQL Cluster NDB 6.3

  • MySQL Server 4.1 (EOL)
  • MYSQL Server 6.0 (Alpha)

MySQL Versions

It is important that you understand the MySQL Versions, especially in evaluating any of the following referenced variants, patches etc.
The common path for MySQL Server versions is with a generally linear numbering systems including historical versions 3.23, 4.0 and 4.1.  These versions have now reached End Of Life (EOL) for support, however emergency security patches are applied where necessary.
Continuing from 4.1, you have the 5.0, 5.1 versions which are both Generally Available (GA), and then version 6.0 which is currently Alpha.

Further complexity happens when within the Sun/MySQL Official products, several forks/branches have occurred.  These include:

  • The MySQL 5.0 Community & Enterprise split occurred at MySQL Version 5.0.27
  • At this time, the Community version (free to download) continued with the intention of allowing for community contributions. Only one patch was ever accepted, and SHOW PROFILES was introduced in MySQL 5.0.37.  To date, 11 versions have been released to the current 5.0.77 version.
  • MySQL Enterprise (available under subscription) is itself comprised of three subtypes, these are Rapid Update Service Packs(monthly), Quarterly Service Packs (quarterly) and Hot-fix releases.  To date 37 versions have been released to the current 5.0.78 version.
  • MySQL Cluster, was part of the MySQL Server product until this was branched/forked at  MySQL Version 5.1.23. This enabled MySQL Cluster to be labeled as Production Ready for Cluster clients, and not be held back by continued delays in the 5.1 server release.   Starting with a new versioning scheme with 6.1, the MySQL Cluster NDB produces new versions far exceeding the volume of the server, with to date 23 versions in 6.1 , 18 in version 6.2 and 24 in version 6.3.      I am not advocating that features and quality are better or worse, simply that activity and interaction with community and users is far greater.
  • MySQL 5.1 Maria is a special branch starting at MySQL Version 5.1.24 that includes the Maria Storage Engine. This is the next generation of the MyISAM Storage Engine, both architected by the creator of MySQL, Monty Widenius.  It is undercertain this will continue as a product released officially by Sun/MySQL.

In Review

With just reading this introduction you can understand the confusion that exists when new customers/clients are beginning to evaluate the different MySQL Versions.

In my next post, I’ll talk more about:

  • MySQL Variants, those I consider variants use the MySQL Interface, protocol and support the standard connectors.  These include community versions (e.g. Solid, Infobright, Sphinx) and commercial versions (e.g. KickFire, InfoBright, Nitro).  
  • MySQL patches are improvements that have been released to the community and are now becoming part of common third party MySQL packages, such as Percona, Proven Scaling and Out Delta
  • MySQL Plugins are a feature of MySQL 5.1, and allow for pluggable storage engines into MySQL.  While several companies have had to produce custom binaries due to the API limitions (especially with the optimizer), a number of engines support the API including Innodb, PBXT and filesystem engine.
  • MySQL Alternatives include any MySQL related products that have now deviated from being supported under the MySQL protocol.  Most notably here is Drizzle.

More Information

Ronald Bradford is Principal at 42SQL. We provide consulting and advisory services for the MySQL ecosystem with a focus on MySQL database performance, architecture and scalability. 42SQL also provides education in MySQL including the “MySQL Essentials” training course. You can find more information regarding this offering and an upcoming schedule at 42SQL Education.

Beginner CSV Engine issues

by

I’ve just started using the CSV engine for a practical application and I’ve come across a few subtle and unexpected issues/limitations.

First, you can’t create any columns in your CSV table nullable.

mysql> create table t1(i INT) ENGINE=CSV;
ERROR 1178 (42000): The storage engine for the table doesn't support nullable columns

RTFM shows this was introduced in 5.1.23. See CSV Limitations

The second and more annoying was creating a CSV table, inserting a sample row (just to check the syntax), then replacing the file ([datadir]/[schema]/[table].CSV) with the same format, but with additional generated rows. This was to no avail when attempting to SELECT from the table.

The solution was to do a REPAIR TABLE [table] in order to see the newly refreshed data.
Futhermore, some more RTFM shows in Repairing and Checking CSV Tables an important caveat to using the CSV engine and loading data directly by file copy.

Warning: Note that during repair, only the rows from the CSV file up to the first damaged row are copied to the new table. All other rows from the first damaged row to the end of the table are removed, even valid rows.

Buyer beware.

Infobright Community Edition(ICE) – It's Free

by

The March NY MySQL Meetup featured a presentation from Infobright, a data warehousing solution built on the MySQL Product.

With a pitch of “Simplicity, Scalability and low TCO” I became more impressed with the capability to delivery on these as the presentation proceeded. Here are some highlights.

  • The company and product has been around for a few years. Infobright started as a compression engine to sit beside Teradata, providing a significant cost saving to clients, and allowing a two way data transfer between Teradata.
  • In September 2008, a open source community edition was released, called ICE. (Which I didn’t know)
  • The technology is based on a Rough Set theory, a mathematical approach
  • Using a column oriented approach, compression generally starts at 10:1, different applications can get 30:1 or better
  • There is basically no tuning, there are no indexes. Knowledge is gleaned at data loading and each data pack node holds key information per column, such as range of values (min,max).
  • Some interesting results are, there is a constant load time, it doesn’t degrade over time as the size of your data increases. Also, Query performance scales with data volume.
  • Depending on queries, the knowledge grid can retrieve results without having to uncompress the data, i.e. introspection of the meta data is all that is needed
  • Infobright is not a pluggable storage engine, rather a custom binary of MySQL. This is due to the restrictions of the API and the lack of optimizer push down conditions for example.

The product is not without some limitations, but you have to realize the product is for a data warehousing implementation, not an OLTP web app. It’s not great with SELECT *, and large text strings for example.

Functionality continues to be added, with a recent release adding many more MySQL Functions, but again, Infobright does not claim to be a solution to everybody, there is not UDF support or SP support at this time, however I’d warrant this is really not needed.

While the presentation went into some detail regarding the knowledge grid, data packs, data pack nodes, and pack to pack integration from a slide perspective, the presentation lacked the technical here is how you use the loader to get data out of MySQL and into Infobright. Here is the throughput, etc. As a marketing presentation it had the right content, but I’d like to now see the companion technical presentation.

Having previously been part of the MySQL Consulting team, and having worked also in the Storage Engine API with the Nitro Storage engine I have a distinct advantage of knowing the complexities of integration with MySQL. We can only hope this continues to improve with future releases of MySQL enabling Infobright and other products to integrate better and keep up to date with the MySQL Release cycle.

Identifying Bad Memory

by

I was having problems recently with a dedicated production server, that runs my MySQL Server and a number of websites. It’s most annoying when your system crashes without any reporting in /var/log/messages

The tool of choice from the host provider SoftLayer was PassMark BurnInTest Linux which is installed with every dedicated server.

I will need to investigate open source alternatives, as this is a commercial product, but for the purposes of my pain, this included tool was well worth the investment.

**************
RESULT SUMMARY
**************
Test Start time: Sun Feb 22 16:02:48 2009
Test Stop time: Sun Feb 22 16:07:49 2009
Test Duration: 000h 05m 01s

Test Name Cycles Operations Result Errors Last Error
CPU - Maths 261 488 Billion PASS 0 No errors
Memory (RAM) 2 3.081 Billion FAIL 1 Error verifying data in RAM
Network: 127.0.0.1 412995 4.295 Billion PASS 0 No errors
TEST RUN FAILED

*********************
SERIOUS ERROR SUMMARY
*********************
SERIOUS : 2009-02-22 16:07:31, RAM, SERIOUS: Error verifying data in RAM (x 1)

It was great to get a simple resolution to the problem, bad memory?
With a scheduled maintenance replacement I was operational again.

 **************
RESULT SUMMARY
**************
Test Start time: Sun Feb 22 20:34:37 2009
Test Stop time: Sun Feb 22 20:39:38 2009
Test Duration: 000h 05m 01s

Test Name Cycles Operations Result Errors Last Error
CPU - Maths 267 406 Billion PASS 0 No errors
Memory (RAM) 1 3.664 Billion PASS 0 No errors
Network: 127.0.0.1 334578 3.480 Billion PASS 0 No errors
TEST RUN PASSED

*********************
SERIOUS ERROR SUMMARY
*********************

Are you monitoring RSS & VSZ?

by

Monitoring MySQL Memory is a rather critical task because you can’t limit MySQL’s usage of physical memory resources. Improperly configured servers running MySQL can crash because you don’t understand memory usage.

MySQL uses memory in a number of different ways. Using the Oracle analogy, you can divide the mysqld memory usage into main areas of:

  • SGA – System Global Area
  • PGA – Process Global Area

The SGA is the footprint that MySQL uses for startup. This is attributed to the base footprint of the mysqld process and a number of buffers including:

NOTE: This is for a default MySQL 5.1 install. Other storage engines and/or other versions of MySQL may have additional buffers. Falcon for example in MySQL 6.x has additional buffers.

The PGA is more complex, and the cause of problems for the possible occurrence of your server running out of memory and needing to swap. The goal of monitoring memory usage is to of course avoid this.
This additional memory is a combination of a few areas including:

  • MEMORY tables
  • Connection management (such as thread_cache and table_cache)
  • Per Connection memory usage

The later is the cause of greatest concern, especially for environments that have persistent connections. Per Connection memory usage is a combination of many buffers including the thread_stack, 2 x net_buffer_length (to max_allowed_packet), read_buffer_size, read_rnd_buffer_size, join_buffer_size, sort_buffer_size, and up to min(max_heap_table_size,tmp_table_size). In fact, for example with temporary tables, a query may use multiple temporary tables.

Memory on a per connection basis is kept until the connection is closed. In some instances such as next_buffer_size, this is apparently reduced aftter each SQL Statement result. With a persistent connection model (e.g. Java), ensuring idle connections drop to a low watermark is a valuable task. The confusing part is MySQL instrumentation does not tell you exactly how much is used, and it’s impossible to calculate with available provided data.

As part of monitoring your server, you should monitor the size of the mysqld memory usage, because this will cause you to be proactive rather then reactive to scarce memory resources. You can easily get this using the ps command. For example:

$ps -eopid,fname,rss,vsz,user,command | grep -e "RSS" -e "mysql"
  PID COMMAND    RSS    VSZ USER     COMMAND
 5463 grep       764   5204 ronald   grep -e RSS -e mysql
13894 mysqld_s   596   3936 root     /bin/sh /usr/bin/mysqld_safe
13933 mysqld   4787812 5127208 mysql /usr/sbin/mysqld --basedir=/usr --datadir=/vol/mysql/mysqldata --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-external-locking --port=3306 --socket=/var/run/mysqld/mysqld.sock
13934 logger     608   3840 root     logger -p daemon.err -t mysqld_safe -i -t mysqld

$ ps -eopid,fname,rss,vsz,user,command | grep " mysqld " | grep -v grep | awk '{print $3,$4}'
4787820 5127208


From man ps
rss RSS resident set size, the non-swapped physical memory that a task has used (in kiloBytes). (alias rssize, rsz).
vsz VSZ virtual memory size of the process in KiB (1024-byte units). Device mappings are currently excluded; this is subject to change.
(alias vsize).

The motto of the story, don’t just monitor the free memory of your system, for a database server, you need to closely monitor the primary process on the server, that is mysqld.

References

How MySQL Uses Memory

More Information

Join me for my MySQL User Conference talk on “Monitoring 101 – Simple stuff to save your bacon”.

I also cover monitoring MySQL in my “MySQL Essentials” training course. For more information visit MySQL Education.

Testing your system

by

I have raised this specific topic 3 times this week alone, twice in a MySQL setting.

The fundamental philosophy of testing is NOT to verify features of your product that work, it is to BREAK your system.

One such discussion this week was with a service provider that deployed a new system into an existing ecosystem. The release has been delayed due to development issue, and credibility with customers is now being further damaged because the system is reaching physical hardware limitations after just one month.

With this was described to me, my simple response was. You did not test you system to stress the system to breaking point. To know the limit of your capacity ahead of time is a proactive analysis, not a reactive one.

It’s not that complicated to do, easier in early stage before you have a 50-100-1000 server total environment, but it’s a best practice not see often enough.

Configuration management concepts for database objects

by

Correctly managing your MySQL database objects such as schemas, tables, indexes, base data etc, is critical to the success of a 24×7 online website. I rarely encounter a robust working solution as part of my consulting so I would like to share my experience in identifying the best practices you should be adopting whether your an existing organization or just an individual with a simple website.

Much of the following concept actually pre dates my involvement in MySQL (since 1999), so this is not just applicable for a MySQL RDBMS. For the purposes of this discussion I’d like to focus on the theory successfully used with clients.

Under version control I have the following directory structure:

NOTE If your first observation was “Arrh, Version Control?”, you are in more trouble then you want to be right from day one. You need Version Control such as svn, cvs, bzr, git etc for any website no matter how small.

/database
  /scripts
  /sql
    /schema
    /patch
    /revert
    /admin
  /data
  ....

The /database is a top level directory, and for software packaging for all database related operations, you simply include all contents from /database.

At it’s core, every database object change for configuration management will be addressed in three (3) files.

  • A schema file
  • A patch file
  • A revert file

In fact, you can add version control rules for example to ensure if you add a patch file, a corresponding revert and schema file is also specified.

For a “current” working environment, there are two paths for database object management.

  • An upgrade path
  • A new version creation.

An upgrade path which is the normal production operation, takes an existing database schema and ‘patches’ this to a new revision. As the name suggests, for each ‘patch’ file a corresponding ‘revert’ file can be used to revert the upgrade. For testing and development environments, a current version of the full schema can always be created without using the upgrade path simply by creating the schema with the current schema file.

For the purposes of understanding how this would work in a real environment, I’ll use the Sakila test database and I’ll step through a few examples.

Seeding your configuration management

Because we already have an existing schema, the first step is to seed our new configuration management with the existing schema information.

This would actually involve some duplication, however this will become more apparent in future examples.

We will be creating the following three (3) files:

  • /database/sql/schema/schema.sakila.sql
  • /database/sql/patch/patch.20090303.01.sql
  • /database/sql/revert/revert.20090303.01.sql

/database/sql/schema/schema.sakila.sql
This will be a copy of the sakila-db/sakila-schema.sql. You will need to edit this file to remove the following lines.

DROP SCHEMA IF EXISTS sakila;
CREATE SCHEMA sakila;
USE sakila;

All configuration files must not contain any schema definitions. This will be discussed in more detail at a later time.

/database/sql/schema/patch.20090303.01.sql
This will be a copy of the above file.

/database/sql/schema/revert.20090303.01.sql

DROP PROCEDURE IF EXISTS rewards_report ;
DROP FUNCTION IF EXISTS get_customer_balance;
DROP PROCEDURE IF EXISTS film_in_stock;
DROP PROCEDURE IF EXISTS film_not_in_stock;
DROP FUNCTION IF EXISTS inventory_held_by_customer;
DROP FUNCTION IF EXISTS inventory_in_stock;
DROP VIEW IF EXISTS customer_list;
DROP VIEW IF EXISTS film_list;
DROP VIEW IF EXISTS nicer_but_slower_film_list;
DROP VIEW IF EXISTS staff_list;
DROP VIEW IF EXISTS sales_by_store;
DROP VIEW IF EXISTS sales_by_film_category;
DROP VIEW IF EXISTS actor_info;
DROP TABLE IF EXISTS actor;
DROP TABLE IF EXISTS address;
DROP TABLE IF EXISTS category;
DROP TABLE IF EXISTS city;
DROP TABLE IF EXISTS country;
DROP TABLE IF EXISTS customer;
DROP TABLE IF EXISTS film;
DROP TABLE IF EXISTS film_actor;
DROP TABLE IF EXISTS film_category;
DROP TABLE IF EXISTS film_text;
DROP TABLE IF EXISTS inventory;
DROP TABLE IF EXISTS language;
DROP TABLE IF EXISTS payment;
DROP TABLE IF EXISTS rental;
DROP TABLE IF EXISTS staff;
DROP TABLE IF EXISTS store;

For the purposes of this theory, I will discuss schema creation and management at a later time. For this example, we assume the ‘sakila’ schema has been created and is empty.

The ‘two’ paths

The default path is to apply the patch file to the appropriate schema. In this case, by using the patch file, this would create the current ‘sakila’ schema.

If this fails for example, you should automatically apply the revert script which should restore your environment to it’s original state, in this case an empty schema.

If you wanted to create a new test environment for example, (following creation of the schema), you could simply apply the schema file.

Let’s perform another iteration, to see the full working process.

Adding new objects

Let’s say we wanted to keep additional information such as famous quotes an actor has made. We want to create a new table ‘actor_quote’.

For this we would first create a patch and revert script to manage this new object.
/database/sql/patch/patch.20090303.02.sql

CREATE TABLE actor_quote (
  quote_id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  actor_id SMALLINT UNSIGNED NOT NULL,
  quote   VARCHAR(200) NOT NULL,
  PRIMARY KEY  (quote_id),
  KEY idx_fk_actor_id (actor_id),
  CONSTRAINT fk_actor_quote_actor FOREIGN KEY (actor_id) REFERENCES actor (actor_id) ON DELETE RESTRICT ON UPDATE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

/database/sql/revert/revert.20090303.02.sql

DROP TABLE IF EXISTS actor_quote;

/database/sql/schema/schema.sakila.sql
The contents of the patch file should be appended to this file.

The ‘two’ paths

If we look at the two paths again.

The normal production operation, by using the patch file, would create the new database object.

If this fails for example, you should automatically apply the revert script which should restore your environment to it’s original state, in this case drop the table if it exists. In this simplest example,

If you wanted to create a new test environment for example, (following creation of the schema), you could simply apply the schema file.

Review

We have only touched on the entire process of configuration management for database objects. The implementation of this practice includes meta data and controlling scripts that manage the order of execution, recording operations performed successfully or unsuccessfully for example.

About Standards

Within this overview a number of standards are in place. These include:

  • SQL scripts do not contain any CREATE/DROP DATABASE|SCHEMA commands
  • SQL scripts do not contain any schema/database specific references. This is important for being able to easily test and verify operations. In our above examples, the default Sakila DB contains such information and would be edited appropriately.
  • For Patch and Revert files a chronological date format for naming is used, e.g. YYYYMMDD.XX, where XX is a sequential number for multiple patch/revert scripts for any given day.
  • All SQL statements must be terminated with ‘;’. This is important for the management processes and automated scripts that take these fundamental schema/patch/revert scripts as source information.
  • Where possible, try to make revert scripts, support either a successful or failed patch process. For example, adding IF EXISTS to a DROP TABLE statements supports both cases.
  • It is reasonably obvious to have schema, patch and revert directories as a naming standard, but file name also include this as a prefix. This is performed as a double check, if a file is seen in isolation it’s type can be determined regardless of directory location. Also for logging, only filenames are used.

More Information

Configuration Management in MySQL is one of the topics discussed in the “MySQL Essentials” training course. You can find more information regarding this and other training offerings including an upcoming schedule at 42SQL Education.