Tuesday, August 25, 2009

Here We Go Again - MySQL for Online Application Series 2009

Tomorrow @10 am Central European time we will host the first part of a new series of MySQL for Online Applications - finally!

Registrations and details are here: http://www.mysql.com/news-and-events/web-seminars/online-apps.html

If you are a MySQL and webinar aficionado, you may remember the successful series that we ran in Spring 2007. Two and more years ago it was a great event, with huge participation - it is still available here.

Now we have renewed the material and we have added more hints and information re HA, performance and tuning, online architectures. There is, as usual, some content for developers, for IT managers, for DBAs and for architects. I hope nobody will be disappointed.

This time we will run four webinars:
Part 1 - Introduction to Scale Out and Replication Services (tomorrow, Wed 26th of Aug)
Part 2 - Tuning and Optimising MySQL for Maximum Performance (Wed 9th of Sep)
Part 3 - Advanced Tecniques for High Traffic Sites (Thu 17th of Sep)
Part 4 - High Availability Architectures for Online Applications (Tue 29th of Sep)

I hope to see you all online tomorrow!

Thursday, June 25, 2009

Meetup Group in Dublin, 2009-06-24


Well signposted :)

Thanks to echolibre for providing the content of the beers tables (in the pub schema, of course)!

Wednesday, June 24, 2009

What future for MySQL customers?

Yes, that's right, I mean *customers*. What do I mean with "MySQL Customer"? I would say "users who work for companies that prefer to buy products (tech support, subscriptions to enterprise binaries, short consulting engagements etc.)" instead of going for the familiar DIY approach.

Two years ago there was one and only MySQL product. There was the pluggable storage engine API, there was InnoDB outside the core development of the product and nothing else. Customers could confidently refer to a single provider, MySQL AB, to buy services and support from the creators of MySQL. Was it good for the customer? I would say yes, because the product was (and still is, of course) open source and there was a strong ecosystem and a wide adoption. But there was not much choice around: apart from relatively few strong but relatively companies, customers were in some way tied only to one provider.

Last year Sun acquired MySQL AB. I would say that very little changed for current customers, but there were more opportunities to see the product adopted in new sectors. Sun brought a strong credibility for MySQL in the Enterprise. MySQL was then perceived as a serious alternative to more expensive products. I say "then perceived" because MySQL has always been a serious alternative, but not many people outside the standard MySQL comfortable zone were aware or agreed on that. I consider myself lucky because I can visit tens of users every month and I am exposed to all sorts of environments. In this scenario, I soon figured out that the open source world, the web, the social networks, the online solutions is one world; the proprietary world, the enterprise, the legacy systems and the IT departments are another world. These worlds barely communicate, they do not know each other and they rarely share the choices they make. So, back to my point, Sun opened lots of doors (and minds) to the possibility of using MySQL in new projects.

Only two months ago we had the MySQL User Conference. It was a disruptive event - not in a negative or positive way, things simply changed very quickly. The announce of an acquisition at the very same time, lots of internal and external projects (Drizzle, just to name IMO the most important one), more stable and new storage engines, improvements and more important, the Percona conference forced many people to think of the future of MySQL. Few days later, the Open Database Alliance generated even more questions.

The new enterprise customers, the once outside the "comfy zone", seemed confused. They wondered about the future of MySQL, some were optimistic, some pessimistic, very few did not react.

Today the community around MySQL is more active than ever. I think it's fair to say that now we have a real community, in the sense of having lots of contributions, debates and actions. There are at least 20 great projects and products that are worth to mention; these project may bring significant improvements for MySQL users. People deeply involved in the development of MySQL or in contributions are also moving from one company to another and they are bringing knowledge into new and sometimes bigger teams. In open source, it's a non sense to think that a company can loose knowledge. A company may loose the skills or a talented person, but the knowledge will stay forever and the whole community will benefit from it.

So, now MySQL customers have more choice. They can use MySQL as is, they can download it from the MySQL web site, install it and use it. Or they can add/integrate/implement modules and products like DRBD, Infobright, xtradb, memcached, innodb 1.1, PBXT, Waffle, or lots of other patches and features available. They can also look at 5.4 or Drizzle, or MariaDB. And more important, customers can now see two significant changes. First, they have more choice to find the best services - consulting, training, support, subscription services, cloud systems, hosting - on the market, still with a cost that is far way lower than the one around commercial products. Second, with such an active community, they have acquired a free life insurance for MySQL and for the technology around it. There's more than any commercial product can bring on the table of a customer, at any time. And again, there is the great value of open source.

Wednesday, May 13, 2009

Follow the MySQL Meetup on Twitter tonight!

If you can't join us tonight for the Meetup event in London, don't
worry, I am sure you will find lots of info and comments on Twitter.

The official tag is #mysqlldn

Monday, May 11, 2009

New Meetup Event in London

If you live near London or in the South East, mark your calendars! A new meetup event is coming this week.

We will meet @6pm this Wednesday at:

The Lamb
94 Lambs Conduit St
Bloombury
WC1N 3LZ London

From MySQL, there will be Andrew Morgan, Mark Baker and myself.

As usual, there isn't a strict agenda, the main topic will probably be the outcome of the recent conferences in Santa Clara, the MySQL User Conference and the Percona Conference. We have lots of tech stuff to cover - Mysql 5.4, MySQL Cluster 7, Drizzle and Xtradb, just to mention few.

I hope to see you there!

-ivan

P.S. As you know, we cannot comment on the recent Oracle acquisition, so you have these options:
A) don't ask and enjoy the evening
B) buy us few pints and expect non-sense from us :)
C) all of the above

Saturday, May 9, 2009

A quick test with MySQL 5.4



Louis I
Louis II
Louis III
Louis IV
Louis V
Louis VI
Louis VII
Louis VIII
Louis IX
Louis X (dit le Hutin)(the Quarrelsome)
Louis XI
Louis XII
Louis XIII
Louis XIV
Louis XV
Louis XVI
Louis XVII
Louis XVIII
et plus personne plus rien..and nobody, nothing more…
qu'est-ce que c'est que ces gens-làwhat’s with those people
qui ne sont pas foutuswho can’t even
de compter jusqu'à vingt?count up to twenty?

Jacques Prevert


I did a quick test with the last version on the same testbed environment that I used for my previous blog and here is my finding.
MySQL 5.4 worked as I expected with sysbench. Performance up to 32 threads are basically the same of my build, but whilst 64 concurrent threads show a degradation in performance of the standard build and my build, with 5.4 they increase significantly, showing a pretty linear scalability - see table and graph.



Threads5.1.33-Std5.1.33-IZ5.4
128.6331.2828.63
258.1560.2158.57
493.56108.1496.57
8170.46179.59166.93
16286.28307.78271.09
32352.90400.14392.68
64307.62366.04576.55
128222.49266.61247.96




I would not recommend to go for 5.4 yet, but the results are extremely promising.

Thursday, April 9, 2009

Forgive me, I am back... with 20% more performance on Solaris 10...

Yes, it's been a long time. Week after week, I had ideas I wanted to share, but no time at all to blog. Priorities are clear: family and work come first, and it's very difficult to keep a balance between the two.

So I left a lot behind. The major project I worked on last summer, MySQL in a virtualised environment and with multiple instances on larger boxes, was part of the material we presented at the MySQL Customer Conference back in October. The results were pretty good and we showed a good level of scalability for real life applications, well beyond the typical sysbench results you can find by googling a bit here and there. I will blog about it at some point, it's just that it takes a lot of time to reorganise all the material to publish and make it understandable.

This week the topic is slightly different. We did it again, we dug into the art of building MySQL binaries and we did it on Solaris 10 for SPARC servers.


The build

Before going any further, a word of caution. My recommendation is always to rely on the certified binaries that our Build team provide, whether they are Community or Enterprise. But if you want to experiment and if you have time to work on extensive testing, then I suggest to have a look at some of the hints you can find on the net. Again, I have to thank my colleagues, Ritu Kamboj and Luojia Chen, for providing invaluable help and support on this matter.

Back to the real stuff. I built a new version of MySQL Enterprise 5.1.32 for a customer by tweaking some config params a bit and we ended up with a boost in performance between 5% and 20% on sysbench and between 18% and 33% on the application testing.

I could use a T2000 SPARC server to build MySQL, running Solaris 10. I used the latest version of SunStudio 12 and I simply changed this:

In the source code, the file .../storage/innobase/include/univ.i should have line 61 changed as:

/* MY CHANGE */
/* # if !defined(__GNUC__) */
# if !defined(__GNUC__) && !defined(__SUNPRO_C)
# undef UNIV_MUST_NOT_INLINE /* Remove compiler warning */
# define UNIV_MUST_NOT_INLINE
# endif


This is the configure command I used:

./configure
'--prefix=/usr/local/mysql'
'--localstatedir=/usr/local/mysql/data'
'--libexecdir=/usr/local/mysql/bin'
'--with-comment=MySQL Enterprise Server - Advanced Edition (GPL) Built by IZ'
'--with-server-suffix=-enterprise-gpl-advanced'
'--enable-thread-safe-client'
'--enable-local-infile'
'--with-mysqld-libs=-lmtmalloc'
'--with-pic'
'--with-client-ldflags=-static'
'--with-mysqld-ldflags=-static'
'--with-zlib-dir=bundled'
'--with-big-tables'
'--with-ssl'
'--with-readline'
'--with-embedded-server'
'--with-plugins=partition,innobase,archive,blackhole,csv,federated'
'--with-extra-charsets=complex'
'CC=/opt/SUNWspro/bin/cc'
'CXX=/opt/SUNWspro/bin/CC'
'CFLAGS=-Xa -fast -xbuiltin=%all -xO4 -xstrconst -mt -m64 -xunroll=2 -xprefetch=auto -xprefetch_level=3'
'LDFLAGS=-m64'
'CPPFLAGS=-D_FORTEC_'
'CXXFLAGS=-fast -xbuiltin=%all -mt -m64 -xO4 -xunroll=2 -xprefetch=auto -xprefetch_level=3 -noex -fsimple=1 -fns=no'


