Home |  MySQL Buzz |  FAQ |  Feeds |  Submit your blog feed |  Feedback |  Archive |  Aggregate feed RSS 2.0 English Deutsch Español Français Italiano 日本語 Русский Português 中文
Previous 30 Newer Entries Showing entries 31 to 60 of 64 Next 4 Older Entries

Displaying posts with tag: scaling (reset)

MySQL 5.4 Sysbench Scalability on 64-way CMT Servers
Employee +0 Vote Up -0Vote Down
As a followup to my MySQL 5.4 Scalability on 64-way CMT Servers blog, I'm posting MySQL 5.4 Sysbench results on the same platform. The tests were carried out using the same basic approach (i.e. turning off entire cores at a time) - see my previous blog for more details.

The Sysbench version used was 0.4.8, and the read-only runs were invoked with the following command:

sysbench --max-time=300 --max-requests=0 --test=oltp --oltp-dist-type=special --oltp-table-size=10000000 \\
   --oltp-read-only=on --num-threads=[NO_THREADS] run
The "oltp-read-only=on" parameter was omitted for the read-write tests. The my.cnf file listed in my previous blog was also used unchanged for these tests.

Here is the data presented graphically. Note that the number of vCPUs is the same as the number of

  [Read more...]
MySQL 5.4 Scalability on 64-way CMT Servers
Employee +0 Vote Up -0Vote Down
Today Sun Microsystems announced MySQL 5.4, a release that focuses on performance and scalability. For a long time it's been possible to escape the confines of a single system with MySQL, thanks to scale-out technologies like replication and sharding. But it ought to be possible to scale-up efficiently as well - to fully utilize the CPU resource on a server with a single instance.

MySQL 5.4 takes a stride in that direction. It features a number of performance and scalability fixes, including the justifiably-famous Google SMP patch along with a range of other fixes. And there's plenty more to come in future releases. For specifics about the MySQL 5.4 fixes, check out Mikael Ronstrom's blog.

So how well does MySQL 5.4 scale? To help answer the question I'm going to take a look at some performance data from one of Sun's CMT

  [Read more...]
MySQL 5.4 Scalability on 64-way CMT Servers
Employee +0 Vote Up -0Vote Down
Today Sun Microsystems announced MySQL 5.4, a release that focuses on performance and scalability. For a long time it's been possible to escape the confines of a single system with MySQL, thanks to scale-out technologies like replication and sharding. But it ought to be possible to scale-up efficiently as well - to fully utilize the CPU resource on a server with a single instance.

MySQL 5.4 takes a stride in that direction. It features a number of performance and scalability fixes, including the justifiably-famous Google SMP patch along with a range of other fixes. And there's plenty more to come in future releases. For specifics about the MySQL 5.4 fixes, check out Mikael Ronstrom's blog.

So how well does MySQL 5.4 scale? To help answer the question I'm going to take a look at some performance data from one of Sun's CMT

  [Read more...]
Predictive caching in a MySQL-backed infrastructure
+0 Vote Up -0Vote Down
Sounds a bit far fetched (pun intended ;-), but we're doing it. This is not inside of the MySQL server, but rather the overall application design. Let me run you through the logic...

Some key aspects to scaling are: not doing unnecessary queries, and caching what you can. Just a quick baseline. The fastest query is the one you don't do, or the one you've already done before - the latter being caching.

A simple yet brilliant example of this is the Youtube trick where a script reads the relay log, converting updates into appropriate selects and running them so that the InnoDB cache will have the blocks in memory when the slave SQL thread executes the actual update. Maatkit now has a tool for this, so it's publically available. It's not quite predictive, but it's a neat trick anyway that sometimes comes in handy. Search engines use similar tricks.

Extending on this, with





  [Read more...]
Predictive caching in a MySQL-backed infrastructure
+0 Vote Up -0Vote Down

Sounds a bit far fetched (pun intended ;-), but we’re doing it. This is not inside of the MySQL server, but rather the overall application design. Let me run you through the logic…

