Mar
30
2010
--

Can we get faster expression handling in MySQL

Andrew from Sphinx continues to work on improving SQL (or SphinxQL) support and now he published benchmarks comparing arithmetic expression handling in Sphinx to one in MySQL. The result ? Sphinx scored 3x to 20x faster. Andrew goes to explain results are not 100% comparable (as we can see in the table results are even different) and some performance can be attributed to Sphinx using different typing for expression. Though I’m wondering how much of Performance difference that really explain. I doubt it is 20x. Andrew goes on to explain there are further optimizations possible for Sphinx such as JIT compilation and expression optimization which he claims can increase the lead even further

Andrew is kind enough to provide explanations and benchmark results though this is far from the first notion of this problem I hear about. Many other companies looking to optimize MySQL problem, especially in analytics space when expressions should be computed over hundreds of millions of rows have expressed their concerns with this part of MySQL execution. I’m wondering if we’re going to see any improvements in this space by Oracle, MariaDB or Drizzle. I think Drizzle is in advantage here – with simplifying MySQL functionality a lot they probably can also simplify expression handling to be faster.


Entry posted by peter |
6 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Written by in: MySQL,Zend Developer |
Mar
29
2010
--

Kevin van Zonneveld’s Blog: Redis PHP Introduction

Kevin van Zonneveld has written up a new post for his blog today looking at using Redis in your application for caching information (similar to memcache).

Mar
26
2010
--

How well do your tables fit in buffer pool

In XtraDB we have the table INNODB_BUFFER_POOL_PAGES_INDEX which shows which pages belong to which indexes in which tables. Using thing information and standard TABLES table we can see how well different tables fit in buffer pool.

SQL:

  1. mysql> SELECT d.*,round(100*cnt*16384/(data_length+index_length),2) fit FROM (SELECT schema_name,table_name,count(*) cnt,sum(dirty),sum(hashed)  FROM INNODB_BUFFER_POOL_PAGES_INDEX GROUP BY schema_name,table_name ORDER BY cnt DESC LIMIT 20) d JOIN TABLES ON (TABLES.table_schema=d.schema_name AND TABLES.table_name=d.table_name);
  2. +————-+———————+———+————+————-+——–+
  3. | schema_name | table_name          | cnt     | sum(dirty) | sum(hashed) | fit    |
  4. +————-+———————+———+————+————-+——–+
  5. | db          | table1              | 1699133 |      13296 |      38584187.49 |
  6. | db          | table2              | 1173272 |      17399 |       1109998.42 |
  7. | db          | table3              |  916641 |       7849 |       1531694.77 |
  8. | db          | table4              |   86999 |       1555 |       7555487.42 |
  9. | db          | table5              |   32701 |       7997 |       3008291.61 |
  10. | db          | table6              |   31990 |       4495 |       25681 | 102.97 |
  11. | db          | table7              |       1 |          0 |           0 | 100.00 |
  12. +————-+———————+———+————+————-+——–+
  13. 7 rows IN SET (26.45 sec)

You can also see in one of the cases the value shown is a bit over 100% – I am not sure where it comes from but more pages reported to belong to the table in buffer pool than on disk. Though it seems to work well enough for estimation purposes.


Entry posted by peter |
3 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Mar
24
2010
--

RAID throughput on FusionIO

Along with maximal possible fsync/sec it is interesting how different software RAID modes affects throughput on FusionIO cards.

In short conclusion, RAID10 modes really disappoint me, the detailed numbers to follow.

To get numbers I run sysbench fileio test with 16KB page size, random read and writes, 1 and 16 threads, O_DIRECT mode.

FusionIO cards are the same as in the previous experiment, as I am running XFS with nobarrier mount options.

OS is CentOS 5.3 with 2.6.18-128.1.10.el5 kernel.

For RAID modes I use:

  • single card ( for baseline)
  • RAID0 over 2 FusionIO cards
  • RAID1 over 2 FusionIO cards
  • RAID1 over 2 RAID0 partitions (4 cards in total)
  • RAID0 over 2 RAID1 partitions (4 cards in total)
  • special RAID10 mode with n2 layout

Latest mode you can get creating RAID as:

mdadm --create --verbose /dev/md0 --level=10 --layout=n2 --raid-devices=4 --chunk=64 /dev/fioa /dev/fiob /dev/fioc /dev/fiod

In this case for all modes use 64KB chunk size ( different chunk sizes also interesting question).

There is graph for 16 threads runs, and raw results are below.

As expected RAID1 over 2 disks shows hit on write throughput comparing to single disk,
but RAID10 modes over 4 disks surprises me, showing almost 2x drops.

Only in RAID10n2 random reads skyrocket, while writes are equal to single disk.

