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 )

On Saturday I moved Tombuntu to a new virtual private server at Linode (the computer in my basement just wasn’t enough anymore). I chose Linode (referral link) because they sell unmanaged Linux VPS systems, so I can continue to run whatever software I like.
I used Linode’s control panel to install Ubuntu 8.04 64 bit. Their Ubuntu images are not exactly the same as an installation from a CD, so I learned a few things while setting up.
Install a more comfortable environment
The Linode Ubuntu system is extremely minimal, things like man pages and tab-completion are not installed to save space. Install the ubuntu-standard metapackage to get a more comfortable command line environment:

apt-get install ubuntu-standard

Setting up users
A Linode Ubuntu system comes configured for only the root user. I prefer the Ubuntu way of using sudo instead of logging in as root.
Create a new user:

adduser myuser

This new user doesn’t have permission to use sudo yet. Open the sudo configuration file (let’s use the simpler nano editor instead of vi):

EDITOR=/usr/bin/nano visudo

Add this line to allow users in the admin group to use sudo:

%admin ALL=(ALL) ALL

Save and close the editor. The admin group may not exists yet, so create it:

groupadd admin

And add the new user to the group:

usermod -g admin myuser

Fix locale warnings
While installing updates and starting some programs, I noticed warnings similar to this one:

perl: warning: Setting locale failed.<br />perl: warning: Please check that your locale settings:<br />LANGUAGE = (unset),<br />LC_ALL = (unset),<br />LANG = "en_CA.UTF-8"<br />are supported and installed on your system.<br />perl: warning: Falling back to the standard locale ("C").<br />locale: Cannot set LC_CTYPE to default locale: No such file or directory<br />locale: Cannot set LC_MESSAGES to default locale: No such file or directory<br />locale: Cannot set LC_ALL to default locale: No such file or directory

Installing the language-pack-en package fixed these warnings:

apt-get install language-pack-en

( 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 )

With a many of webmasters switching to Virtual Private Servers (VPS) from shared hosting and preferring them over costlier fully dedicated servers, the main issue is memory and resource optimization. Most VPS packages have dedicated memory constraints of 128MB to 256MB. Apache and MySQL are known to be the most memory (resource) intensive applications that tend to crash or slow down a low to mid range VPS often. While OpenVZ or Virtuozzo based VPSs can have a memory limit in addition to the dedicated RAM to high memory needs, Xen based VPSs can not use more than the stipulated amount of dedicated RAM, adding to the problem.

In this short do-it-yourself Apache and MySQL optimization guide, I will present some small yet useful tips that I have collected from the net and will also share my own VPS settings and experiences that I hope would be very useful to first time and newbie VPS owners. I would also like to make it clear that I’m not a system admin or server guru of any caliber, but a techie and geek who loves to try and do things himself. So, always make a backup of files concerned and don’t hold me responsible for any kind of loss incurred. Try at your own risk!


( Read more )

In discussing with people I like to ask them a basic question — if you have a server with 16GB of RAM which will be dedicated for MySQL with large Innodb database using typical Web workload what settings you would adjust and interestingly enough most people fail to come up with anything reasonable. So I decided to publish the answer I would like to hear extending it with basics of Hardware OS And Application optimization.
I call this Innodb Performance Optimization Basics so these are general recommendation which work well for many applications, though the optimal settings of course depend on the workload.


( Read more )