Some key aspects to scaling are: not doing unnecessary queries, and caching what you can. Just a quick baseline. The fastest query is the one you don’t do, or the one you’ve already done before - the latter being caching.

A simple yet brilliant example of this is the Youtube trick where a script reads the relay log, converting updates into appropriate selects and running them so that the InnoDB cache will have the blocks in memory when the slave SQL thread executes the actual update. Maatkit now has a tool for this, so it’s publically available. It’s not quite predictive, but it’s a neat trick anyway that sometimes comes in handy. Search engines

  [Read more...]
Solving the PBXT DBT2 Scaling Problem
+0 Vote Up -0Vote Down
One little bit of wisdom I would like to pass on:

If a program runs fast with 20 threads, that does not mean it will run fast with 50. And if it runs fast with 50, it does not mean that it will run fast with 100, and if it runs fast with 100 ... don't bet on it running fast with 200 :)

In my last blog I discussed some improvement to the performance of PBXT running the DBT2 benchmark. Despite the overall significant increase in performance I noted a drop off at 32 threads that indicated a scaling problem. For the last couple of weeks I have been working on this problem and I have managed to fix it:

As before, this test was done





  [Read more...]
BFT: Our case for 100% de-normalization.
+0 Vote Up -0Vote Down

As a long time DBA and Database Architect this idea is repugnant – make a database 100% de-normalized; one table and except for the one query, retrieval by primary key, nothing else works.  And yet we have had great success using this kind of database.

