Dec
18
2013
--

Talking Drupal #028 OTC v2

The Talking Drupal hosts don’t have a set agenda in “Off the Cuff” episodes.  Today they talk about what characteristics a good Drupal developer and website have. 

Hosts

Stephen Cross – www.ParallaxInfoTech.com @stephencross

Jason Pamental – www.hwdesignco.com @jpamental

John Picozzi – www.RubicDesign.com @johnpicozzi 

Nic Laflin – www.nLightened.net @nicxvan

Dec
18
2013
--

Percona Monitoring Plugins 1.1, now with Zabbix support

Percona is glad to announce the release of Percona Monitoring Plugins 1.1. The components are designed to integrate seamlessly with widely deployed solutions such as Nagios, Cacti and Zabbix, and are delivered in the form of templates, plugins, and scripts.

In this release we have added MySQL template for Zabbix 2.0.x adopted from the existing Cacti one.

Percona Monitoring Plugins

Changelog:

* Added MySQL template for Zabbix 2.0.x (first release)
* Added FreeBSD support to Nagios plugins, partially rewritten pmp-check-unix-memory (bugs 1249575, 1244081)
* Added new options to ss_get_mysql_stats.php to better support pt-heartbeat (bugs 1253125, 1253130)
* ss_get_mysql_stats.php script was opening multiple connections to the server (bug 1255371)
* sql query for idle_blocker_duraction check in pmp-check-mysql-innodb did not conform sql mode of ONLY_FULL_GROUP_BY (bug #1240417)

A new tarball is available from downloads area or RPM and DEB packages from our software repositories. The plugins are fully supported for customers with a Percona Support contract and free installation services are provided as part of some contracts. In addition as part of Percona’s Remote DBA installation and setup of these tools are included with our services. You can find links to the documentation, forums and more at the project homepage.

About Percona Monitoring Plugins

Percona Monitoring Plugins are high-quality components to add enterprise-grade MySQL monitoring and graphing capabilities to your existing in-house, on-premises monitoring solutions. The components are designed to integrate seamlessly with widely deployed solutions such as Nagios and Cacti and are delivered in the form of templates, plugins, and scripts which make it easy to monitor MySQL performance.

The post Percona Monitoring Plugins 1.1, now with Zabbix support appeared first on MySQL Performance Blog.

Dec
13
2013
--

Seconds_Behind_Master fluctuating wildly? Check for events caught in a loop

Recently I was working with a customer where we noticed that Seconds_Behind_Master fluctuating from an expected value of 0 seconds behind to a fairly high six figure value.  The servers were configured in a master-master relationship and used 5 figure server_id values, and we had just migrated this cluster from one data centre to another by re-pointing masters.  Seeing large fluctuations in Seconds_Behind_Master can often be explained by long running queries being processed by the SQL_THREAD, however SHOW PROCESSLIST indicated that there were no long running replication events, and we had no other indication that the server was lagging  due to resource constraints — CPU, disk, and memory were under-utilized.

We then moved our investigation to manual review of the binary log where events appeared normal (5 digit server_id values) until every once in a while we would see a rash of server_id 21 events.. Wait, what?  I asked the customer about this server_id and was informed that this was in fact the old master from the original data centre.  We looked again at the dates on the server_id 21 events and sure enough they were from the time period when the cut-over between data centres took place.  Conclusion: We had a series of old events caught in a loop between the masters that due to log_slave_updates and the lack of a server_id 21 host meant that these events would never be terminated and would loop indefinitely.

So what was the fix?  We leveraged the CHANGE MASTER TO syntax to utilize IGNORE_SERVER_IDS setting:

CHANGE MASTER TO IGNORE_SERVER_IDS = 21;

This virtually immediately caused the server_id 21 events to be dropped and for Seconds_Behind_Master to stop fluctuating.

The MySQL manual has a short discussion regarding IGNORE_SERVER_IDS:

IGNORE_SERVER_IDS was added in MySQL 5.5. This option takes a comma-separated list of 0 or more server IDs. Events originating from the corresponding servers are ignored, with the exception of log rotation and deletion events, which are still recorded in the relay log.

In circular replication, the originating server normally acts as the terminator of its own events, so that they are not applied more than once. Thus, this option is useful in circular replication when one of the servers in the circle is removed. Suppose that you have a circular replication setup with 4 servers, having server IDs 1, 2, 3, and 4, and server 3 fails. When bridging the gap by starting replication from server 2 to server 4, you can includeIGNORE_SERVER_IDS = (3) in the CHANGE MASTER TO statement that you issue on server 4 to tell it to use server 2 as its master instead of server 3. Doing so causes it to ignore and not to propagate any statements that originated with the server that is no longer in use.

Hopefully this article helps someone else trying to explain Seconds_Behind_Master variations!

The post Seconds_Behind_Master fluctuating wildly? Check for events caught in a loop appeared first on MySQL Performance Blog.

Dec
12
2013
--

One more InnoDB gap lock to avoid

While troubleshooting deadlocks for a customer, I came around an interesting situation involving InnoDB gap locks. For a non-INSERT write operation where the WHERE clause does not match any row, I expected there should’ve been no locks to be held by the transaction, but I was wrong. Let’s take a look at this table and and example UPDATE.

mysql> SHOW CREATE TABLE preferences \G
*************************** 1. row ***************************
       Table: preferences
Create Table: CREATE TABLE `preferences` (
  `numericId` int(10) unsigned NOT NULL,
  `receiveNotifications` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`numericId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT COUNT(*) FROM preferences;
+----------+
| COUNT(*) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)
mysql> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '2';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 0  Changed: 0  Warnings: 0

InnoDB status shows that this UPDATE holds an X lock on a PRIMARY index record:

---TRANSACTION 4A18101, ACTIVE 12 sec
2 lock struct(s), heap size 376, 1 row lock(s)
MySQL thread id 3, OS thread handle 0x7ff2200cd700, query id 35 localhost msandbox
Trx read view will not see trx with id >= 4A18102, sees < 4A18102
TABLE LOCK table `test`.`preferences` trx id 4A18101 lock mode IX
RECORD LOCKS space id 31766 page no 3 n bits 72 index `PRIMARY` of table `test`.`preferences` trx id 4A18101 lock_mode X

For the why, Heikki explained on this aging bug report, which makes sense and I understand the fix can be difficult, though at the same time a bit annoying I wished it was handled differently. To complete this post, let me demonstrate the deadlock situation I was telling about earlier mysql1 for first session and mysql2 for the second, the order of queries apply:

mysql1> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql1> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '1';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql2> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql2> UPDATE preferences SET receiveNotifications='1' WHERE numericId = '2';
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0
mysql1> INSERT INTO preferences (numericId, receiveNotifications) VALUES ('1', '1'); -- This one goes into LOCK WAIT
mysql2> INSERT INTO preferences (numericId, receiveNotifications) VALUES ('2', '1');
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

So you see how easy it is to cause a deadlock, so make sure to avoid this situation – if a non-INSERT write operation is more likely to not match any row because the INSERT part is yet to come on the transaction, don’t do it or use REPLACE INTO or use READ-COMMITTED transaction isolation.

The post One more InnoDB gap lock to avoid appeared first on MySQL Performance Blog.

Dec
11
2013
--

Talking Drupal #027 – Drupal 8

Show Topics

– Starting Point

– Responsive – Mobile Friendly

– WYSIWYG, In-line editing

– Views integration – Admin forms 

– Block as Entity

– Configuration System 

– Theming

– Drupal 8 resources

Module of the Week

– MultiSelect – https://drupal.org/project/multiselec

Links

– https://drupal.org/drupal-8.0

Hosts

– Stephen Cross – www.ParallaxInfoTech.com @stephencross

– Jason Pamental – www.hwdesignco.com @jpamental

– John Picozzi – www.RubicDesign.com @johnpicozzi 

– Nic Laflin – www.nLightened.net @nicxvan

Dec
11
2013
--

Talking Drupal #027 – Drupal 8

Show Topics

– Starting Point

– Responsive – Mobile Friendly

– WYSIWYG, In-line editing

– Views integration – Admin forms 

– Block as Entity

– Configuration System 

– Theming

– Drupal 8 resources

Module of the Week

– MultiSelect – https://drupal.org/project/multiselec

Links

– https://drupal.org/drupal-8.0

Hosts

– Stephen Cross – www.ParallaxInfoTech.com @stephencross

– Jason Pamental – www.hwdesignco.com @jpamental

– John Picozzi – www.RubicDesign.com @johnpicozzi 

– Nic Laflin – www.nLightened.net @nicxvan

Dec
10
2013
--

Q&A: Geographical disaster recovery with Percona Replication Manager

Q&A: Geographical disaster recovery with Percona Replication ManagerMy December 4 webinar, “Geographical disaster recovery with  Percona Replication Manager (PRM),”  gave rise to a few questions. The recording of the webinar and the slides are available here, and I’ve answered the questions I didn’t have time to address below.

Q1: Hi, I was wondering if corosync will work in cloud environment. As far as I know it is hard to implement because of no support of unicast or multicast.

A1: Corosync supports the udpu transport since somewhere in the 1.3.0 branch. udpu stands for udp unicast and it works in AWS for instance. Most recent distribution are using 1.4.x so it is easy to find.

Q2: For token wouldn’t it make sense to have a value higher than 3000 to account for any packetloss and the default retry of 3 seconds for tcp communication?

A2: Corosync uses udp, not tcp so the argument is somewhat irrelevant.

Q3: Is PRM supported with a Percona support contract?

A3: Yes, PRM is now supported with a Percona Gold or Platinum Support contract. It is not availabe with Silver support.

Q4: Is PRM working with GTID’s in 5.6?

A4: There’s a version in testing phases adapted by Frederic Descamps that works with 5.6/GTID. As soon as it is tested properly, I’ll release it. So far, it is very clean in term of logic.

Q5: Does Percona Replication Manager do anything different with replication over the built in mySQL replication to combat the single threaded nature?

A5: No

Q6: We agree that fencing always has to be configured (even with no shared resources , in cases of mysql stop failure for example) : What about MySQL and Percona’s PRM Ressource agent behavior when fencing? and recommendations concerning fencing? There was no fencing in the demo and there is no fencing configured on the pacemaker crm snippets examples provided in on Percona’s github repo.

A6: Fencing is independent of PRM and is well covered elsewhere on the web. I’ll see to add an example configuration with stonith devices. On real hardware, the most common stonith device are IPMI or ILO. These technologies comes with nearly all the server brands.

Q7: Is there any other Mysql HA setup supported by Percona’s mysql_prm pacemaker resource agent than the Mysql master slave replication ? Like Multi master setups ? In case yes , will you release some crm configurations snippets examples for other Mysql HA setups ?

A7: No, only a single master is supported. The main argument here is that multiple master doesn’t scale writes and are a big source of conflicts.

Q8: Why use Percona Replication Manager over XtraDB cluster (omitting ease of Geo-DR) on a write performance perspective , also considering HA and cost ?

A8: Percona XtraDB Cluster (PXC) is more advanced and capable but, some workloads, like large transactions, are not working well with PXC. Regular replication is well known and many customers are not willing to try a newer technology like (PXC) and prefer to rely on regular replication. Also, PRM can be mixed with PXC without problem. Example of such configuration will be published soon. In term of cost both are free, support is available from Percona (Gold and Platinium) but (PXC) has a premium.

The post Q&A: Geographical disaster recovery with Percona Replication Manager appeared first on MySQL Performance Blog.

Dec
08
2013
--

Video Reading from “Ocean of Dust”

Happy Sunday everyone. Most of my friends know that I’m pretty introverted (maybe that’s why I like writing?), so I wanted to get out of my comfort zone and try video blogging. Below is my first attempt, and I thought it fitting to read a short excerpt from Ocean of Dust. I hope you enjoy it, or at the very least have a good laugh because I’m a dork. :) Either way, it should brighten your Sunday.

 

Dec
07
2013
--

Linux performance tuning tips for MySQL

Because most MySQL production systems probably run on Linux, I’ve decided to place the most important Linux tuning tips that will help improve MySQL performance. There is nothing new here, most of them are well known, however, I’ve decided to collect those Linux configuration tips into 1 blog post.

Filesystem

  • ext4 (or xfs), mount with noatime
  • Scheduler – use deadline or noop
# echo deadline >/sys/block/sda/queue/scheduler
add "elevator=deadline" to grub.conf

(For more info see Linux Schedulers in TPCC like benchmark)

Memory

  • Swappiness and NUMA:
# echo 0 > /proc/sys/vm/swappiness
add "vm.swappiness = 0" to /etc/sysctl.conf

  • Set numa interleave all
numactl --interleave=all

If using Percona Server we can place it into mysqld_safe script, as Percona Server supports NUMA control.

Jeremy Cole blog contains excellent overview of NUMA as well as additional NUMA tools

(and do not forget about innodb_flush_method=O_DIRECT)

CPU

Make sure there is no powersave mode enabled:
Check /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor
and make sure it is not ondemand
Check /proc/cpuinfo and compare cpu MHz number to what is listed under the “model name”
Disable the “ondemand” if it is running

Example: “ondemand” is running on all the servers

$ ps ax| grep kondemand|wc -l
65
$ cat /sys/devices/system/cpu/cpu0/cpufreq/scaling_governor
ondemand

and we have this:

/proc/cpuinfo:
model name : Intel(R) Xeon(R) CPU E5-4640 0 @ 2.40GHz
..
cpu MHz : 1200.000

In this case we will need to disable “ondemand”.

These simple Linux tuning tips will increase MySQL performance and make it more stable (and avoid swapping). I’m am also interested in hearing about your experience with different Linux configurations and MySQL. Please share those in the comments.

The post Linux performance tuning tips for MySQL appeared first on MySQL Performance Blog.

Dec
05
2013
--

The Vanished Knight by M. Gerrick

TheVanishedKnight200x300Misha Gericke is a fellow author in Untethered Realms. Back in October she published The Vanished Knight, a brand new fantasy. You might want to check it out or buy a copy for friends this holiday.

 

Since the death of her parents, Callan Blair has been shunted from one foster family to another, her dangerous secret forcing the move each time. Her latest foster family quickly ships her off to an exclusive boarding school in the Cumbrian countryside. While her foster-brother James makes it his mission to get Callan expelled, a nearby ancient castle holds the secret doorway to another land…

When Callan is forced through the doorway, she finds herself in the magical continent of Tardith, where she’s shocked to learn her schoolmates Gawain and Darrion are respected soldiers in service to the king of Nordaine, one of Tardith’s realms. More than that, the two are potential heirs to the Black Knight—Nordaine’s crown prince.

But when the Black Knight fails to return from a mysterious trip, the realm teeters on the brink of war. Darrion and Gawain set out to find him, while Callan discovers there is more to her family history than she thought. The elves are claiming she is their princess.

Now with Darrion growing ever more antagonistic and her friendship with Gawain blossoming, Callan must decide whether to stay in Nordaine—where her secret grows ever more threatening—or go to the elves and uncover the truth about her family before war sets the realms afire.

 Buy it on Amazon or Amazon UK


Misha

M. Gerrick (AKA Misha Gericke) has basically created stories since before she could write. Many of those stories grew up with her and can be seen in her current projects.

She lives close to Cape Town, with a view over False Bay and Table Mountain.

If you’d like to contact her, feel free to mail her at warofsixcrowns(AT)gmail(DOT)com, Circle her on Google Plus or follow her on Twitter. If you’d like to see her writer-side (beware, it’s pretty insane), please feel free to check out her blog. You can also add The Vanished Knight on Goodreads.

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