PlanetMysql.ru — информация о СУБД MySQL

I am finally a citizen of the world. I beat out the 85% statistic in which American's from the USA do not venture out of the USA. I flew into Paris spent 5 days, then went to Brussels (1 day), Amsterdam (2 days), flew to Barcelona had to stay there a day because Venice was on strike, then to Venice (2 days). From Venice to Rome (5 days), then to (day trip) Naples, Pompeii, back to Rome then back to Paris and finally home.

I can go into detail of how awesome this trip was but I am going to focus on how recharged I am. Man am I! During my travel times I geeked out and read the MySQL 5.1 Plugin Development book by Sergei Golubchik and Andrew Hutchings, which is AWESOME!

Now that I am back I hope to put the things I learned from Plugin Development into action and write posts about them.

Some Posts in the next couple of weeks that I am going to focus on. SQL optimizations, Optimizer problems in 5.1, Stalls, performance performance performance.
PlanetMySQL Voting: Vote UP / Vote DOWN

Did you think that underscores and hyphens in MySQL server option names can be used interchangeably in configuration file? Read this tutorial to find out about an exception to this general rule.
PlanetMySQL Voting: Vote UP / Vote DOWN

Have you ever tried compiling MySQL server from source code? If you have not but want to, here are some easy steps to do so on a Debian/Ubuntu Linux machine. These instructions should work on other Linux distributions e.g. SUSE, RedHat, CentOS etc.
PlanetMySQL Voting: Vote UP / Vote DOWN

PlanetMySQL Voting: Vote UP / Vote DOWN

We have been asked about when you should expect the SkySQL price list to go live:  This will be in January 2011.

While we're on the topic of pricing: We do plan to maintain an aggressive pricing policy, as witnessed by our limited-time Gold offer to purchase a SkySQL™ Enterprise Platinum support subscription at the historical MySQL® Enterprise Gold subscription price of just USD 2999.
PlanetMySQL Voting: Vote UP / Vote DOWN

Introduction

In his presentation about PBXT at the DOAG Conference 2010 Paul McCullagh was speaking about memory resident PBXT tables. They will be available in version 1.1 of the PBXT Storage Engine Plug-in. Memory resident PBXT tables should have similar characteristics like normal MySQL MEMORY tables. But in addition to the MEMORY tables they are transactional and can handle BLOB and TEXT attributes.

An alternative to this feature would be the MySQL dynamic heap row patches provided by eBay and hosted at code.google.com.

In the following blog article we go through the installation of the PBXT Storage Engine Plug-in version 1.1 and how to use PBXT MEMORY resident tables.

Download

I have not found binaries yet. So we have to compile them ourself. Download is pretty simple:

shell> bzr branch lp:pbxt/1.1

Build

To build the pluggable PBXT Storage Engine we have to do the typical 3 steps: configure; make; make install. But there are some pitfalls to circumvent:

shell> ./configure --with-mysql=/home/mysql/source/mysql-5.1.53 --prefix=/home/mysql/product/mysql-5.1.53
shell> make

Pitfalls:

• If you do NOT specify the --with-mysql option you will get the following error message:
  

  checking for mysql source code... configure: error: "no MySQL source found at /home/mysql/tmp"

• If you did NOT run configure and make on those MySQL sources before you will get error messages as follows:
  

  checking for mysql source code... configure: error: "no Makefile found in source/mysql-5.1.53"
  include/my_global.h:80:23: error: my_config.h: No such file or directory                                              
  include/my_global.h:815:2: error: #error "please add -DSTACK_DIRECTION=1 or -1 to your CPPFLAGS"

  and compilation will fail. You have to do first at least a:

  shell> ./configure
  shell> make

  on the MySQL sources to be capable to compile the PBXT Storage Engine against them!

• If you consider to use different configure options a second time, make sure you remove and download the sources from scratch again. Otherwise you may get results which are not expected:
  

  "prefix is not considered during make install" https://bugs.launchpad.net/bugs/677802>

Install

If you run:

shell> sudo make install

the files should be copied to their according locations. Then load the pluggable Storage Engine into MySQL:

mysql> INSTALL PLUGIN PBXT SONAME 'libpbxt.so';

If you get an error message complaining that MySQL cannot find the Storage Engine plug-in you should set your plugin_dir accordingly in your my.cnf:

mysql> SHOW GLOBAL VARIABLES LIKE 'plugin_dir';
+---------------+---------------------------------------------------+
| Variable_name | Value                                             |
+---------------+---------------------------------------------------+
| plugin_dir    | /home/mysql/product/mysql-5.1.53/lib/mysql/plugin |
+---------------+---------------------------------------------------+

Verify

