用户登录
内容聚合
新闻聚合器
Remote L2 Cache for Innodb - The Waffle Grid Project
A few months ago I was at dinner with Yves Trudeau discussing what all consultants discuss in the late hours after a long day of hard work… how to improve performance and scalability. I brought up an idea to him to utilize memcached as an L2 cache for innodb. At first he was skeptical, but as we talked he was more and more intrigued by the idea. The idea was simple, add a set to memcached when something hit the LRU… then issue a get from memcached when you do not find the data locally stored in the buffer pool but before you read from disk. Starting from that point work out any of the issues that would be sure to follow. So Yves continued emailing me asking me questions… then he sent me a note that he had made huge progress with the idea. Huge progress means that he wrote version 0.1 and had it working. That’s when the Idea really turned into a project.
We called it the Waffle Grid Project. Why? A waffle sort of looks like a grid diagram doesn’t it? And I like waffles, they happen to be very tasty. Having a working patch… We burned the midnight oil the last few weeks testing and fixing the code, building a proof of concept, and testing it. So does it work? Yes, it does. Pretty well in fact. Take a look at some of the benchmarks below for a better idea.
Basically what this patch enables you to do is have a central node ( standard run of the mill database ) with several servers acting as remote L2 cache. An example: 1 Main Mysql server with 128GB of memory, 4 remote servers with 64GB of memory each… giving you a L2 cache of about 256GB. With a fast private network the L2 cache should return data faster then can be retrieved off of disk.
Getting My Partitioning On
One of the problems I was trying to tackle was splitting up a logs table up by a date range. It seems like this would be a popular use for partitions since it replicates some of the functionality of the MERGE storage engine, only in most cases, does so better. The problem, however, I ran into was that it did not seem quite as trivial as I thought to partition by year and month. Partitioning by year seems easy - just use the year() function, but partitioning by year and month is far less clear. There's currently no YEARMONTH() function for instance (though there is a YEARWEEK() function) and, even if it was, partitions only support a sub-set of all the available MySQL functions, and CONCAT() is not one of them. So, you have to do things that are a bit more clever. Here is what I ended up with (as a test table): DROP TABLE IF EXISTS LoggerPartition; CREATE TABLE `LoggerPartition` ( `date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `date2` date NOT NULL, `session` char(32) DEFAULT NULL, `host` varchar(255) DEFAULT NULL, `sslmode` enum('enabled','disabled') DEFAULT 'enabled', `requesturi` varchar(255) DEFAULT NULL, `referer` varchar(255) DEFAULT NULL, `useragent` varchar(255) DEFAULT NULL, `remotehost` int(10) unsigned NOT NULL, KEY `date_idx` (`date`), KEY `session_idx` (`session`(16)) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 PARTITION BY RANGE (TO_DAYS(date2)) ( PARTITION p0 VALUES LESS THAN (733407), PARTITION p1 VALUES LESS THAN (733438), PARTITION p2 VALUES LESS THAN (733467), PARTITION p3 VALUES LESS THAN (733498), PARTITION p4 VALUES LESS THAN (733528), PARTITION p5 VALUES LESS THAN (733559), PARTITION p6 VALUES LESS THAN (733589), PARTITION p7 VALUES LESS THAN (733620), PARTITION p8 VALUES LESS THAN (733651), PARTITION p9 VALUES LESS THAN (733681), PARTITION p10 VALUES LESS THAN (733712), PARTITION p11 VALUES LESS THAN (733773) ); INSERT INTO LoggerPartition SELECT date, DATE(date), session, host, sslmode, requesturi, referer, useragent, remotehost FROM Logger; In this case, I was able to get things working by using the TO_DAYS() function, which returns the number of days since we switched from BC to DC (although it's not accurate until after the year 1582). This is nice because it allows one to define any partitioning strategy just by using the proper day ranges. The problem is that it's not very clear - looking at the partition list does not really provide any insight into what each partition is storing (2008-01-01 to 2009-01-01, with each partition representing one month, more or less).
Another issue I ran into is that you cannot use a TIMESTAMP field in the function. Well, actually you can, but I wasn't able to run a single query where MySQL used the right partition(s) - it, instead, used them all. I'm not sure why this was the case, but having a DATE field specifically (date2 in the above case) fixed that issue. On that note, one thing I am going to try is to store the year and month in an integer field, say, called, yearmonth which simply stored values like '200801'. Then I can also partition by range, only my values would be more humanely readable. Both these solutions involve storing more data, however. Ideally, I would have liked to see a solution which uses the TIMESTAMP.
Another peculiarity with partitioning is how PRIMARY KEYs are handled. I was a bit surprised to find out that the field being partitioned must be in the PK if one exists in the table. It sorta makes sense, but also it doesn't :) According to the documentation, this is something that is being worked on, and is something I would like to see resolved. For my use-cases thus far, however, it hasn't entered into the equation.
Aside from the bumps, I did find that partitioning can improve performance. I dropped around 40% off the execution time of my test query when using partitions versus using the date index. Not too shabby! Of course, I'll admit that these aren't official tests. In fact, I'll admit that my solution is likely not the best out there. It is what it is :) But I thought I would share it nonetheless. Hopefully someone out there gets some use out of it.
Marten Mickos at Sun CEC 2008: MySQL Sessions
MySQL Problem and Solution Posts: r0ck.
Taming MySQL is… challenging. Especially in very large, fast-growth, ‘always-on’ environments. It’s one of those things where you seemingly can never know all there is to know about it. That’s why I really like coming across posts like this one from FreshBooks that describes a very real problem that was affecting their users, how they dealt with it, why *that* failed, and what the final fix was. Post a link to your favorite MySQL Problem and Solution post in the comments (oh yeah, and “subscribe to comments” should be working now!)
Interesting SQL Challenge
Setting Up Master-Master Replication On Four Nodes With MySQL 5 On Debian Etch
Setting Up Master-Master Replication On Four Nodes With MySQL 5 On Debian Etch
This tutorial explains how you can set up MySQL master-master replication on four MySQL nodes (running on Debian Etch). The difference to a two node master-master replication is that if you have more than two nodes, the replication goes in a circle, i.e., with four nodes, the replication goes from node1 to node2, from node2 to node3, from node3 to node4, and from node4 to node1.
SQL completion in PHP strings
NetBeans 6.5 is soon to be released. After 10 years of NetBeans that's the first version of Sun's OpenSource IDE featuring PHP support. While 6.5 is waiting to be packaged the development didn't stop and the first features for the successor, NetBeans.next, are already being developed. David Van Couvering just showed a preview of a cool new feature: SQL completion in PHP strings, if it does what the screenshot promises that's a damn great addition in my opinion....
Open SQL Camp experiences
It was hosted by Baron "Xaprb" Schwartz, who I learned is one of the few people I don't have to look down to look in the eye.
There were maybe 100 people over the whole the weekend, including the upper echalon of MySQL and other open source database hackers, as well as technical people from Infobright and Tokutek and PBXT.
There were people who quite literally flew in from the other side of the planet and from Europe.
It was good to see Monty Widenius, and to introduce him to the pleasure that is well made matcha.
Vadim Tkachenko's and Peter Zaitsev's presentation on the Percona patches was interesting and eye opening. The following random roundtable discussion between them, Brian Aker of Drizzle, and Arjen Lentz about the open source future of InnoDB. Oracle/Inno were very notable in their absence.
I got to meet Richard Hipp, the author of SQLite, and then to introduce him to Jim Starkey. Richard's presentation on how much you can and can't trust the operating system, and how to make a database durable in the face of the real world was very illuminating, and more than a little bit scary. I wonder if InnoDB and MyISAM (and PBXT and Maria and Falcon) test as rigiously against the edge conditions of write failure as SQLite does.
Saturday night I was asked to make a custom tree of Drizzle for PBXT, for the blob streaming protocol work, and so I did. The drizzle-blobcontainer patch is now up on my Launchpad account.
Sunday was a hackathon, which I spent plumbing up the Drizzle pluggable error handlers. Almost done, tho hard to debug, since when it doesn't work, Drizzle doesnt output any error messages! :) When it's done, another one or two locks will be removed from the main execution path, plus a whole pile of spagghetti and hardened lava, and Drizzle will be even faster!
Monday morning I caught the train up to New York Penn Station, riding along with Sheeri Cabral of Pythian and Ronald Bradford of 42SQL. Taking the train was cheaper and faster than driving or flying from Charlottesville to NYC.
Installing Oracle 11gR1 on Ubuntu 8.10 Intrepid Ibex
Hello, there! With another Ubuntu release, it has come the time to update our series of posts on how to install Oracle 11g on Ubuntu. If you’ve been following, we’ve been publishing updated howtos since Ubuntu 7.04:
- Installing Oracle 11g on Ubuntu 8.04 Hardy Heron LTS
- Installing Oracle 11g on Ubuntu 7.10 Gutsy Gibbon
- Installing Oracle 11g on Ubuntu 7.04 Feisty Fawn
In fact, in this article I refer several times to previous posts regarding some configuration aspects and why I chose particular values. Also, note that this series of posts is a work in progress and we were able to improve this series with your help. So please do post comment below as your collaboration is very much appreciated.
In this post, we’ll see the steps needed to install Oracle 11gR1 on an Ubuntu 8.10 Intrepid Ibex box all the way to creating your very first database. I’ve been working very hard to ensure that at every new post, the results you get when executing this procedure are as deterministic as possible, leading to a successful setup.
Please keep in mind that this is not a supported architecture, so pay special attention to the order in which I do things. Don’t rush and try to merge steps, as it took me a lot of attempts to make this setup work properly. There are some reboots and also there’s a specific order you need to do things, so please follow the instructions step-by-step and verify the the results of every single command.
PreparationLet’s get down to it, shall we? The first thing to do is to get some files. We need an ISO image of Ubuntu (a CD/DVD will do) and one of Oracle 11gR1. Get Ubuntu 8.10 Server here; and Oracle 11gR1 here. (It’s free, but you have to register on the Oracle website to download it.)
It’s a good idea to check the md5sum of each image after downloading from the Internet.
451 CAOS Links 2008.11.18
Red Hat’s chairman wins enterprising award. Sun updates StarOffice. Barracuda Networks acquires 3SP. Reaction to Sun’s reorganization. Barack Obama’s laptop. And more.
Press releases
Red Hat, Inc.’s Matthew Szulik Named Ernst & Young Entrepreneur Of The Year 2008 Overall National Winner Ernst & Young
Ingres Launches Ingres Database 9.2 Ingres
Sun Microsystems Unveils StarOffice 9 Software Sun Microsystems
Red Hat Increases Authorization to Repurchase Common Stock Red Hat
Barracuda Networks Launches Barracuda SSL VPN Following Acquisition of Reputable SSL VPN Provider Barracuda Networks
Univa UD Launches UniCluster 4.1 and Family of HPC Management Products Univa UD
Community Created GroundWork Monitor Japanese Generally Released To Address Growing Market Demand GroundWork Open Source
Open Kernel Labs Expands Global Market Presence: Launches European HQ Open Kernel Labs
Engine Yard Introduces Developer Support for Merb Engine Yard
Day Software Announces General Availability Of CQ5.1 Day Software
REvolution Computing Integrates Their R Distribution into Microsoft’s New High Performance Computing Server REvolution Computing
Yoggie Opens up its Miniature Hardware Firewall Yoggie Security Systems
Adobe Advances Flash Platform at MAX 2008 Adobe
ERP5 World Forum Defines Road Map for the Future Nexedi
News articles
Norway encourages use of open source software Associated Press
Sun’s Rich Green Set Open Source In Motion; Lift-off Still To Come Charles Babcock, InformationWeek
(InformationWeek evidently had something of an open source special this week.covering topics such as virtualization, SOA, content management, Linux, network management, business intelligence, Enamoly, and enterprise usage in general.)
The Microsoft-Novell Linux deal: Two years later Paul Krill, InfoWorld
Business vs. FOSS: Six Pressure Points Bruce Byfield, Datamation
Rule #2: Create a community Terry Hancock, Free Software Magazine
Open source growth dims LAMP stack to symbolic status Pam Deringer, SearchEnterpriseLinux
Bug Labs creates open source Lego for software engineers Bruce Byfield, Linux.com
Blogs
What if Sun fails with open source? Dave Rosenberg, Cnet
Rumors of the Demise of Open Source Startups: Greatly Exaggerated Mark Radcliffe, Law & Life: Silicon Valley
Open Source and Sustainability, Updated Michael Tiemann, Open Source Initiative
Sun: Dead company walking? Steven J. Vaughan-Nichols, ComputerWorld
ZipTie: New features, new name, new license? Tristan Rhodes, The Open Source Advocate
It’s the Infrastructure, Stupid John Mark Walker, There is No Open Source Community
Shouldnt Obama use Linux, and not a Mac? Amanda McPherson, The Linux Foundation
Announcing Tungsten Replicator Beta for MySQL Robert Hodges, Continuent
French Recording Industry Sues SourceForge For Hosting Open Source P2P Mike Masnick, Techdirt
Meet the IT Channels Top 50 Open Source Companies The VAR Guy
SQL completion - in PHP?
Take a look - yes, you're getting column names in the completion list when working with a string literal in PHP. And notice how it works with aliases...
It's a prototype, but it's a good start, and we expect to have something like this working for you in NetBeans.next
SFTP support in NetBeans PHP
MySQL 5.1-GA is coming
Sheeri has already commented on this, but I want to stress that MySQL 5.1.30 will be GA by December 6th, 2008.
Common wrong Data Types compilation
Yang resigns from Yahoo CEO job
Announcing Tungsten Replicator Beta for MySQL
I have blogged about our goals for Tungsten Replicator quite a bit, for instance here and here. We want the Replicator to be platform-independent and database-neutral. We also want it to be as flexible as possible, so that our users can:
- Support new databases easily
- Filter and transform SQL events flexibly
- Replicate between databases and applications, messaging systems, or files that you don't traditionally combine with replication
There are three main types of plug-ins in Tungsten Replicator.
- Extractors remove data from a source, usually a database.
- Appliers put the events in a target, usually a database.
- Filters transform or drop events after extraction or before application.
Say you are using Memcached to hold pages for a media application. The media database is loaded from a "dumb" 3rd party feed piped in through mysql. Normally you would set up some sort of mechanism within the feed that connects to the database and then updates Memcached accordingly. Okay, that works. However, your feed processor just got a lot more complicated. Now there's a better way. You can write an Applier that converts SQL events from the database to Memcached calls to invalidate corresponding pages. Then you can write a Filter that throws away any SQL events you don't want to see. Voila! Problem solved. Because it works off the database log, this approach works no matter how you load the database. That's even better.
Tungsten Beta has a number of other interesting features beyond pluggable replication. Our next builds will support MySQL row replication fully and have much better heterogeneous replication. I'm going to cover these in future blog posts. Incidentally, MySQL 5.1 row replication is a highly enabling feature for many data integration problems. If you have not checked it out already, I hope our replication will motivate you to do so in the very near future.
Meanwhile, please download load the build and take it out for a spin. Builds, documentation, bug tracking, wikis and much more are available on our community site. Have fun!
Cloud Computing Expo: San Jose
The Cloud Computing Expo at Fairmont Hotel in san Jose, CA is fast approaching and I plan to be there to learn and meet the folks interested in Cloud Computing. If you plan to be there, dont miss the hands on Cloud Computing BootCamp, also being held at Fairmont hotel on 20th Nov. and is Free.
Led by Williamson, the Cloud Computing Bootcamp will illustrate all the major players and provide a hands-on program with configuration samples, live demos and working setups you can further adapt and play with.
Hope to see you there and chat about how you can leverage the Sun Cloud offerings to build applications for the cloud or build your own clouds.
Do we want an Open Source MySQL Monitoring tool ?
Matt Reid wants to know what we want in an Open Source MySQL monitoring solution ?
He is working on the second incarnation of Monolith and wants input from the MySQL community.
Now for me the bigger question is if we want an isolated tool that runs stand alone, or a tool which we can integrate it in something we already have.
To me there is a difference between a tool that I want to use to debug my environment, such as Mytop or MySQL Activity Report, in that case I need some tool that quickly installs with little dependencies and little impact.
On the other side I want a tool that is constantly there, that tells me about trends and performance history. But there I don't want an isolated toool, I want something fully integrated where I can correlate different measurements from disk io, memory usage etc , that tool should also tell me about the things that go
wrong.
We did some research earlier this year to figure out the current state of Open Source monitoring tools. Different tools have a different audience.. some go for the network layer, others take the os level and other even try to go deep inside the applications.
Given that knowledge we even had the idea to refocus that research comparing different monitoring tools such as Zabbix, Zenoss, Hyperic and Nagios again but this time with a focus on monitoring MySQL and submit that as an abstract for the upcoming MySQL conference, we didn't .. maybe next time.
There's plenty of frameworks already that will allow you to send alerts on all of the occasions you list, or allow you to graph all the values you want. And yes we want to see those values too.
But do we want yet another tool , yet another URL to browse to or do we want those alerts and graphs integrated in an existing tool such as Zabbix, Zenoss or
Hyperic .. I guess I prefer the integrated approach.
Meeting MySQL community in Paris - November 18, 2008
I will meet the MySQL community in Paris on November 18, 2008 at 19:30.
I will speak about MySQL Proxy Wizardry, and discuss with the community about any subject that will come up.
The place:
1 rue royale, 227 Bureaux de la Colline
92210 Saint-Cloud
Bâtiment D 9ème étage
Tel : 01 78 15 24 00
最近评论
1 天 15 小时 前
1 天 16 小时 前
2 天 12 小时 前
2 天 12 小时 前
3 天 22 小时 前
1 周 1 天 前
1 周 1 天 前