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

Displaying posts with tag: performance (reset)

Optimizing MySQL, Improving Performance of Database Servers
Employee_Team +0 Vote Up -0Vote Down

Optimization involves improving the performance of a database server and queries that run against it. Optimization reduces query execution time and optimized queries benefit everyone that uses the server. When the server runs more smoothly and processes more queries with less, it performs better as a whole. To learn more about how a MySQL developer can make a difference with optimization, take the MySQL Developers training course.

This 5-day instructor-led course …

  [Read more...]
Semi-sync replication is not slow!
Employee +2 Vote Up -0Vote Down

If you read Yoshinori's post about Semi-sync at Facebook, he lists the objective of using semi-sync as an alternative to running full durability on a master. That is to say that once you can guarantee writes have safely been shipped across the network, you may not strictly need to guarantee that they are safe locally.

This is something that I have been wanting to benchmark for a long time, and reading Jay's post about …

  [Read more...]
MySQL 5.6 Performance on POWER8
+0 Vote Up -0Vote Down

The following sentence is brought to you by IBM Legal: The postings on this site are my own and don’t necessarily represent IBM’s positions, strategies or opinions.

My previous post covered the work needed to get MySQL 5.6.17 running reliably on modern POWER systems. The patch to MySQL 5.6.17 that’s needed is available here.

For those who don’t know, POWER8 is the …

  [Read more...]
How important is it to use 2-byte and 3-byte integers?
Employee +2 Vote Up -0Vote Down

One interesting feature of MySQL, is that it supports a very large number of integer data types. From the MySQL manual:

Type Storage Minimum Value Maximum Value
(Bytes) (Signed/Unsigned) (Signed/Unsigned)
TINYINT 1 -128
  [Read more...]
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...]
10 Newer Entries Showing entries 31 to 40 of 767 10 Older Entries

Planet MySQL © 1995, 2015, 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.