Then, after successful loading, you can see your PBXT Storage Engine:

mysql> SHOW PLUGINS;
+------------+----------+----------------+------------+---------+
| Name       | Status   | Type           | Library    | License |
+------------+----------+----------------+------------+---------+
...
| PBXT       | ACTIVE   | STORAGE ENGINE | libpbxt.so | GPL     |
+------------+----------+----------------+------------+---------+

With the following command you can see which version is currently loaded:

mysql> SHOW ENGINE PBXT STATUS\G
*************************** 1. row ***************************
  Type: PBXT
  Name:
Status:
101120 13:48:07 PBXT 1.1.01 RC STATUS OUTPUT
...

Or from the INFORMATION_SCHEMA, which gives IMHO the wrong information:

mysql> SELECT plugin_name, plugin_version, plugin_type_version, plugin_library_version
  FROM INFORMATION_SCHEMA.plugins
 WHERE plugin_name = 'PBXT';
+-------------+----------------+---------------------+------------------------+
| plugin_name | plugin_version | plugin_type_version | plugin_library_version |
+-------------+----------------+---------------------+------------------------+
| PBXT        | 0.1            | 50153.0             | 1.0                    |
+-------------+----------------+---------------------+------------------------+

Bug #677839: Plugin version is not correctly reported in information_schema

Tests

For the first simple tests we use the MyEnv test table:

mysql> source /home/mysql/myenv/sql/test_table.sql
mysql> alter table test engine = pbxt storage memory;

Unfortunately I have not found yet a way to see if a table is a PBXT MEMORY table or not. Neither with SHOW CREATE TABLE nor with with SHOW TABLE STATUS or SELECT * FROM INFORMATION_SCHEMA.tables;.

The only way I found was to look at the file system:

mysql> system ls -la
-rw-rw---- 1 mysql dba    4 2010-11-20 15:32 test-3.xtr
-rw-rw---- 1 mysql dba 8612 2010-11-20 15:32 test.frm
-rw-rw---- 1 mysql dba  155 2010-11-20 15:32 test.xtd
-rw-rw---- 1 mysql dba 4096 2010-11-20 15:32 test.xti

mysql> ALTER TABLE test ENGINE = PBXT STORAGE MEMORY;

mysql> system ls -la
-rw-rw---- 1 mysql dba 8612 2010-11-20 15:32 test.frm

An other possibility is:

mysql> system cat ./pbxt/tables
[table]
id=37
name=pbxt
location=./test
storage=disk
type=0
[table]
id=42
name=pbxt_memory_blob
location=./test
storage=heap
type=0

Playing around with the table I have NOT found a way to convert a PBXT MEMORY table back to a DISK table yet. Both did NOT work:

mysql> ALTER TABLE test ENGINE=PBXT;
mysql> ALTER TABLE test ENGINE=PBXT STORAGE DISK;

Memory usage of PBXT MEMORY tables

What first interested me with MEMORY tables was their actual memory consumption. MySQL MEMORY tables make fixed sized rows. So I was wondering how PBXT MEMORY table behave.

5 different tables with exactly the same content:

mysql> SHOW TABLE STATUS;
+------------------+--------+------------+---------+----------------+-------------+--------------+
| Name             | Engine | Row_format | Rows    | Avg_row_length | Data_length | Index_length |
+------------------+--------+------------+---------+----------------+-------------+--------------+
| memory           | MEMORY | Fixed      | 1048576 |             74 |    80069616 |      8549392 |
| pbxt             | PBXT   | Dynamic    | 1048576 |             88 |    92275712 |     12619776 |
| pbxt_memory      | PBXT   | Dynamic    | 1048576 |             88 |    92275712 |     12619776 |
| test             | MyISAM | Dynamic    | 1048576 |             20 |    20971520 |     10762240 |
| pbxt_memory_blob | PBXT   | Dynamic    | 1048576 |            527 |   552600576 |     12619776 |
+------------------+--------+------------+---------+----------------+-------------+--------------+

PBXT claims for its memory table to have a dynamic row format. But it seems that for a VARCHAR it allocates the full length: 88 byte (14-26 byte header + 4 byte INT UNSIGNED, 64 VARCHAR + 4 byte TIMESTAMP).

For the table with the LONGTEXT field it looks even much worse. For a LONGTEXT there is some additional memory allocated for what ever reason...
So MEMORY tables seem to work with PBXT but they allocate roughly 6 times more memory when we use BLOB/TEXT attributes compared to VARCHAR. Possibly some more investigation has to be done here...

To limit MySQL MEMORY tables in growth there is a parameter called max_heap_table_size. With this behaviour of PBXT MEMORY tables it would be desirable to have a similar parameter to restrict PBXT MEMORY tables. I suggest: pbxt_max_memory_table_size?

