-
dbbenchmark.com – MySQL (basic) connection pool support added
In this latest release I’ve added a basic MySQL connection pool to the benchmarking script which improves the method in which connections to MySQL are handled and reused. In addition, there have been some optimizations made to the thread handler functions for better debug reporting. Download the latest release now and see how your MySQL server performs against the rest of the community! Download here: download page.
-
2011 MySQL Conferences
Next year will mark a significant change for the MySQL community. At least three major conferences will have dedicated MySQL content that is great for attendees getting the best information on how to use MySQL from the experts in the field.
O’Reilly MySQL Conference & Expo
The 9th Annual MySQL conference will be held at is usual home of recent years. Colin will again be back as committee chair for a 3rd year and this will be my 6th straight MySQL conference.
Date: April 11 – 14, 2011
Location: Hyatt Regency, Santa Clara, California
Website: There is no website at this time
Call for Papers: There are no details for call for papers
Program Chair: Colin Charles from Monty Program AB.
Collaborate 11
Collaborate is a larger conference (4,000-5,000 attendees) that is actually three separate conferences in one run by the IOUG, OAUG and Quest. The IOUG content is generally a focus for Oracle DBA’s. Last year marked the first year with any MySQL sessions, and this year Collaborate will have dedicated MySQL tracks chaired by fellow ACE director Sheeri Cabral who is well known for her work in the MySQL community.
Date: April 10 – 14, 2011
Location: Orange County Convention Center West, Orlando, Florida
Website: http://collaborate11.ioug.org/
Call for Papers: Now open. Closes Friday October 1, 2010
Program Chair: Sheeri Cabral
KScope 11
ODTUG Kaleidoscope (Kscope for short) is a conference (1500 attendees) that is very focused on delivering the best content from the top community contributors for the communities benefit. 2010 was my first Kaleidoscope conference and I felt completely at home. Great people, great events and the best conference food I’ve had in many years.
With a dedicated MySQL track in 2010 for the first time I will again be the MySQL Program Chair in 2011 with an extended format for the MySQL developer and DBA. The focus will be the best way to develop successful applications with MySQL and will include Architecture, Performance Tuning, Best Practices, Case Studies and Hands-On streams.
Date: June 26 – 30, 2011
Website: http://kscope11.com
Location: Long Beach, California
Call for Papers: Closes Tuesday October 26, 2010
Program Chair: Ronald Bradford – Independent Consultant
Recap
2010 is also not over. MySQL Sunday at OOW promises to be a great event in San Francisco in under 2 weeks. You can still register at a very cheap price of $75 for 4 dedicated tracks of MySQL content. Open SQL Camp being organized also by Sheeri in Boston in October will continue the tradition of a small but focused and free event for the MySQL community.
-
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.
|
News Feeds 
