Nov
20
2012
--

Using Percona Server in Production at Percona Live London

Have you been using Percona Server ? Interested to Try ? Using Percona Server in Production Tutorial at Percona Live, London is a great talk for you.

In my experience working with customers I discovered many people are not aware about number of unique features Percona Server offers and how they can help you to achieve better performance and stability in your MySQL environment. I start this talk with many general features which MySQL and Percona Server share giving advice on best production configuration and best practices and then move to special features which are unique to Percona Server. I provide specific examples how to use the features and what benefits they provide.

See you there !

Nov
19
2012
--

Webinar: Looking for Painless MySQL High Availability ?

I have a pleasure to deliver Webinar on Industrial-Strength MySQL Applications Using Percona and Continuent together with Robert Hodges next week, Nov 28.

We will talk about how you can use technologies from Percona and Continuent to build Highly Performance and Highly Available Applications utilizing
MySQL Replication. I’ve been interesting in doing this Webinar for long time as we have a lot of great feedback from our customers using Continuent Tungsten and Percona software very successfully to implement advanced filtering, achieve much higher replication performance with parallel replication as well as make failover a lot more painless and easy to use. If you’re using MySQL Replication in highly demanding environment you would want to learn what these technologies have to offer.

Enjoy! Register for Webinar Now!

Nov
19
2012
--

Concatenating MyISAM files

Recently, I found myself involved in the migration of a large read-only InnoDB database to MyISAM (eventually packed). The only issue was that for one of the table, we were talking of 5 TB of data, 23B rows. Not small… I calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take about 10 days. The bottleneck was clearly the lack of concurrency on the read part from InnoDB and then the key management for MyISAM. The server has many dozen drives so it was easy to add more concurrency so I kicked off, from a script, insertions into 16 identical MyISAM files for distinct parts of the table. That was much faster and would complete within a day.

Then, while the Innodb extraction was running at a nice pace, I thought about the next phase. My first idea was simply to do insert into MyISAM_table select * from MyISAM_table1 and so on for the 16 files. Since MyISAM are flat files, that should be faster, especially with the keys disabled. At that point, I remembered, from a previous disaster recovery work where a database directory has been wiped out that the MyISAM files have no headers which make them difficult (read almost impossible) to locate on a drive with tools like ext3grep. No headers… that means the first byte of byte of a file is the first byte of the first row… So we should be able to concatenate these files. Let’s see.

mysql> create table test_concat(id int unsigned not null, primary key (id)) engine=myisam;
Query OK, 0 rows affected (0.01 sec)

