PlanetMysql.ru — информация о СУБД MySQL

Recently, got an opportunity to do some implementation using Ajax and PHP. Having previously used NetBeans IDE 6.1/6.5 for PHP, it was an obvious choice to come back to NetBeans IDE again.

Although, I am yet to download the latest version, I am currently using 6.8 and I enjoyed every bit of it, while playing with CSS and jQuery for the first time, while implementing Ajax with PHP.

As I am gonna stay with PHP for sometime now, so I guess I better get my NetBeans IDE upgraded, before I miss out on any useful features.

It’s good to be back

Filed under: MySQL, NetBeans, PHP, Web
PlanetMySQL Voting: Vote UP / Vote DOWN

As I updated my website, migrating from Drupal to WordPress, a welcome news followed in the evening when Swansea beating Manchester City 1-0. The female assistant to the referee, Sian Massey, gladly judging Micah Richards for an offside. United had a rough time taking over WBA, but ensuring a relatively comfortable 2-0.

PlanetMySQL Voting: Vote UP / Vote DOWN

Quoting from Vagrant’s web site:

Vagrant is a tool for building and distributing virtualized development environments. By providing automated creation and provisioning of virtual machines using Oracle’s VirtualBox, Vagrant provides the tools to create and configure lightweight, reproducible, and portable virtual environments.

A complementary technology called Veewee makes building VirtualBox VMs easier by automating away a lot of manual steps. Marius Ducea has a great blog post on how to use it.

My observations:
1. According to Vagrant’s web site, it should work on Windows. I’ve tried that with unsatisficatory results. I think it works better on Linux and Mac;
2. Vagrant 0.9 was released recently, and is the version that I am using. However, 0.9 broke some backward compatibility such that a lot of examples on Vagrant configuration file in its web site no longer works. But it does provide good cenough error message for you to fix and adjust;
3. Just a couple of weeks ago, Veewee was not compatible with Vagrant 0.9, but that has since been fixed. I mention this because things appear to be a bit fluid, and you may need to roll up your sleeves and tweak a few things yourself to get what you want;
4. Using Veewee to build Windows VM is not as smooth as doing the same for Linux VMs. In fact a few of the templates do not have the Cygwin setup file that definition.rb refers to. Another drawback is that the initial VM harddisk size is too small (10 gig I believe). Darn it, I forgot where I changed the setting to make it bigger before building it. Share that with me if you know please
5. In addition, it seems hard to build a Multi-VM environment with Windows in the mix. It looks possible, though. Once again please share that with me if you know!

Use cases:
1. Hacking, learning, and development. It provides a safe environment to play around, build and compile things, learn new technologies, etc. For example, I’ve used it to test and play with Tengine and Nginx. I’ve also built a Multi-VM environment where I tested MariaDB 5.3 and the new Percona Tools, pt-table-checksum and pt-table-sync in particular. I think Vagrant is a great tool to learn Chef, Puppet, and Hadoop;
2. Testing: testing software on different distros, continuous integration testing for server software (web server such as Tengine, database server such as MariaDB and Percona Server). In fact, using a Centos 6.2 VM, I found Percona’s 5.5 RPM packages still haven’t resolved header file conflicts, an old problem.

Sample Multi-VM Vagrant file
It is really convinient to have a Multi-VM environment for development and testing. Vagrant can do that but there is a shortage of good samples, especially for Vagrant 0.9. Here is a sample that worked for me. Note that:
1. The VMs in the environment can be made from the same image. In the sample below, “debian” and “xiaosaier”‘s daddy is “debian6″. No joke intended;
2. Use “vagrant ssh debian”, “vagrant ssh centos”, and “vagrant ssh xiaosaier” to get to each VM. Each VM can talk to each other via the IP address defined. Therefore it can be used for good, realistic testing. After having good Chef recipe or Puppet files, the VMs can be made in a certain way, which can be used for Continuous Integration Testing.
3. The hostonly network IP address works very well. However, the VM takes the name of the image it is based on by default. It will be nice to be able to customize the VMs’ names. Let me know if you know how;

Vagrant::Config.run do |config|

config.vm.define :debian do |debian_config|
debian_config.vm.box = "debian6"
debian_config.vm.network :hostonly, "33.33.33.10"
end

config.vm.define :centos do |centos_config|
centos_config.vm.box = "centos62"
centos_config.vm.network :hostonly, "33.33.33.11"
end

config.vm.define iaosaier do |xiaosaier_config|
xiaosaier_config.vm.box = "debian6"
xiaosaier_config.vm.network :hostonly, "33.33.33.12"
end

PS. Note, the combination of colon and letter x turns into an emotion icon in the web page. Watch out!

PlanetMySQL Voting: Vote UP / Vote DOWN

