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 中文
Showing entries 1 to 20

Displaying posts with tag: concurrency (reset)

Quick and dirty concurrent operations from the shell
+1 Vote Up -0Vote Down

Let’s say that you want to measure something in your database, and for that you need several operations to happen in parallel. If you have a capable programming language at your disposal (Perl, Python, Ruby, PHP, or Java would fit the bill) you can code a test that sends several transactions in parallel.

But if all you have is the shell and the mysql client, things can be trickier. Today I needed such a parallel result, and I only had mysql and bash to accomplish the task.

In the shell, it’s easy to run a loop:

for N in $(seq 1 10)
do
mysql -h host1 -e "insert into sometable values($N)"
done

But this does run queries sequentially, and each session will open and close before the next one starts. Therefore there is no concurrency at all.
Then I thought that the method for





  [Read more...]
InnoDB scalability issues due to tables without primary keys
+1 Vote Up -0Vote Down

Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight: Scalability issues due to tables without primary keys. This scalability issue is caused by the usage of tables without primary keys. This issue typically shows itself as contention on the InnoDB dict_sys mutex. Now the dict_sys mutex controls access to the data dictionary. This mutex is used at various important places throughout the InnoDB code and as such any contention on the dict_sys mutex is going to have a InnoDB system-wide negative affect.

The post InnoDB scalability issues due to tables without primary keys appeared first on ovais.tariq.

Shard-Query 2.0 performance on the SSB with InnoDB on Tokutek’s MariaDB distribution
+2 Vote Up -0Vote Down
Scaling up a workload to many cores on a single host

Here are results for Shard-Query 2.0 Beta 1* on the Star Schema Benchmark at scale factor 10.  In the comparison below the “single threaded” response times for InnoDB are the response times reported in my previous test which did not use Shard-Query.

Shard-Query configuration

Shard-Query has been configured to use a single host.  The Shard-Query configuration repository is stored on the host.  Gearman is also running on the host, as are the Gearman workers.  In short, only one host is involved in the testing.

The

  [Read more...]
SQL Locking and Transactions – OSDC 2011 video
+0 Vote Up -0Vote Down
This recent session at OSDC 2011 Canberra is based on part of an Open Query training day, and (due to time constraints) without much of the usual interactivity, exercises and further MySQL specific detail. People liked it anyway, which is nice! The info as presented is not MySQL specific, it provides general insight in how databases implement concurrency and what trade-offs they make. See http://2011.osdc.com.au/SQLL for the talk abstract.
Statement-based vs Row-based Replication
+2 Vote Up -0Vote Down
Replication as most people know it, has mostly been SQL statement propagation from master to slave. This is known as "statement-based" replication. But there is also another kind of replication that is available, "the row-based replication" and that has quite a lot of benefits. In this post I intend on highlighting the advantages and disadvantages of both the types of replication to help you choose the best one. I also follow up with my own recommendation.
Why do I recommend switching over from MyISAM to Innodb!
+1 Vote Up -0Vote Down
Although MyISAM has been the default storage engine for MySQL but its soon going to change with the release of MySQL server 5.5. Not only that, more and more people are shifting over to the Innodb storage engine and the reasons for that is the tremendous benefits, not only in terms of performance, concurrency, ACID-transactions, foreign key constraints, but also because of the way it helps out the DBA with hot-backups support, automatic crash recovery and avoiding data inconsistencies which can prove to be a pain with MyISAM. In this article I try to hammer out the reasons why you should move on to using Innodb instead of MyISAM.
A growing trend: InnoDB mutex contention
+2 Vote Up -2Vote Down

I’ve been noticing an undeniable trend in my consulting engagements in the last year or so, and when I vocalized this today, heads nodded all around me. Everyone sees a growth in the number of cases where otherwise well-optimized systems are artificially limited by InnoDB contention problems.

A year ago, I simply wasn’t seeing the need for analysis of GDB backtraces en masse. These days, I’m writing custom tools to gather and analyze backtraces. A year ago, I simply looked at the SEMAPHORE section of SHOW INNODB STATUS. These days I’m writing custom tools to aggregate and reformat that data so I can interpret it more easily. And I’m actually seeing cases of this type of problem multiple times every week. I remember the first time I ran into a server that was literally optimized to the limit, but struggling under the load. It

  [Read more...]
MySQL University: Concurrency Control: How It Really Works
Employee +1 Vote Up -0Vote Down

This Thursday (September 24th, 14:00 UTC), Heikki Tuuri, the father of InnoDB, will give a session on Concurrency Control: How It Really Works. He'll describe how InnoDB manages concurrency control, so that the system protects data integrity. Beginning with the basics of transaction management, Heikki will include a discussion of the ACID (atomicity, consistency, isolation, and durability) properties, and explain various transaction modes, locking, deadlocks, and more advanced topics such as the impact of next-key (gap) locking, referential integrity, XA (distributed transaction management) support, and more. While the discussion will focus on the

  [Read more...]
MySQL University: Concurrency Control: How It Really Works
Employee +0 Vote Up -0Vote Down

This Thursday (September 24th, 14:00 UTC), Heikki Tuuri, the father of InnoDB, will give a session on Concurrency Control: How It Really Works. He'll describe how InnoDB manages concurrency control, so that the system protects data integrity. Beginning with the basics of transaction management, Heikki will include a discussion of the ACID (atomicity, consistency, isolation, and durability) properties, and explain various transaction modes, locking, deadlocks, and more advanced topics such as the impact of next-key (gap) locking, referential integrity, XA (distributed transaction management) support, and more. While the discussion will focus on

  [Read more...]
MySQL University: Concurrency Control: How It Really Works
Employee +0 Vote Up -0Vote Down

