JFYI, I now placed a PDF of my MySQL Conference talk slides about "Performing MySQL backups using Linux LVM Snapshots" on my MySQL talks page. Enjoy!

 

After having to the UC and seen some of the talks with people using MySQL Cluster it seems that it is time to hand out a good configuration template.

Unfortunately, configuring cluster is too difficult and a lot of things could be done to ease the pain. One of the problematic things customers and users always forget is to dimension the redo log properly.

However, the template below allows you to:

• Load any mysqldump i have seen to date (if you have enough DataMemory/IndexMemory to support the data set).
• ndb_restore on any data (I strongly recommend you run ndb_restore with a less degree of parallelism than what is defaut). This is regulated with the -p option. Run ndb_restore with ndb_restore -p 32 .... You may in some rare cases need to lower the degree of parallelism somewhat.
• Recover in case of node failure/cluster failure. Often the things preventing you from recovery is that you have a too short redo log. If you have a well-sized (6xDataMemory in order to be safe) one then it is likely that you have hit an actual bug rather than a configuration issue.
  

The below template is geared towards MySQL Cluster 6.2 (CGE6.2) and MySQL Cluster 6.3 (CGE6.3).

If you are using 5.0 or regular 5.1 (i do recommend you upgrade to CGE6.x), then you might have to change some things and read an earlier post on how to get going with it. The template only shows the "default" sections, so you need to add your actual [ndb_mgmd] , [ndbd] and [mysqld] processes to it. Moreover, the template is not taken into account disk data tables and configuration for those.

REMEMBER: If you change the DataMemory (in the config template below it is 2048M), then you must update the NoOfFragmentLogfiles (why this is not handled automatically inside cluster is a valid question...) with the correct value according to the formula below! Also, don't forget to update the IndexMemory as well.

This configuration will require (not including the IndexMemory or DataMemory) about 900MB of RAM for each data node, but is very solid. If you run into a problem now, then it is a bug, and not a configuration issue. Good luck.


[tcp default]
SendBufferMemory=2M
ReceiveBufferMemory=2M

[ndb_mgmd default]
datadir=X


[ndbd default]
#redundancy:
NoOfReplicas=2
#avoid swapping:
LockPagesInMainMemory=1

#Bypass FS cache (you should test if this works for you or not)
Odirect=1

#DataMemory (memory for records and ordered indexes)
DataMemory=2048M
#IndexMemory (memory for Primary key hash index and unique hash index)
#Usually between 1/6 or 1/8 of the DataMemory is enough, but depends on the
#number of unique hash indexes (UNIQUE in table def)
IndexMemory=256M

#Redolog
#size of each redo log fragment, 4 redo log fragment makes up on fragment log file.
# A bigger Fragment log file size thatn the default 16M works better with high write load
# and is strongly recommended!!
FragmentLogFileSize=256M

#Set NoOfFragmentLogFiles to 6xDataMemory [in MB]/(4 *FragmentLogFileSize [in MB]
#Thus, NoOfFragmentLogFiles=6*2048/1024=12
#The "6xDataMemory" is a good heuristic and is STRONGLY recommended.
NoOfFragmentLogFiles=12

#RedoBuffer of 32M should let you restore/provisiong quite a lot of data in parallel.
#If you still have problems ("out of redobuffer"), then you probably have to slow disks and
#increasing this will not help, but only postpone the inevitable.
RedoBuffer=32M

#table related things
MaxNoOfTables=4096
MaxNoOfAttributes=24756
MaxNoOfOrderedIndexes=2048
MaxNoOfUniqueHashIndexes=512

#Operation records
#MaxNoOfConcurrentOperations=100000 means that you can load any mysqldump file into cluster.
MaxNoOfConcurrentOperations=100000

#Checkpointing...
Diskcheckpointspeed=10M
Diskcheckpointspeedinrestart=100M
TimeBetweenGlobalCheckpoints=1000
#the default value for TimeBetweenLocalCheckpoints is very good
TimeBetweenLocalCheckpoints=20

#Realtime extensions (only in MySQL Cluster 6.3 (CGE 6.3) , read this how to use this)
#SchedulerSpinTimer=400
#SchedulerExecutionTimer=100
#RealTimeScheduler=1
#LockMaintThreadsToCPU=[cpuid]
#LockExecuteThreadToCPU=[cpuid]

#If you use MySQL Cluster 6.3 (CGE 6.3) and are tight on disk space, e.g ATCA.
#You should also then lock cpu's to a particular core.
#CompressedLCP=1
#CompressedBackup=1

datadir=X

[ndb_mgmd]
hostname=...

#second management server for redundancy
#[ndb_mgmd]
#hostname=...


[ndbd]
hostname=...

[ndbd]
hostname=...

[mysqld]

...

[mysqld]




Please note that this config.ini template is not for disk data tables, but I will update it soon with those configuration parameters.

Bluehost.com and Percona have made it easier for others to use one of my favorite features, user and table accounting, from the Google patch.

By easier I mean that patches for recent versions of MySQL 5.0 have been published. And if you don't want to go through the trouble of applying the patch, I imagine that Bluehost.com has it running on their hosted servers.

The MySQL Performance blog post describes using the microslow patch in combination with SHOW USER_STATISTICS output to debug workload problems. I hope they describe the process in greater detail in another blog post. I use user and table accounting as the first step in debugging workload problems. With that data I determine which users and tables create the majority of the load and then I use SQL statement logging or sampling from SHOW PROCESSLIST to determine the statements that might be the problem.

