Last week I summarized the Percona Live 2012 Conference for you. I went over some of the different topics that were covered during the talks. Today I’m going to briefly discuss the 2 tutorials that I personally felt were most beneficial to me and hopefully they will be to you too! Both talks were designed for Database Administrators (DBA) but could be easily appreciated by application developers and Systems Administrators as well. Ronald Bradford (Effective MySQL) was the speaker at both talks made them feel like a 2 part series. I found the information in “Idiosyncrasies of MYSQL That Bite” to build
on the first talk, “Explaining the EXPLAIN”.
Explaining the EXPLAIN was the first talk of the two. Ronald Bradford used this presentation to discuss how the MySQL Query Optimizer develops a Query Execution Plan (QEP). He pointed out that EXPLAIN will only work on SELECT statements. This means you may need to re-write a query into a SELECT using WHERE. It’s interesting to note that the QEP is generated every time MySQL actually processes a query. There is no way to guarantee that a query is run in the same fashion as the QEP provided using EXPLAIN but chances are good. I learned that based on how your query is formed and how your tables are built, there are times where MySQL might just completely re-write your query to get the results you are looking for, and the resulting queries could be very expensive.
The second talk was called Idiosyncrasies of MySQL That Bite, also by Ronald Bradford. A lot of what he talked about here was actually familiar to me, but there were some extremely valuable insights that came from his time working with databases in general. For instance, I knew that the default date for a DATETIME column was all zeros, but I never considered how badly this could affect critical data where MySQL has replaced malformed dates with the default. I also never thought about how a misspelled “`ENGINE=InoDB`” or charset mismatch could completely change the way perfectly normal queries could run. Luckily, MySQL has the SQL_MODE variable which can be used to modify most of this kind of behavior.
Both talks were valuable to me, partly because in each one, the speaker showed us some very interesting quirks to look out for in MySQL, and what to do when you see them, or how to even prevent them all together. He did it in a way that was fun, and easy to understand. I hope you were able to take away some tips as well.
We just got back from the Percona Live event in Santa Clara, CA and wanted to share our experience with our readers. The event, which was organized by Percona and sponsored by some great companies, was filled with amazing talks from some of the top minds in MySQL.
Day 1 was filled with tutorials on many different topics related to administering database servers using MySQL ranging from security to
troubleshooting and performance tuning. There were so many to choose from, and we found the biggest problem was choosing which talks to attend.
Peter Zaitsev (Percona CEO) had a tutorial on InnoDB and XtraDB specific performance optimizations while Sheeri Cabral (Mozilla) went over some ways to secure the database and verify security using the same tools that black hats would use. After lunch, Rene Cannao (PalominoDB) discussed measuring performance with proper benchmarking and profiling, and showed specific cases where using the tools he introduced, led to a quick resolution of otherwise difficult to diagnose problems. Baron Schwartz (Percona) also discussed ways to more effectively manage MySQL using the Percona Toolkit while Florian Haas (hastexo) discussed High Availability solutions with Yves Trudeau (Percona).
Read the full post »
Red Hat recently released the latest version of their Linux distribution: Red Hat Enterprise Linux 6 (RHEL 6). This is exciting because of the approach Red Hat takes with their Enterprise Linux. They stress stability and reliability over cutting edge features. One way they do this is to lock in to particular versions of software that they provide.
For example, with RHEL 5 they locked to a recent, well-tested version of PHP (5.1.6). During the life the RHEL 5 this has not changed even as newer versions of PHP came out sporting nifty new features (they did add security and bug fixes, however). This meant that you could set up your website on RHEL 5 server and not worry that an update would be released that added (or removed) a feature that changed the way your site behaved. The downside is of course that new features were not available if you wished to take advantage them. Many PHP applications (such as Wordpress) began to require features not available in the RHEL 5 PHP. RHEL 6 locks to much more recent versions of software, allowing you to get new features but retain the benefits of Red Hat’s thorough testing and updating process.
Read the full post »
The very same tools that can be used for good can also be used for evil. phpMyAdmin is no exception. A properly secured phpMyAdmin implementation is quite difficult to detect and exploit. If you are going to use phpMyAdmin here are some recommendations to help stop phpMyAdmin hackers…
1. Use a non-obvious directory/URL.
Obfuscating the presence of phpMyAdmin goes a long way to stopping script kiddies and automated attacks. Something along the lines of https:///mydbadm/ typically works well. Anything that’s not “admin”, “phpMyAdmin-3.3.10-all-languages”, “phpmyadmin”, or similar variations is a move in the right direction.
2. Restrict by IP (if you can) via htaccess.
If you are able to restrict access to phpMyAdmin to a short list of IP’s you will make your life a lot easier!
Read the full post »
Session management is relatively easy for a simple website handled by a single web server. Session information is typically stored in memory or disk and all is well. What about when you have a large website that’s served by a number of web servers? You could store sessions in memory or on disk in the same manner, but that creates a problem. The session information will not be accessible by the other web servers. Since the HTTP protocol is stateless, it is your responsibility to maintain sessions as HTTP requests are spread among the various web servers.
What about “sticky sessions”? Most hardware load balancers support an option called sticky sessions. Sticky sessions can be enabled to keep all HTTP requests from a given user on the same web server. This avoids some of the session management problems, but it introduces a potentially more serious problems. To name a few:
- The distribution of load between web servers can become uneven.
- Scheduled maintenance is more difficult since you cannot simply remove a web server from the load balancer without impacting users.
- High availability is impacted since users will lose their session information if the web server they are “stuck” to crashes. When they are directed to another web server, they might have lost the shopping cart they spend an hour filling. Even worse, they might not go through the effort to refill it!
How are these problems avoided?
Read the full post »
