Diamond Notes

I have one project going on that is using a MySQL Cluster.  We have some tables that are going to grow extremely rapidly, so I am going to convert them to storing (non-indexed) columns on disk.  Before I started working on this, I had the naive thought that this was a fairly simple thing.  Maybe a table alter command of some type.  Nope.  Not the case.  As detailed here: http://dev.mysql.com/doc/refman/5.1/en/mysql-cluster-disk-data-objects.html there are several steps required to create a table that is stored on disk.

1. Create a log file group.  This is used to create undo logs for the table.

2. Create a tablespace and assign the log file group to the tablespace and also assign at least one data file.

3.  Create a “Disk Data Table” that uses the tablespace on disk for storage of table data.

This doesn’t really help my particular situation out as I already have the table created.  While there isn’t a huge amount of data in it currently, I didn’t want to dump the data out of the table(s), rebuild them with this method and then  re-import the data.  I know, I am lazy :)  So, I asked on the cluster mailing list what I could do and Stewart Smith replied:

“It’s not an online operation, but you can ALTER TABLE foo TABLESPACE ts1 STORAGE DISK ENGINE=NDB; to convert table foo to be a disk data table.  It’s a copying alter, so you should be in single user mode and the table will be locked.”

So, I can create the log files and table space, shut my cluster down to single-user mode, and then just alter the table.  Simple(r) even for the lazy among us.  If you are using a MySQL Cluster you absolutely should be subscribed to the cluster mailing list.  It (and the other MySQL mailing lists) can be subscribed to at http://lists.mysql.com.

Thanks Stewart!!

I just wanted to take a minute and let people know that we are continuing to work internally on the query processing tools. The querysniffer tool is feature complete for now (and I can’t imagine what else you might need with it). It grabs the MySQL data from the network and injects it into a database table storing the query, the time stored, the total execution time of the query and the host it was captured from. In addition, you can tell it to not grab the data modifying statements (inserts, updates and deletes). Also, it can replace varibles (”WHERE username=”Jack”) with something like this: WHERE username={string}. Why is this useful? So you can group the queries together. Even though one query might be searching for Jack and another query might be searching for Jill, they are both the same query. Can’t think of any other options, but I probably missed one or two.

After all that work, it becomes simple to write a web interface of php or perl to display statistics. I already have one set up internally displaying the most executed queries and the average execution and total execution time fo a query. While I will release this as part of the package, this will be something that each DBA will end up spending some time tweaking to fit their own needs I am sure.

I haven’t really started working on the benchmarking part of the toolkit yet. I have an idea of implementing a unionfs(??) so that I can run the queries against a database and then roll the database back without to much difficulty. That way I can run INSERTs, UPDATEs and DELETEs without worrying about.

We have some internal paperwork to take care of before these new additions can be released but I am confident it won’t be long until that is taken care of. As soon as it is done we will release the new code to the world.

Hey all,

This has nothing to do with MySQL.   Sorry about this, but I am at a loss.  Several months ago I switched from using godadday shared hosting that I had used for about a year and a half.  It wasn’t because of their service, which was always good, but because of the fact that I got sick of how they started using very blatent sexuality in their commercials.  You may laugh at that, and that is fine, but I have four kids and think about such things.  Anyways, I switched to another shared-website hosting solution.  They lasted a month until some script kiddie broke into the host server through ftp (wow..imagine that..breaking in through an unencrypted access point).

Not only did I have to find out about this from a reader of my site, but I was told by the hosting company that I needed to change my password and clean up everything and they weren’t going to do anything about it.

Because of the following I decided to change again:

• I didn’t cause the problem
• there never should have been a problem in the first place
• a simple script run from the root directory could have searched through all accounts and grepp’ed and sed’ed the offending lines from the html and php saving me and other hosted sites hours of work

I decided that it was time to have a bit more control over things.  So, I got a vps with another company.  Now I could keep the stupid things from happening, but three times in two weeks my entire vps has gone unavailable.  Today it was for almost six hours. No reason given.Is it is just me or is this infuriating?  So, I am going to change again very soon.  This time I am going to solicit some input as I seem to make bad choices when it comes to hosting.  I have to stop changing providers as it just causes more problem. I am looking for a Xen-based vps.  Open to suggestions

Again, sorry for the bother about such an off-topic item.  Please forgive me but I am pulling out my rapidly greying hair.

Thanks MySQL AB!!

For those who don’t read the general mysql mailing list:

Dear MySQL users,

we are proud to present to you the MySQL Server 5.1.22-rc release,
the first 5.1 “release candidate” version of the popular open source
database.

Bear in mind that this is still a “candidate” release, and as with any
other pre-production release, caution should be taken when installing on
production level systems or systems with critical data. For production
level systems using 5.0, we would like to direct your attention to the
product description of MySQL Enterprise at:

