CB Gallery Module

Deprecated: Function ereg() is deprecated in /home/templates/public_html/demo/mycommunity/j15/modules/mod_cbgallery/mod_cbgallery.php on line 380


CB Online

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

  • Log Buffer #416, A Carnival of the Vanities for DBAs
    This log buffer edition sprouts from the beauty, glamour and intelligence of various blog posts from Oracle, SQL Server, and MySQL. Oracle: Oracle Exadata Performance: Latest Improvements and Less Known Features Exadata Storage Index Min/Max Optimization Oracle system V shared memory indicated deleted 12c Parallel Execution New Features: Concurrent UNION ALL Why does index monitoring makes Connor’s scratch his head and charges off to google so many times. SQL Server: Learn how to begin unit testing with tSQLt and SQL Server. ‘Temporal’ tables contain facts that are valid for a period of time. When they are used for financial information they have to be very well constrained to prevent errors getting in and causing incorrect reporting. As big data application success stories (and failures) have appeared in the news and technical publications, several myths have emerged about big data. This article explores a few of the more significant myths, and how they may negatively affect your own big data implementation. When effective end dates don’t align properly with effective start dates for subsequent rows, what are you to do? In order to automate the delivery of an application together with its database, you probably just need the extra database tools that allow you to continue with your current source control system and release management system by integrating the database into it. MySQL: Ronald Bradford on SQL, ANSI Standards, PostgreSQL and MySQL. How to Manage the World’s Top Open Source Databases: ClusterControl 1.2.9 Features Webinar Replay A few interesting findings on MariaDB and MySQL scalability, multi-table OLTP RO MariaDB: The Differences, Expectations, and Future How to Tell If It’s MySQL Swapping

  • Percona XtraDB Cluster 5.5.41-25.11 is now available
    Percona is glad to announce the new release of Percona XtraDB Cluster 5.5 on March 30th 2015. Binaries are available from downloads area or from our software repositories.Based on Percona Server 5.5.41-37.0 including all the bug fixes in it, Galera Replicator 2.11, and on Codership wsrep API 25.11, Percona XtraDB Cluster 5.5.41-25.11 is now the current 5.5 General Availability release. All of Percona‘s software is open-source and free, and all the details of the release can be found in the 5.5.41-25.11 milestone at Launchpad.Bugs Fixed:XtraBackup SST wouldn’t stop when MySQL was SIGKILLed. This would prevent MySQL to initiate a new transfer as port 4444 was already utilized. Bug fixed #1380697.garbd was returning incorrect return code, ie. when garbd was already started, return code was 0. Bugs fixed #1308103 and #1422863.wsrep_sst_xtrabackup-v2 script was causing innobackupex to print a false positive stack trace into the log. Bug fixed #1407599.MyISAM DDL (CREATE TABLE only) isn’t replicated anymore when wsrep_replicate_myisam is OFF. Note, for older nodes in the cluster, wsrep_replicate_myisam should work since the TOI decision (for MyISAM DDL) is done on origin node. Mixing of non-MyISAM and MyISAM tables in the same DDL statement is not recommended with wsrep_replicate_myisam OFF since if any table in list is MyISAM, the whole DDL statement is not put under TOI (total order isolation). This also doesn’t work if default_storage_engine variable is set to MyISAM (which is not recommended for Percona XtraDB Cluster) and a table is created without the ENGINE option. Bug fixed #1402338.Percona XtraDB Cluster now shows a warning in case additional utilities, like pv which may not affect critical path of SST, are not installed. Bug fixed #1248688.wsrep_causal_reads variable was not honored when declared as global. Bug fixed #1361859.garbd would not work when cluster address was specified without the port. Bug fixed #1365193.garbd was running as root user on Debian. Bug fixed #1392388.Errors in garbd init script stop/start functions have been fixed. Bug fixed #1367956.If mysqld gets killed during the SST it will leave an unclean data directory behind. This would cause Percona XtraDB Cluster to fail when the server would be started next time because the data directory would be corrupted. This was fixed by resuming the startup in case wsrep-recover failed to recover due to corrupted data directory. The old behavior is still achievable through --exit-on-recover-fail command line parameter to mysqld_safe or exit-on-recover-fail under [mysqld_safe] in my.cnf. Bug fixed #1378578.gvwstate.dat file was removed on joiner when XtraBackup SST method was used. Bug fixed #1388059.xtrabackup-v2 SST did not clean the undo log directory. Bug fixed #1394836.stderr of SST/Innobackupex is logged to syslog with appropriate tags if sst-syslog is in [sst] or [mysqld_safe] has syslog in my.cnf. This can be overridden by setting the sst-syslog to -1 in [sst]. Bug fixed #1399134.clustercheck can now check if the node is PRIMARY or not, to allow for synced nodes which go out of PRIMARY not to take any writes/reads. Bug fixed #1403566.Race condition between donor and joiner in Xtrabackup SST Configuration has been fixed. This caused XtraBackup SST to fail when joiner took longer to spawn the second listener for SST. Bug fixed #1405668.SST will now fail early if the xtrabackup_checkpoints file is missing on the joiner side. Bug fixed #1405985.socat utility was not properly terminated after a timeout. Bug fixed #1409710.10 seconds timeout in Xtrabackup SST Configuration script was not enough for the joiner to delete existing files before it started the socat receiver on systems with big datadir. Bug fixed #1413879.Conflict between enforce_storage_engine and wsrep_replicate_myisam for CREATE TABLE has been fixed. Bug fixed #1435482.SST processes are now spawned with fork/exec instead of posix_spawn to allow for better cleanup of child processes in event of non-graceful termination (SIGKILL or a crash etc.). Bug fixed #1382797.Variable length arrays in WSREP code were causing debug builds to fail. Bug fixed #1409042.Signal handling in mysqld has been fixed for SST processes. Bug fixed #1399175.Inserts to a table with autoincrement primary key could result in duplicate key error if another node joined or dropped from the cluster during the insert processing. Bug fixed #1366997.Other bugs fixed: #1391634 and #1396757.Release notes for Percona XtraDB Cluster 5.5.41-25.11 are available in our online documentation along with the installation instructions.Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!Please also note that Percona XtraDB Cluster 5.6 series is the latest General Availability series and current GA release is 5.6.22-25.8.The post Percona XtraDB Cluster 5.5.41-25.11 is now available appeared first on MySQL Performance Blog.

  • Greetz replaces Oracle’s Real Application Cluster with Galera Cluster for MySQL high availability
    Highly Available Greetings: Greetz uses MariaDB Galera Cluster for online Greeting Card Platform   Greetz provides on-line personalized greeting cards to thousands of customers. Their webshop application is built on Java and Hibernate (Object Relation mapping) and, prior to moving to MariaDB Galera Cluster, used Oracle(TM) Real Application Cluster as its underlying database. Their infrastructure is based on Linux and they use Ansible for configuration management.   Greetz approached Codershipʼs partner SkySQL (now MariaDB Corp.) to provide them with a replacement database technology for their web-‐‑shop that would significantly reduce the total cost of ownership of their database, while ensuring high availability and a solid, predictable performance.   READ MORE (CLICK HERE)

  • MySQL bind-address
    While I try to keep things simple, sometimes eliminating options and explanations comes back to haunt me. After posting how to open a Fedora firewall port for a LAMP stack, somebody got trapped by my instructions for installing MySQL on Fedora. They got stuck because they had the following setting in their /etc/my.cnf file: bind-address=localhost.localdomain I’d suggested using that bind-address value for a DHCP VMware Fedora installation in Step #7. I was trying to create an example for an isolated testing instance, which is why I set the bind-address to a localhost.localdomain value. They raised the following error when they tried to connect their base operating system’s version of MySQL Workstation to the Fedora VM: Failed to Connect to MySQL at with user student or, this dialog image: Before you do the next step, please ensure you’re using the right IP address. You can find that by running this command as an authorized sudoer: ifconfig | grep inet.*netmask.*broadcast In this case, the command returns: inet netmask broadcast I’ve since added instructions to the older post to set the bind-address value in the my.cnf file as follows when they want to support external connections (naturally that means authorizing port 3306): bind-address= After you reset the /etc/my.cnf file, you must stop and start, or restart the mysqld service. You can do that as the root user like this: systemctl restart mysqld Then, you can test a student user connection from MySQL Workbench like this: If the student user is authorized and the password is correct, you’ll see that the connection now works: As always, I hope this helps those working through similar issues.

  • How to Deploy and Configure MaxScale for SQL Load Balancing with Read-Write Split
    There are two models of load balancing: transport and application layer. HAProxy is a great TCP load balancer, but it’s lack of SQL awareness effectively limits its ability to address certain scaling issues in distributed database environments. In the open source world, there’s been a few SQL-aware load balancers, namely MySQL Proxy, ProxySQL and MaxScale, but they all seemed to be in beta status and unfit for production use. So we were pretty excited when the MariaDB team released a GA version of MaxScale earlier this year. In this blog, we’ll have a look at MaxScale and see how it compares with HAProxy. Deployment Installation is easy, at least on the latest LTS version of Ubuntu (Trusty, 14.04) which we used for our tests. Add a public key: $ apt-key adv --keyserver keyserver.ubuntu.com --recv-keys 8167EE24Add a MaxScale repository to one of *.list files for apt-get: deb [arch=amd64] http://downloads.mariadb.com/software/MaxScale/maxscale/DEB trusty mainRun: $ apt-get update && apt-get install maxscaleand you can enjoy your new software - proxy is installed into the /usr/local/skysql/maxscale directory.    Configuration Once installed, we need to configure it. Along with installation comes an example configuration file, located in: /usr/local/skysql/maxscale/etc/MaxScale_template.cnf. It gives a nice introduction to the available options, and helps to setup the environment. MaxScale uses a pluggable architecture with different plugins providing different features. In this post, we will concentrate on the routing part, and for now, leave out other interesting possibilities like query rewriting. MaxScale uses different types of services; monitors, services, listeners and filters.  For our tests we defined two types of routing services:  ‘router=readwritesplit’, which provides read/write (RW) splitting, ‘router=readconnroute’, which provides round-robin-like (RR) kind of access. Each service was accompanied by a listener, port 3307 for RW split and 3308 for RR service. With RR service, we relied on MaxScale’s monitoring of Galera nodes to route connections only to the nodes in a ‘Synced’ state. [maxscale] threads=4 [Galera Monitor] type=monitor module=galeramon servers=server1,server2,server3 user=maxmon passwd=maxpwd monitor_interval=10000 disable_master_failback=1 [qla] type=filter module=qlafilter options=/tmp/QueryLog [fetch] type=filter module=regexfilter match=fetch replace=select [RW] type=service router=readwritesplit servers=server1,server2,server3 user=root passwd=secretpass max_slave_connections=100% router_options=slave_selection_criteria=LEAST_CURRENT_OPERATIONS [RR] type=service router=readconnroute router_options=synced servers=server1,server2,server3 user=root passwd=secretpass [Debug Interface] type=service router=debugcli [CLI] type=service router=cli [RWlistener] type=listener service=RW protocol=MySQLClient address= port=3307 [RRlistener] type=listener service=RR protocol=MySQLClient address= port=3308 [Debug Listener] type=listener service=Debug Interface protocol=telnetd address= port=4442 [CLI Listener] type=listener service=CLI protocol=maxscaled address= port=6603 [server1] type=server address= port=3306 protocol=MySQLBackend [server2] type=server address= port=3306 protocol=MySQLBackend [server3] type=server address= port=3306 protocol=MySQLBackendThere are couple of interesting bits in the configuration file. As you can see, we had to define user/password pairs several times. Those users are used to check the health of the MySQL nodes and to get access to the list of users defined in the system. For the sake of simplicity we used plain text passwords but it is possible to use hashed passwords for better security.  Finally, since we wanted to compare performance of MaxScale vs HAProxy, we used HAProxy installed from within ClusterControl in a default setup – configured similarly to MaxScale’s RR service.    How does MaxScale work with Galera Cluster? So, let’s talk about how MaxScale sees the Galera Cluster. MaxScale provides an admin CLI which gives you access to some internal statistics. After the first login (user admin, password skysql), you can check available options by running the ‘help’ command. One of the very useful commands is ‘show servers’, which returns a health status of the cluster. Below is the example output of that command. $ /usr/local/skysql/maxscale/bin/maxadmin -u admin Password: MaxScale> show servers Server 0x219bac0 (server1) Server: Status: Slave, Synced, Running Protocol: MySQLBackend Port: 3306 Server Version: 5.6.22-72.0-56-log Node Id: 2 Master Id: -1 Repl Depth: 0 Number of connections: 0 Current no. of conns: 0 Current no. of operations: 0 Server 0x20f7da0 (server2) Server: Status: Slave, Synced, Running Protocol: MySQLBackend Port: 3306 Server Version: 5.6.22-72.0-56-log Node Id: 1 Master Id: -1 Repl Depth: 0 Number of connections: 0 Current no. of conns: 0 Current no. of operations: 0 Server 0x20f7c90 (server3) Server: Status: Master, Synced, Running Protocol: MySQLBackend Port: 3306 Server Version: 5.6.22-72.0-56-log Node Id: 0 Master Id: -1 Repl Depth: 0 Number of connections: 0 Current no. of conns: 0 Current no. of operations: 0We are interested in the status of the nodes right now – as we can see, we have three nodes ‘Running’, all of them are ‘Synced’. Two were elected as ‘Slave’ and one as a ‘Master’. Those states are what we can use in the configuration file. For example, in RR service we defined the following variable: router_options=syncedIt means that, at any given time, connections can be routed to any of the nodes, as long as they are in the ‘synced’ state (i.e. not serving as a donor or joining the cluster). On the other hand, the RW service was looking for ‘Slave’ and ‘Master’ states to route traffic accordingly. In case of a master failure, a new node is elected as a new master. Your application needs to reconnect though, MaxScale currently does not provide failover for currently open connections. What’s worth noting, if you want to setup a RW split, you will need to set the max_slave_connections variable accordingly. By default MaxScale sets it to one and, as a result, only one slave is getting read connections. You can here use a fixed number (2, 5) or a percent of the slave pool (50%, 100%). As we wanted all of our slaves, no matter how many there are out there, to serve the traffic, we set this variable to 100%: max_slave_connections=100%Another interesting bit is the ‘master’ failover part – when MaxScale detects that a node, elected as a master, is unreachable, it promotes one of the ‘slaves’ to the ‘master’ role. Then, by default, when old ‘master’ comes back online, it is immediately promoted to its old master role. As a result, writes may switch back and forth between different nodes should the ‘master’ start to flap. You can switch this default behavior by adding ‘disable_master_failback=1’ directive to the definition of the monitor service. In case of any connectivity issues, it’s worth checking log files (by default located in the /usr/local/skysql/maxscale/log/ directory) and the kind of MySQL users MaxScale had detected. The latter can be done from the CLI, using the ‘show dbusers <service>’ command: MaxScale> show dbusers RW Users table data Hashtable: 0x2a0c900, size 52 No. of entries: 3 Average chain length: 0.5 Longest chain length: 7 User names: sbtest@%, maxmon@%, maxmon@%If you added some new users on the MySQL side, you can refresh MaxScale’s database by running reload dbusers <service>: MaxScale> reload dbusers RWIn general, the CLI gives you some nice options to use. We’ll not go over all of them in this post, but we’d still like to mention some features: configuration (at least partially) can be changed on fly and reloaded (reload config command) server state can be set from the CLI, which enables a DBA to move writes to a different node in the cluster (set server … command) services can be disabled/enabled/restarted from the CLI While in the CLI, help is easily reachable through the following commands: help help <command> (for example help show)Stay tuned for part two of this post where we will cover the performance comparison between MaxScale’s different router services and HAProxy. Blog category: DB OpsTags: galera clusterload balancingMariaDBMaxScaleMySQLsql load balancer