May
30
2014
--

Microsoft, Salesforce Partnership Is Proof That Business Makes Strange Bedfellows

Frenemies It’s often been said that politics makes for strange bedfellows, but the same could be said for business. If you want proof, look no further than yesterday’s partnership announcement between Salesforce and Microsoft. These are two companies that compete fiercely for the same customers in the CRM space, suddenly making nice. If you look closely at the deal, you’ll see… Read More

May
30
2014
--

Nokia Buys Desti To Add AI To HERE Maps, Will Shut App Down

desti1 Nokia, now lightened of its handset load thanks to Microsoft, has made an acquisition to expand its HERE mapping business, one of the assets that has remained with the Finnish company. It has bought Desti, a mapping startup and app maker that uses artificial intelligence and natural language processing to help people find what they are looking for. Desti is a spin-out from SRI International,… Read More

May
30
2014
--

ExactTarget Co-Founder Scott Dorsey Leaves Salesforce, McCorkle New CEO

2579025866_9bc96fb97d_b A changing of the guard at ExactTarget, the email and digital marketing company that Salesforce acquired last year for $2.5 billion, its biggest acquisition to date. ExactTarget co-founder Scott Dorsey is stepping down as CEO, while Scott McCorkle, president of technology and strategy and COO, is stepping up.
Sources tell us that emails went out to staff yesterday from Salesforce CEO Marc… Read More

May
29
2014
--

High Availability with MySQL Fabric: Part II

This is the third post in our MySQL Fabric series. If you missed the previous two, we started with an overall introduction, and then a discussion of MySQL Fabric’s high-availability (HA) features. MySQL Fabric was RC when we started this series, but it went GA recently. You can read the press release here, and see this blog post from Oracle’s Mats Kindahl for more details. In our previous post, we showed a simple HA setup managed with MySQL Fabric, including some basic failure scenarios. Today, we’ll present a similar scenario from an application developer’s point of view, using the Python Connector for the examples. If you’re following the examples on these posts, you’ll notice that the UUID for servers will be changing. That’s because we rebuild the environment between runs. Symbolic names stay the same though. That said, here’s our usual 3 node setup:

[vagrant@store ~]$ mysqlfabric group lookup_servers mycluster
Command :
{ success     = True
  return      = [{'status': 'SECONDARY', 'server_uuid': '3084fcf2-df86-11e3-b46c-0800274fb806', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.70.101'}, {'status': 'SECONDARY', 'server_uuid': '35cc3529-df86-11e3-b46c-0800274fb806', 'mode': 'READ_ONLY', 'weight': 1.0, 'address': '192.168.70.102'}, {'status': 'PRIMARY', 'server_uuid': '3d3f6cda-df86-11e3-b46c-0800274fb806', 'mode': 'READ_WRITE', 'weight': 1.0, 'address': '192.168.70.103'}]
  activities  =
}

For our tests, we will be using this simple script:

import mysql.connector
from mysql.connector import fabric
from mysql.connector import errors
import time
config = {
    'fabric': {
        'host': '192.168.70.100',
        'port': 8080,
        'username': 'admin',
        'password': 'admin',
        'report_errors': True
    },
    'user': 'fabric',
    'password': 'f4bric',
    'database': 'test',
    'autocommit': 'true'
}
fcnx = None
print "starting loop"
while 1:
    if fcnx == None:
	print "connecting"
        fcnx = mysql.connector.connect(**config)
        fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE)
    try:
	print "will run query"
        cur = fcnx.cursor()
        cur.execute("select id, sleep(0.2) from test.test limit 1")
        for (id) in cur:
            print id
	print "will sleep 1 second"
        time.sleep(1)
    except errors.DatabaseError:
        print "sleeping 1 second and reconnecting"
        time.sleep(1)
        del fcnx
        fcnx = mysql.connector.connect(**config)
        fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE)
        fcnx.reset_cache()
        try:
            cur = fcnx.cursor()
            cur.execute("select 1")
        except errors.InterfaceError:
            fcnx = mysql.connector.connect(**config)
            fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE)
            fcnx.reset_cache()