http://mysql.com/products/enterprise/

The MySQL 5.1.22-rc release is now available in source and binary form
for a number of platforms from our download pages at

http://dev.mysql.com/downloads/

and mirror sites. Note that not all mirror sites may be up to date at
this point in time, so if you can’t find this version on some mirror,
please try again later or choose another download site.

Please also note that some of our mirrors are currently experiencing
problems that may result in serving corrupted files. We are working with
the mirror maintainers to resolve this.

We welcome and appreciate your feedback, bug reports, bug fixes,
patches etc.:

http://forge.mysql.com/wiki/Contributing

The following section lists the changes from version to version in the
MySQL source code since the latest released version of MySQL 5.1, the
MySQL 5.1.21-beta release. It can also be viewed online at

http://dev.mysql.com/doc/refman/5.1/en/news-5-1-22.html

Functionality added or changed:
* There is a new innodb_autoinc_lock_mode system variable to
configure the locking behavior that InnoDB uses for generating
auto-increment values. The default behavior now is slightly
different from before, which involves a minor incompatibility
for multiple-row inserts that specify an explicit value for
the auto-increment column in some but not all rows.
This can be used to improve scalability and performance, see
Section 13.5.6.3, “How AUTO_INCREMENT Handling Works in InnoDB.”:
http://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html

Bugs fixed:
* NDB Cluster: Backups of TIMESTAMP columns made with
ndb_restore on a MySQL Cluster using data nodes hosts of one
endian could not be used to restore the cluster’s data to data
node hosts of the other endian.
(Bug#30134: http://bugs.mysql.com/30134)
* NDB Cluster (Replication): Multi-master replication setups did
not handle –log-slave-updates correctly.
(Bug#30017: http://bugs.mysql.com/30017)
* When sorting rows in an INNODB table using a primary key,
where the sort was on the the primary key column and the DESC
operator was applied, the rows would be incorrectly sorted if
you included a simple WHERE field = value clause in the query.
(Bug#31001: http://bugs.mysql.com/31001)
* Replication of InnoDB partitioned tables could lose updates
with row-based or mixed replication format.
(Bug#28430: http://bugs.mysql.com/28430)
* mysql_install_db could fail to find its message file.
(Bug#30678: http://bugs.mysql.com/30678)
* Non-range queries of the form SELECT … FROM … WHERE
keypart_1=const, …, keypart_n=const ORDER BY … FOR UPDATE
sometimes were unnecessarily blocked waiting for a lock if
another transaction was using SELECT … FOR UPDATE on the
same table. (Bug#28570: http://bugs.mysql.com/28570)
* Under some circumstances, a UDF initialization function could
be passed incorrect argument lengths.
(Bug#29804: http://bugs.mysql.com/29804)
* CONNECTION_ID() always returned 0 for the embedded server
(libmysqld). (Bug#30389: http://bugs.mysql.com/30389)
* The mysql_list_fields() C API function incorrectly set
MYSQL_FIELD::decimals for some view columns.
(Bug#29306: http://bugs.mysql.com/29306)
* Read lock requests that were blocked by a pending write lock
request were not allowed to proceed if the statement
requesting the write lock was killed.
(Bug#21281: http://bugs.mysql.com/21281)
* Memory corruption occurred for some queries with a top-level
OR operation in the WHERE condition if they contained equality
predicates and other sargable predicates in disjunctive parts
of the condition. (Bug#30396: http://bugs.mysql.com/30396)
* The server created temporary tables for filesort operations in
the working directory, not in the directory specified by the
tmpdir system variable.
(Bug#30287: http://bugs.mysql.com/30287)
* Using KILL QUERY or KILL CONNECTION to kill a SELECT statement
caused a server crash if the query cache was enabled.
(Bug#30201: http://bugs.mysql.com/30201)
* Operations that used the time zone replicated the time zone
only for successful operations, but did not replicate the time
zone for errors that need to know it.
(Bug#29536: http://bugs.mysql.com/29536)
* mysqldump from the MySQL 5.1.21 distribution could not be used
to create a dump from a MySQL 5.1.20 or older server.
(Bug#30123: http://bugs.mysql.com/30123)
* When using a combination of HANDLER… READ and DELETE on a
table, MySQL continued to open new copies of the table every
time, leading to an exhaustion of file descriptors. This was
caused in MySQL 5.1.15 by a fix for
Bug#21587: http://bugs.mysql.com/21587; the current fix
consists of reverting the earlier fix.
(Bug#29474: http://bugs.mysql.com/29474)
* Tables using the InnoDB storage engine incremented
AUTO_INCREMENT values incorrectly with ON DUPLICATE KEY
UPDATE. (Bug#28781: http://bugs.mysql.com/28781)

Enjoy !
Joerg

–
Joerg Bruehe, Senior Production Engineer
MySQL AB, www.mysql.com

–