You are here

MySQL Performance Blog

MySQL Performance Blog
Checked: 2 hours 42 min ago
Updated: 2 hours 42 min ago
Update every: 2 hours

Subscribe to MySQL Performance Blog

How fast can MySQL Process Data

MySQL Performance Blog - Wed, 09/04/2008 - 7:12pm

Reading Barons post about Kickfire Appliance and of course talking to them directly I learned a lot in their product is about beating data processing limitations of current systems.

This raises valid question how fast can MySQL process (filter) data using it current architecture ?
I decided to test the most simple case - what if we take the in memory table with very narrow row and run simple query which needs to do simple filtering - how many rows per second it will be able to do?

MySQL should have dynamic durability settings

MySQL Performance Blog - Wed, 09/04/2008 - 6:51am

If you’re using Innodb tables MySQL has innodb_flush_log_at_trx_commit variable which defines how durable your transactions are. If you have high durability requirements you set it to 1 and log records are pushed directly to the disk on transaction commit. If you do not bother loosing come committed transactions you can set it to 0 and Innodb will only flush log approximately once per second. Finally you can set it to 2 which is flushes data to operation system cache (so if MySQL crashes transaction is not lost) but does not save from OS crashes or power failures.

Should you have your swap file enabled while running MySQL ?

MySQL Performance Blog - Mon, 07/04/2008 - 2:23am

So you’re running dedicated MySQL Linux box with plenty of memory, so the good question arises if you should have swap file enabled or disable it ? I’ve seen production successfully running on boxes both with and without swap file so it is not the question of you must do it this or that way but rather understanding advantages of both approaches.

I also would like to hear what you do yourself, and why :)

Kickfire Kickfire Kickfire

MySQL Performance Blog - Sat, 05/04/2008 - 5:10am

So it looks like these days Kickfire welcomed everyone to tell they’ve been working with Kickfire - Baron Keith Frank is writing about KickFire. Good Job Kickfire PR team you really energized community.
We also were working with Kickfire for quite a while and are also very excited to give it a try.

Multi-Column IN clause - Unexpected MySQL Issue

MySQL Performance Blog - Sat, 05/04/2008 - 1:08am

We have an application which stores massive amount of urls. To save on indexes instead of using URL we index CRC32 of the URL which allows to find matching urls quickly. There is a bit of chance there would be some false positives but these are filtered out after reading the data so it works all pretty well.

If we just process urls one by one it works great:

Andrew Aksyonoff does Sphinx Talk in Moscow

MySQL Performance Blog - Thu, 03/04/2008 - 11:45pm

On his way to MySQL Users Conference Andrew will stop by in Moscow, Russia and give a talk about Sphinx current features, development plans, and deployment use cases.

Interesting enough the meeting will take place at Moscow State University, Computer Science Faculty - my “Alma Mater” in the same building and even same room which I used to study.

Stored Function to generate Sequences

MySQL Performance Blog - Thu, 03/04/2008 - 3:09am

Today a customer asked me to help them to convert their sequence generation process to the stored procedure and even though I have already seen it somewhere I did not find it with two minutes of googling so I wrote a simple one myself and posting it here for public benefit or my later use :)

MySQL Performance on Memory Appliance

MySQL Performance Blog - Tue, 01/04/2008 - 4:43am

Recently I have had a chance to check out MySQL Performance on “Memory Appliance” by Violin Memory which can be used as extremely high speed storage system.

I helped Violin Memory to optimize MySQL for customer workload and Violin memory and also had a chance to do some benchmarks on my own. 2*Quad Core Xeon running CentOS5 was tested using ext2 filesystem and SysBench tool.

Using MMM to ALTER huge tables

MySQL Performance Blog - Thu, 27/03/2008 - 8:42pm

Few months ago, I wrote about a faster way to do certain table modifications online. It works well when all you want is to remove auto_increment or change ENUM values.

Welcome to the team, Baron

MySQL Performance Blog - Wed, 26/03/2008 - 6:50pm

As you may have seen from his blog, Baron Schwartz is joining our consulting company - Percona in less than a week. This is exciting news for us as Baron is renowned MySQL community member, blogger, co-author of High Performance MySQL second edition book, author of Maatkit and Innotop and just a great guy.

MySQL and Sun - Oportunity for smaller companies ?

MySQL Performance Blog - Wed, 26/03/2008 - 8:27am

Reading Martens interview we see the quite:

“As soon as the deal closed we immediately secured a big deal with a major European national police agency,” said Mickos, now SVP database products at Sun. “Key to them choosing MySQL was that we are now part of a much larger public corporation. The deal wouldn’t have happened when we were private.”

MySQL Public Worklog and Community Focus

MySQL Performance Blog - Wed, 26/03/2008 - 4:17am

MySQL made some tasks from their internal task tracking tool - Worklog a while back. I just have not look at it besides checking Maria related tasks until couple of days ago as Jay announced new Forge going live.
Check it out - there are a lot of nice ideas out where. I can find a lot of things I originally submitted something like 5 years ago our where :)

MySQL 6.0 vs 5.1 in TPC-H queries

MySQL Performance Blog - Tue, 25/03/2008 - 9:34pm

Last week I played with queries from TPC-H benchmarks, particularly comparing MySQL 6.0.4-alpha with 5.1. MySQL 6.0 is interesting here, as there is a lot of new changes in optimizer, which should affect execution plan of TPC-H queries. In reality only two queries (from 22) have significantly better execution time (about them in next posts), but I want to write about is queries that execute slower in new MySQL 6.0 version.

