Not much to add really to the bug I’ve filed here: bug#67099.
I personally can think of some very nasty consequences of applying this on the slaves I manage, and the reason I’m posting the bug is that while I guess this is too late to fix in 5.6 as it’s effectively a new feature, I’m sure many sites may bump into this and be somewhat disappointed if they want to use the new GTID feature and have several slaves. Hence, if the fix/feature has to go into MySQL 5.7 then I hope it goes in sooner rather than later. We will see.
When InnoDB compresses a page it needs the result to fit into its predetermined compressed page size (specified with KEY_BLOCK_SIZE). When the result does not fit we call that a compression failure. In this case InnoDB needs to split up the page and try to compress again. That said, compression failures are bad for performance and should be minimized.
Whether the result of the compression will fit largely depends on the data being compressed and some tables and/or indexes may contain more compressible data than others. And so it would be nice if the compression failure rate, along with other compression stats, could be monitored on a per table or even on a per index basis, wouldn't it?
This is where the new INFORMATION_SCHEMA table in MySQL 5.6 kicks in. INFORMATION_SCHEMA.INNODB_CMP_PER_INDEX provides exactly this helpful information. It contains the
Ever since its first release, we are continuing consolidating and developing InnoDB Full-Text Search feature. There is one recent improvement that worth blogging about. It is an effort with MySQL Optimizer team that simplifies some common queries’ Query Plans and dramatically shorted the query time. I will describe the issue, our solution and the end result by some performance numbers to demonstrate our efforts in continuing enhancement the Full-Text Search capability.
As we had discussed in previous Blogs, InnoDB implements Full-Text index as reversed auxiliary tables. The query once parsed will be reinterpreted into several queries into related auxiliary tables and then results are merged and consolidated to come up with the final result. So at the end of the query, we’ll have all matching records on hand, sorted by their[Read more...]
Read the original article at Anatomy of a Performance Review
A lot of firms come to us with a specific scalability problem. “Our user base is growing rapidly and the website is falling over!” Or they’re selling more widgets, “Our shopping cart is slowing down and we’re seeing users abandon their purchases”. These are real startup growing pains, so what to do?
We like to take a measured approach with these types of challenges, so we thought it would be helpful to run through a hypothetical scenario and see how we work.
Having trouble with scalability? Check out our[Read more...]
For some of you who situated near New York City I am happy to announce that you could attend two events related to leading Full-Text search engines in open source – Sphinx Search.
First meeting organized by NYPHP meetup on Tuesday, September 25th at IBM, 590 Madison Avenue, New York. I’ll be speaking about search services in cloud environment and distributed search tips and tricks. Event is free, please RSVP.
One week later on October 1st, I’ll be doing tutorial about MySQL and Sphinx “[Read more...]
This week we present the Sphinx Storage Engine. Ear Candy is a gotcha about setting variables in the configuration file, and At the Movies is a video about MariaDB.
Tomorrow, August 22 at 10:00am PDT, I’ll present a webinar called Full Text Search Throwdown. This is a no-nonsense performance comparison of solutions for full text indexing for MySQL applications, including:
I’ll compare the performance for building indexes and querying indexes.
If you’re developing an application with text search features, this will be a very practical and informative overview of your technology options!
Register for this free webinar at http://www.percona.com/webinars/2012-08-22-full-text-search-throwdown
Read the original article at 31 Essential Blogs for Startups & Scalability
So many blogs, so little time! Here’s our list of the best we’ve found. Currently our favorite reader is Pulse pictured left. Starting to play around with flipboard too.
One of the original tech blogs, that still covers lots of breaking news, and difficult topics. Very technical, with probing commentary. Beware the actual comments though, as they’re often full of immature
I work on weird and challenging performance issues in the cloud, often deep inside the operating system kernel. These have made for some interesting blog posts in the past, but there’s a lot more I don’t have time to share in that much detail. Here, I’ve summarized ten recent issues that myself and the other engineers at Joyent have worked on. I’d love to see similar summaries from other performance experts worldwide – it’s useful to see the types of issues people are working on and the tools they are using.# Target Analyzed Key Tool Fixed [Read more...]
With its distributed, shared-nothing, real-time design, MySQL Cluster (http://www.mysql.com/products/cluster/) has attracted a lot of attention from developers who need to scale both read and write traffic with ultra-low latency and fault-tolerance, using commodity hardware. With many proven deployments (http://www.mysql.com/customers/cluster/) in web, gaming, telecoms and mobile use-cases, MySQL Cluster is certainly able to meet these sorts of requirements.
But, as a distributed database, developers do need to think a little differently about data access patterns along with schema and query optimisations in order to get the best possible performance.
Sharing best practices developed by working with MySQL Cluster's largest users, we recently ran a Performance Essentials webinar[Read more...]
A while ago I had a discussion with someone about the future of server infrastructure. Among other things, we were wondering whether companies will continue to run on dedicated servers or if eventually everyone just ends up in a Cloud environment. During the discussion I raised a point that in principle Cloud is a great idea that will keep attracting more and more people, but it is missing one important piece that stops many from using it – a high performance storage. Apparently, this has just changed.
Yesterday I received an e-mail announcing a new EC2 instance type – hi1.4xlarge. It features 16 logical CPUs (35 ECUs), 60GB of RAM, and… two 1TB SSD-based disk volumes! These are great specs that should work for nearly any database. Even assuming someone has a MySQL database larger than 2TB, not all tables will[Read more...]
Engineering threads within a Data NodeA new version of the white paper “Guide to Optimizing Performance of the MySQL Cluster Database (http://www.mysql.com/why-mysql/white-papers/mysql_wp_cluster_performance.php" target="_blank)” has been released; download it here (http://www.mysql.com/why-mysql/white-papers/mysql_wp_cluster_performance.php" target="_blank).
This paper steps you through:
I’m leaving myself some room for bug fixes. It works for us in house. I would love to help others to give it a try. especially those who could benefit from making nearly immediately answered queries to the NIST’s NVD database.
The code in this release cannot by itself track the feed from the feds in real time. The nvd entry loader needs a little bit of love in the area of record merging before this starts working. It’s on my TODO list.
I’m sorry for the outage of git.colliertech.org. I’ll get that back up here shortly. In the meantime, feel free to grab it from this location while the CPAN indexes and processes my submission.
don’t forget to check the cryptographic signature:[Read more...]
Can OLTP database workloads use Amazon S3 as primary storage? Now they can, thanks to the Cloud Storage Engine (ClouSE), but the question is: how fast?
To answer the question about performance, we decided to run db_STRESS benchmark on a MySQL database in Amazon EC2. We compared 3 configurations:
MySQL Cluster partitioning keyI’ll be presenting a webinar covering MySQL Cluster performance on Thursday, July 26. As always, the webinar will be free but you’ll need to register here (http://www.mysql.com/news-and-events/web-seminars/display-719.html" target="_blank) – you’ll then also receive a link to the charts and a recording of the session after the event. [Read more...]
The other day at PSCE I worked on a customer case of what turned out to be a problem with poor data locality or a data fragmentation problem if you will. I tought that it would make a good article as it was a great demonstration of how badly it can affect MySQL performance. And while the post is mostly around MyISAM tables, the problem is not really specific to any particular storage engine, it can affect a database that runs on InnoDB in a very similar way.The problem
MyISAM lacks support for clustering keys or even anything remotely similar. Its data file format allows new information to be written anywhere inside a table. Anywhere can be either at the end of a file where it can be simply appended or an empty space somewhere in the middle left after previously deleted row(s). This implies no[Read more...]
Have just read INSERT, Don’t DELETE by Aaron Brown, and have some lengthy response, which is why I write this post instead of commenting on said post.
I wish to offer my counter thought and suggest that DELETEs are probably the better choice.
Aaron suggests that, when one wishes to purge rows from some table, a trick can be used: instead of DELETEing unwanted rows, one can INSERT "good" rows into a new table, then switch over with RENAME (but please read referenced post for complete details).
I respectfully disagree on several points discussed.
The fact one needs to block writes during the time of creation of new table is problematic: you need to essentially turn off parts of your application. The posts suggests one could[Read more...]
This week we talk about NULLs and how to find the right data type.
MySQL Connect will be held in San Francisco on Saturday September 29th and Sunday September 30th. This is a technical conference about MySQL, bringing together MySQL engineers at Oracle and the MySQL Community, and will include sessions about the latest MySQL features and roadmaps. The[Read more...]
Cloud-powered BLOB type provides ACID guarantees and fast direct access to blobs via Web URLs.
Typically unstructured data (such as pictures, media files, documents)
a) Is either stored on the file system, unlike the related with it relational data which is stored in the database. This is well known, “convenient” practice that allows fast access to files but offers no transactional story and no unified data management (for db and filesystem)
b) Or is stored in BLOBs. This ensures transactional consistency and reduces management complexities, but is really bad for performance and scalability.
We took advantage of the cloud, and came up with an upgrade to the BLOB – a solution that combines the benefits of the two.
Weblob is a new data type that is[Read more...]
In current code, a (group) commit to InnoDB does not less than three
This article describes how I tuned MariaDB to give the best write throughput with SSD based storage.
When you have a write-heavy application writing into InnoDB, you will probably experience the InnoDB Checkpoint Blues. The effect manifests as stalls – short periods of time where the troughput falls to zero and I/O activity goes crazy. The phenomenon is well known and described i.e. here. More background about checkpointing can be found[Read more...]
A few days ago MariaDB, MySQL and Percona all three released new versions of the 5.5 server. So I decided it’s time to run sysbench once more and compare the OLTP performance. The test candidates are:
For the benchmarks I used our trusty old pitbull machine which has 24 cpu cores, 24G of RAM and a nice RAID-0 composed of 3 SAS SSD.
The benchmark was sysbench-0.5 multi-table OLTP, using 8 tables with total 10G of data. InnoDB buffer pool was 16G, InnoDB log group capacity 4G (the maximum for MySQL). The mysqld process was constrained to 16 cores, sysbench to the other 8.
The only nonstandard tuning was for I/O scalability: innodb_io_capacity = 20000 and innodb_flush_neighbor_pages = none (for Percona Server[Read more...]