The external development community is growing, if slowly, and I am happy to see this. We have had some nice patches and major features and I look forward to more. I am sure I have left others off of this list -- let me know.

• SHOW PROFILES from Proven Scaling
• Reduced memory usage for the HEAP engine from eBay and Igor
• Microslow logging from Percona
• InnoDB performance improvements from Yasufumi Kinoshita at NTT (here and here)
  
• The Google patch
• Maatkit
• PBXT storage engine and blob streaming engine courtesy of Primebase
• InnoDB (it is GPL after all)
• Dormando's proxy
• Support for memcached
  

A few weeks ago I was asked to isolate some functionalities from Mark Callaghan?s MySQL patch bundle. They were extensions adding per-user and per-table accounting to the database, accessible with a new set of commands such as SHOW TABLE_STATISTICS, SHOW INDEX_STATISTICS and SHOW USER_STATISTICS. The first two can interest anyone to periodically check what data or which index are the most active or which are not being used at all and could be candidates for dropping. All the patch features will surely be a great help for hosting providers to produce detailed reports on how each customer utillizes the database.

Having many different users running their queries on a single database instance means they will be constantly "battling" for the resources, each one caring only for his own application, completely ignoring the fact they chose shared hosting. Of course this alone is not a problem and theoretically if you don?t over-allocate, the users should never become conscious of the struggle happening underneath.

Obviously, that?s only in the ideal world and either it's not even possible or, at best, economically inefficient to allocate the entire machine for only a few customers simply because their skills in SQL are limited.

The real life customers generate very uneven load - some barely any and some a lot. It has to be said that the SQL performance is very tricky, it's very easy to produce large sets of data for the hardware to process from even small tables. Therefore often the resulting database load may not be directly related to the web traffic some customer has. The load can also be changing in time, mostly growing, often spiking. One way or another after a while every server becomes too tight for everyone. And for you it is best to be pro-active, to observe the changes as they happen and react before any user can start complaining. You can watch the load growth and based on that make the decision on splitting half of the users to some newly purchased hardware. But how to avoid the undesired situation that you only sort out the least active customers from the heavier ones?

It's probably not uncommon someone uploads a really badly designed application, which runs queries not using indexes or which examine a lot of rows. This can elevate the load, but just as well it can bring the server down. You can have slow query log enabled and review it occasionally, but doing it offline means you can?t react in the real time on the critical situation. You can have some sophisticated mechanisms to browse the process list or scan the slow log live, but that is a bit ugly solution.

That is where this patch can be very useful. With simple MySQL SHOW command it gives you the basic statistics on the database activity broken down by users, tables or indexes. Monitoring those numbers every minute can show you changes in usage by customer and immediately catch any spikes as they happen pinpointing the source to make an intervention. Looking at totals over a longer period you can catch users causing the most traffic to help you with some strategic decisions.

Sample outputs:

PLAIN TEXT SQL:

1. mysql> SHOW USER_STATISTICS LIKE 'hosting2'\G
2. *************************** 1. row ***************************
3.                   User: hosting2
4.      Total_connections: 84
5. Concurrent_connections: 0
6.         Connected_time: 20
7.              Busy_time: 1
8.           Rows_fetched: 7861
9.           Rows_updated: 240
10.        Select_commands: 1759
11.        Update_commands: 254
12.         Other_commands: 85
13.    Commit_transactions: 0
14.  Rollback_transactions: 0

PLAIN TEXT SQL:

1. mysql> SHOW TABLE_STATISTICS LIKE 'hosting2%';
2. +--------------------------------+-----------+--------------+-------------------------+
3. | TABLE                          | Rows_read | Rows_changed | Rows_changed_x_#indexes |
4. +--------------------------------+-----------+--------------+-------------------------+
5. | hosting2.wp_terms              |       335 |            2 |                       4 |
6. | hosting2.wp_comments           |        12 |            1 |                       3 |
7. | hosting2.wp_options            |      6586 |          200 |                     400 |
8. | hosting2.wp_postmeta           |         5 |            2 |                       6 |
9. | hosting2.wp_posts              |       542 |            4 |                      12 |
10. | hosting2.wp_term_relationships |       720 |            9 |                      18 |
11. | hosting2.wp_users              |       109 |            1 |                       3 |
12. | hosting2.wp_links              |       910 |            7 |                      21 |
13. | hosting2.wp_term_taxonomy      |       729 |            3 |                       6 |
14. | hosting2.wp_usermeta           |       427 |            6 |                      18 |
15. +--------------------------------+-----------+--------------+-------------------------+
16. 10 rows IN SET (0.00 sec)

PLAIN TEXT SQL:

1. mysql> SHOW INDEX_STATISTICS LIKE 'hosting2%';     
2. +--------------------------------------------+-----------+
3. | INDEX                                      | Rows_read |
4. +--------------------------------------------+-----------+
5. | hosting2.wp_options.option_name            |       287 |
6. | hosting2.wp_usermeta.meta_key              |         8 |
7. | hosting2.wp_term_relationships.PRIMARY     |       720 |
8. | hosting2.wp_postmeta.post_id               |         1 |
9. | hosting2.wp_links.link_visible             |       455 |
10. | hosting2.wp_terms.PRIMARY                  |        67 |
11. | hosting2.wp_posts.type_status_date         |       459 |
12. | hosting2.wp_term_taxonomy.term_id_taxonomy |       204 |
13. | hosting2.wp_posts.PRIMARY                  |         9 |
14. | hosting2.wp_term_taxonomy.PRIMARY          |         5 |
15. | hosting2.wp_usermeta.user_id               |       413 |
16. +--------------------------------------------+-----------+
17. 11 rows IN SET (0.00 sec)

