Oklahoma City MySQL Group Forming
I’m excited to learn, just one day before the event, that Oklahoma City has formed a MySQL User Group. OKCMySQL.org
July 23, 2014 will be the inaugural meet-up for the OKC MySQL user group. As a special guest speaker, Peter Zaitsev (CEO of Percona and co-author of High Performance MySQL) will be giving a presentation on “Best Indexing Practices“, followed by a Q/A session.
Time is very short. RPVP on Meetup.
I hope to see you at The Paramount – 701 West Sheridan, Oklahoma City, OK (map).
Inaugural Meet-up Oklahoma City MySQL Meetup
The inaugural Meet-up Oklahoma City MySQL Meetup is Wednesday, July 23, 2014!
As a special guest speaker, Peter Zaitsev (CEO of Percona and co-author of High Performance MySQL) will be giving a presentation on “Best Indexing Practices”, followed by a Q/A session.
Please RSVP if you plan to attend so we can get a good headcount for food and beverages that will be provided and as always, please spread the word to friends or colleagues in the industry.
It should be a great night and great first event for the MySQL user group in OKC!
Reference architecture for a write-intensive MySQL deployment
We designed Percona Cloud Tools (both hardware and software setup) to handle a very high-intensive MySQL write workload. For example, we already observe inserts of 1bln+ datapoints per day. So I wanted to share what kind of hardware we use to achieve this result.Let me describe what we use, and later I will explain why.Server:Chassis: Supermicro SC825TQ-R740LPB 2U Rackmount ChassisMotherboard: Supermicro X9DRI-F dual socketCPU: Dual Intel Xeon Ivy Bridge E5-2643v2 (6x 3.5Ghz cores, 12x HT cores, 25M L3)Memory: 256GB (16x 16GB 256-bit quad-channel) ECC registered DDR3-1600Raid: LSI MegaRAID 9260-4i 4-port 6G/s hardware RAID controller, 512M bufferMainStorage: PCIe SSD HGST FlashMAX II 4.8TBSecondary Storage (OS, logs): RAID 1 over 2x 3TB hard drivesSoftware:OS: Ubuntu 12.04 LTSMySQL: Percona Server 5.6.19 + TokuDBBackup: LVM partitions + mylvmbackupWhen selecting hardware for your application, you need to look at many aspects – typically you’re looking for a solution for which you already have experience in working with and has also proved to be the most efficient option. For us it has been as follows:Cloud vs Bare Metal We have experience having hardware hosted at the data center as well as cash for upfront investments in hardware so we decided to go for physical self-hosted hardware instead of the cloud. Going this route also gave us maximum flexibility in choosing a hardware setup that was the most optimal for our application rather than selecting one of the stock options.Scale Up vs Scale Out We have designed a system from scratch to be able to utilize multiple servers through sharding – so our main concern is choosing the most optimal configuration for the server and provisioning servers as needed. In addition to raw performance we also need to consider power usage and overhead of managing many servers which typically makes having slightly more high-end hardware worth it.Resource Usage Every application uses resources in different ways so an optimal configuration will be different depending on your application. Yet all applications use the same resources you need to consider. Typically you want to plan for all of your resources to be substantially used – providing some margin for spikes and maintenance.CPUOur application processes a lot of data and uses the TokuDB storage engine which uses a lot of CPU for compression, so we needed powerful CPUs.Many MySQL functions are not parallel, think executing single query or Alter table so we’re going for CPU with faster cores rather than larger amount of cores. The resulting configuration with 2 sockets giving 12 cores and 24 threads is good enough for our workloads.Lower end CPUs such as Xeon E3 have very attractive price/performance but only support 32GB of memory which was not enough for our application.MemoryFor database boxes memory is mainly used as a cache, so depending on your application you may be better off investing in memory or storage for optimal performance. Check out this blog post for more details.Accessing data in memory is much faster than even on the fastest flash storage so it is still important. For our workload having recent data in memory is very important so we get as much “cheap” memory as we can populating all 16 slots with 16GB dimms which have attractive cost per GB at this point.Storage There are multiple uses for the storage so there are many variables to considerBandwidthWe need to be able access data on the storage device quickly and with stable response time. HGST FlashMax II has been able to meet these very demanding needs.EnduranceWhen using flash storage you need to worry about endurance – how much beating with writes flash storage can handle before it wears out. Some low cost MLC SSDs would wear out in the time frame of weeks if being written with maximum speed. HGST FlashMax II has endurance rating of 10 Petabytes written (for a random workload) – 30 Petabytes written (for a sequential workload)We also use TokuDB storage engine which significantly reduces amount of writes compared to Innodb.DurabilityDoes the storage provide true durability with data guaranteed to be persisted when write is acknowledged at the operating system level when power goes down or is loss possible? We do not want to risk database corruption in case of power failure so we were looking for storage solution which guarantees durability. HGST FlashMax II guarantees durability which has been confirmed by our stress tests.SizeTo scale application storage demands you need to scale both number of IO operations storage can handle and storage size. For flash storage it is often the size which becomes limiting factor. HGST FlashMax II 4.8 TB capacity is best available on the market which allows us to go “All Flash” and achieve very quick data access to all our data set.Secondary StorageNot every application need requires flash storage properties.We have secondary storage with conventional drives for operating system and logs. Sequential read/write pattern works well with low cost conventional drives and also allow us to increase flash life time, having it handling less writes.We’re using RAID with BBU for secondary storage to be able to have fully durable binary logs without paying high performance penalty.Why PCIe SSD over SATA SSD? There are arguments that SATA SSD provides just a good enough performance for MySQL and there is no need for PCIe. While these arguments are valid in one dimension, there are several more to consider.First, like I said PCIe SSD still provides a best absolute response time and it is an important factor for an end user experience in SaaS systems like Percona Cloud Tools. Second, consider maintenance operations like backup, ALTER TABLES or slave setups. While these operations are boring and do not get as much attention as a response time or throughput in benchmarks, it is still operations that DBAs performs basically daily, and it is very important to finish a backup or ALTER TABLE in a predictable time, especially on 3-4TB datasize range. And this is where PCIe SSD performs much better than SATA SSDs. For SATA SSD, especially bigger size, write endurance is another point of concern.Why TokuDB engine? The TokuDB engine is the best when it comes to insert operations to a huge dataset, and few more factors makes it a no-brainer:TokuDB compression is a huge win. I estimate into this storage ( FlashMAX II 4.8TB) we will fit about 20-30TB of raw data.TokuDB is SSD friendly, as it performs much less data writes per INSERT operation than InnoDB, which greatly extends SSD (which is, well, expensive to say the least) lifetime.The post Reference architecture for a write-intensive MySQL deployment appeared first on MySQL Performance Blog.
Announcing ClusterControl Support for MariaDB 10
July 22, 2014
We just wanted to make it official: Severalnines ClusterControl now supports MariaDB 10!
As most of you know will know by now, MariaDB 10 is the newest and most advanced version of the popular MariaDB relational database system. Whilst remaining application-compatible with the MySQL database, it adds many new capabilities to address the most challenging web and enterprise application use cases. Cluster deployments would be based on MariaDB Galera Cluster 10, which is a complete merge of MariaDB 10.0.12 and Galera Cluster.
ClusterControl for MariaDB Clusters
ClusterControl gives you the power to deploy, manage, monitor and scale entire MariaDB Galera clusters efficiently and reliably. ClusterControl acts as a virtual DBA assistant and frees system administrators and developers from the complexity and learning curves associated with database clusters.
MariaDB European Roadshow - Summer 2014
We had the opportunity to showcase ClusterControl for MariaDB 10 recently during the European MariaDB Roadshow, which took us to Helsinki, Amsterdam, Paris, Hamburg, Berlin and Frankfurt. We were delighted of course to be invited by SkySQL as guest speakers and to have the opportunity to talk to existing and future MariaDB users.
There is one more date scheduled at the moment for this roadshow - London in September.
Joint live webinar: Management & Automation of MariaDB Galera Clusters
If you’ve missed the European Roadshow and/or would like a more in-depth presentation and demo of ClusterControl, please do register for our joint webinar with SkySQL on 30th September 2014.
#DBHangOps 07/24/14 -- More Indexing!
#DBHangOps 07/24/14 -- More Indexing!
Join in #DBHangOps this Thursday, July, 24, 2014 at 11:00am pacific (18:00 GMT), where we pick up on our last conversation about indexing:
More discussion on geo spatial indexes
Fulltext indexing and ranking
MariaDB indexing features (From Gerry!)
Anything else indexing!
Be sure to check out the #DBHangOps twitter search, the @DBHangOps twitter feed, or this blog post to get a link for the google hangout on Thursday!
See all of you on Thursday!