XKCD (as usual) makes a very good point – this time about password strength, and I reckon it’s something app developers need to consider urgently. Geeks can debate the exact amount of entropy, but that’s not really the issue: insisting on mixed upper/lower and/or non-alpha and/or numerical components to a user password does not really improve security, and definitely makes life more difficult for users.

So basically, the functions that do a “is this a strong password” should seriously reconsider their approach, particularly if they’re used to have the app decide whether to accept the password as “good enough” at all.

PlanetMySQL Voting: Vote UP / Vote DOWN

Supporting successful services on the web means scaling your back-end databases across multiple dimensions. This blog focuses on scaling access methods to your data using SQL and/or NoSQL interfaces.

In Part 1 of the blog series , I discussed scaling database performance using auto-sharding and active/active geographic replication in MySQL Cluster to enable applications to scale both within and across data centers.  

In Part 2, I discussed the need to scale operational agility to keep pace with demand, which includes being able to add capacity and performance to the database, and to evolve the schema – all without downtime.

So in this blog I want to explore another dimension to scalability -  how multiple interfaces can be used to scale access to the database, enabling users to simultaneously serve multiple applications, each with distinct access requirements.

Data Access Interfaces to MySQL Cluster

MySQL Cluster automatically shards tables across pools of commodity data nodes, rather than store those tables in a single MySQL Server. It is therefore able to present multiple interfaces to the database, giving developers a choice between:

- S    -  SQL for complex reporting-type queries;

- S    -  Simple Key/Value interfaces bypassing the SQL layer for blazing fast reads & writes;

- S    -  Real-time interfaces for micro-second latency, again bypassing the SQL layer

With this choice of interfaces, developers are free to work in their own preferred environments, enhancing productivity and agility and enabling them to innovate faster.

SQL or NoSQL - Selecting the Right Interface

The following chart shows all of the access methods available to the database. The native API for MySQL Cluster is the C++ based NDB API. All other interfaces access the data through the NDB API.

At the extreme right hand side of the chart, an application has embedded the NDB API library enabling it to make native C++ calls to the database, and therefore delivering the lowest possible latency.

On the extreme left hand side of the chart, MySQL presents a standard SQL interface to the data nodes, and provides connectivity to all of the standard MySQL connectors including:

- Common web development languages and frameworks, i.e. PHP, Perl, Python, Ruby, Ruby on Rails, Spring, Django, etc;

- JDBC (for additional connectivity into ORMs including EclipseLink, Hibernate, etc)

- .NET

- ODBC

Whichever API is chosen for an application, it is important to emphasize that all of these SQL and NoSQL access methods can be used simultaneously, across the same data set, to provide the ultimate in developer flexibility. Therefore, MySQL Cluster maybe supporting any combination of the following services, in real-time:

- Relational queries using the SQL API;

- Key/Value-based web services using the REST/JSON and memcached APIs;

- Enterprise applications with the ClusterJ and JPA APIs;

- Real-time web services (i.e. presence and location based) using the NDB API.

The following figure aims to summarize the capabilities and use-cases for each API.

Schema-less Data Store with the memcached API

As part of the MySQL Cluster 7.2 Development Milestone Release , Oracle announced the preview of native memcached Key/Value API support for MySQL Cluster enabling direct access to the database from the memcached API without passing through the SQL layer. You can read more about the implementation and how to get going with it in this excellent post from Andrew Morgan.

The following image shows the implementation of the memcached API for MySQL Cluster 

Implementation is simple - the application sends read and write requests to the memcached process (using the standard memcached API). This in turn invokes the Memcached Driver for NDB (which is part of the same process), which in turn calls the NDB API for very quick access to the data held in MySQL Cluster’s data nodes.

The solution has been designed to be very flexible, allowing the application architect to find a configuration that best fits their needs. It is possible to co-locate the memcached API in either the data nodes or application nodes, or alternatively within a dedicated memcached layer.

The benefit of this approach is that users can configure behavior on a per-key-prefix basis (through tables in MySQL Cluster) and the application doesn’t have to care – it just uses the memcached API and relies on the software to store data in the right place(s) and to keep everything synchronized.

By default, every Key / Value is written to the same table with each Key / Value pair stored in a single row – thus allowing schema-less data storage. Alternatively, the developer can define a key-prefix so that each value is linked to a pre-defined column in a specific table.

Of course if the application needs to access the same data through SQL then developers can map key prefixes to existing table columns, enabling Memcached access to schema-structured data already stored in MySQL Cluster.

Summary

MySQL Cluster provides developers and architects with a huge amount of flexibility in accessing their persistent data stores - a reflection that one size no longer fits all in the world of web services and databases.

You can learn more about this, and the other dimensions to scaling web databases in our new Guide. 

