|
|
|
Posted
7 months
ago
by
Reggie Burnett
MySQL Connector/Net 6.4.6, a new version of the all-managed .NET driver for MySQL has been released. This is a maintenance release and is recommended for use in production environments. It is appropriate for use with MySQL server versions 5.0-5.6. This is intended to be the final release for Connector/NET 6.4.
|
|
Posted
7 months
ago
by
Matt Reid
If you’ve ever used non-opensource code, or applications that you don’t have complete control over, then you may have run into situations you need to alter data on a per-row basis but been unable to do so for lack of application SQL
... [More]
access. The solution to this type of problem is to use a MySQL TRIGGER, which allows us to execute arbitrary SQL commands when defined events occur. Why is this useful and how does it work? Well…
For example, I have a freeRADIUS server that uses MySQL as a backend for the user authentication, and one of my server applications (HostBill) provides a freeRADIUS plugin that allows my users to manage their RADIUS accounts; however the default freeRADIUS schema lacks a DATETIME column on the user table. When a user is created (INSERT) or has their password changed (UPDATE) I have no row data that tells me the dates when these operations were issued. Typically this would be a trivial change: issue an ALTER TABLE statement to add two columns and then add some NOW() statements to the application’s SQL calls.
However, the problem is that the application in question is ion-cube encrypted so I cannot make SQL changes to add support for my ‘date_created’ and ‘date_modified’ columns. I could intercept the TCP steams via MySQL Proxy or a custom script but that’s quite silly to have to do for this. Other methods also exist but are rather hack-ish.
MySQL TRIGGERs easily solve this situation. Here are two TRIGGERs, one that runs on INSERT and the other on UPDATE. Now the database can have ‘date_created’ and ‘date_modified’ data without any application changes. Whenever a row is inserted into the table the ‘date_create’ column will be populated via the NOW() function and correspondingly when a row is modified the ‘date_modified’ column will be updated with the NOW() function.
DELIMITER |
CREATE TRIGGER trigger_radcheckDatetimeInsert BEFORE INSERT ON radcheck FOR EACH ROW
BEGIN
SET NEW.date_create = NOW();
END;
|
CREATE TRIGGER trigger_radcheckDatetimeModify BEFORE UPDATE ON radcheck FOR EACH ROW
BEGIN
SET NEW.date_modify = NOW();
END;
|
DELIMITER ; [Less]
|
|
Posted
7 months
ago
by
MySQL Performance Blog
Percona is glad to announce the release of Percona Playback 0.5 on November 26th, 2012. Downloads are available from our download site and Percona Software Repositories. Percona Playback is a tool for replaying the load of one database server to
... [More]
another. Currently it can read queries from MySQL query-log and tcpdump files and run them on other MySQL server. With Percona Playback you can measure how a server or database upgrade, change in my.cnf or schema change can affect the overall performance of your database server.
This release should be considered Alpha quality.
New features:
Reports have been extended and now return the list of queries with a response time longer than the ones run on the original. Additional statistics, like the query types (SELECT, UPDATE,…) with the proportion off faster and slower statement for each categories, has been added as well.
Percona Playback now supports reading the slow query log from standard input (stdin) instead of log file.
For this release binary packages were prepared, full list is available in our documentation.
Bugs fixed:
Multi-line queries in the slow query log didn’t work correctly. Bug fixed #1035217 (Stewart Smith).
Raw tcpdump format wasn’t documented. Bug fixed #1034527 (Hrvoje Matijakovic).
Replaying the load with Percona Playback and tcpdump
Miguel already showed in his post how to use Percona Playback with the query_log plugin. Following example shows how to capture traffic with tcpdump and create a dump file that will be later used with Percona Playback.
On the production server we start the tcpdump with the -w option to collect packets into the raw tcpdump file:
tcpdump -i any port 3306 -w example.dump
After we collect some amount of traffic we can stop the tcpdump and use the dump file to replay the load on the test server. Warning: All the queries will be run with Percona Playback, not just the SELECT but the UPDATE,DELETE and INSERT ones as well. So be sure not to run this on production database unless you don’t need the data anymore.
percona-playback --input-plugin=tcpdump --tcpdump-file=example.dump \
--tcpdump-mode=accurate --db-plugin=libmysqlclient --mysql-host=10.8.2.10 \
--mysql-username=root --mysql-password=passW0rd --mysql-schema=imdb
This example uses accurate tcpdump-mode, which means Percona Playback will preserve query execution times and it will pause between queries to playback the same load that was recorded on production with the same accuracy.
After the dump file has been replayed on the server, Percona Playback will show the playback statistics in the report like:
Executed 1271 queries
Spent 00:00:59.856656 executing queries versus an expected 00:00:47.085301 time.
61 queries were quicker than expected, 21 were slower
A total of 0 queries had errors.
Expected 30298 rows, got 30298 (a difference of 0)
Number of queries where number of rows differed: 0.
We can use this information to decide if the test server needs further tuning or how it will be able to sustain production load.
If you encounter any bugs, please report them to our bug tracking system. [Less]
|
|
Posted
7 months
ago
by
Petri Virsunen
We are pleased to inform you that the new Continuent Tungsten 1.5.3 is now available for download at wwww.continuent.com/software. This is a maintenance release that is recommended for all our customers, but is not considered a critical
... [More]
upgrade. Please contact us if you need consultative help with this upgrade.
This release is a 1.5 maintenance release which has been primarily aimed at [Less]
|
|
Posted
7 months
ago
by
Dave Stokes
Last year a wrote a blog entry on holiday gifts for DBAs that went over quite well with the relatives and loved ones of DBAs. So if you are a MySQL DBA, print out a copy of this, circle the items you like, and get something you like this year. And
... [More]
yes, shopping for you is worse than styling Donald Trump’s hair in a high wind or explaining foreign keys to any cast member of any popular reality or music show. So take some pity on those who are buying you gifts.
T-Shirts
Cafepress has a series of DBA shirts including ‘Not Normalized’ , ‘Kiss me — I’m the DBA’, and the Key Rule shirt.
Think Geek has some good shirts including ‘SELECT * FROM users WHERE clue > 0. Uh oh, zero rows were returned!’
and ‘Go away or I will replace you with a very small shell script’
Zazzle offers a If first you don’t succeed; call it version 1.0 and the ‘SQizzL My Nizzle’ t-shirts.
New books on MySQL since last Holiday season
MySQL Troubleshooting: What To Do When Queries Don’t Work by Sveta Smirnova needs to be on the book shelf of any serious MySQL DBA or Developer.
MySQL 5th edition by Paul DuBois comes out next March but can be pre-ordered.
Ronald Bradford’s Effective MySQL Backup and Recovery is a page turning thriller for DBAs. Effective MySQL Replication Techniques in Depth goes hand in hand with its new sibling. Buy both, save on shipping!
I have not seen Expert MySQL yet by Dr. Charles Bell knows his stuff and has written other fantastic books.
Office Chairs like the Gravitonus are also igh war items for DBAs and need replacement on a regular basis.
You can also get non-DBA specific for presents such as movie passes, gift cards, and other generic gifts. But the above are your best choices for your DBA this time of year. And if you have special DBA gifts, please share them here!! [Less]
|
|
Posted
7 months
ago
by
Jay Janssen
I learn more and more about Galera every day. As I learn more, I try to keep my myq_gadgets toolkit up to date with what I consider is important to keep any eye on on a PXC node. In that spirit, I just today pushed some changes to the
... [More]
‘wsrep’ report, and I thought I’d go over some of the status variables and metrics being tracked there with the aim to show folks what they should be watching (at least from my opinion, this is subject to change!).
First, let’s take a look at the output:
[root@node3 ~]# myq_status -t 1 wsrep
Wsrep Cluster Node Queue Ops Bytes Flow Conflct
time name P cnf # name cmt sta Up Dn Up Dn Up Dn pau snt dst lcf bfa
19:17:01 trime P 3 3 node3 Sync T/T 0 0 35 40 54K 61K 0.0 0 17 0 2
19:17:03 trime P 3 3 node3 Sync T/T 0 0 70 85 107K 124K 0.0 0 13 0 2
19:17:04 trime P 3 3 node3 Sync T/T 0 0 72 81 111K 121K 0.0 0 16 0 3
19:17:05 trime P 3 3 node3 Sync T/T 0 0 70 85 108K 124K 0.0 0 17 0 4
19:17:06 trime P 3 3 node3 Sync T/T 0 0 66 82 100K 124K 0.0 0 17 0 3
19:17:07 trime P 3 3 node3 Sync T/T 0 0 68 78 105K 117K 0.0 0 22 0 0
19:17:08 trime P 3 3 node3 Sync T/T 0 0 65 93 101K 135K 0.0 0 14 1 5
19:17:09 trime P 3 3 node3 Sync T/T 0 0 73 83 111K 125K 0.0 0 19 0 3
19:17:10 trime P 3 3 node3 Sync T/T 0 0 30 46 46K 66K 0.0 0 10 0 2
19:17:12 trime P 3 3 node3 Sync T/T 0 0 64 80 97K 120K 0.0 0 19 0 4
19:17:13 trime P 3 3 node3 Sync T/T 0 0 69 88 106K 131K 0.0 0 28 0 1
19:17:14 trime P 3 3 node3 Sync T/T 0 0 70 83 106K 121K 0.0 0 11 0 3
19:17:15 trime P 3 3 node3 Sync T/T 0 0 72 84 111K 126K 0.0 0 15 0 3
As I’ve mentioned before, myq_status gives an iostat-like output of your server. This tool takes what are usually global counters in SHOW GLOBAL STATUS and calculates the change each second and reports that. There’s lot of other reports it can run, but this one is focused on ‘wsrep%’ status variables.
It’s important to note that this reflects the status of a single PXC node in my cluster, node3 to be precise, so some information is cluster-wide, other information is specific to this particular node. I tend to open a window for each node and run the tool on each so I can see things across the entire cluster at a glance. Sometime in the future, I’d like to build a tool that polls every cluster node, but that’s not available currently.
Let’s go through the columns.
Cluster
There are 4 columns in the cluster section, and it’s important to understand that this tool only currently connections to a single node (by default, localhost). The state of the cluster could be divergent across multiple nodes, so be careful to not assume all nodes have these values!
name
The cluster’s name (first 5 characters). This is wsrep_cluster_name.
P
Either P for Primary or N for Non-primary. This is the state of this partition of the cluster. If a cluster gets split brained, then only a quorum (>=51%) of the remaining nodes will remain Primary. Non-primary clusters are the remaining minority and will not allow database operations.
cnf
This is wserep_cluster_conf_id — the version # of the cluster configuration. This changes every time a node joins or leaves the cluster. Seeing high values here may indicate you have nodes frequently dropping out and rejoining the cluster and you may need some retuning of some node timeouts to prevent this.
#
The number of nodes in the cluster.
Node
This is state data about the local node that the tool happens to be connected to.
name
The name of this local node (first 5 characters). This is handy when you have this tool running in several windows on several nodes.
cmt
This is the wsrep_local_state_comment — basically a plaintext word describing the state of the node in terms of the rest of the cluster. ’Sync’ (Synced) is what you want to see, but ‘Dono’ (Donor), ‘Join’ (Joiner), and others are possible. This is handy to quickly spot which node was elected to Donate either SST or IST to another node entering the cluster.
sta
Short for state, this is two True/False values (T/F) for wsrep_ready and wsrep_connected. These are somewhat redundant with the local_state value, so I may remove them in the future.
Queue
This is information about the replication queues in both directions.
The ‘Up’ queue is outbound replication. This generally increases when some other node is having difficulty receiving replication events.
The ‘Dn’ (down) queue is inbound replication. Positive values here can be an indicator that this node is slow to apply replication writesets.
Ops
Ops are simply replication transactions or writesets. Up is outbound, i.e., where this node was the originator of the transaction. Dn is download, that is, transactions from other nodes in the cluster.
Bytes
Just like Ops, but in Bytes instead of transaction counts. I have seen production clusters having performance issues where I noticed that the Ops and Bytes went to Zeros on all the nodes for a few seconds, and then a massive 90M+ replication transaction came through. Using the Up and Dn columns, I could easily see which node was the originator of the transaction.
Flow
Flow gives some information about Flow Control events. Galera has some sophisticated ways of metering replication so lag does not become a problem.
pau
wsrep_flow_control_paused — This is the amount of time since the last time SHOW GLOBAL STATUS was run that replication was paused due to flow control. This is a general indicator that flow control is slowing replication (and hence overall cluster writes) down.
snt
wsrep_flow_control_sent — how many flow control events were SENT from this node. Handy to find the node slowing the others down.
dst
This does not go under the Flow group. This is wsrep_cert_deps_distance — This is a general indicator of how many parallel replication threads you could use. In practice I haven’t found this extremely helpful yet and I may remove this in the future. I think being aware of how Flow control works and watching flow control events and queue sizes is a better way to detect replication lag, and this really just tells you if multi-threaded replication could help improve replication speed at all.
Conflct
Replication conflicts, as described in my last post. lcf is local certification failures, and bfa is brute force aborts. This should be helpful to understand that these conflicts are or are not happening.
Interpreting the results
Let’s look at that output again and make some observations about our cluster and this node:
[root@node3 ~]# myq_status -t 1 wsrep
Wsrep Cluster Node Queue Ops Bytes Flow Conflct
time name P cnf # name cmt sta Up Dn Up Dn Up Dn pau snt dst lcf bfa
19:17:01 trime P 3 3 node3 Sync T/T 0 0 35 40 54K 61K 0.0 0 17 0 2
19:17:03 trime P 3 3 node3 Sync T/T 0 0 70 85 107K 124K 0.0 0 13 0 2
19:17:04 trime P 3 3 node3 Sync T/T 0 0 72 81 111K 121K 0.0 0 16 0 3
19:17:05 trime P 3 3 node3 Sync T/T 0 0 70 85 108K 124K 0.0 0 17 0 4
19:17:06 trime P 3 3 node3 Sync T/T 0 0 66 82 100K 124K 0.0 0 17 0 3
19:17:07 trime P 3 3 node3 Sync T/T 0 0 68 78 105K 117K 0.0 0 22 0 0
19:17:08 trime P 3 3 node3 Sync T/T 0 0 65 93 101K 135K 0.0 0 14 1 5
19:17:09 trime P 3 3 node3 Sync T/T 0 0 73 83 111K 125K 0.0 0 19 0 3
19:17:10 trime P 3 3 node3 Sync T/T 0 0 30 46 46K 66K 0.0 0 10 0 2
19:17:12 trime P 3 3 node3 Sync T/T 0 0 64 80 97K 120K 0.0 0 19 0 4
19:17:13 trime P 3 3 node3 Sync T/T 0 0 69 88 106K 131K 0.0 0 28 0 1
19:17:14 trime P 3 3 node3 Sync T/T 0 0 70 83 106K 121K 0.0 0 11 0 3
19:17:15 trime P 3 3 node3 Sync T/T 0 0 72 84 111K 126K 0.0 0 15 0 3
We can see are are connected to the node identified as ‘node3′. Our cluster is Primary and there are 3 nodes total belonging to it.
There isn’t any replication queue activity, and I find this is common except during cluster stalls. There are clearly a fair amount of transactions being replicated to and from this node: approximately 100K worth of data outbound, and just a hair more than that coming in.
Our replication is performing well, because the Flow control columns are zeroes, but we do see some replication conflicts. Mostly these are brute force aborts, but I was able to see the (very occasional) local certification failure. This makes sense to me because the replication inbound queue always reports as empty, so it seems that replication is being applied nearly immediately. Local certification failures only happen when the inbound queue is > 0. Instead brute force aborts are applying writesets rolling back locally open transactions.
In fact, this is a sysbench test what is running full speed (these are VMs, so that’s not that particularly fast) on two of my three nodes, and more slowly on the third. I had to decrease my table size from 250k to 2.5k to start seeing replication conflicts regularly.
Hopefully this is helpful for you to get an idea of how to observe and interpret Galera status variables. [Less]
|
|
Posted
7 months
ago
by
Oracle MySQL Group
If you are a database developer who wants to create applications using MySQL, then the MySQL for Developers course is for you. This course covers how to plan, design and implement applications using the MySQL database with realistic examples in Java
... [More]
and PHP.
To see more details of the content of the MySQL for Developers course, go to http://oracle.com/education/mysql and click on the Learning Paths tab and select the MySQL Developer path.
You can take this course as a:
Live-Virtual Event: Follow this live instructor-led event from your own desk - no travel required. Choose from a selection of events on the calendar in languages such as English, German and Korean.
In-Class Event: Travel to an education center to take this class. Below is a sample of events on the schedule.
Location
Date
Language
Vienna, Austria
4 March 2013
German
London, England
4 March 2013
English
Gummersbach, Germany
11 February 2013
Germany
Hamburg, Germany
14 January 2013
Germany
Munich, Germany
15 April 2013
Germany
Budapest, Hungary
15 April 2013
Hungarian
Milan, Italy
21 January 2013
Italy
Rome, Italy
11 March 2013
Italy
Amsterdam, Netherlands
28 January 2013
Dutch
Nieuwegein, Netherlands
13 May 2013
Dutch
Lisbon, Portugal
18 February 2013
European Portugese
Porto, Portugal
18 February 2013
European Portugese
Barcelona, Spain
18 February 2013
Spanish
Madrid, Spain
28 January 2013
Spanish
Bern, Switzerland
11 April 2013
German
Zurich, Switzerland
11 April 2013
German
Nairobi, Kenya
21 January 2013
English
Petaling Jaya, Malaysia
17 December 2012
English
Sao Paulo, Brazil
11 March 2013
Brazilian Portugese
For more information on this class or other courses on the authentic MySQL curriculum, or to express your interest in additional events, go to http://oracle.com/education/mysql.
Note, many organizations deploy both Oracle Database and MySQL side by side to serve different needs, and as a database professional you can find training courses on both topics at Oracle University! Check out the upcoming Oracle Database training courses and MySQL training courses. Even if you're only managing Oracle Databases at this point of time, getting familiar with MySQL will broaden your career path with growing job demand. [Less]
|
|
Posted
7 months
ago
by
Colin Charles
I’ve had many dealings with SK Planet, in South Korea, so when they asked me to speak at their inaugural Tech Planet, I jumped at the opportunity. I was already pre-given a topic titled “NewSQL”, so I talked about the evolution of
... [More]
SQL -> Big Data -> NoSQL -> NewSQL, all thanks to papers written by Google research, and then focused on how MySQL & MariaDB is gaining many new interfaces: mariasql, HandlerSocket, dynamic columns, memcached InnoDB plugin, node.js with NDBCLUSTER, CassandraSE, LevelDB, Galera Cluster and more. This is a topic I will talk more about later.
The event was great. It started with a speaker’s dinner in where the CTO of SK Planet & multiple VP’s dined with the speakers. We had a private room at the InterContinental COEX, and started with smoked salmon, Russian crab meat & cream cheese. This was followed on quickly by crab meat soup. The main course was beef tenderloin, mero fish & steamed black rice, asparagus and black garlic. We ended with a cranberry tian with raspberry sauce. This was filled with lots of Equus red wine.
The next day, we had the day of the event itself and it was huge. There were probably over 800 attendees, and SkySQL had a booth maintained by the Korean partners: OSS Korea, PrixMedia, and Oh New Innovation. This was followed by lunch with the CEO of SK Planet with great conversation & food. Started with tuna & avocado, to clear the palate, some lemon sorbet, and a great main consisting of beef tenderloin & king prawn with red wine sauce. We had much to chat about, and as lunch came to a close, it was time for me to give my talk.
It was well attended, there were some good questions, and there are naturally some good action items. I’m glad that the whole ecosystem provided me multiple opportunities to talk about amazing work being done. The press covered the Tech Planet event. I managed to snag an interview as well. More photos of the event at my techplanet2012 tag on Flickr.
All in, an exciting space to be in now. Thank you so much for SK Planet for the invite, and I look forward to being around in 2013.
Related posts:
Sun Tech Days Hyderabad
Planet MySQL on Twitter
Some Planet MySQL changes over time [Less]
|
|
Posted
7 months
ago
by
Ted Wennmark
Last week we had our first Swedish MySQL User Meeting "SMUG" in Stockholm. The event was at Oracle engineering office at Söder and 20 users attended this first event!I presented a talk on news from MySQL connect, it was a blend of MySQL
... [More]
5.6 RC and MySQL Cluster 7.3DM, you can see slides on slideshareLook out for our next SMUG event ;) [Less]
|
|
Posted
7 months
ago
by
MySQL Performance Blog
Time for another poll! I wonder What CPU Architecture do you use for running MySQL in Production ? I guess most of the game here is between Intel and AMD x86-64 variants though I wonder if there is still a lot of use for others in the wild. If I
... [More]
am missing some Architecture which is still alive in your data center please post a comment and I will add it to the list.
Please select all what applies. If you would like to share what works better for you in the comments I appreciate it.
Note: There is a poll embedded within this post, please visit the site to participate in this post's poll. [Less]
|
Copyright
©
2013
Black Duck Software, Inc.
and its contributors, Some Rights Reserved. Unless otherwise marked, this work is licensed under a
Creative Commons Attribution 3.0 Unported License
. Ohloh
®
and the Ohloh logo are trademarks of
Black Duck Software, Inc.
in the United States and/or other jurisdictions. All other trademarks are the property of their respective holders.