Use Cacti to monitor MongoDB or Redis activity

If you don’t know yet Cacti, you need to have a look at this great open source solution: www.cacti.net

It’s a complete network graphing solution designed to harness the power of RRDTool’s data storage and graphing functionality.

Its came along with great community and numerous plugin, including few NoSQL monitoring template: Redis and MongoDB

The top two items are current connections at the time the poller sampled the data; the bottom line, Total Connections, is the number of new connections created per second during the polling interval. That’s the important number to watch, and despite the name it should not be equal to the sum of the first two lines!

 

 

 

 

 

 

 

 

 

MySQL Cluster 7.2 released

MySQL Cluster 7.2 (DMR2): NoSQL, Key/Value, Memcached

During Oracle Open World, they announced  the second Development Milestone Release (7.2.1)

New features include and improvements include the following

  • 70x Higher Performance with Adaptive Query Localization (AQL)
  • New NoSQL Interface and Schema-less Storage with the memcached API
  • Integration with MySQL 5.5
    • MySQL Cluster 7.2.1 is integrated with MySQL Server 5.5, providing binary compatibility to existing MySQL Server deployments.
    • Users can now fully exploit the latest capabilities of both the InnoDB and MySQL Cluster storage engines within a single application.
  • Enhancing Cross Data Center Scalability: Simplified Active / Active Replication
  • Enhancing Cross Data Center Scalability: Multi-Site Clustering
  • User Privilege Consolidation

 

You can download the DMR for evaluation now from: http://dev.mysql.com/downloads/cluster/ (select Development Milestone Release tab).

You can also read up on the detail of each of these features in the new article posted at the MySQL Developer Zone. In this blog, I’ll summarize the main parts of the announcement.

 

MySQL 5.6 new features unveiled

From an Oracle press release today, mysql 5.6 new features have been made available for community to test.

From the press release:

These latest open source features are focused on better scalability, performance and flexibility.
Enhancements include the InnoDB storage engine, to provide support for full text search, greater performance for write-intensive applications, and better scalability under high concurrency/user loads.
New and improved MySQL replication features help with performance, scale-out and data integration.
Oracle is providing early access to MySQL 5.6 features which are in active development, to enable customers and the MySQL community to provide feedback. After development is complete, these features are intended to move into future Development Milestone and General Availability Releases.

Find out more information on:

MySQL 5.1.57 has been released