This simple script requests a MODE_READWRITE connection and then issues selects in a loop. The reason it requests a RW connector is that it makes it easier for us to provoke a failure, as we have two SECONDARY nodes that could be used for queries if we requested a MODE_READONLY connection. The select includes a short sleep to make it easier to catch it in SHOW PROCESSLIST. In order to work, this script needs the test.test table to exist in the mycluster group. Running the following statements in the PRIMARY node will do it:

mysql> create database if not exists test;
mysql> create table if not exists test.test (id int unsigned not null auto_increment primary key) engine = innodb;
mysql> insert into test.test values (null);

Dealing with failure

With everything set up, we can start the script and then cause a PRIMARY failure. In this case, we’ll simulate a failure by shutting down mysqld on it:

mysql> select @@hostname;
+-------------+
| @@hostname  |
+-------------+
| node3.local |
+-------------+
1 row in set (0.00 sec)
mysql> show processlist;
+----+--------+--------------------+------+------------------+------+-----------------------------------------------------------------------+----------------------------------------------+
| Id | User   | Host               | db   | Command          | Time | State                                                                 | Info                                         |
+----+--------+--------------------+------+------------------+------+-----------------------------------------------------------------------+----------------------------------------------+
|  5 | fabric | store:39929        | NULL | Sleep            |  217 |                                                                       | NULL                                         |
|  6 | fabric | node1:37999        | NULL | Binlog Dump GTID |  217 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                         |
|  7 | fabric | node2:49750        | NULL | Binlog Dump GTID |  216 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL                                         |
| 16 | root   | localhost          | NULL | Query            |    0 | init                                                                  | show processlist                             |
| 20 | fabric | 192.168.70.1:55889 | test | Query            |    0 | User sleep                                                            | select id, sleep(0.2) from test.test limit 1 |
+----+--------+--------------------+------+------------------+------+-----------------------------------------------------------------------+----------------------------------------------+
5 rows in set (0.00 sec)
[vagrant@node3 ~]$ sudo service mysqld stop
Stopping mysqld:                                           [  OK  ]

While this happens, here’s the output from the script:

will sleep 1 second
will run query
(1, 0)
will sleep 1 second
will run query
(1, 0)
will sleep 1 second
will run query
(1, 0)
will sleep 1 second
will run query
sleeping 1 second and reconnecting
will run query
(1, 0)
will sleep 1 second
will run query
(1, 0)
will sleep 1 second
will run query
(1, 0)

The ‘sleeping 1 second and reconnecting’ line means the script got an exception while running a query (when the PRIMARY node was stopped, waited one second and then reconnected. The next lines confirm that everything went back to normal after the reconnection. The relevant piece of code that handles the reconnection is this:

fcnx = mysql.connector.connect(**config)
        fcnx.set_property(group='mycluster', mode=fabric.MODE_READWRITE)
        fcnx.reset_cache()

If fcnx.reset_cache() is not invoked, then the driver won’t connect to the xml-rpc server again, but will use it’s local cache of the group’s status instead. As the PRIMARY node is offline, this will cause the reconnect attempt to fail. By reseting the cache, we’re forcing the driver to connect to the xml-rpc server and fetch up to date group status information. If more failures happen and there is no PRIMARY (or candidate for promotion) node in the group, the following error is received:

will run query
(1, 0)
will sleep 1 second
will run query
sleeping 1 second and reconnecting
will run query
Traceback (most recent call last):
  File "./reader_test.py", line 34, in
    cur = fcnx.cursor()
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1062, in cursor
    self._connect()
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1012, in _connect
    exc))
mysql.connector.errors.InterfaceError: Error getting connection: No MySQL server available for group 'mycluster'

Running without MySQL Fabric

As we have discussed in previous posts, the XML-PRC server can become a single point of failure under certain circumstances. Specifically, there are at least two problem scenarios when this server is down:

  • When a node goes down
  • When new connection attempts are made