The statistics for tables and indexes can be cleaned with the corresponding FLUSH command (e.g. FLUSH INDEX_STATISTICS).

All that can be fed into scripts to bring you automated reports or to perform actions such as:

• list users by the number of queries issued or any other information
• find active tables which do not have any indexes used meaning table scans
• nag users they should consider upgrading to a dedicated server because their usage is very high
• temporarily block users having huge spike in MySQL usage

Personally I would see a few more things implemented. A few examples to mention:

• per-user CPU time usage, some queries may be CPU bound rather than reading lots of rows
• support for prepared statements which all currently fall under Other_commands
• a few MySQL performance counters broken down by users (full joins, table scans, disk temp tables, etc.)
• limiting command availability to SUPER users only or restricting regular users to see their own statistics only
• information_schema support to allow SELECT query transformations, joins and better filtering for the statistics

Also combining these new commands with our msl patch, which introduces the advanced query logging, would give the administrator powerful tool to almost effortlessly catch the users doing bad things on the database and point them to the specific problems they have (e.g. particular query using full joins).

If you want the statistics patch mentioned in this article, then Bluehost.com CEO, Matt Heatton, has published it on his blog.

Entry posted by Maciej Dobrzanski | 10 comments

Add to: | | | |

The MySQL User Guide is worth looking at. Its not the reference manual (which is excellent - kudos to our Documentation Team). Its target audience are users that are new to databases or users that are new to MySQL in general.

What’s really interesting about the MySQL User Guide is that you can help shape it. You, the community, can participate in writing it!

I for one, know that this is the easiest way you can start contributing to any open source project. Documenting it. Soon, you will realise that you’ve become an expert (writing documentation, or giving training, will always keep you sharp). Some move on to then delving in coding, some go on being consultants, and some end up being hired by the company that sponsors the project ;)

The URL again: http://userguide.forge.mysql.com/

Happy writing!

Technorati Tags: mysql, community, documentation, forge, user guide, contributing, open source, advocacy, mysql user guide

I?ve already taken a look at MySQL?s changing business model and the potential business drivers behind the company considering introducing new functionality under to Enterprise customers only. One area that I didn?t dive into was the impact on the company?s development model.

This, in fact, was the focus of Jeremy Cole?s initial take on the news as well as a significant response from Marten Mickos. ?MySQL will start offering some features (specifically ones related to online backups) only in MySQL Enterprise,? explained Jeremy.

?As I?ve discussed before, the size of the user base for MySQL Enterprise is much smaller than for MySQL Community,? he added. ?That means these critical features will be tested by only a few of their customers. So, in effect, they will be giving their paying customers real, true, untested code. How is this supposed to work??

Marten has partially answered that question in an interview with Glyn Moody at Computerworld UK:

?GM: One issue is that you seem to be throwing away an advantage of open source in the sense that if it is closed then obviously people can’t help you make it better.

MM: That’s true ? absolutely, it’s true. That’s why for any such code we will have to hire more QA people, and do more work because we don’t get that help from the community.?

It occurs to me that this could create a vicious circle: the more QA people MySQL needs to hire, the more revenue it will need to generate to cover costs. The more revenue it needs to generate, the more value it needs to provide Enterprise users. The more value it needs to provide enterprise users, the more likely it is to introduce proprietary add-ons. The more proprietary add-ons it has, the more QA people it needs to employ. You get the picture.

This is of course different from the ?virtuous circle? which sees the community users benefiting from MySQL?s commercial activities in that they also fund the development of GPL features. In fact, the fear for some community users is that withholding new features from the community version in fact breaks that virtuous circle.

In response to Jeremy?s post, Marten pointed out that InnoDB, WebYog and indeed MySQL already have features that are only available for paying customers. ?All those products are working well and serving customers well,? he wrote, while comparing MySQL?s position to PostgreSQL-based vendors.

?The same applies to the largest group of PostgreSQL-based companies: EnterpriseDB, Greenplum, Netezza, etc. It seems to me that the situation is analogous between Postgres and MySQL: a great product under an open source license, and various commercial initiatives around it,? he added.

With all due respect to Marten, there is a significant difference between the captive open source development model for MySQL and the community open source development model for PostgreSQL.

The vast majority of MySQL development is done by MySQL employees. To date that development model, combined with the dual licensing and support subscription models, has served both Community and Enterprise users equally. As the company adds more features and services to the Enterprise product it will increasingly have to try to serve two masters, however.

As Marten told Computerworld: ?So as we do this, of course, we meet exactly the crossfire that we are now in, meaning the same solution seems to upset one market and please the other one. So then the question is: How do we ensure that we are not completely upsetting our open source users when we do something commercially, or vice versa.?

The difference between MySQL and the PostgreSQL-based vendors in this regard the PostgreSQL community isn?t dependent on EnterpriseDB et al for code.