Transactions

To find if transactions work as expected we run some simple tests. In the first example we kill a DELETE statement from an other session:

mysql> SELECT COUNT(*) FROM pbxt_memory;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.73 sec)

mysql> DELETE FROM pbxt_memory;
ERROR 1317 (70100): Query execution was interrupted

mysql> SELECT COUNT(*) FROM pbxt_memory;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    9
Current database: test

+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (0.71 sec)

What we can see is that the count takes longer than with a MEMORY table. But this can be explained because PBXT has to span a transaction and really has to count the data. Where MEMORY tables know always instantaneous, because of their non-parallel write behaviour, how many rows a table contains.

In the next simple example we start a transaction, delete some rows and roll back:

mysql> START TRANSACTION;

mysql> DELETE FROM pbxt_memory;
Query OK, 1048576 rows affected (11.48 sec)

mysql> SELECT COUNT(*) FROM pbxt_memory;
+----------+
| count(*) |
+----------+
|        0 |
+----------+

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT COUNT(*) FROM pbxt_memory;
+----------+
| count(*) |
+----------+
|  1048576 |
+----------+
1 row in set (12.02 sec)

Interesting behaviour we can see when we look at the timing of the commands...

Indexing

For unique in memory data structures, hash indexes perform better than tree indexes. With MEMORY tables there are the HASH indexes for this purpose.
When we try the same thing with PBXT MEMORY tables it seems to work:

mysql> ALTER TABLE pbxt_memory ADD INDEX (ts) USING HASH;
Query OK, 1048576 rows affected (39.42 sec)
Records: 1048576  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE TABLE pbxt_memory\G
*************************** 1. row ***************************
       Table: pbxt_memory
