Multi-master MySQL, with the MMM management software brings a whole host of new features, and manageability to your MySQL deployments. Run backups, alter tables, perform upgrades all without slowing down your production users.
Read more at Database Journal – Using Multi-master MySQL To Get A Leg Up On Database Performance
Last month, I blogged about a case involving InnoDB, where all threads acting on InnoDB tables completely stuck for about few hours doing nothing; until we found a way to[...]
A short post marks Pythian’s 195th edition of Log Buffer, a blog of blogs encapsulating what’s going on in the world of database administration.
Remember if you find a link or interesting blog post that you think Log Buffer should mention, send a note to the editor at Log Buffer and be sure to include the link, and a short note on why you think that others will want to read it too.
Now on to Log Buffer #195. Alex Gorbachev starts us off with his suggested readings and funnily enough, …
[Read more]The most efficient performance optimization of a SQL statement is to eliminate it. Cary Millsap’s recent Kaleidoscope presentation again highlighted that improving performance is function of code path. Removing code will improve performance.
You may think that it could be hard to eliminate SQL, however when you know every SQL statement that is executed in your code path obvious improvements may be possible. In the sequence SQL was implemented sometimes easy observations can lead to great gains. Let me provide some actual client examples that were discovered by using the MySQL General Log.
Example 1
5 Query SELECT * FROM `artist` 5 Query SELECT * FROM `artist` 5 Query …[Read more]
When working interactively with the MySQL client, you receive feedback of the time the query took to complete to a granularity of 10 ms.
Enabling profiling is a simple way to get more a more accurate timing of running queries. In the following example you can see the time the kernel took to run an explain, the query, and alter, and repeat explain and query.
mysql> set profiling=1; mysql> EXPLAIN SELECT ... mysql> SELECT ... mysql> ALTER ... mysql> show profiles; +----------+------------+------------------------- | Query_ID | Duration | Query +----------+------------+------------------------- | 1 | 0.00036500 | EXPLAIN SELECT sbvi.id a | 2 | 0.00432700 | SELECT sbvi.id as sbvi_i | 3 | 2.83206100 | alter table sbvi drop in | 4 | 0.00047500 | explain SELECT sbvi.id a | 5 | 0.00367100 | SELECT sbvi.id as sbvi_i +----------+------------+-------------------------
More information at …
[Read more]
Again, another simple test. Same basic tuning as yesterday, and
the table schema is the same:
CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` char(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
);
The table is again filled with 1.3 million rows, but this time
I'm doing an UPDATE. The update is again a simple primary key
update:
UPDATE t1 SET c2 = CONCAT('xxx', RAND(), 'yyy') WHERE c1 =
<random value 1 - 1000000>;
I run this on the Maria, InnoDB and MyISAM engines. The issues
with the MyISAM and Maria engines here is that they lack row
level locking. In MariaDB 5.1.47, the InnoDB version is 1.0.6, so
it is more scalable than what it used to be. The testbench is not
an incredibly hot machiine, just a 4 core AMD box.
I run the test in some different configurations, using a single
thread, using 10 threads and using 100 threads. Here we can see
that InnoDB Row-level locking …
Note: Article has been edited as I was confusing MarisDB and the
Maria Stoare Engine!
Just for the heck of it, I decided to try a very simple benchmark
on the Maria Storage Engine again. This time, I'm using a simple
SELECT. The table I use looks like this:
CREATE TABLE `t1` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` char(100) DEFAULT NULL,
PRIMARY KEY (`c1`)
);
Which should be simple enough. I fill this with 1.3 millions
rows, with c1 having consequitive numbers from 1 and up. My
benchmark consists of random SELECTs of the c2 column from this
table, using a primary key lookup on c1. I run this on 400
concurrent threads with each thread doing 1000 SELECTs. Ignoring
the actual values, MyISAM and InnoDB come out pretty close in
performance, with InnoDB slightly behind. Which is reasonable.
Maria is at less than half the performance of MyISAM though. This
is worrying. And I know what you say, …
In MySQL, even though the name read_buffer_size implies that the variable controls only read buffering, but it actually does dual purpose by providing sequential IO buffering for both reads and[...]
A couple of question I get a lot from MySQL customers is “how will this hardware upgrade improve my transactions per second (TPS)” and “what level of TPS will MySQL perform on this hardware if I’m running ACID settings?” Running sysbench against MySQL with different values for per-thread and global memory buffer sizes, ACID settings, and other settings gives me concrete values to bring to the customer to show the impact that more RAM, faster CPUs, faster disks, or cnf changes have on the server. Here are some examples for a common question: “If I’m using full ACID settings vs non-ACID settings what performance am I going to get from this server?”
Let’s find out by running sysbench with the following settings (most are self explanatory – if not the man page can explain them):
The need: Often there is a requirement where data in a particular table has to be processed, and the data processing might be slow, while the table might be a one that is used by your application extensively. For example, a logging table that logs page hits. Or there might be an archiving operation that has to be performed on a particular table. Archiving / processing / aggregating records, all these operations are slow and can really blog down a website, combine that with the added overhead if the table that needs to have these operations performed is one that...