The relationship between the PostgreSQL-based vendors and the PostgreSQL community is more symbiotic. EnterpriseDB, Greenplum, and Netezza work with the community, employ core developers and contribute code, but they are also independent of the project.

While they benefit from contributing code and improving the strength of PostgreSQL for all, the BSD license means they have no obligation to contribute their own proprietary developments. The PostgreSQL-based vendors have much more freedom, therefore, to decide based on their own business drivers when code should be open source, and when it should not.

In fact, if any dependency exists in the PostgreSQL model it is EnterpriseDB and Greenplum?s dependency on the PostgreSQL community. MySQL has no such dependency on the community.

MySQL also has no obligation to contribute new features to the open source model, but then it is then it has built a business on supplying the same code to both user groups, and has benefited from doing so.

As Zack Urlocker noted: ?While the number of customers who pay us is much smaller than the number of community users who do not, most of our paying customers first used MySQL because it is available freely under the GPL open source license. And in many cases, we know that MySQL is popular because of the work of the community who are out there using it, blogging about it, creating add-on tools, products or services.?

He added: ?MySQL is in the middle trying to make sure we are balanced in our actions and not neglecting the interests of either market. It’s not always obvious how to benefit both groups and there are few successful models to guide us at this point. So we are constantly forging new territory, experimenting, trying new things, and listening for input.?

Balance is the critical word here, and if MySQL does choose to develop closed source extensions to the GPL code it will probably have to find some way of balancing that with providing more value to the community.

Looks like MySQL build team should add a test scenario to cover this in the automation. Even if you specify –without-server with the latest 5.1.24; it still builds all most all plugins. This is really bad deal. Forget about mandatory plugins like MyISAM, Heap and Merge; it also builds rest of the plugins unless you skip them using –without-<name> or –without-plugin-<name>. The mandatory check should also be relaxed when one uses –without-server. The configure currently throws an error with you try to skip any mandatory plugins with –without-server too.