Create Table: CREATE TABLE `pbxt_memory` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `data` varchar(64) DEFAULT NULL,
  `ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `ts` (`ts`) USING HASH
) ENGINE=PBXT DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

But Paul told me that PBXT does not know about HASH indexes. So I would expect at least a warning if not even an error message...

Partitioning with PBXT MEMORY tables

Just by accident I had the idea of creating a partitioned PBXT MEMORY table:

CREATE TABLE test (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
, data VARCHAR(64)
, ts TIMESTAMP
)
ENGINE = PBXT STORAGE MEMORY
PARTITION BY RANGE( id )
(
  PARTITION p0 VALUES LESS THAN (1000)
, PARTITION p1 VALUES LESS THAN (2000)
, PARTITION p2 VALUES LESS THAN (3000)
, PARTITION p3 VALUES LESS THAN (4000)
);

After filling some data in I had a look at the execution plan:

mysql> EXPLAIN PARTITIONS
SELECT MAX(ts)
  FROM test
 WHERE id BETWEEN 2000 AND 3999;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | test  | p2,p3      | range | PRIMARY       | PRIMARY | 4       | NULL |    2 | Using where |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+-------------+

So it seems event this works. I am not sure where partitioned PBXT MEMORY tables would make sense. But who knows...

It would be interesting now to tell MySQL that it should use PBXT MEMORY tables instead of MySQL MEMORY/MYISAM tables...

PlanetMySQL Voting: Vote UP / Vote DOWN

I know some people have used mymonitor on and off over the years, but it was a long ago since I created it, and things has moved on and changed. Among the things that didn't work well was the build process, and the fact that there were a few bugs in the normalization of SQL statements. Now this is fixed.

If you have NOT used MyMonitor, it is a small ncurses based tool for monitoring SQL statements in your server, and work by polling the PROCESSLIST and the normalizing and sorting and grouping the result. What is "normalizing" then? It's the term I use to describe the process where a statement has all the literals replaced with ? in them, so I can see that:
SELECT * FROM mytable WHERE id = 861
is actually the same statement as
SELECT * FROM mytable WHERE id = 387
And by the way, this is also the same statement as
SELECT * FROM mytable WHERE id = 177

MyMonitor can look like this in action:

You get the point I think. MyMonitor has a few more tricks up it's sleeve, like the ability to write out the monitored statements to a file and to do some simple timing on statements. All in all, this is a simple tool that can be real helpful at times. Many times less powerful than, say, MySQL Enterprise Monitor or MONYog, but still useful.

If you are wondering: No GUI needed. What IS needed though is ncurses. And Windows support? Nope, but have a look at my MyQuery project instead, it has many more features, including staement monitoring.

Download mymonitor from Sourceforge here: http://sourceforge.net/projects/mymonitor/

Cheers
/Karlsson
PlanetMySQL Voting: Vote UP / Vote DOWN

I know some people have used mymonitor on and off over the years, but it was a long ago since I created it, and things has moved on and changed. Among the things that didn't work well was the build process, and the fact that there were a few bugs in the normalization of SQL statements. Now this is fixed.

If you have NOT used MyMonitor, it is a small ncurses based tool for monitoring SQL statements in your server, and work by polling the PROCESSLIST and the normalizing and sorting and grouping the result. What is "normalizing" then? It's the term I use to describe the process where a statement has all the literals replaced with ? in them, so I can see that:
SELECT * FROM mytable WHERE id = 861
is actually the same statement as
SELECT * FROM mytable WHERE id = 387
And by the way, this is also the same statement as
SELECT * FROM mytable WHERE id = 177

MyMonitor can look like this in action:

You get the point I think. MyMonitor has a few more tricks up it's sleeve, like the ability to write out the monitored statements to a file and to do some simple timing on statements. All in all, this is a simple tool that can be real helpful at times. Many times less powerful than, say, MySQL Enterprise Monitor or MONYog, but still useful.

If you are wondering: No GUI needed. What IS needed though is ncurses. And Windows support? Nope, but have a look at my MyQuery project instead, it has many more features, including staement monitoring.

Download mymonitor from Sourceforge here: http://sourceforge.net/projects/mymonitor/

Cheers
/Karlsson
PlanetMySQL Voting: Vote UP / Vote DOWN

After upgrading from PDI 3.x to 4.0.1, I've found that the old way of exporting an repository subfolder in a single step is gone. It's still important for me to be able to do this since I use separate repositories for Development, Test, and Production, and these systems must remain isolated.

As a work around, here is what I am doing for now:


You can still keep your jobs and transforms organized in repository folders/sub-folders. But you have to now export each (open) job and transformation individually using File -> Export -> To XML

I just save these files to an empty temporary directory on the file system.

Then I disconnect from the source ( development) repository and connect to the destination repository, ( ie. Test or Production).

In there, use :

Tools -> Repository -> Import Repository

In the dialog that displays:
Navigate to your temporary file system directory, and select all files you want to import. You can select more then one / all files by holding the shift key,
much like selecting multiple rows in a spreadsheet.

Click on the "Open" button

This will import your jobs and transformations at once. It will even create your sub-folders in the repository if they don't exist.

---------------------------------------------------
The first step, of course, is to backup your repository. My repositories use MySQL databases, so here is the form of the command I use:

mysqldump --user=youruser --host=your.host --password=your.password --skip-opt --add-locks --create-options --databases DATABASENAME > backupfilename.out

Between release cycles, I verify my backups by using them to restore/refresh my Development and Test repositories from Production.

---------------------------------------------------

I attended the Pentaho Agile-BI tour in Denver this week and met some other folks using PDI and the BI Suite, which was great. One of the discussions some of us had was about forming a Pentaho user group in the Denver / Boulder Area. If anyone is interested look me up on LinkedIn.com or send me an email and lets at least start an online discussion group.
PlanetMySQL Voting: Vote UP / Vote DOWN

After upgrading from PDI 3.x to 4.0.1, I've found that the old way of exporting an repository subfolder in a single step is gone. It's still important for me to be able to do this since I use separate repositories for Development, Test, and Production, and these systems must remain isolated.

As a work around, here is what I am doing for now:


You can still keep your jobs and transforms organized in repository folders/sub-folders. But you have to now export each (open) job and transformation individually using File -> Export -> To XML

I just save these files to an empty temporary directory on the file system.

Then I disconnect from the source ( development) repository and connect to the destination repository, ( ie. Test or Production).

In there, use :

Tools -> Repository -> Import Repository

In the dialog that displays:
Navigate to your temporary file system directory, and select all files you want to import. You can select more then one / all files by holding the shift key,
much like selecting multiple rows in a spreadsheet.

Click on the "Open" button

This will import your jobs and transformations at once. It will even create your sub-folders in the repository if they don't exist.

---------------------------------------------------
The first step, of course, is to backup your repository. My repositories use MySQL databases, so here is the form of the command I use:

mysqldump --user=youruser --host=your.host --password=your.password --skip-opt --add-locks --create-options --databases DATABASENAME > backupfilename.out

Between release cycles, I verify my backups by using them to restore/refresh my Development and Test repositories from Production.

---------------------------------------------------

I attended the Pentaho Agile-BI tour in Denver this week and met some other folks using PDI and the BI Suite, which was great. One of the discussions some of us had was about forming a Pentaho user group in the Denver / Boulder Area. If anyone is interested look me up on LinkedIn.com or send me an email and lets at least start an online discussion group.
PlanetMySQL Voting: Vote UP / Vote DOWN