mysql> create table test_concat_part like test_concat;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test_concat (id) value (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into test_concat_part (id) value (4),(5),(6);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> flush tables;
Query OK, 0 rows affected (0.01 sec)

Then, at the shell command line:

root@django:/var/lib/mysql/test# ls
test_concat.frm  test_concat.MYD  test_concat.MYI  test_concat_part.frm  test_concat_part.MYD  test_concat_part.MYI
root@django:/var/lib/mysql/test# 
root@django:/var/lib/mysql/test# cat test_concat_part.MYD >> test_concat.MYD
root@django:/var/lib/mysql/test# myisamchk -rq test_concat
- check record delete-chain
- recovering (with sort) MyISAM-table 'test_concat'
Data records: 3
- Fixing index 1
Data records: 6

And then, back in mysql:

mysql> use test
Database changed
mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test_concat;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
|  5 |
|  6 |
+----+
6 rows in set (0.00 sec)

So, yes, you can concatenate MyISAM files, even when multiple keys are defined. Not for everyday use but still pretty cool.

Addendum

Following Peter’s comment, I added varchar and deleted rows to the mix:

mysql> truncate table test_concat;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table test_concat_part;
Query OK, 0 rows affected (0.01 sec)

mysql> alter table test_concat add data varchar(10);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table test_concat_part add data varchar(10);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> insert into test_concat (id,data) value (1,'one'),(2,'two'),(3,'three');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> insert into test_concat_part (id,data) value (44,'todelete'),(4,'four'),(5,'five'),(6,'six');
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> delete from test_concat_part where id = 44;
Query OK, 1 row affected (0.00 sec)

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

root@django:/var/lib/mysql/test# myisamchk -rq test_concat
- check record delete-chain
- recovering (with sort) MyISAM-table 'test_concat'
Data records: 3
- Fixing index 1
myisamchk: error: Couldn't fix table with quick recovery: Found wrong number of deleted records
myisamchk: error: Run recovery again without -q
MyISAM-table 'test_concat' is not fixed because of errors
Try fixing it by using the --safe-recover (-o), the --force (-f) option or by not using the --quick (-q) flag
root@django:/var/lib/mysql/test# myisamchk -r test_concat
- recovering (with sort) MyISAM-table 'test_concat'
Data records: 6
- Fixing index 1

mysql> select * from test_concat;
+----+-------+
| id | data  |
+----+-------+
|  1 | one   |
|  2 | two   |
|  3 | three |
|  4 | four  |
|  5 | five  |
|  6 | six   |
+----+-------+
6 rows in set (0.00 sec)

So varchar columns are supported without any issue but, deleted rows prevent the use of the quick option for myisamchk.

Written by in: MySQL,Zend Developer |
Nov
19
2012
--

Speaking at Percona Live London 2012: Choosing Hardware for MySQL

Another Percona Live event is coming up, this time in London on the 3rd and 4th of December. I’m sure it is going to be as awesome as it was last year.

This year, I will talk about ‘Choosing Hardware For MySQL‘.

Of course, the most important hardware components will be covered: CPU, Memory, Network, Disk Subsystem and also the Cloud.
I will try to help attendees better understand how MySQL uses hardware (and how it does not), how it optimizes random IO operations on disk, what scalability means for MySQL, what it’s limitations are and so on…
Want more? Just attend!

 
You can register here.

Nov
16
2012
--

Question of the week: Maximum number of tables per MySQL instance.

I’ve got the great response to my two previous polls (1,2) so lets continue learning about how we all use MySQL. The question of this week is What is the maximum number of tables per MySQL instance do you use ?

Note: There is a poll embedded within this post, please visit the site to participate in this post’s poll.

Thank you for participating!

Nov
15
2012
--

Percona Toolkit version 2.1.6 released

The Percona Toolkit team is happy to announce the release of Percona Toolkit version 2.1.6. This is the sixth stable release in the 2.1 series, and primarily a bug-fix release; We suggest that users upgrade to the latest version of the tools.

The complete list of changes is on the Launchpad milestone for 2.1.6, but here are some highlights the release:

  • pt-online-schema-change can now handle column renames
  • pt-online-schema-change’s DELETE trigger no longer fails when altering a primary key
  • pt-stalk got a plugin hook interface
  • pt-show-grant support for column-level grants
  • pt-table-checksum will warn if slaves have a differing binlog_format

Remember that if you need help with Percona Toolkit, we provide flat-fee, unlimited support services with rapid 24×7 response on a commercial basis via our Support contracts. Links to the downloads, documentation, bug reports, mailing list, and more are available from the Percona Toolkit project homepage.

Nov
15
2012
--

Announcing Percona XtraDB Cluster 5.5.28-23.7

Percona is glad to announce the release of Percona XtraDB Cluster on November 15th, 2012. Binaries are available from downloads area or from our software repositories.

Features:

  • Percona XtraDB Cluster has ported Twitter’s MySQL NUMA patch. This patch implements improved NUMA support as it prevents imbalanced memory allocation across NUMA nodes.
  • Number of binlog files can be restricted when using Percona XtraDB Cluster with the new max_binlog_files option.
  • New status variable wsrep_incoming_addresses has been introduced. It contains a comma-separated list of incoming (client) addresses in the cluster component.
  • Multiple addresses can now be specified in the gcomm:// URLwhen defining the address to connect to cluster. Option wsrep_urls is now deprecated, wsrep_cluster_address should be used to specify multiple addresses instead.
  • GTID can now be recovered by starting mysqld with –wsrep-recover option. This will parse GTID from log, and if the GTID is found, assign it as initial position for actual server start.
  • SET PASSWORD command replication has been implemented. The SET statement can contain other variables and several password changes, and each password change will be replicated separately as one TOI (total order isolation) call. The user does not have to be explicitly defined, current user will be detected and correct SET PASSWORD statement will be generated for TOI replication in every use case.
  • SQL statements aborted due to multi-master conflict can be automatically retried. This can be controlled with wsrep_retry_autocommit variable. The retry will happen only if there is real abort for the SQL statement. If statement itself enters into commit phase and detects certification failure, there will be no retry.
  • This version of Percona XtraDB Cluster implements significant speedups and memory footprint reduction on ranged queries.

Bugs fixed:

  • Some wsrep variables, like wsrep_provider, wsrep_provider_options and wsrep_cluster_address, could be “doubly” allocated which could cause memory leak. Bug fixed #1072839.
  • In case variables wsrep_node_address and wsrep_node_incoming_address were not set explicitly, port number would be set to 0. Bug fixed #1071882.
  • Server would hang before storage engine initialization with rsync SST and with option wsrep_start_position enabled. Bug fixed #1075495.
  • SST script interface has been upgraded to use named instead of positional parameters. Bug fixed #1045026.
  • When server was started with no wsrep_provider and with regular MySQL replication slave configured, could cause intermittent failure to start MySQL slave during startup. Bug fixed #1075617.
  • If DDL is processing with RSU method and there is MDL conflict with local transaction, this conflict would remain unresolved. Bug fixed #1039514.
  • Static SST method list has been removed. Bug fixed #1045040.
  • DDL in multi-statement would cause a server crash. Bug fixed #1049024.
  • mysqld_safe doesn’t restart the node automatically anymore. Bug fixed #1077632.
  • Server would fail to start if the variable wsrep_provider was unset. Bug fixed #1077652.

Other bug fixes: bug #1049103, bug #1045811, bug #1057910, bug #1052668, bug #1073220, bug #1076382,

Based on Percona Server 5.5.28-29.1 including all the bug fixes in it, Percona XtraDB Cluster 5.5.28-23.7 is now the current stable release. All of Percona‘s software is open-source and free.
We did our best to eliminate bugs and problems, but this is a software, so bugs are expected. If you encounter them, please report them to our bug tracking system.

Nov
15
2012
--

Joe Celko’s review of “SQL Performance Explained”

Joe Celko’s review of my book SQL Performance Explained was just published. The bottom line: “This book is definitively worth having in the company library.”

Nov
14
2012
--

Should Readers say which Book you Write next?

Your book is published and it’s time to think about the next one. You have lots of great ideas, but wait… is it your place to choose your new book, or your readers?

“What’re you talking about, Graeme?” I hear you cry. “I’ll write what I want to.”

Agreed, it is usually the case that the author decides what comes next. It’s probably been planned and outlined even while the last book was being edited. Sometimes your contract may dictate this for you, if you have signed with a publisher for a multi-book deal.

If you are writing a series, then it ‘s a “no-brainer”: You write the next book.

But what, if you are like me, you don’t want to be tied down to one setting or set of characters for the next few years? You want to try something different. Can you veer off course, write a book or two, and then come back to continue the series?

Yes, you can, but this is where you need to think about your readers. If you were to give them the choice of the sequel or something else, if they truly enjoyed your previous book then I bet they’d vote for the sequel. Oh dear, now you’re no longer in control. :)

