|
|
|
Posted
9 days
ago
by
Mikael Ronström
As many have noted we have released another milestone release of MySQL Cluster 7.3. One of the main features of 7.3 is obviously foreign keys. In this post I am going to describe one more feature added to MySQL Cluster in the second milestone release
... [More]
which is called Connection Thread Scalability.http://dev.mysql.com/tech-resources/articles/cluster-7.3-dmr2.htmlAlmost all software designed for multithreaded use cases in the 1990s have some sort of big kernel mutex, as a matter of a fact this is also true for some hyped new software written in this millenium and even in this decade. Linux had its big kernel mutex, InnoDB had its kernel mutex, MySQL server had its LOCK_open mutex. All these mutexes are characterized by the fact that these mutexes protects many things that often have no connection with each other. Most of these mutexes have been fixed by now, the Linux big kernel mutex is almost gone by now, the LOCK_open is more or less removed as a bottleneck, the InnoDB kernel mutex has been split into ten different mutexes.In MySQL Cluster we have had two types of kernel mutexes. In the data nodes the "kernel mutex" was actually a single-thread execution model. This model was very efficient but limited scalability. In MySQL Cluster 7.0 we extended the single threaded data nodes to be able to run up to 8 threads in parallel instead. In MySQL Cluster 7.2 we extended this to enable support of up to 32 or more threads.The "kernel mutex" in the NDB API we call the transporter mutex. This mutex meant that all communication from a certain process, using a specific API node, used one protected region that protected communication with all other data nodes. This mutex could in some cases be held for substantial times.This has meant that there has been limitation on how much throughput can be processed using one API node. It has been possible to process much throughputanyways using multiple API nodes per process (this is the configuration parameter ndb-cluster-connection-pool).What we have done in MySQL Cluster 7.3 is that we have fixed this bottleneck. We have split the transporter mutex and replaced it with mutexes that protects sending to a specific data node, mutexes that protects receiving from a certain data node, mutexes that protect memory buffers and mutexes that protect execution on behalf of a certain NDB API connection.This means a significant improvement of throughput per API node. If we run a benchmark with just one data node using the flexAsynch benchmark that handles around 300-400k transactions per second per API node, this improvement increases throughput by around 50%. A Sysbench benchmark for one data node is improved by a factor of 3.3x. Finally a DBT2 benchmark with one data node is improved by a factor of 7.5x.The bottleneck for an API node is that only one thread can process incoming messages for one connection between an API node and a data node. For flexAsynch there is a lot of processing of messages per node connection, it's much smaller in Sysbench and even smaller in DBT2 and thus these benchmarks see a much higher improvement due to this new feature.If we run with multiple data nodes the improvement increases even more since the connections to different data nodes from one API node are now more or less independent of each other.The feature will improve performance of applications without any changes of the application, the changes are entirely done inside the NDB API and thus improve performance both of NDB API applications as well as MySQL applications.It is still possible to use multiple API nodes for a certain client process. But the need to do so is much smaller and in many cases even removed. [Less]
|
|
Posted
10 days
ago
by
Todd Farmer
The MySQL general query log can be a useful debugging tool, showing commands received from clients. In versions through MySQL 5.5, you could count on the GQL to log every command it received – the logging happened before parsing. That can
... [More]
be helpful – for example, the GQL entries might have records of somebody unsuccessfully attempting to exploit SQL injection vulnerabilities that result in syntax exceptions.
Here’s a sample, which I’ll run in both 5.5 and 5.6 and show the resulting GQL:
mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)
mysql> SELECT NOTHING();
ERROR 1305 (42000): FUNCTION NOTHING does not exist
mysql> SELECT 2;
+---+
| 2 |
+---+
| 2 |
+---+
1 row in set (0.00 sec)
In 5.5, this produces the following in the general query log:
130513 18:26:34 1 Query SELECT 1
130513 18:26:40 1 Query SELECT NOTHING()
130513 18:26:44 1 Query SELECT 2
In 5.6, the same produces the following:
130425 21:53:25 1 Query SELECT 1
130425 21:53:35 1 Query SELECT 2
The behavior hasn’t changed between 5.5 and 5.6 with respect to successfully-parsed, but unauthorized statements. With the limited-privilege anonymous user account, I issued the following against both 5.5 and 5.6 servers:
mysql> SHOW GRANTS;
+--------------------------------------+
| Grants for @localhost |
+--------------------------------------+
| GRANT USAGE ON *.* TO ''@'localhost' |
+--------------------------------------+
1 row in set (0.00 sec)
mysql> SELECT * FROM mysql.user;
ERROR 1142 (42000): SELECT command denied to user ''@'localhost' for table 'user'
The general query log for both 5.5 and 5.6 recorded the attempt to SELECT from mysql.user system table:
130513 18:31:10 3 Query SHOW GRANTS
130513 18:31:11 3 Query SELECT * FROM mysql.user
The documentation doesn’t explicitly note this behavior change (I filed Bug#68937 to include this in the manual) – it talks about the password-masking feature which triggered this behavioral change, though (and this page also documents which statements are rewritten). In order to mask passwords in log files, the log entries have to be written after they are parsed. When I issue the following statement in 5.6, the password is masked in the general query log:
mysql> SET PASSWORD = PASSWORD('test');
Query OK, 0 rows affected (0.00 sec)
Here’s the corresponding general query log entry:
130513 18:45:59 2 Query SET PASSWORD FOR `root`=<secret>
That’s much appreciated behavior – there’s typically no reason to expose passwords in logs. For those who do need this temporarily for diagnostic purposes, there’s a –log-raw option which logs before parsing, just like in 5.5. This means that plain-text passwords as well as statements with syntax errors get logged. Here’s the result in 5.6 with –log-raw enabled:
130513 18:43:10 1 Query SELECT NOTHING()
Unfortunately, there’s no status variable to tell a DBA whether or not they are protected by the new 5.6 behavior, or whether the running server has been started with –log-raw to override it and is still logging plain-text passwords. I filed Bug#68936 to address that. I would also love to allow users (with appropriate permissions) the ability to change this configuration option without restart of MySQL Server, but it’s probably not something that will need – or want – to be changed in a production environment where downtime is critical.
I’m happy to see plain-text passwords removed from logs in 5.6, and hope that this post helps clarify associated behavioral changes related to the general query log in 5.6.
[Less]
|
|
Posted
10 days
ago
by
Justin Swanhart
tl;dr: Make sure you flash an LSI-9211 to IT firmware rev#14 to get it to work with Linux and SSD trim. You may have to downgrade from newer firmware to older firmware to get the card to work. Finding a SATA III controller with more
... [More]
than one PCI-e laneAfter a recent hardware issue I decided to upgrade my computer to use new Intel 520 120MB SSD drives in RAID for improved performance. The motherboard I use (an ASUS Rampage III extreme) has a Marvel SATA III controller with two ports, but I discovered that it is connected via only a single PCI-e lane (each lane can do at most 400MB/sec*). This means that it can't effectively support even a single Intel 520 because one device can saturate the SATA III bus (An Intel 520 is rated at up to 550MB/sec sequential write).So I went on a quest for a new SATA 3 controller. To Frys! I exclaimed. But unfortunately, all the PCI-e 2.x SATA III controllers used a single lane! (These cards also feature RAID which is a double wtf). Well, having been thwarted by my Frys run (I could have upgraded the motherboard, but I want to wait for the next generation of processors with DDR4 ram) I went to Central Computers in Mountain View. There I found LSI PCI-e HBAs. LSI HBAs use 8 PCI-e lanes. I decided to get an LSI 9211-8i for a little less than they sell on NewEgg. This is a RAID controller, and I specifically told the technician that I wanted a RAID controller that supported TRIM and he said LSI supports trim**. So I laid down my money and headed home.Strange Windows 7 Professional 64 bit performanceI booted into Windows 7 and performance on AS-SSD (a simple benchmark utility targeted at SSD performance) for the drive was horrible. So horrible the test could not complete. During the test the performance is reported to be pegged at 98 IOPS. To compare, the same test, same drive, same OS, but different card: 13000 IOPS. 13K IOPS is low for the drive but it was an old sata 2 card. Regardless, it shows the drive can do way better than the 98 IOPS that the LSI card reported while attempting to complete the test. I messed around with this for quite awhile, pulling out hair. I eventually tried upgrading the card to R16 firmware. Same issue. I figured that maybe there was some sort of windows weirdness so I decided to test Linux. I have contacted LSI about the issue with AS-SSD to see if there is possibly a driver issue to blame.Linux and the case of the mysterious hanging mkfs.ext4I installed CentOS 6.4 minimal on another 32GB OCZ SSD I had lying around. CentOS found the LSI card just fine. I could run a `dd` on the Intel 520 drives and they could do 500MB/sec+ sequential write. Great. Next I tried to format a filesystem on the device using `mkfs.ext4`. ext4 now supports TRIM and will discard all of the blocks on a target when it creates a new filesystem. However, when I tried to create a filesystem with `mkfs.ext4`, it would simply hang at 0/X bytes discarded. Eventually the kernel started printing messages like "attempting task abort!" and "task abort: SUCCESS scmd" into the log. There was no way to kill `mkfs.ext4`. I did some searching and found an old post to the OCZ forum about problems with an OCZ Vertex 3, which is sandforce based, same as the Intel 520. When I was doing my tests, my Vertex 3 was on another SATA bus, isolated from the LSI so I think this may be SANDFORCE related. If you didn't read the post, that user upgraded to firmware R14 to fix the problem. Unfortunately I had already upgraded to R16. Fixing the mkfs.ext4 issueI decided to try to downflash to R14. But of course, the flash utility won't let you downgrade by default. However, I found a workaround by using a VERY DANGEROUS mode on the controller: RESET MODE. In order to use this function you must use the DOS flash method. I created a USB FreeDOS boot disk. The flash method may work from UEFI but UEFI is a PITA. It doesn't work from the Linux flash utility or the Windows flash utility.
To place the card in RESET MODE:
DO NOT REBOOT WITH THE CARD IN THIS STATE OR YOU WILL HAVE TO RMA THE CARD!!!!!
C:\sas2flsh.exe -o -e 6
Now you must flash the new firmware AND bios:
C:\>sas2flsh.exe -o -f FIRMWARE.bin -b BIOS.rom
It is safe to reboot after flashing
With the LSI card flashed down to R14 I was able to create a filesystem on the device, but TRIM was not working. /sys/block/sda/queue/discard_max_bytes was 0. It turns out only LSI HBA initiator devices support trim. So you can't use RAID and TRIM on this card or even TRIM on non-RAID devices (JBOD). Sales guy sold me another lemon. Oh well, software raid 0 isn't a large overhead. Getting rid of raid (flash to IT mode)Oh bugger, sas2flsh won't let you switch from IR (RAID) to IT (INITIATOR) firmware either without going into RESET mode. Follow the same instructions above, but RESET the card then flash the IT firmware instead of IR. The BIOS is the same in either case.Final setupI'm doing software RAID1 over a portion of the two Intel, swap on a portion of the OCZ, and software RAID0 over the remainder of all three. This gives me a safe area for my OS and important files, a large high performance database test area, and the swap on the OCZ will be regarded as unused by TRIM. This will improve garbage collection on the OCZ which was in use for some time before I bought the 520s.* You will see it quoted as 5GT/s and/or 5Gbit/sec but that is before 8b/10b encoding is applied for the data transfer over the bushttp://en.wikipedia.org/wiki/8b/10b_encoding** Turns out they only support trim for HBA Initiators, not HBA RAID. You can flash to IT firmware though. See the post.*** This post isn't MySQL specific. [Less]
|
|
Posted
10 days
ago
by
Stewart Smith
I wonder how much longer the ARCHIVE storage engine is going to ship with MySQL…. I think I’m the last person to actually fix a bug in it, and that was, well, a good number of years ago now. It was created to solve a simple problem: write
... [More]
once read hardly ever. Useful for logs and the like. A zlib stream of rows in a file.
You can actually easily beat ARCHIVE for INSERT speed with a non-indexed MyISAM table, and with things like TokuDB around you can probably get pretty close to compression while at the same time having these things known as “indexes”.
ARCHIVE for a long time held this niche though and was widely and quietly used (and likely still is). It has the great benefit of being fairly lightweight – it’s only about 2500 lines of code (1130 if you exclude azio.c, the slightly modified gzio.c from zlib).
It also use the table discovery mechanism that NDB uses. If you remove the FRM file for an ARCHIVE table, the ARCHIVE storage engine will extract the copy it keeps to replace it. You can also do consistent backups with ARCHIVE as it’s an append-only engine. The ARCHIVE engine was certainly the simplest example code of this and a few other storage engine API things.
I’d love to see someone compare storage space and performance of ARCHIVE against TokuDB and InnoDB (hint hint, the Internet should solve this for me). [Less]
|
|
Posted
10 days
ago
by
Joshua Prunier
I configure all our master databases to use row-based binary logging where I work. In my opinion it is a much safer option than row-base replication. The advantages and disadvantages of both types of MySQL replication are detailed in the online
... [More]
documentation here. You can't view the events a slave is applying directly with 'show processlist' but by issuing 'show open tables where in use' you can detect what table is receiving the attention of the SQL thread. If you need more information the mysqlbinlog command must be used to decode the slaves relay logs or masters binary logs.Our developers often change a lot of rows with a single update statement. This usually results in some reasonable replication lag on downstream slaves. Occasionally the lag continues to grow and eventually nagios complains. Investigating the lag I sometimes discover the root of the problem is due to millions of rows updated on a table with no primary key. Putting a primary key constraint on a table is just good practice, especially on InnoDB tables. This is really necessary for any large table that will be replicated.To show what replication is actually doing I have cloned the sakila.film table, omitting the indexes, inserted all its rows and finally updated the description column for all 1,000 rows in the new table.I have edited the output of mysqlbinlog to only show the entries related to the first row created by the insert and update statements above. The @ symbol followed by a number is a mapping to the columns in the table. So @1=film_id, @2=title, @3=description and so on. Note that the update statement records a before and after picture of the row. This is can be used in a pinch to fix mistaken updates if the damage is small instead of having to restore from backups.So row-based replication is performing as named and creating a binary log event for each row affected. My single insert and update statement on the master then became 1,000 separate events on the slave.Digging in to the MySQL source code I was unable to confirm exactly how the SQL thread applies relay log events on a slave. I assume it is similar to what happens when a normal update statement is executed on a table with no indexes. The server must perform a full table scan to locate a single row. For a table with a million plus rows a million full table scans is expensive! A primary key or suitable unique index will prevent this type of problem. [Less]
|
|
Posted
10 days
ago
by
Dathan Pattishall
I recently went to MongoDB Days, a conference about everything MongoDB in SF. Starting my career as a Systems Programmer then Web Developer, MySQL DB[Admin|Architect], to Software|System Architecture I like to keep an open mind about new technology
... [More]
and trends. When you work with a lot of different languages, and technology you find out that it’s basically the same Science from about 40 years ago.An index in MongoDB is like an index in mySQL since a Btree is a Btree regardless of what application uses it. Just like with mySQL the best performance improvement for an application using MongoDB as a datastore is adding the correct indexes.To create an index in MongoDB:db.<tableName>.ensureIndex({ col#1:1, col#2:-1, col#3:1 }); // note 1 means ASC -1 means DESCMongoDB follows the same left-most-prefix rule meaningcol#1, col#2, col#3 is an indexcol#1, col#2 is an indexcol#1 is an indexcol#2, col#3 IS NOT AN INDEXSo, just like with mySQL for ONE compound index you get a total of THREE indexes if you follow the left-most-prefix rule, the columns from left to right (in order) in a compound index is an index.MongoDB also gets a performance boost by using Covering indexes just like mySQL. What is a Covering index? Instead of reading from the datapage (or document store for MongoDB) which exists on disk your reading the data from the index which should be in memory for the most part. A common practice is to follow the left-most-prefix pattern, then add the columns which you are returning at the end of the compound index. For instanceSELECT photoId from Photos WHERE userId=? AND dateCreate=? AND privacy=?The index in mySQL I would make isALTER TABLE Photos ADD INDEX `userId-dateCreate-privacy-photoId` (userId,dateCreate,privacy,photoId)Thus following the left-most-prefix of a compound index I have an index onuserId, dateCreate, privacy, photoIduserId, dateCreate, privacyuserId, dateCreateuserIdand a Covering Index satisfied by the query above.For mongoDB its the samedb.photos.ensureIndex({ userId: 1, dateCreate: 1, privacy: 1, photoId: 1});So, in conclusion, understand the Computer Science of a Btree, Hash, LinkedList and you will understand how indexes work across technology and find that essentially it's the same. More info on indexes for mySQL can be found here.Also note:Explain in mongoDB is your friend just like Explain in mySQL [Less]
|
|
Posted
10 days
ago
by
Tokuview Blog
At tomorrow’s Effective MySQL Meetup, I’ll be presenting “Fractal Tree Indexes : Theory and Practice (MySQL and MongoDB).” The meetup is at 6:30pm Tuesday, May 14, 2013, and will be held at Alley NYC in New York City. I’ll give
... [More]
an overview on how Fractal Tree® indexes work, and then get into specific product features that Fractal Trees enable in MySQL and MongoDB. Some benchmarking and customer use-cases will be discussed, but my intent is for this to be a deep technical dive. Several Tokutek Engineers will also be on hand, so bring any questions you’ve got.
I hope to see you there! [Less]
|
|
Posted
10 days
ago
by
Anders Karlsson
Yes, finally! This took some time, but I have been so busy with other things, work-related as well as domestic, that I just haven't had the time for this. But finally version 1.6 is available for download from sourceforge. The downloads is as usual
... [More]
the autoconf enabled source code and PDF documentation in PDF.So, what is new you ask, well there is one big new feature which took a lot more effort than I expected. When this program was written at first, I still have the table/use use in mind. What this means is that I visioned JSON objects to be mapped to a table. This is not how programmers view JSON, but this is how data is viewed in many databases, even NoSQL ones such as MongoDB. So I wanted an import tool for simple row-structured JSON objects.Now, there is a different way to look at things, which is to see the data in the JSON file as objects, and each member as one or more rows in a table. This sort of makes up an object (yes, this is very simplistic, but you get the point). So data might look like this:[{"nodename": "server1" "users": [{"id": "joe", "name": "Joe bloggs"}, {"id": "sue", "name": "Sue Bloggs"} ], "hosts" [{"name"; "internal", "address": "192.168.0.78"}, {"name": "external", "address": "11.186.19.177"} ] },{"nodename": "server2" "users": [{"id": "dick", "name": "Rickard Bloggs"} ], "hosts" [{"name"; "internal", "address": "192.168.0.75"}, {"name": "external", "address": "11.186.19.161"} ] }]Here we would be loading into tables users and hosts and we would load some 7 rows in those two tables. I think what is also clear is that there is a whole bunch of stuff here to make this smarter, like other fields of the object affecting the data that is loaded, either being added to the data or to filter what data is loaded. But none of that is in place right now, for this version, this is just a simple object to table load. The old row-by-row formats are still supported (plain JSON format and Array format).Also, something cool to add is to add support for MariaDB dynamic colums. I have some ideas here, but I have yet to write the code.In addition, this release adds a --dry-run option has been added, which allows you to test config files and settings, before starting to load.I'm planning to write more about MySQL / MariaDB and JSON here eventually, and also about plain JSON, but for now, have fun, take care and happy SQLing./Karlsson [Less]
|
|
Posted
10 days
ago
by
Geoffrey Anderson
Heyo! Now that we’ve gone through the Percona Live MySQL Conference and Expo review and had an amazing turnout to talk about it, it’s time to open up the discussion around things that DBAs want to talk about and need to be
... [More]
conscientious of. Join us on Wednesday at 12:00pm PDT (19:00 GMT) to take part in the discussion and share your knowledge and experience with the following topics:
Filesystems and MySQL — Which do you use and why?
Do you handle I/O alignment? How do you do it?
Scheduler changes?
Nagios checks! — Any new checks you’ve added recently?
The worst settings in MySQL that you always change
What are the most important variables to you, and what do you set them to?
What will be important variables in 5.6 that you should set?
Be sure to check out the #DBHangops twitter search, the DBHangops Twitter Feed, or this blog post get a link for the google hangout tomorrow!
Looking forward to seeing you all there! [Less]
|
|
Posted
10 days
ago
by
podcast
This week we discuss the basics of using the performance schema in MySQL 5.5 and 5.6. Ear Candy is about a temporal gotcha when using dates and times that do not exist, and At the Movies is David Stokes giving some useful for System Administrators
... [More]
who also are in a DBA role.
News
Continuent's Tungsten Replicator is now completely open source
TokuDB is now open source
read more [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.