Newsfeeds

Planet MySQL

Planet MySQL - http://www.planetmysql.org/

Cloud systems vs. NoSQL email with tons of questions
I had an email after my webinar on NoSQL/SQL for Oracle Development Tools User Group last week (http://www.odtug.com/apex/f?p=500:1:0) from an attendee that was chock full of some questions. I decided to answer them to clarify with this fellow NoSQL and Cloud Systems. I'm pretty happy with my answers. I'd be glad for any thoughts from people about my replies.Here are my responses (the fellow's name is also Patrick):Patrick,You are welcome! Thank you for attending. I put that together a bit hasty but thought it was a good topic to be covering as there are so many organizations that are considering such an architecture.Patrick Francois wrote:> Hi,>> Thank you for the NoSQL Webinar!> Not an easy theme. ..kind of "wide open".> I have recently also tried getting more info on NoSQL and related systems and performance issues. ...and get confused, especially also with the "cloud systems".Yeah, a lot of buzzword-BS and reinventing the wheel going on. They key to remember is that SQL is not going to be replace SQL any time soon, but the combination of SQL and NoSQL can have its advantages.>> I checked for example this document regarding benchmarking "cloud systems":> (maybe you know that document as well)> -> "BenchmarkingCloudServingSystemswithYCSB" > (YCSB -> Yahoo!CloudServingBenchmark)> -> straight link: http://www.brianfrankcooper.net/pubs/ycsb.pdf>> There they speak about : explosion of new systems for datastorage and management "in the cloud"> They mention there all these different NoSQL storage systems,> and say for example also: Some systems are offered only as cloud services, either directly in the case of Amazon SimpleDB[1]Yeah, SimpleDB being Amazon's S3, a proprietary system. You can download it and run it on your own cluster or private cloud.> ....>> That's why I'm also a bit confused about "cloud systems" and "NoSQL".Cloud systems - usually virtual machines that can be easy spun up for elasticity - I want to add more servers to my virtual network on the fly. This can be via either your own VMWare Labmanager setup or something like EC2. There are also real hardware clouds using services such as what Rackspace offers.By "cloud systems" and "NoSQL", it just means running these databases in an environment such as EC2, VMware, or Rackspace, etc... For instance, you can have relational databases in the cloud such as with MySQL, Drizzle, or etc...You can also run them NoSQL databases on your own systems. NoSQL and Cloud systems are not mutually exclusive of each other.> Basically if you speak about cloud systems, you also speak about scaling out same as you are speaking about "scaling out" when speaking about NoSQL.Scaling out can be in the cloud as well as outside the cloud. It just means running applications and databases over a several systems versus using an every increasing big huge server and growing that server to scale as was done in the Good Old Days (TM).>> So, does it imply that NoSQL systems are cloud systems?No, they can be, but are not. NoSQL means a database system that doesn't use SQL to access data, non-relational> How would you see the relation "NoSQL" / "Cloud systems".They complement each other. NoSQL works well in a cloud paradigm.>> --> Even "cloud system" meaning as such is rather unclear.> There was the question about "Cassandra server farm":> If I create an own cassandra server farm, can I then still speak about a "cloud system", or can I speak about a "cloud system" only when underlying servers are also geographically distrubuted?Geographical distribution isn't the determinant in the definition of cloud system. I could have a cloud system down in my cellar if I wanted to if I have multiple real or virtual servers.>> There is also the "security" issue when it comes to "cloud systems" and some say security is bigger because data is distributed. I understand that in that way, you cannot really get hand (or get hacked) on all the data at once, eventually just a part of the data.> But if I think on an own Cassandra farm, where eventually all machines are in the same machine room and network, I can imagine if you can get into one machine, you can get into all of them.This issue requires you to do some homework about how to secure your servers. Set up a good image with everything locked down and use something like puppet to have it come up with all the goodies you set up in that image. Define a list of must-haves before that box (virtual or real) is on the network.>> From the YCSB-document "some systems are offered only as cloud services",> I understand that for using Amazon's SimpleDB, you could not create your own server farm, rather need to buy that service from Amazon?Yeah, you're not going to run your own S3. You can run other NoSQL or distrubuted file systems - pick your choice with a Google Search.>> That is causing me confusion between "NoSQL" and "Cloud systems".NoSQL - as I defined in the presentation - schema less, often non-relational, doesn't use SQL as the Linga Franca of data access.Cloud systems - multiple boxes, real or virtual, elastic, as I mention above.Two different but mutually complementary concepts.> --> Memcached and Membased sounded very interesting. I will check more on those.Please do. And do join the mailing lists. I see Matt Ingenthron and Perry Krug answering emails every day!> --> Can you eventually also provide the slides you used?Certainly - let me make sure they care clean and I'll send them to you!
Careful how you monitor MySQL
I was recently struck by a problem which is unusual. In order to keep an eye on the database server I use nagios, cacti, merlin and some local scripts to monitor the database instance and ensure that it is working properly. That normally works fine. The different monitor processes do various things, one of which is to monitor the replication status of a slave, and warn me if the replication is not working or if it’s behind. This is done with the command SHOW SLAVE STATUS. The server I was looking at runs some large local batch jobs aggregating data. Unfortunately, I was experiencing that replication was interfering with these batch jobs so decided to see if things would perform better if I stopped replication and let the batch jobs complete, restarting replication afterwards. So the command STOP SLAVE was sent to the server, and this took some time. In the meantime SHOW SLAVE STATUS hangs. The STOP SLAVE command waits for the SQL replication thread to finish it’s task but that thread was waiting on the tables being used by the batch process, which was already running. The consequence of this was that STOP SLAVE waited, all calls to SHOW SLAVE STATUS blocked and with nagios, cacti, merlin and the local scripts all doing periodic SHOW SLAVE STATUS commands which would hang I ended up with the mysqld running out of user connections. Nasty! While I see if MySQL can do something about the behaviour of stopping the slave and showing the slave status I’m going to implement grants which limit the monitor users so they are only allowed to have a user concurrent connections open. This is done using the syntax GRANT whatever ON whereever TO some_user WITH MAX_USER_CONNECTIONS 5; 5 looks like a good number for nagios, merlin and the local scripts and 10 is probably sufficient as I collect a lot of graph data for the mysql server and the cacti requests are often done in parallel. So if you monitor MySQL it may be worth you applying some sort of connection limit on your monitoring scripts, if not on other database users.
mycheckpoint (rev. 190): HTTP server; interactive charts
Revision 190 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision: HTTP server: mycheckpoint can now act as a web server. Point your browser and start browsing through HTML reports. See mock up demo. Interactive charts: HTML line charts are now interactive, presenting with accurate data as you move over them. See sample. Enhanced auto-deploy: now auto-recognizing failed upgrades. Reduced footprint: much code taken out of the views, leading to faster loading times. Better configuration file use: now supporting all command line options in config file. Remote host monitoring accessibility: now supporting complete configurable accessibility details. Bug fixes: thanks to the bug reporters! mycheckpoint is free, simple, easy to use (now easier with HTTP server) and useful. I encourage you to try it out: even compared with other existing and emerging monitoring tools, I believe you will find it a breeze; it’s low impact and lightness appealing; it’s alerts mechanism assuring; its geeky SQL-based nature with ability to drill down to fine details — geeky-kind-of-attractive. </encouragement> HTTP server You can now run mycheckpoint in http mode: bash$ mycheckpoint http mycheckpoint will listen on port 12306, and will present you with easy browsing through the reports of your mycheckpoint databases. The http server automatically detects those schemata used by mycheckpoint, and utilizes the existing HTML views, integrating them into the greater web framework. While in http mode, mycheckpoint does nothing besides serving web pages. It does not actively exercise monitoring: you must still use the usual cron jobs or other scheduled tasks by which you invoke mycheckpoint for monitoring. The http server is directed at a single MySQL server, as with the following example: bash$ mycheckpoint --host=slave1.localdomain --port=3306 --http-port=12306 http It is assumed that this server has the monitoring schemata. See mock up demo. The demo uses presents with real output from a mycheckpoint HTTP server; I haven’t got the means to put up a live demo. Interactive charts The openark line charts, used in the HTML reports, are now interactive. As you scroll over, the legend presents you with series values. No more “I have this huge spike once every 4 hours, which reduces all other values to something that looks like zero but is actually NOT”. Hover, and see the real values. See sample. Enhanced auto-deploy The idea with mycheckpoint is that it should know how to self upgrade the schema on version upgrade (much like automatic WordPress upgrades). mycheckpoint does bookkeeping of installed versions within the database, and upgrades by simple comparison. It now, following a couple of reported bugs, also recognizes failure of partial, failed upgrades. This adds to the automation of mycheckpoint‘s installation. Reduced footprint Some of mycheckpoint‘s views are complicated, and lead to a large amount of code in view declaration. This leads to increased table definition size (large .frm files). There has been some work to reduce this size where possible. Work is still ongoing, but some 30% has been taken off already. This leads to faster table (view) load time. Better configuration file use Any argument supported on the command line is now also supported in the config style. Much like is handled with MySQL. For example, one can issue: mycheckpoint --monitored-host=sql02.mydb.com --monitored-user=monitor --monitored-password=123456 But now also: mycheckpoint With the following in /etc/mycheckpoint.cnf: [mycheckpoint] monitored_host = sql02.mydb.com monitored_user = monitor monitored_password = 123456 Rules are: If an option is specified on command line, it takes precedence over anything else. Otherwise, if it’s specified in the configuration file, value is read from file. Otherwise use default value is used. On command line, option format is xxx-yyy-zzz: words split with dash/minus character. On configuration file, option format is xxx_yyy_zzz: words split with underscore. Unlike MySQL configuration format, dashes cannot be used. If an option is specified multiple times on configuration file — well — I have the answer, but I won’t tell. Just don’t do it. It’s bad for your health. Future plans Work is going on. These are the non-scheduled future tasks I see: Monitoring InnoDB Plugin & XtraDB status. A proper man page. Anything else that interests me & the users. Try it out Try out mycheckpoint. It’s a different kind of monitoring solution. Simple monitoring (charting) is immediate. For more interesting results you will need basic SQL skills, and in return you’ll get a lot of power under your hands. Download mycheckpoint here Visit the project’s homepage Browse the documentation Report bugs mycheckpoint is released under the New BSD License.
Debugging Communication Link Failure exceptions in Connector/J
Have you seen error messages similar to the following: Communications link failure – Last packet sent to the server was X ms ago. Judging from the forums, many people have had problems with this. Here’s a brief overview of the causes, and potential solutions. Generally speaking, this error suggests that the network connection has been closed. There can be several root causes: Firewalls or routers may clamp down on idle connections (the MySQL client/server protocol doesn’t ping). The MySQL Server may be closing idle connections which exceed the wait_timeout or interactive_timeout threshold There’s a couple of useful diagnostic details which can be useful. For starters, when a recent (5.1.13) version of Connector/J is used, you should see additional details around both the last packet sent and received. Older versions may simply indicate the last time a packet was sent to the server, which is frequently zero ms ago. That’s not terribly useful, and it may be that you just sent a packet, but haven’t received a packet from the server for 12 hours. Knowing how long it’s been since Connector/J last received a packet from the server is useful information, so if you are not seeing this in your exception message, update your driver. The second useful diagnostic detail shows up when Connector/J notices that the time a packet was last sent/received exceeds the wait_timeout or interactive_timeout threshold. It will attempt to notify you of this in the exception message. The following can be helpful in avoiding such problems, but ultimately network connections can be volatile: Ensure connections are valid when checked out of connection pool (use query which starts with “/* ping */” *exactly* to execute lightweight ping instead of full query) Minimize duration a Connection object is left idle while other application logic is executed Explicitly validate Connection before using after being left idle for extended period of time Ensure wait_timeout and interactive_timeout are set sufficiently high Ensure tcpKeepalive is enabled Ensure that any configurable firewall or router timeout setting accounts for maximum expected idle connection time. I’ve seen exception messages which indicate Connections being used after sitting idle for hours – sometimes days. If you do this, make sure that you are explicitly testing the connection before using it after lengthy idle periods. Network connections fail, and applications need to be prepared to handle that. But expecting connections to survive extended periods where left idle and work magically when used again hours later is just asking for trouble.
Whoa! MyQuery 3.3.0 Beta Released at last!
After a long wait, today I release MyQuery 3.3.0. For you who haven't seen MyQuery before, this is a Windows based interactive query tool for MySQL (sorry no Mac or Linux support this time) . The emphasis is much on script editing and running, and on the needs of the DBA, more than on the end user. Also, for the database designer, there is some cool stuff in here.MyQuery supports running scripts up to a specific point in the script, or starting from a specific point and supports a kind of "edit and continue", so you can develop a script, run it til it breaks, correct the error, and then continue where you left off.That is not all, after all these releases MyQuery has gained quite a few useful features. Script editing is done with the Scintilla color coded editor for examples, and there are tools for managing database objects such as tables, views and events.Every release of MyQuery has a theme, and MyQuery 3.3.0 has a very distinct theme: Extensibility. There is now support for adding your own favoutite SQL SELECT or Scripts to a user defined tools menu. And that is not all, there are many more types of tool that you can define, from opening a web link in a browser to running code in a specified DLL, using the new MyQuery Plugin API.The main new features in summary are:The User defined tools featureThe MyQuery Plugin APIA login dialog which now supports saved login settings for different serversMySQL Cluster / NDB monitoring through User tool pluginInnoDB Lock tree monitoring through a User tool pluginSeveral bugfixesThis is still a beta version. It is hence feature complete, but I know there is a bug or two, in particular the plugin API (which is an advanced feature which required a fair amount of refactoring to work). Feedback is welcome!I will write a few more blogposts here to describe some 3.3 details, such as the InnoDB Lock tree monitor, the NDB Monitor, the Plugin API and things like this, but for now, just download the thing and enjoy! As usual it's available on Sourceforge:http://sourceforge.net/projects/myquery/files/myquery//Karlsson