Changes in MySQL 5.1.57

    Functionality added or changed:

      * When invoked with the --auto-generate-sql option, mysqlslap
        dropped the schema specified with the --create-schema option
        at the end of the test run, which may have been unexpected by
        the user. mysqlslap no longer drops the schema, but has a new
        --create-and-drop schema that both creates and drops a schema.
        (Bug #58090, Bug #11765157)

      * A new system variable, max_long_data_size, now controls the
        maximum size of parameter values that can be sent with the
        mysql_stmt_send_long_data() C API function. If not set at
        server startup, the default is the value of the
        max_allowed_packet system variable. This variable is
        deprecated. In MySQL 5.6, it is removed and the maximum
        parameter size is controlled by max_allowed_packet.

    Bugs fixed:

      * InnoDB Storage Engine: Replication: Trying to update a column,
        previously set to NULL, of an InnoDB table with no primary key
        caused replication to fail with Can't find record in 'table'
        on the slave. (Bug #11766865, Bug #60091)

      * InnoDB Storage Engine: The server could halt if InnoDB
        interpreted a very heavy I/O load for 15 minutes or more as an
        indication that the server was hung. This change fixes the
        logic that measures how long InnoDB threads were waiting,
        which formerly could produce false positives. (Bug #11877216,
        Bug #11755413, Bug #47183)

      * Replication: Using the --server-id option with mysqlbinlog
        could cause format description log events to be filtered out
        of the binary log, leaving mysqlbinlog unable to read the
        remainder of the log. Now such events are always read without
        regard to the value of this option.
        As part of the fix for this problem, mysqlbinlog now also
        reads rotate log events without regard to the value of
        --server-id. (Bug #11766427, Bug #59530)

      * Partitioning: A problem with a previous fix for poor
        performance of INSERT ON DUPLICATE KEY UPDATE statements on
        tables having many partitions caused the handler function for
        reading a row from a specific index to fail to store the ID of
        the partition last used. This caused some statements to fail
        with Can't find record errors. (Bug #59297, Bug #11766232)

      * InnoDB invoked some zlib functions without proper
        initialization. (Bug #11849231)

      * Selecting from a view for which the definition included a
        HAVING clause failed with an error:
1356: View '...' references invalid table(s) or column(s)
or function(s) or definer/invoker of view lack rights to use them
        (Bug #60295, Bug #11829681)

      * The server permitted max_allowed_packet to be set lower than
        net_buffer_length, which does not make sense because
        max_allowed_packet is the upper limit on net_buffer_length
        values. Now a warning occurs and the value remains unchanged.
        (Bug #59959, Bug #11766769)

      * The server read one byte too many when trying to process an
        XML string lacking a closing quote (') or double quote (")
        character used as an argument for UpdateXML() or
        ExtractValue(). (Bug #59901, Bug #11766725)
        See also Bug #44332, Bug #11752979.

      * Attempting to create a spatial index on a CHAR column longer
        than 31 bytes led to an assertion failure if the server was
        compiled with safemutex support. (Bug #59888, Bug #11766714)

      * Aggregation followed by a subquery could produce an incorrect
        result. (Bug #59839, Bug #11766675)

      * An incorrect character set pointer passed to
        my_strtoll10_mb2() caused an assertion to be raised. (Bug
        #59648, Bug #11766519)

      * A missing variable initialization for Item_func_set_user_var
        objects could cause an assertion to be raised. (Bug #59527,
        Bug #11766424)

      * mysqldump did not quote database names in ALTER DATABASE
        statements in its output, which could cause an error at reload
        time for database names containing a dash. (Bug #59398, Bug
        #11766310)

      * In Item_func_month::val_str(), a Valgrind warning for a
        too-late NULL value check was corrected. (Bug #59166, Bug
        #11766126)

      * In Item::get_date, a Valgrind warning for a missing NULL value
        check was corrected. (Bug #59164, Bug #11766124)

      * In extract_date_time(), a Valgrind warning for a missing
        end-of-string check was corrected. (Bug #59151, Bug #11766112)

      * In string context, the MIN() and MAX() functions did not take
        into account the unsignedness of a BIGINT UNSIGNED argument.
        (Bug #59132, Bug #11766094)

      * In Item_func::val_decimal, a Valgrind warning for a missing
        NULL value check was corrected. (Bug #59125, Bug #11766087)

      * In Item_func_str_to_date::val_str, a Valgrind warning for an
        uninitialized variable was corrected. (Bug #58154, Bug
        #11765216)

      * The code for PROCEDURE ANALYSE() had a missing DBUG_RETURN
        statement, which could cause a server crash in debug builds.
        (Bug #58140, Bug #11765202)

      * An assertion could be raised in
        Item_func_int_val::fix_num_length_and_dec() due to overflow
        for geometry functions. (Bug #57900, Bug #11764994)

      * An assertion could be raised if a statement that required a
        name lock on a table (for example, DROP TRIGGER) executed
        concurrently with an INFORMATION_SCHEMA query that also used
        the table. (Bug #56541, Bug #11763784)

      * For a client connected using SSL, the Ssl_cipher_list status
        variable was empty and did not show the possible cipher types.
        (Bug #52596, Bug #11760210)

      * With lower_case_table_names=2, resolution of objects qualified
        by database names could fail. (Bug #50924, Bug #11758687)

      * A potential invalid memory access discovered by Valgrind was
        fixed. (Bug #48053, Bug #11756169)

      * Bitmap functions used in one thread could change bitmaps used
        by other threads, causing an assertion to be raised. (Bug
        #43152, Bug #11752069)

      * SHOW EVENTS did not always show events from the correct
        database. (Bug #41907, Bug #11751148)

 

MySQL 5.6.2 bring new NoSQL Interface via memcached

Architects and strategists can start looking ahead to the exciting new thing with SQL in its name: MySQL 5.6, builds on the momentum of 5.5, and Oracle’s investment and commitment to MySQL, by delivering better performance and scalability. Trying to make the bridge between relationnal SQL and NoSQL movement.

This release focused on:

  • Optimizer improvements for all-around query performance.
  • InnoDB improvements for higher transactional throughput.
  • New NoSQL-style memcached APIs.
  • Partitioning improvements for querying and managing huge tables.
  • Replication improvements covering many aspects.
  • Better performance monitoring by expanding the data available through the PERFORMANCE_SCHEMA.

 

MySQL 5.6.2 is available for download

What’s new in MySQL 5.6.2 is available here

 

MySQL 5.5.11 released

MySQL Community Server 5.5.11 has been released
It includes several high-impact enhancements to improve the performance and scalability of the MySQL Database, taking advantage of the latest multi-CPU and multi-core hardware and operating systems. In addition, with release 5.5, InnoDB is now the default storage engine for the MySQL Database, delivering ACID transactions, referential integrity and crash recovery by default.

Announcement

Download

MySQL 5.1.56 has been released

Changes in MySQL 5.1.56:

   Functionality added or changed:

     * mysqldump --xml now displays comments from column definitions.
       (Bug #13618)

   Bugs fixed:

     * InnoDB Storage Engine: InnoDB returned values for
       "rows examined" in the query plan that were higher than
       expected. NULL values were treated in an inconsistent way. The
       inaccurate statistics could trigger "false positives" in
       combination with the MAX_JOIN_SIZE setting, because the
       queries did not really examine as many rows as reported.
       (Bug #30423)

     * Partitioning: Trying to use the same column more than once in
       the partitioning key when partitioning a table by KEY caused
       mysqld to crash. Such duplication of key columns is now
       expressly disallowed, and fails with an appropriate error.
       (Bug #53354, Bug #57924)

     * Replication: When using the statement-based logging format,
       INSERT ON DUPLICATE KEY UPDATE and INSERT IGNORE statements
       affecting transactional tables that did not fail were not
       written to the binary log if they did not insert any rows.
       (With statement-based logging, all successful statements
       should be logged, whether they do or do not cause any rows to
       be changed.) (Bug #59338)

     * Replication: Formerly, STOP SLAVE stopped the slave I/O thread
       first and then stopped the slave SQL thread; thus, it was
       possible for the I/O thread to stop after replicating only
       part of a transaction which the SQL thread was executing, in
       wich case---if the transaction could not be rolled back
       safely---the SQL thread could hang.
       Now, STOP SLAVE stops the slave SQL thread first and then
       stops the I/O thread; this guarantees that the I/O thread can
       fetch any remaining events in the transaction that the SQL
       thread is executing, so that the SQL thread can finish the
       transaction if it cannot be rolled back safely. (Bug #58546)

     * A query of the following form returned an incorrect result,
       where the values for col_name in the result set were entirely
       replaced with NULL values:
           SELECT DISTINCT col_name ... ORDER BY col_name DESC;
       (Bug #59308, Bug #11766241)

     * DELETE or UPDATE statements could fail if they used DATE or
       DATETIME values with a year, month, or day part of zero.
       (Bug #59173)

     * The ESCAPE clause for the LIKE operator allows only
       expressions that evaluate to a constant at execution time, but
       aggregrate functions were not being rejected. (Bug #59149)

     * Memory leaks detected by Valgrind, some of which could cause
       incorrect query results, were corrected. (Bug #59110, Bug
       #11766075)

     * mysqlslap failed to check for a NULL return from
       mysql_store_result() and crashed trying to process the result
       set. (Bug #59109)

     * In debug builds, SUBSTRING_INDEX(FORMAT(...), FORMAT(...))
       could cause a server crash. (Bug #58371)

     * When mysqldadmin was run with the --sleep and --count options,
       it went into an infinite loop executing the specified command.
       (Bug #58221)

     * Some string manipulating SQL functions use a shared string
       object intended to contain an immutable empty string. This
       object was used by the SQL function SUBSTRING_INDEX() to
       return an empty string when one argument was of the wrong
       datatype. If the string object was then modified by the SQL
       function INSERT(), undefined behavior ensued. (Bug #58165, Bug
       #11765225)

     * Parsing nested regular expressions could lead to recursion
       resulting in a stack overflow crash. (Bug #58026, Bug
       #11765099)

     * The mysql client went into an infinite loop if the standard
       input was a directory. (Bug #57450)

     * The expression const1 BETWEEN const2 AND field was optimized
       incorrectly and produced incorrect results.
       (Bug #57030, Bug #11764215)

     * Some RPM installation scripts used a hardcoded value for the
       data directory, which could result in a failed installation
       for users who have a nonstandard data directory location. The
       same was true for other configuration values such as the PID
       file name. (Bug #56581, Bug #11763817)

     * On FreeBSD and OpenBSD, the server incorrectly checked the
       range of the system date, causing legal values to be rejected.
       (Bug #55755, Bug #11763089)

     * When using ExtractValue() or UpdateXML(), if the XML to be
       read contained an incomplete XML comment, MySQL read beyond
       the end of the XML string when processing, leading to a crash
       of the server. (Bug #44332)

Download here:
http://dev.mysql.com/downloads/
Enjoy

 

Percona Server version 5.1.53-12.4 released

Percona Server version 5.1.53-12.4 is now available for download. It is now the current stable release version.

Percona Server with XtraDB is a backwards-compatible replacement for MySQL that is much faster and more scalable, easier to monitor and tune, and has features to make operational tasks easier.

Full release note details: http://www.percona.com/docs/wiki/percona-server:release_notes_51#release_5153-rel124

Percona Server 5.1.52-12.3 released – now both SQL and NOSQL

Percona Server 5.1.52-12.3 has been released and includes HandlerSocket.
This is third-party plugin, developed Inada Naoki, DeNA Co., Ltd and explained in Yoshinori Matsunobu’s blog post.

What is so special about it:

  • It provides NOSQL-like requests to data stored in XtraDB. So in the same time you can access your data in SQL and NOSQL ways. This is first open source solution which allows that.
  • It has persistent storage (XtraDB is persistent)
  • It handles really high load. In my tests using 2 dedicated web servers ( using perl clients) I reached 200,000 req/sec and the clients were real bottleneck, while Percona Server was busy only 5-7%. I did not have more clients in my lab to put more load, but I have no doubts we have handle 1,000,000 req/sec with 5 separate web applications. The tests were done with Percona Server installed on Cisco UCS C250 server with 12cores/24threads and 380GB of RAM .

How it can be used:

  • To provide high requests rate for simple requests like: PK-lookup, index-lookup or index-range
  • To replace caching layer (or remove it at all). With declared access rate, there is no needs to have memcached.
    This also solves cache management and data invalidation problems

 

More information about percona server available here

Facebook – Predictability is more important to scalability than performance

Quick reminder that predictability is more important to scalability than  performance.

Facebook as recently explained their strategy in this matter, and gave a very interesting MySQL Tech Talk .