This week we discuss fine-tuning Galera Cluster. In this week's ear candy we talk about recent changes to mysqldump and locking; At the Movies presents "Using Amazon Web Services for MySQL at Scale".
Events
DB Hangops -
every other Wednesay at noon Pacific time
FOSDEM 2014
- Sat February 1 - Sun February 2 in Brussels, Belgium.
Upcoming MySQL events
Training
SkySQL Trainings
In some application scenarios (e.g. PHP applications) client connections have very short durations, maybe only executing a single query. This means that the time spent processing connects and disconnects can have a large impact on the overall performance.
In MySQL 5.6 we started working on optimizing the code handling connects and disconnects. And this work has accelerated in MySQL 5.7. In this blog post I will first show the results we have achieved and then describe what we have done to get them.
The results
The graph below shows a comparison of the most recent 5.5 and 5.6 releases as well as the 5.7.2 and 5.7.3 milestones. We measured the number of queries per second (QPS) where each client executes a single query (point select) before disconnecting. For each server version we also tested with both Performance Schema disabled and enabled. Details about server hardware and configuration settings used, …
[Read more]This week we talk about how to install and set up Galera Cluster. Ear Candy talks about the new MySQL repos from Oracle and what to know about using them; At the Movies is Michael Stonebreaker talking about how to process today's big data transactional processing needs.
Galera Cluster
Codership documentation of Galera Cluster
Installing Percona XtraDB Cluster on Ubuntu
documentation
MariaDB's Galera Cluster documentation
I took a quick look at MariaDB 10.0 single-treaded performance (simple read-only sysbench). One thing immediately leaps to the eye, and I thought it worthy of mention. It contains an important lesson about the use of synchronisation primitives and in particular "atomic operations" in MariaDB (and MySQL).
I am using the Linux perf tool on this sysbench command:
sysbench --num-threads=1 --test=oltp --oltp-test-mode=simple --oltp-read-only --oltp-skip-trx
Look at the top offender in the output from perf
report:
1,54% mysqld mysqld [.] set_thread_state_v1
The only thing this does is set a string for SHOW PROCESSLIST (and the like) about what the thread is doing. And we are spending a whopping 1.5% of the total time doing this.
And why? That becomes clear when looking at the disassembly …
[Read more]Or I could place in the title – “Yes, we done it!”
After reaching 500K QPS in Read-Only on SQL queries, it was natural to expect a much higher performance level from InnoDB Memcached Plugin which is by-passing all SQL related layers.. However the story is not simple, and yet far from finished
While for today we have already our first “preview” results showing that we’re able to reach over 1,000,000 Query/sec level with the latest MySQL 5.7 code:
click here to read the full article..
If you are responsible for a MySQL Database, you make choices based on your priorities; cost, security and performance.
To learn more about improving performance, take the MySQL Performance Tuning course.
In this 4-day instructor-led course you will learn practical, safe and highly efficient ways to optimize performance for the MySQL Server. It will help you develop the skills needed to use tools for monitoring, evaluating and tuning MySQL.
You can take this course via the following delivery methods:Training-on-Demand:
ScienceLogic has a pretty fantastic network monitoring
appliance. So good in fact that InfoWorld gave it their
"2013 Best Network Monitoring System on the
Planet" award. Inside their "ultraflexible,
ultrascalable, carrier-grade" enterprise appliance,
ScienceLogic relies on MySQL and has since their start in
2003. Check out some of the things they've been able to do
with MySQL and their reasons for continuing to use MySQL in these
highlights from our new MySQL ScienceLogic case study.
MySQL 5.6 introduced a new feature called extended secondary
keys. We get a lot of questions about it and find that most
of them come from a few incorrect assumption. In this post I’ll
try to get rid of the confusion once and for all. Famous last
words… here goes:
Q1: Do I need to do anything
to enable extended secondary keys?No, nothing at all. It’s on
by default and I can’t see any sensible reason why you would want
to disable it. However, it is possible to disable it by tuning
the optimizer_switch: SET
optimizer_switch=’use_index_extensions={on|off}’.
Q2: Does extended secondary keys only work with InnoDB?
No, it should work with any storage engine that uses the primary key columns as reference to the row, which means most storage engines with clustered primary keys. I say “should” because it requires a minimum of work from the storage engine provider; it must …
[Read more]
MySQL 5.6 introduced a new feature called extended secondary
keys. We get a lot of questions about it and find that most
of them come from a few incorrect assumption. In this post I'll
try to get rid of the confusion once and for all. Famous last
words... here goes:
Q1: Do I need to do anything to enable extended
secondary keys?
No, nothing at all. It's on by default and I can't see any
sensible reason why you would want to disable it. However, it is
possible to disable it by tuning the optimizer_switch: SET
optimizer_switch='use_index_extensions={on|off}'.
Q2: Does extended secondary keys only work with
InnoDB?
No, it should work with any storage engine that uses the primary
key columns as reference to the row, which means most storage
engines with clustered primary keys. I say "should" because it
requires a minimum of work from the storage engine provider; it …
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.