The first case is obvious enough. If MySQL Fabric is not running and a node fails, there won’t be any action, and clients will get an error whenever they send a query to the failed node. This is worse if the PRIMARY fails, as failover won’t happen and the cluster will be unavailable for writes. The second case means that while MySQL Fabric is not running, no new connections to the group can be established. This is because when connecting to a group, MySQL Fabric-aware clients first connect to the XML-RPC server to get a list of nodes and roles, and only then use their local cache for decisions. A way to mitigate this is to use connection pooling, which reduces the need for establishing new connections, and therefore minimises the chance of failure due to MySQL Fabric being down. This, of course, is assuming that something is monitoring MySQL Fabric ensuring some host provides the XML-PRC service. If that is not the case, failure will be delayed, but it will eventually happen anyway. Here is an example of what happens when MySQL Fabric is down and the PRIMARY node goes down:

Traceback (most recent call last):
  File "./reader_test.py", line 35, in
    cur.execute("select id, sleep(0.2) from test.test limit 1")
  File "/Library/Python/2.7/site-packages/mysql/connector/cursor.py", line 491, in execute
    self._handle_result(self._connection.cmd_query(stmt))
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1144, in cmd_query
    self.handle_mysql_error(exc)
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 1099, in handle_mysql_error
    self.reset_cache()
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 832, in reset_cache
    self._fabric.reset_cache(group=group)
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 369, in reset_cache
    self.get_group_servers(group, use_cache=False)
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 478, in get_group_servers
    inst = self.get_instance()
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 390, in get_instance
    if not inst.is_connected:
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 772, in is_connected
    self._proxy._some_nonexisting_method()  # pylint: disable=W0212
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/xmlrpclib.py", line 1224, in __call__
    return self.__send(self.__name, args)
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/xmlrpclib.py", line 1578, in __request
    verbose=self.__verbose
  File "/Library/Python/2.7/site-packages/mysql/connector/fabric/connection.py", line 272, in request
    raise InterfaceError("Connection with Fabric failed: " + msg)
mysql.connector.errors.InterfaceError: Connection with Fabric failed:

This happens when a new connection attempt is made after resetting the local cache.

Making sure MySQL Fabric stays up

As of this writing, it is the user’s responsibility to make sure MySQL Fabric is up and running. This means you can use whatever you feel comfortable with in terms of HA, like Pacemaker. While it does add some complexity to the setup, the XML-RPC server is very simple to manage and so a simple resource manager should work. For the backend, MySQL Fabric is storage engine agnostic, so an easy way to resolve this could be to use a small MySQL Cluster set up to ensure the backend is available. MySQL’s team blogged about such a set up here. We think the ndb approach is probably the simplest for providing HA at the MySQL Fabric store level, but believe that MySQL Fabric itself should provide or make it easy to achieve HA at the XML-RPC server level. If ndb is used as store, this means any node can take a write, which in turns means multiple XML-PRC instances should be able to write to the store concurrently. This means that in theory, improving this could be as easy as allowing Fabric-aware drivers to get a list of Fabric servers instead of a single IP and port to connect to.

What’s next

In the past two posts, we’ve presented MySQL Fabric’s HA features, seen how it handles failures at the node level, how to use MySQL databases with a MySQL Fabric-aware driver, and what remains unresolved for now. In our next post, we’ll review MySQL Fabric’s Sharding features.

The post High Availability with MySQL Fabric: Part II appeared first on MySQL Performance Blog.

May
29
2014
--

Philz Coffee Drops Euclid Analytics Over Privacy Concerns

philz cupz In the conflict between advocates of consumer privacy and those promoting the benefits of wide-reaching data collection, it’s not often we hear about the former getting a win. Yet it appears that’s exactly what happened in San Francisco last night. Philz Coffee, the Bay Area coffee shop mainstay, announced that it would no longer collect the information of customers and passersby… Read More

May
29
2014
--

Microsoft And Salesforce Announce Broad Product-Integration Partnership

