Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 20 10 Older Entries

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...]
Showing entries 1 to 10 of 20 10 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.