Holy crap 15815 is fixed for 4.1?

Is 15815 fixed for 4.1.23 community edition?


Paul DuBois may have indicated that in the bug report. Getting details soon.

Update: Got the official word that the patch will be provided to the community, but you will need to build and test mysql yourself.

x86_64 + INNODB + 4.1 + throwing a server in to live traffic

As many of you know 4.1 does not have the thread bug fix as mentioned in this post. So, throwing a new mySQL server into production can cause a spike of threads that exposes this bug. Why? Well, for the most part INNODB has not filled its buffer pool for the most part.


For example, assume you have a box with 16 GB of ram, running 64-bit Linux. Let's look at MySQL when it first starts.

Here is some sample output from top

top - 18:46:22 up 400 days, 22:30,  3 users,  load average: 0.65, 1.08, 0.73
Tasks: 132 total,   2 running, 130 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.7% us,  0.5% sy,  0.0% ni, 92.2% id,  6.5% wa,  0.0% hi,  0.2% si
Mem:  16253552k total,  3406008k used, 12847544k free,    79364k buffers
Swap:  8388600k total,      160k used,  8388440k free,  1708900k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                    
14567 mysql     16   0 14.1g 1.4g 3324 S  1.3  8.9   0:09.02 mysqld                                                     
14562 mysql     16   0 14.1g 1.4g 3324 S  0.3  8.9   0:00.01 mysqld                                                     
14566 mysql     15   0 14.1g 1.4g 3324 S  0.3  8.9   0:02.26 mysqld                                                     

Even though innodb_buffer_pool_size=13G, the mysql process (using LINUX Threads) is not 14G it's 1.4g. RES in top is the non-swapped physical memory a task is using. This is very important.

Now, if I put this server into production now, INNODB would have to do extra work to figure out what tables and such need to be in the buffer pool as well as serve the onslaught of traffic.

Assume that we just put the server into production at this level. Your request rate of 100 connections per second require 1000 selects a second. Within the 1st second of deploy 100 threads will all request data unbuffered. Causing a huge slowdown, that is very hard to recover from.


Now a way around this:

mysql -uuser -p DB -e'SELECT * FROM Table WHERE id > 10 million - MAX(id)' > /dev/null

Now mysql RES is taking 4.0g as the process runs

14556 mysql     16   0 14.1g 4.0g 3392 S  0.0 25.8   0:06.19 mysqld                                                     
14557 mysql     16   0 14.1g 4.0g 3392 S  0.0 25.8   0:00.00 mysqld                                                     
14558 mysql     20   0 14.1g 4.0g 3392 S  0.0 25.8   0:00.00 mysqld    

But, still this is not good, you want to fill the buffer pool with data that will likely be accessed for your application, and get RES to the size of your buffer pull. So, keep doing this for your most highly accessed tabled ORDER BY the column that you primarily sort by.

Another Query

top - 20:33:35 up 401 days,  3 users,  load average: 1.64, 1.94, 1.12
Tasks: 140 total,   2 running, 137 sleeping,   0 stopped,   1 zombie
Cpu(s):  7.5% us, 18.4% sy,  0.3% ni, 52.6% id, 10.8% wa,  1.0% hi,  9.5% si
Mem:  16253552k total, 16226832k used,    26720k free,    13768k buffers
Swap:  8388600k total,      160k used,  8388440k free,  9153788k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                    
14556 mysql     16   0 14.1g 6.6g 3396 S  0.0 42.4   0:06.19 mysqld                                                     
14557 mysql     16   0 14.1g 6.6g 3396 S  0.0 42.4   0:00.00 mysqld                                                     
14558 mysql     20   0 14.1g 6.6g 3396 S  0.0 42.4   0:00.00 mysqld                                                     
14559 mysql     16   0 14.1g 6.6g 3396 S  0.0 42.4   0:00.05 mysqld       

To finally

top - 20:33:35 up 401 days,  3 users,  load average: 1.65, 2.12, 1.38
Tasks: 195 total,   4 running, 191 sleeping,   0 stopped,   0 zombie
Cpu(s): 43.1% us,  7.6% sy,  0.0% ni, 17.0% id, 30.5% wa,  0.2% hi,  1.7% si
Mem:  16359928k total, 16346856k used,    13072k free,    45176k buffers
Swap:  8296104k total,   132392k used,  8163712k free,   547404k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND                                                    
26259 mysql     15   0 14.9g  14g 3752 S  8.6 95.2   0:00.35 mysqld                                                     
26260 mysql     16   0 14.9g  14g 3752 S  0.0 95.2   0:00.17 mysqld                                                     
26261 mysql     16   0 14.9g  14g 3752 S  2.0 95.2   0:00.19 mysqld          

Building mySQL into a RPM from Source

bkf clone -rmysql-5.0.33 bk://mysql.bkbits.net/mysql-5.0-community mysql-5.0.33

cd mysql-5.0.33

BUILD/autorun.sh

cd ../

tar cvzf /usr/src/redhat/SOURCES/mysql-5.0.33.tar.gz mysql-5.0.33

export CC=/usr/bin/gcc
export CXX=/usr/bin/gcc
export LD_LIBARY_PATH=$LD_LIBARY_PATH:/lib64:/usr/lib64
export CFLAGS="-O2 -static -fomit-frame-pointer -ffixed-ebp"
export CXXFLAGS="-02 -felide-constructors -fno-exceptions -fno-rtti"

rpmbuild -ba --define '_with_static 1' mysql-5.0.33.spec

Now mysql-5.0.33.spec is just any spec file distributed with the RHEL source RPM - if requested I can upload it.


UPDATE: Lenz left a refinement in a comment here

Really good stuff

Speaking at the mySQL User conference 2007

I'll be giving a talk at mysqluc2007. The talk will be entitled, Federation at Flickr: Doing Billions of Queries per day. Personally I think it's a pretty catchy title, and full of info that might be helpful for many other organizations.

I've won 2005 (1st year that they offered this award), and 2006 Application of the year award for the uses of mySQL. Three years in a row would be cool! This will be my second speaking engagement at mysql uc. The 1st one was entitled Friendster back in 2005. It says I'm a Sr. Support Engineer, I have no idea what that is, really I'm the Database (DataLayer) Architect / Database Administrator, or Technical Yahoo - in Yahoo Land. I'm not to big on titles because doing is much greater then being(tm).




Hope to see you there.

Great Breakdown at tweakers.net

Scalability is going to be a huge focus from the community this year due to recent changes at MySQL. Tweakers.net has a pretty good breakdown of the present mySQL binary with and without the fix to INNODB scalability bug.

Tweakers.net also has an interesting comparison of POSTGRES on various platforms. It's behaving nicely under high concurrency, much better then INNODB even with the patch to it's scalability bug. If Falcon ever gets released it would be nice to Bench it.

If I get some time, I might run some of my own benchmarks to see what else can be squeezed out of MySQL.