This Thursday (September 24th, 14:00 UTC), Heikki Tuuri, the father of InnoDB, will give a session on Concurrency Control: How It Really Works. He'll describe how InnoDB manages concurrency control, so that the system protects data integrity. Beginning with the basics of transaction management, Heikki will include a discussion of the ACID (atomicity, consistency, isolation, and durability) properties, and explain various transaction modes, locking, deadlocks, and more advanced topics such as the impact of next-key (gap) locking, referential integrity, XA (distributed transaction management) support, and more. While the discussion will focus

  [Read more...]
iptables trick to limit concurrent tcp connections
+0 Vote Up -0Vote Down
This is sort of a self-documenting post, and a self-support group about ill-behaved tomcat apps. Sometimes, you have multiple nodes accesing your MySQL server (or any kind of server, for that matter) concurrently. Eventually, software in one or more of these nodes might do nasty things (you know who you are buddy:)) MySQL provides a […] Related posts:
  • Using MySQL Proxy to benchmark query performance By transparently sitting between client and server on each request,...
  • YARPP powered by AdBistroPowered by
    Real-World Concurrency
    Employee +0 Vote Up -0Vote Down

    One interesting and useful paper on real-world concurrency by Bryan Cantrill and Jeff Bonwick.

    Abstract: In this look at how concurrency affects practitioners in the real world, Cantrill and Bonwick argue that much of the anxiety over concurrency is unwarranted. Most developers who build typical MVC systems can leverage parallelism by combining pieces of already concurrent software such as database and operating systems (i.e., concurrency through architecture), rather than by writing multithreaded code themselves. And for those who actually must deal with threads and locks, the authors include a helpful list of best practices to help minimize the pain.

    Real-World Concurrency
    Employee +0 Vote Up -0Vote Down

    One interesting and useful paper on real-world concurrency by Bryan Cantrill and Jeff Bonwick.

    Abstract: In this look at how concurrency affects practitioners in the real world, Cantrill and Bonwick argue that much of the anxiety over concurrency is unwarranted. Most developers who build typical MVC systems can leverage parallelism by combining pieces of already concurrent software such as database and operating systems (i.e., concurrency through architecture), rather than by writing multithreaded code themselves. And for those who actually must deal with threads and locks, the authors include a helpful list of best practices to help minimize the pain.

    InnoDB Conference Presentations Now Online
    Employee +2 Vote Up -0Vote Down

    Well, it took us a little while (we’ve been busy !), but we’ve now posted our presentations on InnoDB from the MySQL Conference and Expo 2009. You can download these presentations by Heikki Tuuri, Ken Jacobs and Calvin Sun from the InnoDB website, as follows:

      [Read more...]
    Software is Hard Sometimes …
    Employee +0 Vote Up -0Vote Down

    Some months ago, Google released a patch for InnoDB that boosts performance on multi-core servers. We decided to incorporate the change into the InnoDB Plugin to make everybody happy: users of InnoDB don’t have to apply the patch, and Google no longer has to maintain the patch for new versions of InnoDB. And it makes us at Innobase happy because it improves our product (as you can in this post about InnoDB Plugin release 1.0.3).

    However, there are always technical and business issues to address. Given the low-level changes in the patch, was it technically sound? Was the patch stable and as rock solid as is the rest of InnoDB? Although it was written for the built-in InnoDB in MySQL 5.0.37, we needed to

      [Read more...]
    Talk,Talk, Talk: Innobase Speaks
    +0 Vote Up -0Vote Down

    That should read “Talks, Talks, Talks” … There will be several presentations by InnoDB experts at the upcoming 2009 MySQL Conference and Expo. Whether you’re a newbie or an experienced DBA deeply familiar with InnoDB, you won’t want to miss these important talks about InnoDB:

      [Read more...]
    Plug In for Performance and Scalability
    +0 Vote Up -0Vote Down

    Why should you care about the latest “early adopter” release of the InnoDB Plugin, version 1.0.3?   One word: performance! The release introduces these features:

    • Enhanced concurrency & scalability: the “Google SMP patch” using atomic instructions for mutexing
    • More efficient memory allocation: ability to use more scalable platform memory allocator
    • Improved out-of-the-box scalability: unlimited concurrent thread execution by default
    • Dynamic tuning: at run-time, enable or disable insert buffering and adaptive hash indexing

    These new performance features can yield up to twice the throughput or more, depending on your workload, platform and other tuning considerations. In another post, we explore some details about these changes, but first, what do these enhancements

      [Read more...]
    Great things afoot in the MySQL community
    +0 Vote Up -0Vote Down

    tl;dr: The MySQL community rocks. Percona, XtraDB, Drizzle, SSD storage, InnoDB IO scalability challenges.

    For anyone who lives and dies by MySQL and InnoDB, things are finally starting to heat up and get interesting. I’ve been banging the “MySQL/InnoDB scales poorly” drums for years now, and despite having paid Enterprise licenses, I haven’t been able to get anywhere. I was pretty excited when Sun

      [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...]
    Statement-based replication is disabled for Falcon
    Employee +0 Vote Up -0Vote Down
    Contrary to what I said earlier, Falcon has decided to deliberately disable statement-based replication using the same capabilities mechanism that InnoDB uses.

    The reason is that isolation between concurrent transactions cannot be guaranteed, meaning that two concurrent transactions are not guaranteed to be serializable (the result of a concurrent transaction that has committed can "leak" into an ongoing transaction). Since they are not serializable, it means they cannot be written to the binary log in an order that produce the same result on the slave as on the master.

    However, when using row-based replication they are serializable, because whatever values are written to the tables are also written to the binary log, so if data "leaks" into an ongoing

      [Read more...]
    Showing entries 1 to 20

    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.