Here are some comments:

  • CC and CXX refer to your SunStudio 12 compiler, so you should set the right path
  • Partitioning, InnoDB, Archive, Blackhole, CSV and Federated are available (see '--with-plugins')
  • Flags -xO4 (optimisation level) and -Xa (only for the C compiler) seem to give a good improvement.

The results

We ran two different sets of tests, one generic and one related to the application that the customer is going to implement.

The first set was based on the popular sysbench. We used InnoDB (since InnoDB is the storage engine that the customer was going to use), measuring read and write performance. The system scaled pretty well up to 64 threads - see table below - reaching 366tps with the new build against 307tps with the original build.


Threads IZ Std IZ vs Std

1 31.28 28.63 9.26%
2 60.21 58.15 3.54%
4 108.14 93.56 15.58%
8 179.59 170.46 5.36%
16 307.78 286.28 7.51%
32 400.14 352.90 13.39%
64 366.04 307.62 18.99%
128 266.61 222.49 19.83%


The final output on sysbench with 64 threads on the new build is here:

sysbench
--test=oltp
--oltp-table-size=1000000
--mysql-socket=/tmp/mysql.sock
--mysql-user=root
prepare

sysbench
--num-threads=64
--max-requests=100000
--test=oltp
--oltp-table-size=1000000
--mysql-socket=/tmp/mysql.sock
--mysql-user=root
run

Number of threads: 64

OLTP test statistics:
queries performed:
read: 1402170
write: 500775
other: 200310
total: 2103255
transactions: 100155 (366.04 per sec.)
deadlocks: 0 (0.00 per sec.)
read/write requests: 1902945 (6954.84 per sec.)
other operations: 200310 (732.09 per sec.)

Test execution summary:
total time: 273.6145s
total number of events: 100155
total time taken by event execution: 17481.6191
per-request statistics:
min: 40.55ms
avg: 174.55ms
max: 1029.61ms
approx. 95 percentile: 317.37ms
Threads fairness:
events (avg/stddev): 1564.9219/21.04
execution time (avg/stddev): 273.1503/0.04



In the second test, we used a large InnoDB table, with 200M rows, using approx 80GB of space. We used a relatively large buffer size (4 GB), but we measured the performance with a cold cache. This test first creates a large MyISAM temp table from the main InnoDB table, then it appends more rows on the temp table and it scans it to select and to aggregate data. The initial query is based on key access, but the cardinality is not very high and the filter on the SELECT query gets a result of several million rows.
The table below shows the results of the tests. In this test, the queries were executed in a single thread, in a typical transformation for OLTP data on reporting. As you can see, the large scan on the InnoDB tables has a significant improvement of 27% in the new build, whilst the initial sequential scan on the MyISAM table showed an amazing improvement of 33%. The numbers showed in the table are execution times in seconds.


IZ Build Std Build IZ vs Std

INS MyISAM TEMP fm InnoDB 4,584.61 5,830.58 27.18%
SEL MAX(Ln)) FROM MyISAM TEMP 898.31 1,194.78 33.00%
2nd INS MyISAM TEMP fm InnoDB 1,343.36 1,590.90 18.43%
SEL Cols and SUM() fm MyISAM TEMP 1,499.29 1,807.32 20.55%
SEL Cols and SUM() fm MyISAM TEMP 884.03 1,105.91 25.10%
SEL Cols and SUM() fm MyISAM TEMP 871.29 1,092.53 25.39%
SEL Cols and SUM() fm MyISAM TEMP 1,516.87 1,817.73 19.83%
SEL Cols and SUM() fm MyISAM TEMP 883.57 1,109.12 25.53%
SEL Cols and SUM() fm MyISAM TEMP 871.01 1,094.52 25.66%
SEL w/WHERE fm MyISAM TEMP 1,406.66 1,637.22 16.39%
SEL w/WHERE fm MyISAM TEMP 890.06 1,114.06 25.17%

TOTAL (sec) 15,649.06 19,394.67 23.94%


For your reference, here is the MySQL config file, for both tests:


[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
innodb_locks_unsafe_for_binlog=1
back_log=50
innodb_additional_mem_pool_size=10M
innodb_buffer_pool_size=4G
innodb_flush_method=O_DIRECT
innodb_file_per_table
innodb_file_io_threads=8
innodb_log_buffer_size=6M
innodb_log_file_size=120M
join_buffer_size=8M
key_buffer_size=10M
log_long_format
long_query_time=60
lower_case_table_names=1
max_heap_table_size=16M
query_cache_limit=2M
query_cache_size=64M
read_buffer_size=4M
read_rnd_buffer_size=10M
sort_buffer_size=2M
table_cache=32
thread_cache_size=8
tmp_table_size=16M

Older Posts