When one need clients (directory client) and its libraries (directories like libmysql, libmysql_r and mysys, mystrings, dbug) we do not need to build the whole ‘SQL‘ dir and no need to enter the ‘storage‘ directory at all. I patched the configure script to escape all these cases now including skipping of mandatory plugins; and I can see that everything works as expected after the patch. Even though I could not find a easy way to strip ‘ndbclient’ out of the ‘ndb’ engine. But this will allow me to build on systems with gcc2 where we only need client programs : (

I’m still looking for new entries. I get quite a few suggestions, but not all of them make it into quiz questions. Do send in your suggestions!

Today’s quiz question, which subsequently became three, comes courtesy of Janek Bogucki.

Since questions 2 and 3 contain hints on the other questions, they are embedded within the answers to previous questions.

Question 1: What happens if you throw the following statement at your MySQL server?

CREATE TABLE log( name CHAR(20) NOT NULL, count INT UNSIGNED NOT NULL )

(more…)

Sometimes you have the task of storing multiple of boolean values (yes/now or something similar) in the table and if you get many columns and many rows you may want to store them as efficient way as possible.
For MyISAM tables you could use BIT(1) fields which get combined together for efficient storage:

PLAIN TEXT SQL:

1. CREATE TABLE `bbool` (
2.   `b1` bit(1) NOT NULL,
3.   `b2` bit(1) NOT NULL,
4.   `b3` bit(1) NOT NULL,
5.   `b4` bit(1) NOT NULL,
6.   `b5` bit(1) NOT NULL,
7.   `b6` bit(1) NOT NULL,
8.   `b7` bit(1) NOT NULL,
9.   `b8` bit(1) NOT NULL,
10.   `b9` bit(1) NOT NULL,
11.   `b10` bit(1) NOT NULL
12. ) ENGINE=MyISAM
13.  
14. mysql> SHOW TABLE STATUS LIKE 'bbool' \G
15. *************************** 1. row ***************************
16.            Name: bbool
17.          Engine: MyISAM
18.         Version: 10
19.      Row_format: Fixed
20.            Rows: 10
21.  Avg_row_length: 7
22.     Data_length: 70
23. Max_data_length: 1970324836974591
24.    Index_length: 1024
25.       Data_free: 0
26.  AUTO_INCREMENT: NULL
27.     Create_time: 2008-04-24 00:41:01
28.     Update_time: 2008-04-24 00:45:40
29.      Check_time: NULL
30.       Collation: latin1_swedish_ci
31.        Checksum: NULL
32.  Create_options:
33.         Comment:
34. 1 row IN SET (0.00 sec)

As you can see for MyISAM 10 columns take just 7 bytes - less than a byte per column. This is just minimum row length we can have for this table - myisam_data_pointer_size is 6 default plus we need space for delete flag which makes 7 minimum row size MyISAM can have in this configuration.

This trick however does not work for Innodb which allocates 1 byte for each BIT(1) column. So we can get 1 byte per column for boolean flag storage in Innodb (not accounting for standard row overhead) if we use BIT(1), TINYINT or ENUM types but can we do better ?

In fact we can - by using CHAR(0) type (without NOT NULL flag) - this will be pretty much column containing NULL bit only which can store one of two values - NULL or Empty String.

Lets see how these 3 different table format look in Innodb (I've populated each with some 2M rows so difference is more visible)

PLAIN TEXT SQL:

1. CREATE TABLE `tbool` (
2.   `t1` tinyint(4) NOT NULL,
3.   `t2` tinyint(4) NOT NULL,
4.   `t3` tinyint(4) NOT NULL,
5.   `t4` tinyint(4) NOT NULL,
6.   `t5` tinyint(4) NOT NULL,
7.   `t6` tinyint(4) NOT NULL,
8.   `t7` tinyint(4) NOT NULL,
9.   `t8` tinyint(4) NOT NULL,
10.   `t9` tinyint(4) NOT NULL,
11.   `t10` tinyint(4) NOT NULL
12. ) ENGINE=InnoDB
13.  
14. CREATE TABLE `cbool` (
15.   `c1` char(0) DEFAULT NULL,
16.   `c2` char(0) DEFAULT NULL,
17.   `c3` char(0) DEFAULT NULL,
18.   `c4` char(0) DEFAULT NULL,
19.   `c5` char(0) DEFAULT NULL,
20.   `c6` char(0) DEFAULT NULL,
21.   `c7` char(0) DEFAULT NULL,
22.   `c8` char(0) DEFAULT NULL,
23.   `c9` char(0) DEFAULT NULL,
24.   `c10` char(0) DEFAULT NULL
25. ) ENGINE=InnoDB DEFAULT CHARSET=latin1
26.  
27. mysql> SHOW TABLE STATUS LIKE "%bool%" \G
28. *************************** 1. row ***************************
29.            Name: bbool
30.          Engine: InnoDB
31.         Version: 10
32.      Row_format: Compact
33.            Rows: 2097405
34.  Avg_row_length: 37
35.     Data_length: 78233600
36. Max_data_length: 0
37.    Index_length: 0
38.       Data_free: 0
39.  AUTO_INCREMENT: NULL
40.     Create_time: 2008-04-24 00:54:18
41.     Update_time: NULL
42.      Check_time: NULL
43.       Collation: latin1_swedish_ci
44.        Checksum: NULL
45.  Create_options:
46.         Comment: InnoDB free: 6144 kB
47. *************************** 2. row ***************************
48.            Name: cbool
49.          Engine: InnoDB
50.         Version: 10
51.      Row_format: Compact
52.            Rows: 2097678
53.  Avg_row_length: 34
54.     Data_length: 71942144
55. Max_data_length: 0
56.    Index_length: 0
57.       Data_free: 0
58.  AUTO_INCREMENT: NULL
59.     Create_time: 2008-04-24 00:37:48
60.     Update_time: NULL
61.      Check_time: NULL
62.       Collation: latin1_swedish_ci
63.        Checksum: NULL
64.  Create_options:
65.         Comment: InnoDB free: 4096 kB
66. *************************** 3. row ***************************
67.            Name: tbool
68.          Engine: InnoDB
69.         Version: 10
70.      Row_format: Compact
71.            Rows: 2097405
72.  Avg_row_length: 37
73.     Data_length: 78233600
74. Max_data_length: 0
75.    Index_length: 0
76.       Data_free: 0
77.  AUTO_INCREMENT: NULL
78.     Create_time: 2008-04-24 00:58:01
79.     Update_time: NULL
80.      Check_time: NULL
81.       Collation: latin1_swedish_ci
82.        Checksum: NULL
83.  Create_options:
84.         Comment: InnoDB free: 6144 kB
85. 3 rows IN SET (0.11 sec)

As you can see table which uses BIT(1) column type takes same space as the one which uses TINYINT NOT NULL while CHAR(0) is about 10% smaller. This is modest space savings of course but considering large per row overhead Innodb has this will transform to much larger savings if you have hundreds of such columns.

Lets see how things look for MyISAM for same tables:

PLAIN TEXT SQL:

1. mysql> SHOW TABLE STATUS LIKE "%bool%" \G
2. *************************** 1. row ***************************
3.            Name: bbool
4.          Engine: MyISAM
5.         Version: 10
6.      Row_format: Fixed
7.            Rows: 2097152
8.  Avg_row_length: 7
9.     Data_length: 14680064
10. Max_data_length: 1970324836974591
11.    Index_length: 1024
12.       Data_free: 0
13.  AUTO_INCREMENT: NULL
14.     Create_time: 2008-04-24 01:14:06
15.     Update_time: 2008-04-24 01:14:09
16.      Check_time: NULL
17.       Collation: latin1_swedish_ci
18.        Checksum: NULL
19.  Create_options:
20.         Comment:
21. *************************** 2. row ***************************
22.            Name: cbool
23.          Engine: MyISAM
24.         Version: 10
25.      Row_format: Fixed
26.            Rows: 2097152
27.  Avg_row_length: 7
28.     Data_length: 14680064
29. Max_data_length: 1970324836974591
30.    Index_length: 1024
31.       Data_free: 0
32.  AUTO_INCREMENT: NULL
33.     Create_time: 2008-04-24 01:14:13
34.     Update_time: 2008-04-24 01:14:17
35.      Check_time: NULL
36.       Collation: latin1_swedish_ci
37.        Checksum: NULL
38.  Create_options:
39.         Comment:
40. *************************** 3. row ***************************
41.            Name: tbool
42.          Engine: MyISAM
43.         Version: 10
44.      Row_format: Fixed
45.            Rows: 2097152
46.  Avg_row_length: 11
47.     Data_length: 23068672
48. Max_data_length: 3096224743817215
49.    Index_length: 1024
50.       Data_free: 0
51.  AUTO_INCREMENT: NULL
52.     Create_time: 2008-04-24 01:14:23
53.     Update_time: 2008-04-24 01:14:26
54.      Check_time: NULL
55.       Collation: latin1_swedish_ci
56.        Checksum: NULL
57.  Create_options:
58.         Comment:
59. 3 rows IN SET (0.00 sec)

As you can see for MyISAM BIT(1) NOT NULL type is as compact as CHAR(0) while TINYINT NOT NULL is a bit less compact.

Looking at results of these tests using CHAR(0) is the most efficient if you would like optimal structure both for MyISAM and Innodb tables, however it is not as convenient to work with. Using NULL as one of flag values means you can't use normal "=" comparison operator with them:

PLAIN TEXT SQL:

1. mysql> SELECT count(*) FROM cbool WHERE c1=NULL;
2. +----------+
3. | count(*) |
4. +----------+
5. |        0 |
6. +----------+
7. 1 row IN SET (0.20 sec)

You can use IS NULL operator which is painful because you need to have different query based on paramete

At MySQL we run a whole regression test suite on various platforms after each code “push”. If you are coming from CVS or SVN and alike, then think about a “commit” when we say “push”.

The test language we use is described here: MySQL Test Framework

We at the Falcon team wanted to see our progression in terms of bugs fixed, so for over 18 months we additionally are running Falcon related tests on a weekly basis. With lot’s of teams working on MySQL sometimes there are tests, which fail in rather random fashion. Such tests are usually disabled, so that other teams don’t get distracted. For our Weekly Falcon Test Overview we enable all disabled test, to see where we are.

I will try to do a public weekly report about the status of Falcon along with the Weekly Falcon Test Overview results, bugs we have fixed, and noteworthy news.

Last week we saw this bug fixes for Falcon:

• Jim Starkey fixed couple of bugs.
• Sergey Vojtovich fixed this tablespace related bugs: BUG#33723, BUG#34048, and BUG#34617.
• Vladislav Vaintroub was very busy hunting down a duplicate key bug we saw lately in Falcon.

As we are an Open Source company, our bug system is open to all: http://bugs.mysql.com/

If you are interested in Falcon related bugs, you can use our saved search at: http://bugs.mysql.com/saved/FalconBugs

As a special add-on for fans of numbers and statistics, here is the list of the Falcon test overview status in plain text:

2006-09-01 Failed 30/67 tests, 55.22% were successful. 2006-09-15 Failed 28/75 tests, 62.67% were successful. 2006-09-22 Failed 30/76 tests, 60.53% were successful. 2006-09-29 Failed 26/77 tests, 66.23% were successful. 2006-10-06 Failed 26/79 tests, 67.09% were successful. 2006-10-13 Failed 25/83 tests, 69.88% were successful. 2006-10-20 Failed 26/83 tests, 68.67% were successful. 2006-10-27 Failed 28/88 tests, 68.18% were successful. 2006-11-03 Failed 31/92 tests, 66.30% were successful. 2006-11-10 Failed 31/99 tests, 68.69% were successful. 2006-11-17 Failed 26/101 tests, 74.26% were successful. 2006-11-24 Failed 26/102 tests, 74.51% were successful. 2006-12-01 Failed 25/102 tests, 75.49% were successful. 2006-12-08 Failed 25/105 tests, 76.19% were successful. 2006-12-15 Failed 22/106 tests, 79.25% were successful. 2006-12-22 Failed 23/107 tests, 78.50% were successful. 2006-12-29 Failed 24/107 tests, 77.57% were successful. 2007-01-05 Failed 24/107 tests, 77.57% were successful. 2007-01-12 Failed 25/110 tests, 77.27% were successful. 2007-01-19 Failed 26/110 tests, 76.36% were successful. 2007-01-26 Failed 23/110 tests, 79.09% were successful. 2007-02-02 Failed 22/110 tests, 80.00% were successful. 2007-02-11 Failed 22/110 tests, 80.00% were successful. 2007-02-16 Failed 23/114 tests, 79.82% were successful. 2007-02-23 Failed 27/123 tests, 78.05% were successful. 2007-03-02 Failed 27/123 tests, 78.05% were successful. 2007-03-10 Failed 43/127 tests, 66.14% were successful. 2007-03-17 Failed 30/127 tests, 76.38% were successful. 2007-03-24 Failed 29/129 tests, 77.52% were successful. 2007-04-15 Failed 28/132 tests, 78.79% were successful. 2007-04-20 Failed 32/142 tests, 77.46% were successful. 2007-04-27 Failed 28/145 tests, 80.69% were successful. 2007-05-04 Failed 30/148 tests, 79.73% were successful. 2007-05-11 Failed 27/148 tests, 81.76% were successful. 2007-05-18 Failed 26/149 tests, 82.55% were successful. 2007-05-25 Failed 33/150 tests, 78.00% were successful. 2007-06-01 Failed 32/155 tests, 79.35% were successful. 2007-06-08 Failed 29/155 tests, 81.29% were successful. 2007-07-06 Failed 23/158 tests, 85.44% were successful. 2007-07-13 Failed 19/159 tests, 88.05% were successful. 2007-07-20 Failed 20/159 tests, 87.42% were successful. 2007-07-27 Failed 20/160 tests, 87.50% were successful. 2007-08-03 Failed 20/161 tests, 87.58% were successful. 2007-09-07 Failed 24/170 tests, 85.88% were successful. 2007-09-14 Failed 29/172 tests, 83.14% were successful. 2007-09-21 Failed 28/172 tests, 83.72% were successful. 2008-01-10 Failed 21/185 tests, 88.65% were successful. 2008-01-24 Failed 21/185 tests, 88.65% were successful. 2008-02-01 Failed 22/185 tests, 88.11% were successful. 2008-02-28 Failed 17/191 tests, 91.10% were successful. 2008-03-13 Failed 21/194 tests, 89.18% were successful. 2008-03-20 Failed 21/194 tests, 89.18% were successful. 2008-03-28 Failed 19/189 tests, 89.95% were successful. 2008-04-04 Failed 21/194 tests, 89.18% were successful.

Now I figured out the reason for MySQL Server 5.1.24 crash when used with InnoDB plugin 1.0. As I had a older my.cnf in the path and it had innodb_flush_method=fdatasync as the default flush method. But from 5.1.24 onwards, fdatasync is not supported as the flush method (not sure why we have such a change in the final stage of RC code, but  …)

Even though I get an error in the mysqld.err log that InnoDB failed to register; but server starts and loads rest of InnoDB information schema plugins without the main InnoDB plugin.

InnoDB: Unrecognized value fdatasync for innodb_flush_method 080423 22:36:04 [ERROR] Plugin ‘InnoDB’ init function returned error. 080423 22:36:04 [ERROR] Plugin ‘InnoDB’ registration as a STORAGE ENGINE failed.

.csharpcode, .csharpcode pre { font-size: small; color: black; font-family: consolas, "Courier New", courier, monospace; background-color: #ffffff; /*white-space: pre;*/ } .csharpcode pre { margin: 0em; } .csharpcode .rem { color: #008000; } .csharpcode .kwrd { color: #0000ff; } .csharpcode .str { color: #006080; } .csharpcode .op { color: #0000c0; } .csharpcode .preproc { color: #cc6633; } .csharpcode .asp { background-color: #ffff00; } .csharpcode .html { color: #800000; } .csharpcode .attr { color: #ff0000; } .csharpcode .alt { background-color: #f4f4f4; width: 100%; margin: 0em; } .csharpcode .lnum { color: #606060; }

As the server started with rest of the InnoDB plugins; upon querying any of them; the server simply crashes…

mysql> show plugins; +---------------------+--------+--------------------+---------+---------+ | Name | Status | Type | Library | License | +---------------------+--------+--------------------+---------+---------+ | binlog | ACTIVE | STORAGE ENGINE | NULL | GPL | | partition | ACTIVE | STORAGE ENGINE | NULL | GPL | | ARCHIVE | ACTIVE | STORAGE ENGINE | NULL | GPL | | BLACKHOLE | ACTIVE | STORAGE ENGINE | NULL | GPL | | CSV | ACTIVE | STORAGE ENGINE | NULL | GPL | | FEDERATED | ACTIVE | STORAGE ENGINE | NULL | GPL | | MEMORY | ACTIVE | STORAGE ENGINE | NULL | GPL | | INNODB_TRX | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCKS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_LOCK_WAITS | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMP_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | INNODB_CMPMEM_RESET | ACTIVE | INFORMATION SCHEMA | NULL | GPL | | MyISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | | MRG_MYISAM | ACTIVE | STORAGE ENGINE | NULL | GPL | +---------------------+--------+--------------------+---------+---------+ 16 rows in set (0.00 sec)   mysql> select * from Information_schema.Innodb_locks; ERROR 2013 (HY000): Lost connection to MySQL server during query

You can crash in the same way if you have any invalid InnoDB variable in the config file. It looks like InnoDB needs to have dependency on the main plugin when it is loading the information schema plugins or at least it should have validation checks up on querying the information schema tables for the main plugin. I will file a bug report on this.

Good thing is I was able to get InnoDB plugin to work with MySQL 5.1.24 and started doing some benchmarks, and results seemed to be very positive. I will post the updated results later tomorrow.

If you're following PlanetMySQL you've already seen Baron's post about MySQL Conference which many of us just have returned from.
It was great event as well as 5 conferences I've been before that, though however it more and more becomes MySQL marketing channel and business event rather than Users Conference as it originated. This Year even name was changed to be MySQL Conference and Expo though I have not noticed it until Baron pointed out

There is nothing wrong with MySQL Conference and Expo - there are product conferences out where for pretty much any mature product with large user base, however it may not put users interest first any more.
I've been talking to a a lot of people and many inside MySQL/Sun and out share the same opinion so we thought we should organize one.

I would like to see the conference which is focused on the product users interests rather than business interests of any particular company (or personal interests of small group of people), I would like it to be affordable so more people can attend and I'd like to see it open so everyone is invited to contribute and process is as open as possible.

Just chatting about idea in the blog comments is poor way to make things happen so we organized OurSQL Google Group which is open for everyone interested in discussing organization of such conference (or set of conferences in different locations) to join. We named the group after Sheeri's podcast - though conference name may change in the future. We just wanted to avoid using "MySQL" term due to potential trademark issues.

You can also check Arjens post on the same issue.

Entry posted by peter | One comment

Add to: | | | |

There's been talk of a community conf, not to compete with but augmenting the Sun/MySQL one.
A Google Group was created to discuss the possility of such an event, its potential, dates/location, and get it going! It's a public group, please blog & tell others about it! Sheeri suggested OurSQL, like her podcast.
Here's the URL:

http://groups.google.com/group/oursql-conference

The group was set up by Peter and myself, after earlier discussion with Sheeri, and others - Baron is currently asleep but you may have seen his post earlier today where Marten also noted his support for such initiatives - initial group invites include MySQL community members and Sun/MySQL employees alike, but as the above text indicates, it's a public group and anybody can join. So please do join and participate in the discussion!
See also Peter's post on the same.

I will be speaking at Community One in San Francisco, the Sun event immediately before Java One.
The session is Creative Programming with MySQL, which is an euphemism for "hacking MySQL" :).
I will cover the basics of programming MySQL independently of your language of choice, using stored routines, storage engines, triggers, events, and MySQL Proxy.

Track: Databases
Room: Esplanade 307
Date: 2008-05-05
Start Time: 12:25

If you are in the area, come along!

We had over 400 participants in yesterday’s MySQL-Sun event in China! Plus another 30 or so in the press event, happening in parallel.

Beijing, if nobody noticed, is preparing for the Olympics. Here’s the so called Birds Nest, that we passed several times on the way to various meetings — including yesterday’s event.

After initial linguistic difficulties, Larry Stefonic (MySQL VP APAC) found our way to the event.

This was the first MySQL-Sun event with a banner across a street!

Everything takes place in Chinese here. About as much as it’s Japanese in Japan. In Europe, we seem to give way too much way for English.

And to accommodate to the local situation, I gave my presentation (on MySQL-Sun integration) in Chinese. It was about 8 minutes, and I was happy to be several times interrupted for applause. And I didn’t say anything particularly smart — just read aloud my normal presentation, albeit in Chinese. Judging by the warm reception, that’s not an everyday gesture by Westerners in Beijing.

Now, after nearly three weeks on the road, I’m finally going back home. Hence the popular Chinese gesture: the Thumbs Up sign.

Thanks to everyone who arranged the event — especially, Scott Chen at Sun in Beijing, and Yoko Suga-san with the MySQL APAC team!

I’m not sure how I didn’t see this earlier, but it looks like MySQL 5.1 is coming with a load balancer for replicated servers. I’m absolutely pumped about this - we’ve got a few sites running with multiple db slaves and it’s so annoying having to check if they’re behind the master.

It looks like the load balancer will automatically pull slaves out if they fall behind, and route connections to the ones that are the most up to date. It is based on MySQL Proxy, which is currently in Alpha.

MySQL Load Balancer

The big news coming from Java One is that Sun is removing the last licensing hurdles in Java. What this means is Java is becoming fully Open Source.

Java users can especially thank Sun now. Also this supports Sun's vision of Open Source.

"We've been engaging with the open-source community for Java to finish off the OpenJDK project, and the specific thing that we've been working on with them is clearing the last bits that we didn't have the rights," to distribute, Sands said.

"Over the past year, we have pretty much removed most of those encumbrances," Sands said. Work still needs to be done to offer the Java sound engine and SNMP code via open source; that effort is expected to be completed this year. Developers, though, may be able to proceed without a component like the sound engine, Sands said.
Source: Yahoo News

I think Monty found the right environment to work in.

Update: Original post mentioned "Java now fully Open Source" however as the article points, Java is expected to become fully open source later this year. I wonder how much role MySQL conference played in this announcement coming earlier.

The conference that many of us just went to is called the MySQL Conference and Expo, but a lot of people don’t call it that. They call it by the name it had in 2006 and earlier: MySQL User’s Conference. In fact, some people say (or blog) that they dislike the new name and they’re going to call it the old name, because [… insert reason here…].

I call it by the new name that some people dislike so much. Why? Because it is a conference and expo, not a user’s conference. There’s no reason to pretend otherwise. The conference is organized and owned by MySQL, not the users. It isn’t a community event. It isn’t about you and me first and foremost. It’s about a company trying to successfully build a business, and other companies paying to be sponsors and show their products in the expo hall. Times have changed.

I’m not saying any of this is bad. Being successful in business is a good thing, and having sponsors and partners is fine too. I’m just pointing out that trying to make it be a user’s conference, just by calling it one, isn’t going to work.

If community members want a community conference, we’ll have to make one. MySQL/Sun cannot do this for us, because then it wouldn’t be a community conference.

There’s a simple test of whether people want this: if it happens, then the community wanted it badly enough to do something about it.

The PostgreSQL East 2008 conference I went to a few weeks ago was a great example of how this works. And the attendance fee was $75, not thousands. A conference doesn’t have to be expensive.

Who wants a conference by, for, and of the community?

Community, conferences, mysql, mysqluc08

Why use InnoDB?

InnoDB is commonly viewed as anything but performant, especially when compared to MyISAM. Many actually call it slow. This view is mostly supported by old facts and mis-information. In reality, you would be very hard-pressed to find a current, production-quality MySQL Database Engine with the CPU efficiency of InnoDB. It has its performance "quirks" and there are definitely workloads for which it is not optimal, but for standard OLTP (Online Transaction Processing) loads, it is tough to find a better, safer fit.

read more