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

Displaying posts with tag: performance (reset)

A Multi-Table Trick to Speed up Single-Table UPDATE/DELETE Statements
Employee_Team +2 Vote Up -0Vote Down

In MySQL, query optimization of single-table UPDATE/DELETE statements is more limited than for SELECT statements. I guess the main reason for this is to limit the optimizer overhead for very simple statements. However, this also means that optimization opportunities are sometimes missed for more complex UPDATE/DELETE statements.

Example

Using the DBT-3 database, the following SQL statement will increase prices by 10% on parts from suppliers in the specified country:

UPDATE part
SET p_retailprice = p_retailprice*1.10
WHERE p_partkey IN
     (SELECT …
  [Read more...]
How to improve InnoDB performance by 55% for write-bound loads
+2 Vote Up -0Vote Down

During April’s Percona Live MySQL Conference and Expo 2014, I attended a talk on MySQL 5.7 performance an scalability given by Dimitri Kravtchuk, the Oracle MySQL benchmark specialist. He mentioned at some point that the InnoDB double write buffer was a real performance killer. For the ones that don’t know what the innodb double write buffer is, it is a disk buffer were pages are written before being written to the actual data file. Upon restart, pages in the double write buffer are rewritten to their …

  [Read more...]
MySQL-5.7 improves DML oriented workloads
Employee_Team +3 Vote Up -0Vote Down

In MySQL 5.7, we have improved the scalability of DML oriented workloads in InnoDB. This is the result of a number of changes, which I will outline below.

(1) Fix index->lock contention

This RW lock protects all indexes, both the cluster and the secondary indexes.

Before 5.7, every modifications to non-leaf pages (every modifications for the tree structure) required to exclude the other threads’ access to the whole index by X-lock, and every concurrent accessing the index tree were blocked. This was the major reason of the index->lock contention in concurrent DML workloads.

In MySQL 5.7 concurrent access is …

  [Read more...]
Lua sysbench – crash course
+0 Vote Up -0Vote Down

This is a follow-up on my previous blog post about using Lua enabled sysbench. Today I will dive into how to write Lua scripts for sysbench. Look at this simple example:

function prepare ()
  local i
  print("creating table sbtest.t1 ...")
  db_query("create table t1 (c1 int unsigned primary key, c2 int)")
  db_query("begin")
  for i= 1, 1000 do
    db_query("insert into t1 values (" .. i .. "," .. i .. ")")
  end
  db_query("commit")
end

function cleanup()
  db_query("drop table t1")
end

function help()
  print("sysbench Lua demo; no special command line options available")
end

function …
  [Read more...]
Using Lua-enabled sysbench
+2 Vote Up -0Vote Down

A quite common benchmark for MySQL is sysbench. It was written nearly 10 years ago by Alexey Kopytov.

Sysbench has modes to benchmark raw CPU performance, mutex speed, scheduler overhead and file IO performance. The probably most often used sysbench mode is OLTP. This benchmark mimics a OLTP scenario with small transactions hitting an optimized database. There are many variables to play with, most important is the number of simulated application threads (option --num-threads). The OLTP benchmark can be run read-only, then it does 14 SELECT queries per transaction. Or it can be run read-write …

  [Read more...]
Re-factoring some internals of prepared statements in 5.7
Employee_Team +5 Vote Up -0Vote Down

When the MySQL server receives a SELECT query, the query goes through several consecutive phases:

  • parsing: SQL words are recognized, the query is split into different parts following the SQL grammar rules: a list of selected expressions, a list of tables to read, a WHERE condition, …
  • resolution: the output of the parsing stage contains names of columns and names of tables. Resolution is about making sense out of this. For example, in “WHERE foo=3“, “foo” is a column name without a table name; by applying SQL name resolution rules, we …
  [Read more...]
OurSQL Episode 184: Digging Through the Tool Chest
+1 Vote Up -0Vote Down

This week we discuss more Percona tools: pt-online-schema-change and pt-slave-find. Ear Candy is using the CONNECT storage engine to read GPX files, and At the Movies is Performance at Scale with TokuDB.

Events
DB Hangops - every other Wednesay at noon Pacific time

Upcoming MySQL events

OSCon 2014 at the Oregon Convention Center in Portland, Oregon from Sunday July 20th through Thursday July 24th.


  [Read more...]
Parallel Query for MySQL with Shard-Query
+0 Vote Up -0Vote Down

While Shard-Query can work over multiple nodes, this blog post focuses on using Shard-Query with a single node.  Shard-Query can add parallelism to queries which use partitioned tables.  Very large tables can often be partitioned fairly easily. Shard-Query can leverage partitioning to add paralellism, because each partition can be queried independently. Because MySQL 5.6 supports the partition hint, Shard-Query can add parallelism to any partitioning method (even subpartioning) on 5.6 but it is limited to RANGE/LIST partitioning methods on early versions.

The output from Shard-Query is from the commandline …

  [Read more...]
Benchmarking MySQL Performance
Employee_Team +1 Vote Up -0Vote Down

Benchmarking lets you test how a system responds when it is given work to do, and removes the guesswork from your performance tuning efforts. The workloads you use when benchmarking are very different from real life deployments, which can be extremely variable. If you remember this, benchmarking is extremely useful, enabling you to measure current performance and determine the performance impact of any changes. Such changes could be the addition of new hardware or opening up your application to more users.

Benchmarking is one of the topics that you can learn more about by taking the …

  [Read more...]
Five reasons why vertical scalability matters
Employee +2 Vote Up -0Vote Down

The latest benchmarks show that MySQL 5.7 is now able to scale to 60 cores, which is quite an incredible feat when you compare to the 4-8 core scaling of MySQL 5.1 just a few years ago. These improvements are the result of a lot of heavy lifting to reorganize internal locking structures, and I have an earlier blog post on what is a mutex anyway? which may help serve as an introduction.

While I consider horizontal scaling and projects like …

  [Read more...]
10 Newer Entries Showing entries 31 to 40 of 763 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.