-
Galera Cluster 2.1 Release Rolling Out
Galera Cluster release 2.1 has been published. This is a maintenance release and includes the new Galera Replication library version 2.1 and corresponding MySQL server releases for both MySQL 5.5.23 and MySQL 5.1.62 versions. The replication API version is still at version #23. The 2.1 release has a number of fixes in Galera replication and MySQL provider layers, including:fixes for foreign key support (with 5.5.23 release only)fixes for incremental state transfersupport for wsrep_sst_method=skip to avoid performing any state snapshot transfer during node joiningwsrep_urls configuration variable to specify a list of known cluster nodes where node should try to connect during startupsupporting MySQL replication filters ...to name but a few, a comprehensive list of bug fixes can be browsed in launchpad bug trackers (Galera 2.1, MySQL 5.5.23-23.6, MySQL 5.1.62-23.4). The 2.1 release is backwards compatible with earlier releases, and rolling online upgrade for the Cluster is possible. Downloads can be reached at downloads page as usual.
-
MySQL Connector/Net 6.4.5 has been released
MySQL Connector/Net 6.4.5 has been released! This is an update to our 6.4 driver and brings several bug fixes. It
is appropriate for production use with MySQL server versions 5.0-5.5
It is now available in source and binary form from http://dev.mysql.com/downloads/connector/net/#downloads
and mirror sites (note that not all mirror sites may be up to date
at this point-if you can't find this version on some mirror, please
try again later or choose another download site.)
You can read about the changes in this version at http://dev.mysql.com/doc/refman/5.5/en/connector-net-news-6-4-5.html
You can find our team blog at http://blogs.oracle.com/MySQLOnWindows.
You can also post questions on our forums at http://forums.mysql.com/.
Enjoy and thanks for the support!
-
mySQL Partitioning summary and what to watch out for
I have been looking for efficient ways to purge data from "Fast Disks" for applications that are time based and do not look at data after a time window has passed. For instance keeping a table where one stores log data from access logs and the data is okay to roll up the data and throw away the details every month. Another example is keeping a log of invites got a Facebook user from a Facebook user. Since the Facebook News feed is time based, stream publish postIds from 4 months ago do not necessarily need to be hot-the user rarely looks 4 months in the pass. Additionally with Timeline, it is hard to see a single story from 10 months ago if it is not as important as other stories. Keeping the data is necessary to delete Facebook posts from your application. So, get rid of each Invite off fast disk, and archive it on slow disks just in case you need to see the data again.One solution is to code some logic into the application to switch SQL tables based on the create date of the row and map it to a table. This works but its not very clean. Another solution is to walk the table in question and delete the old rows with DELETE statements. This is not good. The reason is DELETE IS VERY slow. In fact, the rule of thumb is this. You can do 10000s of SELECTS per seconds 10000s of Updates per second 1000s of Inserts per second, 10s of deletes a second. (I am hand waving and being general here). Additionally for INNODB (from this point just assume I am putting everything in the context of using INNODB) DELETES undo references are stored in the master ibdata file for innodb growing said data file unbounded over time. Ever notice that it grows even with innodb_file_per_table on? That's from deletes actions and is useless data. Additionally the only way to shrink that file is to do a full export to text and import after blowing away the master ibdata file(s).Another solution is a mySQL partition table, think of it as a layer that sits in-front of the storage engine and relies heavily on the optimizer. The partition table maps statements based on the partition setup to the correct underlying tables. So, if you have 12 partitions for a table called FacebookInviteHistory, there will be 12 STORAGE ENGINE TABLES for the partition table FacebookInviteHistory in the format ofFacebookInviteHistory#P#<PartitionName>.ibdWhere FacebookInviteHistory is a pointer table in the format ofFacebookInviteHistory.parFacebookInviteHistory.frm The benefits of using partition table is the following:Underlying tables can be assigned to specific disk media. Aggregate functions such as sum and count can easily be parallelized across all partitions providing very quick access to results. Data that loses it usefulness can be easily removed by dropping the partition containing only that data. Finally queries can be greatly optimized by the fact that data satisfying a given where clause can be stored only on 1 or more partitions which automatically excludes looking at other partitions-this is called pruning.These are benefits IF you set up partitioning correctly AND the partition engine gets enough info from the optimizer to pick the correct partition, else by default it queries ALL partitions. Querying all tables messes up the rule of thumb by reducing that 10K select number to 1K even if the data is not in the other partitions due to the fact of wasted iops and traveling the btree to be told that "Dude the data is not here".This being said it still looks like a good feature to use but I have some questions which Google could not answer for me.Question: Does adding or dropping partitions lock other partitions?Short Answer: NoLong Answer:For this table:CREATE TABLE PartitionTest ( senderId bigint(20) unsigned NOT NULL, recipientId bigint(20) unsigned NOT NULL, createDate datetime NOT NULL DEFAULT 0, PRIMARY KEY (senderId, recipientId, createDate))PARTITION BY RANGE ( TO_DAYS(createDate) ) ( PARTITION Jan2012 VALUES LESS THAN (TO_DAYS('2012-02-01')), PARTITION Feb2012 VALUES LESS THAN (TO_DAYS('2012-03-01')), PARTITION Mar2012 VALUES LESS THAN (TO_DAYS('2012-04-01')), PARTITION Apr2012 VALUES LESS THAN (TO_DAYS('2012-05-01')), PARTITION May2012 VALUES LESS THAN (TO_DAYS('2012-06-01')), PARTITION Jun2012 VALUES LESS THAN (TO_DAYS('2012-07-01')), PARTITION Jul2012 VALUES LESS THAN (TO_DAYS('2012-08-01')));I wrote a benchmarking tool that simulates my type of concurrency writing at double the rate. Think of this tool as mysqlslap but specific for my table structures and load. Next I ran an administration command to add a partition drop it and add it again.mysql> ALTER TABLE PartitionTest ADD PARTITION (PARTITION Aug2012 VALUES LESS THAN (TO_DAYS('2012-09-01')));Query OK, 0 rows affected (0.03 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE PartitionTest DROP PARTITION Aug2012;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> ALTER TABLE PartitionTest ADD PARTITION (PARTITION Aug2012 VALUES LESS THAN (TO_DAYS('2012-09-01')));Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0QPS from the tool did not drop.Question: Can you create a circular queue of Partition tables?Short Answer: No in 5.1, Yes in 5.6 sort of.Long Answer:Lets imagine you wanted to create 12 partitions with a RANGE ON MONTH(createDate) each month would map to one of the 12 partitions. The goal is to drop data that is over two months old, so on June 1st-Jan to April's data can be dropped. This does not work in 5.1. In 5.1 you need to add ranges that is greater then the previous range. In 5.6 you can truncate a partition. But in both mysql versions pruning just does not work on a range on MONTH of createDate so its just not an efficient partition for reads. For date and datetime fields TO_DAYS and YEAR, are pruning friendly. In 5.6 TO_SECONDS is pruning friendly. For some good reading check out Mikael's blog on 5.1 and 5.6 date columns and pruning. Try not to use timestamp columns when partitioning data. Its been very buggy in the past and does not work well with pruning. Even though you may save 4 bytes per row with timestamp the work is just not in place to partition TIMESTAMP COLUMNS.Question: Can you force reading from a partition?Short Answer: Yes with some work in 5.1 but in 5.6 the feature is better.Long Answer: If you know how the data is partitioned you can construct SQL to read the entire partition in 5.1. In 5.6 there is a command to read from a partition.In summary I am rolling out partition tables to over 1.6 TB of data across 10 shards. So far so good.
-
WordPress on S3: no more backups
WordPress on S3: no more backups
How much trouble will it be if your webserver failed? No trouble at all, if your website keeps its content on reliable Amazon S3 storage.
There are a lot of nuances in ensuring proper backups and restores of websites. When was the last backup taken? How much data might have been lost? How long will it take to recover it? When was the last time you tested restore? Do you even have an offsite backup?
Now that you can run dynamic websites off Amazon S3 storage, we’ll demonstrate why you no longer need to worry about backing up and restoring your website data. Losing the webserver is no longer a disaster. Cloud storage offers almost unsurpassable reliability a lot of website owners (small & large) would benefit from. In a way you get an "instantaneous backup" to the cloud. Your data is always safe with the cloud storage. The webserver can simply be replaced in minutes.
Contents
Crash test. Start the Webserver.
Crash the Webserver – it is a crash test after all
Launch the replacement Webserver
Feeling adventurous? Change the Webserver machine.
See also
OblakSoft has
released the 1st ever dynamic WordPress
site running on top of Amazon S3: Yapixx.
Yapixx is ready-to-run WordPress on S3, it stores all its data (content and
media) in Amazon S3 storage. Yapixx stands for Yet Another Picture
Sharing Site.
Yapixx taps
into power of Amazon S3. Amazon S3 is inexpensive, highly reliable, available
and scalable storage service. Using Amazon S3 to store Yapixx data has the
following benefits:
No backup and recovery of the site data is needed
Storage is extremelyreliable and durable by Amazon S3 design
Pictures are served by Amazon S3 directly, which makes Yapixx highly scalable
Storage cost scales with usage, no upfront reservation is needed
Storage consumption scales up and down with the amount of data stored
The crash
test is going to illustrate the first point: no backup and recovery is
needed. The data for the web site is safely stored in Amazon S3, so the
machine that Yapixx runs on can be replaced in minutes.
Crash test. Start the Webserver.
To get
started with the crash test, follow the five steps to deploy Yapixx:
Sign up for an AWS account.
Create an S3 bucket.
Start EC2 instance using read-only Yapixx AMI.
Connect to the web application from a web browser.
Enter the S3 data location and authentication information.
Refer to the
complete step-by-step
guide for extra pointers in setting up Yapixx.
Now Yapixx
is up and running and you can upload pictures. Upload some pictures to Yapixx.
Make sure the pictures are uploaded successfully.
Crash the Webserver – it is a crash test after all.
Then
terminate the EC2 instance that is running Yapixx. As a result the server should
get completely destroyed.
Is all
lost? Not at all!
Launch the replacement Webserver.
To get a
new server running, repeat the steps 3-5 of the deployment instructions:
Start EC2 instance using read-only Yapixx AMI.
Connect to the web application from a web browser.
Enter the S3 data location and authentication information.
The full
step-by-step guide for Yapixx crash test is available at here.
Make sure
that you enter the same S3 data location information! Yapixx is going to ask
for a confirmation that this is the only instance that accesses the S3 data
location: running multiple instances accessing the same S3 data location may
lead to data corruption and data loss.
Feeling adventurous? Change the Webserver machine.
To add
extra thrill, try using a different instance size: if you originally deployed a
micro instance, try using a small instance, or vice versa. This will get you a
feeling of how easy it is to scale servers up and down. Now, that’s truly
elastic!
It’ll be
even more fun if it was your WordPress site, will not it be? You can rely on
Yapixx as the starting point to take your site to Amazon S3. Launch your own
WordPress site to the cloud today! It’s easy and FREE, no writing code is
required. Start at http://www.oblaksoft.com/downloads.
We hope you
enjoyed the crash test! Are you interested in making your site highly
available with the help of cloud storage?
See also
WordPress
on S3: run
a beautiful website on Amazon cloud storage.
WordPress
on S3: how it
works.
WordPress on
S3: the
beauty of simplicity (blog).
-
Celebrating 10 years with MySQL – releasing SQLyog 10.0 GA
Hello,
We are delighted to announce the release of SQLyog 10.0. The version number makes this release very special. We must say the journey from SQLyog 1.0 to 10.0 was very exciting and rewarding. SQLyog was first released in 2002 as a MySQL GUI. Now we are a decade away from the first release. During the course of time, we have introduced tons of features taking SQLyog beyond the definition of a GUI. We couldn’t have done it without your encouragement and patronage. A big thank you for being with us in this endeavour.
Listed below are the features and enhancements introduced in this release:
Refreshing modern looks. We redesigned the user interface with a soothing color palette. We call it Twilight. You can also design your own themes and there is an option to switch to your default desktop theme as well.
The data tab is now moved to the upper pane. With this, all important tabs are in the upper pane and you can see more of what you want to see, and more than one table data can be opened under the same connection.
Schema synchronization logic is optimized and now it is up to 100 times faster for large schemas.
Introduced many keyboard shortcuts for power users.
Added an option for multi-column sorting and filtering of table data.
Many more usability enhancements and bug fixes.
Please refer to the version history for detailed release notes.
SQLyog customers can download SQLyog 10.0 GA from the Customer Area. Please make sure to get the new registration keys for SQLyog 10.0 from the Customer Area.
To evaluate SQLyog 10.0 GA please download a 30-day Trial.
We are very excited about this release, and hope that you will like it. We would love to hear from you!
Cheers,
Team SQLyog
Tweet
|
News Feeds 
