It is a known fact that ext3 is not the most efficient file system out there and for example file removals can be painfully slow and cause a lot of random I/O. However, as it turns out, it can sometimes have a much more severe impact on the MySQL performance that it would seem. When or why?

When you run DROP TABLE there are several things that need to happen – write lock on a table so it cannot be used by any other thread, the data file(s) removal by the storage engine and of course in the end MySQL has to destroy the definition file (.frm). That's not all that happens, there is one other thing:

CODE:
 

VOID(pthread_mutex_lock(&LOCK_open)); 
error= mysql_rm_table_part2(thd, tables, if_exists, drop_temporary, 0, 0); 
pthread_mutex_unlock(&LOCK_open); 
 

( Read more )

In benchmarks passion (see my two previous posts) I managed to setup all three devices (RAID was on board; Intel X25-E SSD connected to HighPoint controller, FusionIO card) on our working horse Dell PowerEdge R900 (btw, to do that I had to switch from CentOS 5.2 to Ubuntu 8.10, as CentOS was not able to start with attached SSD card to HighPoint controller) and along with other tests I ran tpcc-like IO-bound workload on all devices.

For tests I used MySQL 5.4/InnoDB, and all other parameters are the same from previous posts (100W, buffer_pool 3GB). Filesystem – XFS mounted with nobarrier option.

Graphical results are here

and average results:

RAID10 – 7439.850 TPM
SSD – 10681.050 TPM
FusionIO – 17372.250 TPM