This makes me asking if RAID1 mode is really usable, and how it performs
on regular hard drives or SSD disks.

The performance drop in RAID settings is unexpected. I am working with Fusion-io engineers to figure out the issue.

The next experiment I am going to look into is different page sizes.

Raw results (in requests / seconds, more is better):

single disk
read/1

12765.49
read/16

31604.86
write/1

14357.65
write/16

32447.07
raid0 2 disks
read/1

12046.12
read/16

57410.58
write/1

12993.91
write/16

43023.12
raid1 2 disks
read/1

11484.17
read/16

51084.02
write/1

9821.12
write/16

15220.57
raid1 over raid0 4 disks
read/1

10227.13
read/16

61392.25
write/1

7395.75
write/16

13536.86
raid0 over raid1 4 disks
read/1

10810.08
read/16

66316.29
write/1

8830.49
write/16

18687.97
raid10 n2
read/1

11612.89
read/16

99170.51
write/1

10634.62
write/16

31038.5

Script for reference:

CODE:

  1. #!/bin/sh
  2. set -u
  3. set -x
  4. set -e
  5.  
  6. for size in 50G; do
  7.    for mode in rndrd rndwr; do
  8.    #for mode in rndwr; do
  9.    #for blksize in 512 4096 8192 16384 32768 65536  ; do
  10.    for blksize in 16384 ; do
  11.       sysbench –test=fileio –file-num=64 –file-total-size=$size prepare
  12.       #for threads in 1 4 8; do
  13.       for threads in 1 16 ; do
  14.          echo “====== testing $blksize in $threads threads”
  15.          echo PARAMS $size $mode $threads $blksize> sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize
  16.          for i in 1 2 3 ; do
  17.            sysbench –test=fileio –file-total-size=$size –file-test-mode=$mode\
  18.             –max-time=180 –max-requests=100000000 –num-threads=$threads –init-rng=on \
  19.             –file-num=64 –file-extra-flags=direct –file-fsync-freq=0 –file-block-size=$blksize run \
  20.             | tee -a sysbench-size-$size-mode-$mode-threads-$threads-blksz-$blksize 2>&1
  21.          done
  22.       done
  23.       sysbench –test=fileio –file-total-size=$size cleanup
  24.    done
  25.    done
  26. done

Entry posted by Vadim |
5 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Mar
24
2010
--

xtrabackup-1.1

Dear Community,

It is time to announce the next version of backup software from Percona – XtraBackup 1.1.

The list of changes in version 1.1 includes:
Changelog:

  • XtraBackup is built on a base of MySQL 5.1.44 with InnoDB plugin 1.0.6
  • Added –host option
  • tar4ibd can treat over 64GB file
  • tar4ibd is default method for stream, even tar is specified
  • the binary supports compressed tables and Baraccuda format

Fixed bugs:

The binary packages for RHEL4,5, Debian, FreeBSD, Windows, Mac OS as well as source code of the XtraBackup is available on http://www.percona.com/percona-builds/XtraBackup/XtraBackup-1.1/.

Debian and RPM are available in Percona repository.

The project lives on Launchpad : https://launchpad.net/percona-xtrabackup and you can report bug to Launchpad bug system:
https://launchpad.net/percona-xtrabackup/+filebug. The documentation is available on our Wiki.

For general questions use our Pecona-discussions group, and for development question Percona-dev group.

For support, commercial and sponsorship inquiries contact Percona.


Entry posted by Aleksandr Kuzminsky |
5 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Mar
24
2010
--

Brandon Savage’s Blog: The Adventures Of Merging Propel With Zend Framework

In a new post to his blog Brandon Savage takes a quick loook at hos he integrated Propel into a Zend Framework app he’d built to replace an older site.

Mar
23
2010
--

fsyncs on software raid on FusionIO

As soon as we get couple FusionIO cards, there is question how to join them in single space for database. FusionIO does not provide any mirroring/stripping solutions and totally relies on OS tools there.

So for Linux we have software RAID and LVM, I tried to followup on my post
How many fsync / sec FusionIO can handle, and check what overhead we can expect using additional layers over FusionIO card.

The card I used is Fusion-io ioDrive Duo 320GB, physically it is two cards on single board, and visible as two cards to OS.

By some reason I was not able to setup LVM on cards, so I’ve finished tests only for software RAID0 and RAID1.

I used XFS filesystem mounted with “-o nobarrier” option, and I’ve the test I used in previous post on next configurations:

  • Single card
  • RAID0 over two cards
  • RAID1 over two cards

There what I’ve got:

  • Single card: 14050.59 req/sec
  • RAID0: 13039.00 req/sec
  • RAID1: 324.71 req/sec

By single card I’ve results much better than in my previous test, probably
Duo card has better characteristics.