This does not replace the original normalized database, rather it is more like a permeant cache fed from the main database.  It is a MySQL (http://mysql.com/) database which has certain advantages over Memcached or other true caches such as it is permanent until our processes replace it.

Consider what it might take to build a simple web page: get a request, process it which might take many queries and some significant processing then send back your html.

  [Read more...]
Don’t fix, work around – MySQL
+0 Vote Up -0Vote Down
Sun decided to not go down the route of reviewing and accepting the patches, but are now suggesting - are you sitting down? - running multiple instances on the same hardware.
Hadoop Primer – Yet Another Hadoop Introduction
+0 Vote Up -0Vote Down

I just came upon a pretty good Hadoop introduction paper posted on Sun’s wiki. Apache Hadoop is a free Java software framework that supports data intensive distributed applications. It enables applications to work with thousands of nodes and petabytes of data. Hadoop was inspired by Google's MapReduce and Google File System (GFS) (wikipedia). I wouldn’t call it

  [Read more...]
Raid is obsolete
+0 Vote Up -0Vote Down

In a lot of environments.

Peter gives a nice overview why you don't always need to invest in big fat redundant hardware.

We've tackled the topic last year already ..

Now I often get weird looks when I dare to mention that Raid is obsolete ..people fail to hear the "in a lot of environments"

Obviously the catch is in the second part, you won't be doing this for your small shop around the corner with just one machine. You'll only be doing this in an environment where you can work with a redundant array of inexpensive disks. Not with a server that has to sit in a remote and isolated location.

Next to that there are situations where you will be using raid, but not for redundancy, but for disk throughput.

Capacity Planning, Architecture, Scaling, Response time, Throughput
+0 Vote Up -0Vote Down
First of all let me start off saying that I learned a lot of Capacity Planning from two people. Jozo Dujmovic, and John Allspaw-who by the way is coming out with a book.

Capacity != Performance
. You may have the capacity to do a bubble sort but a bubble sort is still a bubble sort.

Really to Scale you need to know when your application will break. I have a tool set to help determine what application is producing what SQL and use that to figure out which SQL is producing the most load on the system. Some common tricks I do is put the execution path automatically as a SQL comment, then sample the FULL Processlist to build a graph on what application, function, SQL pattern is the top load.

On top of that I use





  [Read more...]
How to scale writes with master-master replication in MySQL
+0 Vote Up -0Vote Down

This post is SEO bait for people trying to scale MySQL’s write capacity by writing to both servers in master-master replication. The short answer: you can’t do it. It’s impossible.

I keep hearing this line of reasoning: “if I make a MySQL replication ‘cluster’ and move half the writes to machine A and half of them to machine B, I can increase my overall write capacity.” It’s a fallacy. All writes are repeated on both machines: the writes you do on machine A are repeated via replication on machine B, and vice versa. You don’t shield either machine from any of the load.

In addition, doing this introduces a very dangerous side effect: in case of a problem, neither machine has the authoritative data. Neither machine’s data can be trusted, but neither machine’s data

  [Read more...]
Using BASE instead of ACID for scalability
+0 Vote Up -0Vote Down

My editor Andy Oram recently sent me an ACM article on BASE, a technique for improving scalability by being willing to give up some other properties of traditional transactional systems.

It’s a really good read. In many ways it is the same religion everyone who’s successfully scaled a system Really Really Big has advocated. But this is different: it’s a very clear article, with a great writing style that really cuts out the fat and teaches the principles without being specific to any environment or sounding egotistical.

He mentions a lot of current thinking in the field, including the CAP principle, which Robert Hodges of Continuent first turned me onto a

  [Read more...]
Mutex contention and other bottlenecks in MySQL
+0 Vote Up -0Vote Down
Over the last few weeks I have been doing some work on improving the concurrency performance of PBXT. The last Alpha version (1.0.03) has quite a few problems in this area.

Most of the problems have been with r/w lock and mutex contention but, I soon discovered that MySQL has some serious problems of it's own. In fact, I had to remove some of the bottlenecks in MySQL in order to continue the optimization of PBXT.

The result for simple SELECT performance is shown in the graph below.

Here you can see that the gain is over 60% for 32 or more concurrent threads. Both results show the performance with the newly optimized version of PBXT. The test is running on a 2.16 MHz dual core





  [Read more...]
Horizontal Scaling with HiveDB
+0 Vote Up -0Vote Down

At the MySQL Conference & Expo 2008, Britt Crawford and Justin McCarthy, both from Cafepress.com, gave us a very interesting talk on scaling with HiveDB. I took a few notes (pasted below), their slides are online (warning: 6.1MB PDF), and if you’re after their abstract its available as well.

I also took a video of them (refer to Slide 12, for the IRC conversation):

The quick notes:

  • OLTP optimised (as it serves cafepress.com)
  • Cannot lock tables, or take it offline
  • Constant response time is more important than low latency (little slower query is ok, just
  [Read more...]
Sun caught in a pincer with MySQL
+0 Vote Up -0Vote Down

Over the years, the database world has been buzzing with the strategic threat posed to the established players by upstart open-source database systems. Oracle and IBM would no longer be able to gouge defenseless small and medium-sized businesses of non-trivial portions of their IT budgets for a mere database licence. Oracle, IBM and Microsoft, for their part, have tried their best to respond to this threat, but it is clear that they cannot simply squash open-source products, but rather evolve with the changing landscape.

the countered threat from Oracle

Oracle made some strategic purchases in the past few years to establish a foothold in the embedded and front-end database market by acquiring Sleepycat (maintainers of BerkeleyDB) and InnoBase (makers of InnoDB storage engine for MySQL). These two also happened to provide the only two transactional backends for MySQL, whlie InnoDB is the only one to be used

  [Read more...]
Liveblogging: 10,000 Tables Can?t Be Wrong
+0 Vote Up -0Vote Down

10,000 Tables Can?t Be Wrong: Designing a Highly Scalable MySQL Architecture for Write-intensive Applications by Richard Chart

Chose MySQL for performance and stability, and less important but still there, experience and support. Support is becoming increasingly more and more important.

Starting point: 1 appliance supporting 200 devices
Problem/Goal: Extensible architecture with deep host and app monitoring, over 1000 devices with 100 mgmt points each
Distributed collection over a WAN, with latency and security concerns
Current reality: several times the scale of the original goal
Commercial embedded product, so they actually pay for the embedded MySQL server

Future: The fundamentals are sound: next generation of the product moves up another order of magnitude

Data Characteristics
>90% writes






  [Read more...]
Liveblogging: A Match Made in Heaven? The Social Graph and the Database
+0 Vote Up -0Vote Down

Jeff Rothschild of Facebook’s “A Match Made in Heaven? The Social Graph and the Database”

Taking a look at the social graph and what it means for the database.

The social graph:

  • At it’s heart it’s about people and their connections.
  • Learning about people who are in your world.
  • Can be a powerful tool for accelerating the use of an application.

“The social graph has transformed a seemingly simple application such as photos into something tremendously more powerful.” We’re interested about what people are saying about us, and about our friends. Social applications are compelling.

Facebook users blew through the estimate for 6 months of storage in 6 weeks. It is serving 250,000 photos per second at peak time, not





  [Read more...]
Panel Video: Scaling MySQL ? Up or Out?
+0 Vote Up -0Vote Down

Yesterday’s keynote panel on “Scaling MySQL — Up or Out?”

Directly download the 310MB wmv file (not if you are on the conference wireless please!), or watch it in your browser via streaming — simply click the “play” link on this page.

Keith Murphy managed to take painstaking notes with all the facts and figures. As well, Venu Anuganti presents a chart with the results as well as notes on the more detailed answers. Ronald Bradford has a brief summary of the 20 seconds of wisdom from each panelist.

MySQL Conference Liveblogging: Applied Partitioning And Scaling your (OLTP) Database System (Wednesday 11:55AM)
+0 Vote Up -0Vote Down
  • Phil Hilderbrand of thePlatform for Media, Inc presents
  • classic partitioning
    • old school - union in the archive tables
    • auto partitioning and partition pruning
    • great for data warehousing
    • query performance improved
    • maintenance is clearly improved
  • design issues in applying partitioning to OLTP (On-Line Transaction Processing)
    • often id driven access vs date driven access
    • 1 big clients could be 80% of the whole database, so there's a difficulty selecting partitioning schemes
  • partitioning is only supported starting from MySQL 5.1
  [Read more...]
MySQL Conference Liveblogging: Portable Scale-out Benchmarks For MySQL (Wednesday 10:50AM)
+0 Vote Up -0Vote Down
  • Robert Hodges from Continuent presents
  • About Continuent
    • leading provider of open source database availability and scaling solutions
  • solutions
    • uni/cluster - multi-master database clustering that replicates data across multiple databases and load balances reads
    • uses "database virtualization"
  • scale-out design motivation
    • protection from db and site failures
    • continuous operation during upgrades
  • how come not everyone has it already?
  • creating identical replicas across different hosts is hard
    • Brewer's conjecture
  • trade-offs
    • DDL support
    • inconsistent reads between replicas
    • deadlocks
    • sequences
    • non-deterministic SQL
  [Read more...]
More progress on High Performance MySQL, Second Edition
+0 Vote Up -0Vote Down

Whew! I just finished a marathon of revisions. It's been a while since I posted about our progress, so here's an update for the curious readers.

Progress report on High Performance MySQL, Second Edition
+0 Vote Up -0Vote Down

It's been a while since I've written about progress on the book. I actually stopped working on it as much at the beginning of the month, because on October 31(st) I managed to finish a first draft of the last big chapter! Now I'm back to full-time work at my employer, and I'm working on the book in the evenings and weekends only. Read on for details of what I've been working on and what's next in the pipeline.

Scaling Drupal
+0 Vote Up -0Vote Down

John Quinn writes about Scaling Drupal he is taking a one step at a time approach and is still writing his 4th and 5 stages.

His first step obviously is separating the drupal from a separate database server, and he chooses mysql for this purpose, moving your DB to a different machine is a good thing to do.

However then he gets this crazy idea of using NFS to share his his drupal shared files :(
(he even dares to mention that the setup ease is good) Folks, we abandonned NFS in the late nineties. NFS is still a recipe for disaster, it has performance issues , it as stability issues (stale locks), and no security admin in his right mind will tolerate portmap to be running in his DMZ.
(Also think about

  [Read more...]
High Performance MySQL, Second Edition: Replication, Scaling and High Availability
+0 Vote Up -0Vote Down

Continuing in the tradition, which I hope has been as helpful to you as it has been to me, I'm opening the floor for suggestions on chapter 9 of the upcoming High Performance MySQL, Second Edition. Unlike the other chapters for which I've listed outlines, this one isn't substantially written yet. It's in detailed outline form at this point (a tactic that has worked very well for us so far -- I'll write about that someday).

I'm trying to get feedback much earlier in this chapter's lifecycle, for several reasons. Two of the most important are that this is one of the first chapters I've had a chance to really take from scratch, and the chapters I haven't written from scratch have been harder to organize, as you've probably seen from the last few outlines I posted. There's a lot of value in working top-down on this deep encyclopedia-style material.

Read on for the outline and more thoughts I just can't keep to myself.

Coming soon: High Performance MySQL, Second Edition
+0 Vote Up -0Vote Down

We've begun writing the second edition of the now-classic High Performance MySQL. "We" means co-authors Arjen Lentz, Baron Schwartz, Vadim Tkachenko, and Peter Zaitzev. O'Reilly is still the publisher, and Andy Oram is still the editor. With a team like this, I think the second edition will be a book you don't want to miss. Though in theory we're revising the first edition, the truth is we're starting from scratch and re-writing the book, and significantly expanding it at the same time. A lot has changed since Jeremy and Derek wrote the first edition. Today's MySQL deployments push the limits further than many people thought possible a few years ago. We'll teach you how they do it.

BLOB locators + BLOB streaming + Replication = Yeah!
Employee +0 Vote Up -0Vote Down
On the MySQL Conference & Expo 2007, I had the chance of meeting up with Paul (the author of PBXT) and Mikael. We briefly touched the topic of the BLOB Streaming Protocol that Paul is working on, which I find really neat. On the way back home, I traveled with Anders Karlsson (one of MySQL:s Sales Engineers), who is responsible for the BLOB Locator worklog and he described the concepts from his viewpoint.

Since I work with replication, these things got me thinking on what the impact is for replication and how it affects usability, efficiency, and scale-out. Being a RESTful guy, I started thinking about URIs both when

  [Read more...]
Archive strategies for OLTP servers, Part 3
+0 Vote Up -0Vote Down

In the first two articles in this series, I discussed archiving basics, relationships and dependencies, and specific archiving techniques for online transaction processing (OLTP) database servers. This article covers how to move the data from the OLTP source to the archive destination, what the archive destination might look like, and how to un-archive data. If you can un-archive easily and reliably, a whole new world of possibilities opens up.

Archive strategies for OLTP servers, Part 2
+0 Vote Up -0Vote Down

In the first article in this series on archiving strategies for online transaction processing (OLTP) database servers, I covered some basics: why to archive, and what to consider when gathering requirements for the archived data itself. This article is more technical. I want to help you understand how to choose which rows are archivable, and how to deal with complex data relationships and dependencies. In that context, I'll also discuss a few concrete archiving strategies, their strengths and shortcomings, and how they can satisfy your requirements, especially requirements for data consistency, which as you will see is one of the most difficult problems in archiving.

Archive strategies for OLTP servers, Part 1
+0 Vote Up -0Vote Down

In May 2005, I wrote a widely-referenced article about how to efficiently archive and/or purge data from online transaction processing (OLTP) database servers. That article focused on how to write efficient archiving SQL. In this article I'll discuss archiving strategy, not tactics. OLTP servers tend to have complex schemas, which makes it important and sometimes difficult to design a good archiving strategy.

Previous 30 Newer Entries Showing entries 31 to 60 of 64 Next 4 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.