The benchmarks were executed using the benchmark scripts dbt2-0.37.50 available at dev.mysql.com, the benchmark program is the flexAsynch program mentioned in some of my earlier blogs. We used 8 LQH threads per data node.
| Showing entries 1 to 30 of 26687 | Next 30 Older Entries |
InnoDB locks the buffer pool mutex and scans the LRU to remove pages when DROP TABLE is done for a table that uses innodb_file_per_table. If you read the source you might notice that it scans the LRU twice, once in buf_LRU_drop_page_hash_for_tablespace and then again in buf_LRU_invalidate_tablespace. Locking the buffer pool mutex and scanning the LRU isn't cheap when the buffer pool is large. It takes ~1 second on servers I use and that is too much as nothing gets done during that time by other threads.
I changed InnoDB to remove one of the LRU scans and reduced the stall in half. However that was not good enough. The next step was to avoid any LRU scan during DROP TABLE. Several bugs have been filed for this but the primary ones are
[Read more...]The explain statement can be an important tool for understanding how a query is being executed and what you can do to make it run better. Although the output of EXPLAIN is relatively straightforward it can be confusing to inexperienced users or can be mangled by terminal wrapping.
To help with these problems as well as provide a pastebin for MariaDB developers to share explains during development we created The MariaDB/MySQL Explain Analyzer. This tool:
This is the
[Read more...]Users of MySQL Replication sometimes throttle client requests to give slaves time to catch up to the master. PECL/mysqlnd_ms 1.4, the current development version, features some throttling through the quality-of-service filter and global transaction identifier (GTID). Both the plugins client-side GTID emulation and the MySQL 5.6 built-in GTID feature can be used to slow down PHP MySQL requests, if wanted.
The replication plugin has a neat feature called quality-of-service filter. If, for example, the quality of service you need from a MySQL Replication cluster is "read your writes",
[Read more...]Often I see a SQL problem solved incorrectly and I do not mean inefficiently. Simply incorrectly. In many cases the developer remains unaware that they aren’t getting the results they were expecting or even if a result is correct, it is only by chance, for example because the database engine was smart enough to figure out some non-sense in a query. In a few posts I will try to disclose some of the more common problems.
Aggregate with GROUP BYUnlike many other database systems, MySQL actually permits that an aggregate query returns columns not used in the aggregation (i.e. not listed in GROUP BY clause). It could be considered as flexibility, but in practice this can easily lead to mistakes if a person that designs queries does not understand how they will be executed. For example, what values an aggregate query returns for a
[Read more...]
Have you ever tried to kill a query, but rather than just go away, it remained among the running ones for an extended period of time? Or perhaps you have noticed some threads makred with killed showing up from time to time and not actually dying. What are these zombies? Why does MySQL sometimes seem to fail to terminate queries quickly? Is there any way to force the kill command to actually work instantaneously? This article sheds some light on it.
Threads and connectionsMySQL uses a separate thread for each client connection. A query sent to MySQL is handled by a thread that was previously associated with the connection over which the query arrived. Anyone with sufficient privileges can see the list of currently active threads, along with some additional details, by running SHOW PROCESSLIST command, which returns a
[Read more...]My old post (http://www.jroller.com/mmatthews/entry/speeding_up_batch_inserts_for) on the performance gains from batch rewritten statements gets regurgitated in various forums, and conference sessions, and often people miss the nuances of it.
Under the hood, what is happening with this feature is the following:
(1) The driver attempts to detect that the SQL being prepared is an INSERT. We (on purpose) don‘t ship a full-fledged parser in the driver, so this works 95% of the time. For the other 5%, you‘re out of luck unless you can simplify your query text.
(2) If the statement is an INSERT, the driver attempts to determine if it can be rewritten as a multi-value INSERT. From the code itself, the conditions are:
// Needs to be INSERT,
[Read more...]My first computer program was written almost quarter a century ago on a BK-0010 computer. It was very simple: the program asked the user to enter their name and then greeted the user using the entered name, like “Hello, Artem!”. I was fascinated. A couple of lines written in Vilnius BASIC transformed a piece of metal and silicon into a considerate thing that cared about a person’s name enough to remember it :-). Of course, the first experience doesn’t represent the day-to-day routine of software development, but the moments when I see a couple of lines making an amazing transformation still enchant me, and remind me why I’ve been writing code all this time.
I’ve just experienced this very same first-time feeling as we’ve released
[Read more...]Our previous GA release of Percona XtraDB Cluster caused a lot of interest and feedback. I am happy to announce next version Percona XtraDB Cluster 5.5.23, which comes with bug fixes and improvements.
List of changes:
Binaries are available from downloads area or from
[Read more...]We are excited to have Gerry Narvaja start today at Tokutek! Gerry has spent more than 25 years in the software industry, most of them working with databases for different kinds of applications, from embedded to large-scale web products. Gerry worked first at MySQL, and then Sun Microsystems supporting the Sales teams. In 2008 he transitioned into being a Senior MySQL DBA. Gerry graduated as an Electronic Engineer from I.T.B.A (Instituto Tecnológico de Buenos Aires) and has an M.B.A. from Universidad del Salvador in collaboration with S.U.N.Y.A (State University of NY at Albany).
Gerry enjoys helping users to solve complex database production issues. For almost a year he has been co-hosting the popular MySQL Community podcast, OurSQL, which was given the
[Read more...]In this post I describe what happens when a slave's Foreign Key setup is different from that of the master. I'm in particular interested in a setup where the slave has a subset of the master's foreign keys, or no foreign keys at all. I wish to observe whether integrity holds.
Which foreign keys do we have and how do we drop them? If you want to do this by hand, well, good luck! Fortunately, common_schema provides with quite a few handy views and routines to assist us. Consider viewing the existing foreign keys on sakila:
[Read more...]master> SELECT create_statement FROM common_schema.sql_foreign_keys WHERE TABLE_SCHEMA='sakila';
For as long as I can remember in my public speaking activities, I have always planned my presentations with some sort of live demo in it. I am always surprised when a conference venue asks me to provide my slides in advance, to be loaded in an anonymous computer with no chance of demos. I always turn down such offers, as I always want to provide a demo.
There have been times when technical or time constraints prevented me from demoing something, and in these cases I felt that the presentation was lacking a vital part. But I always try. I have even given demos during lightning talks, and those were the ones that made me feel really good.
I have given hundreds of presentations, and hundreds of demos, and as in every human activity, I have made plenty of mistakes. I believe I have learned some valuable lesson from my mistakes, and this
[Read more...]Reddit takes SQL injection very seriously.
How seriously?
Check their headers:
scabral-07890:~ scabral$ curl --head www.reddit.com
HTTP/1.1 200 OK
Content-Type: text/html; charset=UTF-8
Set-Cookie: reddit_first=%7B%22organic_pos%22%3A%201%2C%20%22firsttime%22%3A%20%22first%22%7D; Domain=reddit.com; expires=Thu, 31 Dec 2037 23:59:59 GMT; Path=/
Server: '; DROP TABLE servertypes; --
Date: Sat, 12 May 2012 13:54:20 GMT
Connection: keep-alive
scabral-07890:~ scabral$
A colleague at PICC showed me this when he learned of my talk on MySQL security!
This week we present how to use pt-archiver and pt-find, two Percona Toolkit tools. We focus on the common usage of the tools and the gotchas we ran into using them.
News/Events/Feedback
Conferences:
MySQL Innovation Day Schedule Tuesday June 5th, Redwood Shores, CA. Register here (free). Content will be available via live stream, so save the date!
Some of our users have encountered problems with establishing a connection over the tunnel.php script despite the fact that the tunnel.php script is installed correctly. The following error message occurs:
‘Can’t connect to MySQL server on ‘your.sitename.com’ (10061): Authentication failed.’
The problem appears to be with cached proxy servers and will be fixed in one of the next builds of our product.
Currently it can be fixed by replacing the dbforgemysql.exe.config file, that can be found in dbForge Studio for MySQL installation folder, with the attached one.
Today on MySql Forums, there was a question in the newbie section about two users — I have a doubt on db host and db user relationship . What does this mean ?
name host tom % joe 127.0.0.1New DBAs are often confused by the quirky methods of authentication that MySQL uses. Heck, extremely experienced MySQL DBAs can get confused.
From the manual, 6.2.4. Access Control, Stage 1: Connection Verification
[Read more...]When you attempt to connect to a MySQL server, the server accepts or rejects the connection based on your identity and whether you can verify your identity by supplying the correct
I was lucky enough to get my hands on new Fusion-io ioDrive2 Duo card. So I decided to run the same series of tests I did for other Flash devices. This is ioDrive2 Duo 2.4TB card and it is visible to OS as two devices (1.2TB each), which can be connected together via software RAID. So I tested in two modes: single drive, and software RAID-0 over two drives.
I should note that to run this card you need to have an external power, by the same reason I mentioned in the previous post: PCIe slot can provide only 25W power, which is not enough for ioDrive2 Duo to provide full performance. I mention this, as it may be challenge for some servers: some models may not
Circus Oraclimus is back in town. It was last time only four weeks ago and a few months before that as well, and while it the first few times was quite funny to watch the clowns, it does not remain funny to see the same absurd tricks over and over again.
I am referring to this: http://bugs.mysql.com/bug.php?id=56889.
If you have both MySQL 5.5 and 5.6 installed on the same Windows system using the .msi installer you cannot upgrade 5.5. The 5.5 installer refuses to run claiming that a ‘newer version’ [of 5.5.x] is installed. It never was – and still is not – a problem upgrading 5.1. with 5.5 (and 5.6) installed. So this is a bug and it is verified. And it was actually fixed by Vladislav Vaintrub before he left Oracle for Monty Program 1½ years ago. For some reason the
[Read more...]
Installing Apache2 With PHP5 And MySQL Support On Ubuntu 12.04 LTS (LAMP)
LAMP is short for Linux, Apache, MySQL, PHP. This tutorial shows how you can install an Apache2 webserver on an Ubuntu 12.04 LTS server with PHP5 support (mod_php) and MySQL support.
In April, I got to give a talk at Percona Live, about why The Right Read Optimization is Actually Write Optimization. It was my first industry talk, so I was delighted when someone in the audience said “I feel like I just earned a college credit.”
Box offered to host everyone’s slides from the conference here (mine is here). A big thanks from me to Sheeri Cabral, for
[Read more...]| Showing entries 1 to 30 of 26687 | Next 30 Older Entries |