As ever, let me know your thoughts in the comments below. 

PlanetMySQL Voting: Vote UP / Vote DOWN

In my previous post, I discussed scaling web database performance in MySQL Cluster using auto-sharding and active/active geographic replication - enabling users to scale both within and across data centers.  

I also mentioned that while scaling write-performance of any web service is critical, it is only 1 of multiple dimensions to scalability, which include:

- The need to scale operational agility to keep pace with demand. This means being able to add capacity and performance to the database, and to evolve the schema – all without downtime;

- The need to scale queries by having flexibility in the APIs used to access the database – including SQL and NoSQL interfaces;

- The need to scale the database while maintaining continuous availability.

All of these subjects are discussed in more detail in our new Scaling Web Databases guide.

In this posting, we look at scaling operational agility. 

As a web service gains in popularity it is important to be able to evolve the underlying infrastructure seamlessly, without incurring downtime and without having to add lots of additional DBA or developer resource.

Users may need to increase the capacity and performance of the database; enhance their application (and therefore their database schema) to deliver new capabilities and upgrade their underlying platforms.

MySQL Cluster can perform all of these operations and more on-line – without interrupting service to the application or clients.  

On-Line, On-Demand Scaling

MySQL Cluster allows users to scale both database performance and capacity by adding Application and Data Nodes on-line, enabling users to start with small clusters and then scale them on-demand, without downtime, as a service grows. Scaling could be the result of more users, new application functionality or more applications needing to share the database.

In the following example, the cluster on the left is configured with two application and data nodes and a single management server.  As the service grows, the users are able to scale the database and add management redundancy – all of which can be performed as an online operation.  An added advantage of scaling the Application Nodes is that they provide elasticity in scaling, so can be scaled back down if demand to the database decreases.

When new data nodes and node groups are added, the existing nodes in the cluster initiate a rolling restart to reconfigure for the new resource.  This rolling restart ensures that the cluster remains operational during the addition of new nodes.  Tables are then repartitioned and redundant rows are deleted with the OPTIMIZE TABLE command.  All of these operations are transactional, ensuring that a node failure during the add-node process will not corrupt the database.

The operations can be performed manually from the command line or automated with MySQL Cluster Manager , part of the commercial MySQL Cluster Carrier Grade Edition.

On-Line Cluster Maintenance

With its shared-nothing architecture, it is possible to avoid database outages by using rolling restarts to not only add but also upgrade nodes within the cluster.  Using this approach, users can:

- Upgrade or patch the underlying hardware and operating system;

- Upgrade or patch MySQL Cluster, with full online upgrades between releases.

MySQL Cluster supports on-line, non-blocking backups, ensuring service interruptions are again avoided during this critical database maintenance task.  Users are able to exercise fine-grained control when restoring a MySQL Cluster from backup using ndb_restore. Users can restore only specified tables or databases, or exclude specific tables or databases from being restored, using ndb_restore options --include-tables, --include-databases, --exclude-tables, and --exclude-databases.

On-Line Schema Evolution

As services evolve, developers often want to add new functionality, which in many instances may demand updating the database schema.  

This operation can be very disruptive for many databases, with ALTER TABLE commands taking the database offline for the duration of the operation.  When users have large tables with many millions of rows, downtime can stretch into hours or even days.

MySQL Cluster supports on-line schema changes, enabling users to add new columns and tables and add and remove indexes – all while continuing to serve read and write requests, and without affecting response times.

Unlike other on-line schema update solutions, MySQL Cluster does not need to create temporary tables, therefore avoiding the user having to provision double the usual memory or disk space in order to complete the operation.

Summary

So in addition to scaling write performance, MySQL Cluster can also scale operational agility.  I'll post more on scaling of data access methods and availability levels over the next few weeks.

You can read more about all of these capabilities in the new Scaling Web Databases guide.  

And of course, you can try MySQL Cluster out for yourself - its available under the GPL:

The GA release is 7.1 which can be downloaded here, but I'd recommend taking a look at the latest Development Milestone Release for MySQL Cluster 7.2 which has some great new capabilities (localized JOIN operations, simpler provisioning, etc) which can be downloaded from here (select the Development Releases tab).

As ever, let me know if there are other dimensions of scalability that I should be discussing 

PlanetMySQL Voting: Vote UP / Vote DOWN

I did a clean install on my Macbook Pro this morning, part of the clean install means, re-downloading things like MySQL. When I installed it, I noticed that the pref-pane wouldn’t start MySQL. Even when it asked for my admin password. I thought maybe it was a boot up issue, so I rebooted, and got an error telling me that the permissions for the start up item were in secure. Here’s what I did to debug/fix the problem.

• Change permission of /Library/StartupItems/MySQLCOM/MySQLCOM
• edit /usr/local/mysql/support-files/mysql.server and edited line 46 and 47 to say