However what should be noted – both SSD and FusionIO are run in “non-durable” mode, that is you may lose some transactions in case of power outage (see my post http://itezer.com/blog/mysql-performance/161-SSD-XFS-LVM-fsync-write-cache-barrier-and-lost-transactions.html).

While results for SSD (note it is single device, in comparison to RAID 10 on 8 disks) and FusionIO are impressive, it is worth to consider price/performance parameter.

Here is my very rough calculation:
For RAID 10 we use 8 73GB SAS 2.5″ 15K RPM disks, with price 190$ per disks it gives us 1520$ for 292GB useful space, or ~ 5.2$ per GB.
For SSD I can get 32GB card for 390$, which is ~12.1$ per GB
For FusionIO I really not sure what is price (it was given as only for tests), but quick googling gave me 30$ per GB, so for 160GB card gives 4800$.

Now simple dividing TPM on price of IO system, we have
RAID 10 – 4.8 TPM / $
SSD – 27 TPM / $
FusionIO – 3.6 TPM / $

Please note that price of transaction is not the main criteria to consider, as total TCO for systems with SSD may be much cheaper (considering you need less servers, less space, less power). Also worth to consider that SSD is only 32GB space and to have the same space as FusionIO we need 4 cards (but it still will be cheaper than FusionIO), but it also may improve performance as such setup will be able to handle IO requests in parallel.

[Author: Vadim, via www.mysqlperformanceblog.com]

We finally managed to get Intel X25-E SSD drive into our lab. I attached it to our Dell PowerEdge R900. The story making it running is worth separate mentioning - along with Intel X25-E I got HighPoint 2300 controller and CentOS 5.2 just could not start with two RAID controllers (Perc/6i and HighPoint 2300). The problem was solved by installing Ubuntu 8.10 which is currently running all this system. Originally I wanted to publish some nice benchmarks where InnoDB on SSD outperforms RAID 10, but recently I faced issue which can make previous results inconsistent.

In short words using Intel SSD X25-E card with enabled write-cache (which is default and most performance mode) does not warranty storing all InnoDB transactions on permanent storage.
I am having some déjà vu here, as Peter was rolling this 5 years ago http://lkml.org/lkml/2004/3/17/188 regarding regular IDE disks, and I did not expect this question poping up again.

Long story is:
I started with puting XFS on SSD and running very primitive test with INSERT INTO fs VALUES(0) into auto-increment field into InnoDB table. InnoDB parameters are

innodb_buffer_pool_size=3G
innodb_data_file_path=ibdata1:10M:autoextend
innodb_file_per_table=1
innodb_log_buffer_size=8M
innodb_log_files_in_group=2
innodb_log_file_size=256M
innodb_thread_concurrency=0
innodb_flush_log_at_trx_commit=1
innodb_flush_method             = O_DIRECT
 

Actually most interesting one are innodb_flush_log_at_trx_commit=1 and innodb_flush_method = O_DIRECT (I tried also default innodb_flush_method, with the same result), using innodb_flush_log_at_trx_commit=1 I expect to have all committed transactions even in case of system failure.

( Read more )

For one our project I needed proxy solution, and mysql-proxy is one of well-known, so it was logical first choice. The obvious question which come in mind is what performance penalty we have using mysql-proxy, version 0.7.1.
So it is easy to test. (By the way sysbench recently was pushed to Launchpad, see lp:sysbench, and Percona is going to be active developer of this project and scripting benchmarks).

I took lp:sysbench with LUA script oltp_complex_ro, and tested for couple connections, here are results (in transactions per second, more is better)

Threads	MySQL-5.0.77	MySQL-proxy+MySQL-5.0.77
1	660.02	349.86
2	1158.66	477.77
4	1223.84	485.21
8	1224.22	455.69
16	1109.72	441.55
32	1059.23	419.05
64	909.98	414.30
128	882.46	406.28

ok, and let’s look on response time (for 1 thread).

MySQL

per-request statistics:
min: 1.31ms
avg: 1.51ms
max: 5.30ms
approx. 95 percentile: 1.56ms

Proxy+MySQL

per-request statistics:
min: 2.04ms
avg: 2.86ms
max: 6.44ms
approx. 95 percentile: 4.30ms

Well, I expected some penalty of using proxy… but 2-3x times, that’s overkill. Worth to consider if you want to run Query Analyzer with MySQL-proxy on your MySQL Enterprise setup.

There is alternative – Dormando-proxy, which I want to try also, but the problem is it crashed under sysbench load, so I was not able to get any results yet.

[Vadim - via www.mysqlperformanceblog.com]

One of the most typical reasons for performance and scalability problems I encounter is simply failing to do the math. And these are typically bad one because it often leads to implementing architectures which are not up for job they are intended to solve.

Let me start with example to make it clear. Lets say you’re doing some reports from your apache log files – how many distinct visitors hit the page and stuff like that. You picked full logs because they are great in flexibility – you can run any adhoc queries and drill down as much as you like. Initially traffic was small and young and with 10000 page views a day you few days of history the queries there instant which gave you a confidence this approach will work.

As the time passes and you get 1.000.000 events per day and looking to do reporting for up to the whole year worth of data you find things not working any more with response times for individual queries taking half an hour or more when it previously took seconds.

So what math would be in the case like this ? Say you have query like “SELECT page,count(*) cnt FROM logs GROUP BY page ORDER BY cnt DESC LIMIT 100″ to find the most visited pages as a simple example.

Before you can do the math (or say apply mathematical model) you really need to understand how things work. I like to call it having X-Ray vision. If you do not understand what is happening and you see MySQL Server (or any system really) as a black box magically providing you with results you can’t model its behavior which means you have little or no ability to predict it without running benchmarks.

( Read more )

The Problem

Original ActiveRecord sessions st ore is slow. It is fine for some low traffic sites, but it is too slow to use it for something larger. First of all, it is slow because ActiveRecord itself is slow. It is powerful ORM framework, but it is obviously overkill for such a simple task as a sessions management.

There are other possible solutions like cookie session store (session size is limited, no sensible data in sessions), memcached (no persistence + harder to implement high-available solutions).

That is why SqlSession store was created. It works with mysql directly with database APIs and works much faster than original AR session store. But sometimes it is still slow too because:

• it creates/updates session on each hit – every bot or occasional web serfer create session records in your database so you end up with thousands of thousands of useless records in your sessions table while 99% of hits do not require any session updates.
• it uses 32-characters string as a key for sessions records – all RDBMS work with string keys MUCH slower than with integers, so it would be much better to use integers, but we have these long session IDs and all session stores use them as a table key.
• it uses auto_increment primary key, which causes table-level locks in InnoDB for all MySQL versions prior to 5.1.21. These table-level locks with unnecessary inserts cause really weird problems for large sites.

The Solution

As a solution for mentioned problems FastSessions Rails plugin was born.

So, first of all, we removed id field from the sessions table, so we do not need to use auto-increment locks. Next step was to make lookups faster and we’ve used a following technique: instead of using (session_id) as a lookup key, we’ve started using (CRC32(session_id), session_id) – two-columns key which really helps MySQL to find sessions faster because key cardinality is higher (so, mysql is able to find a record earlier w/o checking a lots of index rows). We’ve benchmarked this approach and it shows 10-15% performance gain on large sessions tables.

( Read more )

Google is well-known for its fast searching engine as it can provide search queries within a count of half-second or even less.
Other services of Google perform considerable service for internet users as well except Gmail which sometimes shows poor performance. Google is typical with minimalist design. Yet, the speed performance of Google leaves no lack to the users to get the result of queries at once.

Good news. The key weapon of Google’s velocity is known as Page Speed, an internal tool that works to make efficient its site contents, is unfolded nowadays for website developers. Firefox plugin incorporated with Firebug is the first open-sourced tool that allows individual users to get the websites much faster than usual.

Excerpted from Google Blog Post:

A simple illustration on how Page Speed works to optimize your sites performance is that when you upload an image, compressed type of files will automatically provided for you. Thereby, you can directly display the compressed image on your site with no trouble. JavaScript or CSS which is used for faster display of your web pages can be identified obviously by Page Speed. The brilliance of this internal tool presents unquestionable comfort for individual users who are accessing your website accordingly.

( Read more )

Steve Souders is currently doing more to improve the performance of web pages and web browsers than anyone else out there. When he worked at Yahoo! he was responsible for YSlow (a great tool for measuring ways to improve the performance of your site) and he wrote the book on improving page performance: High Performance Web Sites. Now he works for Google but much of what he's up to is the same: Making web pages load faster.

I've been really excited about one of his recent project releases: UA Profiler. The profiler is a tool that you can run in your browser to determine the status of a number of network-performance-specific features that tie heavily to browser page load performance.

Here's a look at the current breakdown:



We can see Firefox 3.1 taking a lead, fixing 9 out of 11 of the issues tested for. Firefox 3, Chrome, and Safari 4 all come after with 8 fixed. Firefox 2, Safari 3.1, and IE 8 next at 7. Those numbers help to give you an overall feel of the page load performance that you'll see in a browser. (Naturally these tests don't take any rendering or JavaScript performance numbers into account but network performance generally trumps their total runtime anyway.)


( Read more )