Query is pretty simple

MySQL File System Fragmentation Benchmarks

MySQL Performance Blog - Sat, 22/03/2008 - 3:47am

Few days ago I wrote about testing writing to many files and seeing how this affects sequential read performance. I was very interested to see how it shows itself with real tables so I've got the script and ran tests for MyISAM and Innodb tables on ext3 filesystem. Here is what I found:

How many people will leave MySQL now ?

MySQL Performance Blog - Fri, 21/03/2008 - 8:41pm

During the recent days we had few announcements of people leaving Sun/MySQL - few days ago I’ve seen announcement by Ronald Bradford and now I see Antony Curtis followed. I know bunch of other guys which are considering to leave or stay.

Big Iron for tests anyone ?

MySQL Performance Blog - Fri, 21/03/2008 - 5:44am

MySQL Users Conference is coming and with it my presentation about Innodb Scalability limits. We did bunch of tests but we surely could get benefit of some extra hardware for testing, so if you could provide us with dedicated remote access for benchmarks it would be great.

Here is what we’re looking for in particular:

- More than Dual Core Opteron systems. Would be good to see how they scale
- More than 8 core systems
- Non x86 based systems (Niagara etc)
- Fancy IO subsystem - more than 8 hard drives
- SSD based storage.

MySQL Query Cache WhiteSpace and comments

MySQL Performance Blog - Thu, 20/03/2008 - 5:56pm

Commenting on my previous post on MySQL Query Cache Gerry pokes me as I'm all wrong and both comments and whitespace are fixed in MySQL 5.0. This was not what I remember seeing in production so I decided to do some tests on the matter:

I did the test two ways. First using command line client and second using little PHP script which just does the same query. I did this as command line client is known to optimize queries sometimes by skipping "unnecessary" comments.

The tool I’ve been waiting for years

MySQL Performance Blog - Wed, 19/03/2008 - 4:54am

I've just been pointed to the nice tool which I was waiting for years to see. It is fincore - little perl script which allows you to see what pages of file are cached in OS memory. This is really cool.

When it comes to MySQL it is very useful with MyISAM tables which has their data file cached by OS cache only so you do not have any good information from MySQL side on what data is cached. You can also use it with Innodb to see how much memory are you wasting with double buffering by not using of O_DIRECT.

Working with many files and file system fragmentation

MySQL Performance Blog - Tue, 18/03/2008 - 8:10pm

Working on performance optimization project (not directly MySQL related) we did a test - creating 100 files writing 4K in the random file for some time and when checking the read speed on the files we end up with, compared to just writing the file sequentially and reading it back.

The performance difference was huge - we could read single file at 80MB/sec while fragmented files only deliver about 2MB/sec - this is a massive difference.

Researching your MySQL table sizes

MySQL Performance Blog - Tue, 18/03/2008 - 2:28am

I posted a simple INFORMATION_SCHEMA query to find largest tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes:

Find total number of tables, rows, total data in index size for given MySQL Instance

MySQL Error Message Nonsenses

MySQL Performance Blog - Mon, 17/03/2008 - 12:54am

What MySQL honestly was never good at is giving good helpful error messages. Start with basics for example - The error message in case of syntax error gives you information about tokens near by but little details:

  1. mysql> SELECT * FROM  user oder BY pwd;
  2. ERROR 1064 (42000): You have an error IN your SQL syntax; CHECK the manual that corresponds TO your MySQL server version FOR the RIGHT syntax TO USE near 'by pwd' at line 1

Sharding and Time Base Partitioning

MySQL Performance Blog - Sat, 15/03/2008 - 5:18am

For large number of online applications once you implemented proper sharding you can consider your scaling problems solved - by getting more and more hardware you can grow. As I recently wrote it however does not mean it is the most optimal way by itself to do things.

Economics of Performance Optimization

MySQL Performance Blog - Thu, 13/03/2008 - 9:21pm

I think every person responsible for Development or Operations of growing application sooner or later have to decide on couple few questions on how to tackle application performance. These questions are:

  • Should we Optimize Application or get more Hardware ?
  • Should we do things ourselves or hire an experts to help us ?

The answer on these questions actually depend on a lot of things, some of which we’ll try to cover here.

Speeding up GROUP BY if you want aproximate results

MySQL Performance Blog - Fri, 07/03/2008 - 7:33am

Doing performance analyzes today I wanted to count how many hits come to the pages which get more than couple of visits per day. We had SQL logs in the database so It was pretty simple query:

  1. SELECT sum(cnt) FROM (SELECT count(*) cnt FROM performance_log_080306 GROUP BY page HAVING cnt>2) pv;

Unfortunately this query ran for over half an hour badly overloaded server and I had to kill it in the end.

Sphinx 0.9.8 reaches RC stage, Docs updated

MySQL Performance Blog - Fri, 07/03/2008 - 1:57am

Andrew Aksenoff is pretty slow with release numbers for Sphinx. By MySQL Users Conference 2007 Sphinx version 0.9.7 was released and today we had just 0.9.8-rc1 announced This minor change in version number corresponds to about double source size (looking at download size) and major rewrite for many portions. Sphinx 0.9.8 snapshots were more stable than 0.9.7 for a long time but I guess Andrew did not call them releases because documentation was incomplete and this just was fixed now.


Subscribe to MySQL Performance Blog