There is already a bug filed for this..
PlanetMySQL Voting: Vote UP / Vote DOWN

Revision 208 of mycheckpoint, a MySQL monitoring solution, has been released. New and updated in this revision:

• Aggregation tables: aggregated data makes for fast reports on previously slow queries.
• Enhanced charting: interactive charts now present time stamps dynamically (see demo); “Zoom in” charts are available (see demo) on mycheckpoint‘s HTTP server.
• RPM distribution: a “noarch” RPM mycheckpoint build is now available.
• Initial work on formalizing test environment

mycheckpoint celebrates one year of existence!

Aggregation tables

I really wanted to avoid using these: everything was so more beautiful with one single dataset and dozens of supporting views (OK, the views themselves are hardly “beautiful”).

However it was impossible (for my level of expertise) to optimize query performance what with all those views on per-hour and per-day aggregation. The GROUP BYs and the JOINs did not make it possible for condition pushdown (i.e. using MERGE algorithm) where desired.

As result, mycheckpoint now manages aggregation tables: per-hour and per-day. The impact on sample taking is neglect able (making for two additional fast queries), but the impact on reading aggregated data is overwhelming. Generating a HTML full report could take a few minutes to complete. It now returns in no time. This makes charting more attractive, and allows for enhanced charting, such as zooming in on charts, as described following.

Aggregation tables will automatically be created and retroactively populated upon using revision 208. There’s nothing special to do; be advised that for one single execution of mycheckpoint, many INSERT queries are going to be executed. Shouldn’t take more than a couple minutes on commodity hardware and a few months of history.

It is possible to disable aggregation tables, or make for a complete rebuild of tables; by default, though, aggregation is ON.

Enhanced charting

Two enhancements here:

1. The interactive line charts already know how to update legend data as mouse hovers over them. Now they also present accurate date & time. This provides with fully informative charts.
2. As with other monitoring tools, it is possible to “zoom in” on a chart: zooming in will present any chart in “last 24 hours”, “last 10 days” and “complete history” views, magnified on screen. See demo here.

RPM distribution

No excuse for this being so late, I know. But RPM distribution is now available. Yeepee!

This is a noarch distribution, courtesy of Python’s distutils; you should be able to install the package on any RPM supporting platform. I have only tested in on CentOS; feedback is welcome.

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.

Umm, I’ll repeat this last one: mycheckpoint is released under the New BSD License. Still, and will continue to be. Thanks for the good advice by Lenz, Domas and others.

PlanetMySQL Voting: Vote UP / Vote DOWN

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.

PlanetMySQL Voting: Vote UP / Vote DOWN

Mat Keep and I will be presenting this free webinar on Wednesday 14 July.

The MEMORY storage engine has been widely adopted by MySQL users to provide near-instant responsiveness with use cases such as caching and web session management. As these services evolve to support more users, so the scalability and availability demands can start to exceed the capabilities of the MEMORY storage engine.

The MySQL Cluster database, which itself can be implemented as a MySQL storage engine, is a viable alternative to address these evolving web service demands. MySQL Cluster can be configured and run in the same way as the MEMORY storage engine (ie on a single host with no replication and no persistence). As web services evolve, any of these attributes can then be added in any combination to deliver higher levels of scalability, availability and database functionality, especially for those workloads which predominately access data by the primary key.

The MEMORY storage engine has been widely adopted by MySQL users to provide near-instant responsiveness with use cases such as caching and web session management. As these services evolve to support more users, so the scalability and availability demands can start to exceed the capabilities of the MEMORY storage engine.

The MySQL Cluster database, which itself can be implemented as a MySQL storage engine, is a viable alternative to address these evolving web service demands. MySQL Cluster can be configured and run in the same way as the MEMORY storage engine (ie on a single host with no replication and no persistence). As web services evolve, any of these attributes can then be added in any combination to deliver higher levels of scalability, availability and database functionality, especially for those workloads which predominately access data by the primary key.

As always, the webinar is free of charge but you will need to register here.

Time:

• Wed, Jul 14: 06:00 Hawaii time
• Wed, Jul 14:  09:00 Pacific time (America)
• Wed, Jul 14: 10:00 Mountain time (America)
• Wed, Jul 14: 11:00 Central time (America)
• Wed, Jul 14: 12:00 Eastern time (America)
• Wed, Jul 14: 16:00 UTC
• Wed, Jul 14: 17:00 Western European time
• Wed, Jul 14: 18:00 Central European time
• Wed, Jul 14: 19:00 Eastern European time

If you can’t make the live webinar then register anyway and you’ll get sent a link to the recording after the event.

PlanetMySQL Voting: Vote UP / Vote DOWN