RAID0 shows some overhead 8%, but it is acceptable.

And something is terrible wrong with RAID1, I am getting 40x drops in amount of fsyncs.
So it seems we can’t use innodb transactional log files with innodb_flush_log_at_trx_commit=1 on software RAID1 over FusionIO.

For reference I used command:

sysbench --test=fileio --file-num=1 --file-total-size=50G --file-fsync-all=on --file-test-mode=seqrewr --max-time=100 --file-block-size=4096 --max-requests=0 run

And next steps is to check how RAID setup affects IO throughput.


Entry posted by Vadim |
6 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Written by in: Innodb,MySQL,Zend Developer |
Mar
23
2010
--

Too many connections? No problem!

Did that ever happen to you in production?

CODE:

  1. [percona@sandbox msb_5_0_87]$ ./use
  2. ERROR 1040 (00000): Too many connections

Just happened to one of our customers. Want to know what we did?

For demo purposes I’ll use sandbox here (so the ./use is actually executing mysql cli). Oh and mind it is not a general-purpose best-practice, but rather a break-and-enter hack when the server is flooded. So, when this happens in production, the problem is – how do you quickly regain access to mysql server to see what are all the sessions doing and how do you do that without restarting the application? Here’s the trick:

CODE:

  1. [percona@sandbox msb_5_0_87]$ gdb -p $(cat data/mysql_sandbox5087.pid) \
  2.                                      -ex “set max_connections=5000″ -batch
  3. [Thread debugging using libthread_db enabled]
  4. [New Thread 0x2ad3fe33b5c0 (LWP 1809)]
  5. [New Thread 0x4ed19940 (LWP 27302)]
  6. [New Thread 0x41a8b940 (LWP 27203)]
  7. [New Thread 0x42ec5940 (LWP 1813)]
  8. [New Thread 0x424c4940 (LWP 1812)]
  9. 0x00000035f36cc4c2 in select () from /lib64/libc.so.6

And here’s the result:

CODE:

  1. [percona@test9 msb_5_0_87]$ ./use
  2. Welcome to the MySQL monitor.  Commands end with ; or \g.
  3. Your MySQL connection id is 8
  4. Server version: 5.0.87-percona-highperf-log MySQL Percona High Performance Edition, Revision 61 (GPL)
  5.  
  6. Type ‘help;’ or \h for help. Type \c to clear the current input statement.
  7.  
  8. mysql [localhost] {msandbox} ((none))> select @@global.max_connections;
  9. +————————–+
  10. | @@global.max_connections |
  11. +————————–+
  12. |                     5000 |
  13. +————————–+
  14. 1 row in set (0.00 sec)

Credit for the gdb magic goes to Domas.

Few notes:

  • You would usually have one connection reserved for SUPER user, but that does not help if your application is connecting as a SUPER user (which is a bad idea anyway).
  • This worked for me on 5.0.87-percona-highperf, but use it at your own risk and better test it before you actually have to do it in production.
  • This example assumes you had less than 5000 max_connections configured ;)

Entry posted by Aurimas Mikalauskas |
19 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Mar
21
2010
--

Ibuildings techPortal: Zend Studio formatted for Zend Framework and ATK

On the Ibuildings techPortal site today Ivo Jansch takes a look at a type formatter they’ve created to work with Zend Studio to more correctly format your code as per the official coding standard for the Zend Framework.

Mar
20
2010
--

InnoDB TABLE/INDEX stats

In Released and new coming features I did not mentioned two additional INFORMATION_SCHEMA tables available in XtraDB:
It is

  • INNODB_TABLE_STATS
  • INNODB_INDEX_STATS

These table show statistics about InnoDB tables ( taken from InnoDB data dictionary).

INNODB_TABLE_STATS is

  • | table_name | table name in InnoDB internal style (‘database/table’) |
  • | rows | estimated number of all rows |
  • | clust_size | cluster index (table/primary key) size in number of pages|
  • | other_size | other index (non primary key) size in number of pages|
  • | modified | internal counter to judge whether statistics recalculation should be done |

INNODB_INDEX_STATS is

  • | table_name | table name in InnoDB internal style (‘database/table’) |
  • | index_name | index name |
  • | fields | How many fields the index key has. (it is internal structure of InnoDB, it may be larger than the ‘CREATE TABLE’) |
  • | row_per_keys | estimate rows per 1 key value. ([1 column value], [2 columns value], [3 columns value], …) |
  • | index_size | index size in pages |
  • | leaf_pages | number of leaf pages |

Using these stats you can estimate how big is index is, and also what is statistics per index (or at least what InnoDB thinks about statistics in index)


Entry posted by Vadim |
5 comments

Add to: delicious | digg | reddit | netscape | Google Bookmarks

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com