Screen Shot 2014-05-29 at 1.32.27 PM This afternoon, Microsoft and Salesforce announced a broad partnership that will bring their respective software and service products closer together. Salesforce will support Windows, Windows 8.1, build “interoperability Salesforce and Office 365,” Microsoft’s subscription-based productivity suite, and integrate OneDrive for Business, SharePoint Online, And Outlook in… Read More

May
29
2014
--

In A Bid To Be The MS Word Of The Mobile Generation, Quip Adds Better Search, URL Links

1-Quip-Publish-Link Quip, the mobile-friendly word processing app co-founded by the ex-CTO of Facebook Bret Taylor and ex-Googler Kevin Gibbs, has picked up some 5,000 companies as users since it launched last August, with the list of customers ranging from tech early adopters like Taylor’s alma mater Facebook (where there are “thousands” of users, Taylor tells me), New Relic and Taser… Read More

May
29
2014
--

Trifacta Raises $25 Million For Its Data Transformation Software

8031897271_9c63e48a29_b While companies spend trillions of dollars on big data technologies to compile, store, and deliver the volumes of data they’re collecting and the software and services that they have to make sense of that data once it’s collected, there’s a divide that separates the two parts of the process. San Francisco, Calif.-based Trifacta is selling a software that purports to bridge… Read More

May
29
2014
--

ExtraHop Raises $41M Series C Round For Its Real-Time IT Analytics Service

UseCase_nagle_delay_slide2_useCaseMarquee Seattle-based ExtraHop, a service that offers real-time analytics based on analyzing data right off the wire instead of using software that runs on the server, today announced that it has raised a $41 million Series C round led by Technology Crossover Ventures (TCV). While TCV may not be a household name, the company has previously invested in the likes of Zillor, Expedia, Splunk and Spotify. Read More

May
29
2014
--

How MySQL ‘queries’ and ‘questions’ are measured

MySQL has status variables “questions” and “queries” which are rather close but also a bit different, making it confusing for many people. The manual describing it might not be very easy to understand:

Queries
The number of statements executed by the server. This variable includes statements executed within stored programs, unlike the Questions variable. It does not count COM_PING or COM_STATISTICS commands.
 Questions
The number of statements executed by the server. This includes only statements sent to the server by clients and not statements executed within stored programs, unlike the Queries variable. This variable does not count COM_PING, COM_STATISTICS, COM_STMT_PREPARE, COM_STMT_CLOSE, or COM_STMT_RESET commands.

In a nutshell if you’re not using prepared statements the big difference between those is what “Questions” would count stored procedure calls as a single statement where “Queries” will count all statements called inside of stored procedures as well.

There seems to be also more subtle difference between them:

Running on MySQL 5.6.17 having created a new connection I see…

mysql> show  status like "questions";
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Questions     | 2     |
+---------------+-------+
1 row in set (0.00 sec)
mysql> show  status like "queries";
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Queries       | 21241470 |
+---------------+----------+
1 row in set (0.00 sec)

Which tells me what “queries” is a global status variable while “questions” is a session and can be used to see how many statements were issued to the server through the current connection.

There is also a global questions variable that shows the number for a server since the start:

mysql> show  global status like "questions";
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| Questions     | 23375398 |
+---------------+----------+
1 row in set (0.00 sec)

When it comes to global values neither “queries” nor “questions” are reset when FLUSH STATUS is called.

What the manual does not describe in detail though is: When exactly those counters are incremented? This might look irrelevant but really it is not, especially if you’re capturing those values with high resolution and using them to diagnose non trivial MySQL performance incidents.

If you would count queries when they start – when a spike in the amount of queries in the given second could be due to the spike in the traffic, however, and if you measure queries at the completion – spikes could also mean that some critical resource became available which allowed for many queries to complete. Think for example of table-level locks or row-level locks with Innodb as very common cause.

So what is the case with MySQL? MySQL increments this counter Before executing the query so you may well see a very uniform rate of questions when actually a lot of queries were started but were not completing quickly due to waiting on some resource.

To check for unusual numbers of queries running concurrently and struggling to complete in time looking at threads_running status variable is a great idea.

The post How MySQL ‘queries’ and ‘questions’ are measured appeared first on MySQL Performance Blog.

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