You might get away with jumping ship if you haven’t declared a series. Even though you’ve left your book open for a sequel, the readers don’t know if there’ll be one or not. Often, authors come back years later to write a sequel, a prequel, or other books in the same world or featuring the same characters.

The advantage of this is that you could write several standalone books, and see which one(s) your readers prefer before delivering a sequel. Not only are you capitalizing on your best-received book, but you can make it look like you listened to your fans and wrote a sequel for them. Manipulative? I don’t think so – you’re not harming anyone.

The disadvantage is that if you wait too long between books in a series, you’ll upset your readers. A well-known example is when Stephen King took 5 or 6 years between each volume of the Dark Tower. Fans got upset that it forced them to re-read the entire series each time, to remind themselves. Not everyone likes to re-read books.

In summary, I would say if you have declared a series, write them back to back. If not, readers are usually fine if you return later and write a sequel.

It has been suggested by some that authors poll their readers with several book ideas, and let their fans pick the one they want to read. This idea hasn’t gone down well, I suspect because writers perform best when their passion is high. Sometimes you just know what book you want/need to write – a particular story has to be told. But if you really can’t select between two of your greatest ideas, why not ask your readers. They’ll love you for it.

How much would you be swayed by your readers? Would you trust them to tell you which book to write next?

 

Nov
14
2012
--

FOSDEM 2013: MySQL & Friends Devroom CfP is open !

2013 is near… and so is the next FOSDEM edition !

This year again, MySQL will be represented by its Community.
If you want to discuss with friends of MySQL it’s the place to be in
February !

Like every year, FOSDEM takes place the first week-end of February in
Brussels.

We will again have a room in the H building (100 seat). The MySQL &
Friends dev room will be open all day Sunday, February 3rd.

Call for Papers is open until December 21st here.

This year all submissions will be reviewed by a committee that will
create the schedule.
The committee is composed by :

* Andrew Hutchings (HP)
* Andrew Morgan (Oracle)
* Sergey Petrunia (MariaDB)
* MC Brown (Couchbase)
* Giuseppe Maxia (Continuent)
* Frédéric Descamps (Percona)

Thank you for submitting your sessions and see you soon in Brussels to
talk MySQL and/or have some nice Belgian Beers ;-)

Original mail to the Community MySQL mailing list

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com