sorted by: recent | see :
popular
Content Tagged PBXT
PBXT Transactional Characteristics
Let me first say that the PBXT storage engine has some great people behind it. At the users conference last April, I had a chance to meet Paul McCullagh, who created PBXT, and some of the people who work on it. They are dedicated individuals who are creating something unique.
Like the InnoDB storage engine, which is backed by the Innobase company, PBXT has a company that backs it, Primebase Technologies. This means that if needed, support can be got from the company that created the product. For enterprise companies this might be important.
The basics characteristics of PBXT:
- MVCC: Multi-version concurrency control, enables reading without locking.
- Transactional: Support for
BEGIN,COMMITandROLLBACKand recovery on startup. - ACID compliant: Atomic, Consistent, Isolated, Durable (once committed, changes cannot be lost).
- Row-level locking: updates use row-level locking, allowing for maximum concurrency.
- Deadlock detection: immediate notification if client processes are deadlocked.
- Referential Integrity: foreign-key support.
- Write-once: PBXT avoids double-writes by using a log-based architecture.
Much of this is the same as for the other transactional storage engines, so I won’t spend time on them. What sets PBXT apart from other storage engines is the write-once characteristic. It is worth understanding.
Mutex contention and other bottlenecks in MySQL
Most of the problems have been with r/w lock and mutex contention but, I soon discovered that MySQL has some serious problems of it's own. In fact, I had to remove some of the bottlenecks in MySQL in order to continue the optimization of PBXT.
The result for simple SELECT performance is shown in the graph below.
Here you can see that the gain is over 60% for 32 or more concurrent threads. Both results show the performance with the newly optimized version of PBXT. The test is running on a 2.16 MHz dual core processor, so I expect an even greater improvement on 4 or 8 cores. The query I ran for this test is of the form SELECT * FROM table WHERE ID = ?.So what did it do to achieve this? Well first of all, as you will see below, I cheated in some cases. I commented out or avoided some locks that were a bit too complicated to solve properly right now. But in other cases, I used solutions that can actually be taken over, as-is, by MySQL. In particular, the use of spinlocks.
All-in-all though, my intension here is just to demonstration the potential for concurrency optimization in MySQL.
Optimization 1: LOCK_plugin in plugin_foreach_with_mask()
The LOCK_plugin mutex in plugin_foreach_with_mask() is the first bottleneck you hit in just about any query. In my tests with 32 threads it takes over 60% of the overall execution time.
In order to get further with my own optimizations, I commented out the pthread_mutex_lock() and pthread_mutex_lock() calls in this function, knowing that the lock is only really needed if plug-ins are installed or uninstalled. However, later I needed to find a better solution (see below).
Optimization 2: LOCK_grant in check_grant()
After removing the above bottleneck I hit a wall in check_grant(). pthread_rwlock_rdlock() was taking 50%, and pthread_rwlock_unlock() was taking 45.6% CPU time! Once again I commented out the calls rw_rdlock(&LOCK_grant) and rw_unlock(&LOCK_grant) in check_grant() to get around the problem.
In order to really eliminate this lock, MySQL needs to switch to a different type of read/write lock. 99.9% of the time only a read lock is required because a write lock is only required when loading and changing privileges.
For similar purposes, in PBXT, I have invented a special type of read/write lock that requires almost zero time to gain a read lock ... hmmmm ;)
Optimization 3: Mutex in LOCK and UNLOCK tables
I then discovered that 51.7% of the time was taken in pthread_mutex_lock() called from thr_lock() called from open_and_lock_tables().
And, 44.5% of the time was taken in thread_mutex_lock() called from thr_unlock() called from mysql_unlock_tables().
Now this is a tough nut. The locks used here are used all over the place, but I think they can be replaced with a spinlock to good effect (see below). I did not try this though. Instead I used LOCK TABLES in my test code, to avoid the calls to LOCK and UNLOCK tables for every query.
Optimization 4: LOCK_plugin in plugin_unlock_list()
Once again the LOCK_plugin is the bottleneck, this time taking 94.7% of the CPU time in plugin_unlock_list(). This time I did a bit of work. Instead of commenting it out, I replaced LOCK_plugin with a spinlock (I copied and adapted the PBXT engine implementation for the server).
This worked to remove the bottleneck because LOCK_plugin is normally only held for a very short time. However, when a plugin is installed or unstalled this lock will be a killer and some more work probably needs to be done here.
Optimization 5: pthread_setschedparam()
I was a bit shocked to find pthread_setschedparam() was now taking 17% of the CPU time required to execute the SELECT. This call can be easily avoided by first checking to see if the schedule parameter needs to be changed at all. For the moment, I commented the call out.
Of course, the more optimized the code is, the worse such a call becomes. After all other optimizations pthread_setschedparam() CPU time increases to 52.6%!
Optimization 6: LOCK_thread_count in dispatch_command()
The LOCK_thread_count mutex in dispatch_command() is next in line with 96.1% of the execution time.
Changing this to a spinlock completely removes the bottleneck.
Optimization 7: LOCK_alarm in thr_end_alarm() and thr_alarm()
my_net_read() calls my_real_read() which calls the functions thr_end_alarm() and thr_alarm(). At this point in the optimization these 2 calls required 99.5% of the CPU time between them. Replacing LOCK_alarm with a spinlock fixed this problem.
Conclusion:
Without too much effort it is possible to make a huge improvement to the threading performance of MySQL. The fact that such bottlenecks have not yet been investigated may be due the fact that MySQL currently has no performance analysis team.
Following the last optimization, execution time was divided as follows:
25.8% of the time in net_end_statement(), which hangs in net_flush()
32.8% of the time in my_net_read()
7.6% in ha_pbxt::index_read(), this is the time spent in the engine
32.2% in init_sql_alloc() which waits on the spinlock in malloc()
From this you can see that the optimization is almost optimal because the program is spending almost 60% of its time waiting on the network.
However, it is also clear where the next optimization would come from. Remove the call to malloc() in init_sql_alloc() which is called by open_tables(). This could be done by reusing the block of memory required by the thread, from call to call.
Ultimately, the goal of optimizing for scale like this is to bring the code to the point that it is either network, CPU, or disk bound. Only then will the end-user really see an improvement in performance as the hardware is upgraded.
I think I have shown that it is worth putting some effort into such optimizations. Even more so as multi-core systems become more and more commonplace.
MySQL
optimization
scaling
concurrency
PBXT
PrimeBase-XT
contension
PBXT compiles without change under MySQL 5.1.25!
This has never been the case before. Just search the PBXT code for MYSQL_VERSION_ID, and you will find things like:
#if MYSQL_VERSION_ID XT_RETURN_VOID;and, even worse:
#else
XT_RETURN(0);
#endif
#if MYSQL_VERSION_ID #if MYSQL_VERSION_ID >= 50124The lack of changes that affect pluggable storage engines can only mean that the bug fixes required are diminishing in scope.
#define USE_CONST_SAVE
#endif
#else
#if MYSQL_VERSION_ID >= 60005
#define USE_CONST_SAVE
#endif
#endif
And I believe this is a far better gauge of whether GA is close than any other marketing orientated statements! :)
MySQL Conference Liveblogging: Introduction To The BLOB Streaming Project (Wednesday 3:00PM)
- Paul McCullagh presents
- BLOB
- invented by Jim Starkey
- Basic Large OBject
- Binary Large OBject
- photos, films, mp4 files, pdfs, etc
- how MySQL handles BLOBs
- mysql client send buffer -> receive buffer on the server (max_allowed_packet)
- streaming a BLOB
- continuous data stream
- stream BLOB data directly in and out of the database
- store BLOBs of any size (>4GB) in the database
- create a scalable back-end that can handle any throughput and storage requirements. Wouldn't need to know in advance how big the database will get
- provide an open system that can be used by all engines
- provide extensions for BLOB streaming to existing MySQL clients
- why put BLOBs in the database?
- referential integrity (no invalid references), can take a lot of time to sort out missing data
- all data in one place - structured and unstructured. Good for testing
- small BLOBs are handled better by databases
- backups are consistent
- BLOBs can be replicated
- why "not to BLOB" (currently)?
- a BLOB column makes a table slow
- big rows in memory
- sequential scans are not possible
- database becomes too big
- cannot be copied easily
- backups become slow
- space not freed on delete
- database doesn't scale well
- solution to these problems
- a separate BLOB repository, outside of database rows
- references are stored in the table
- allows for incremental backups
- automatic defrag and compaction
- BLOBs not written to binlogs
- repository can be scaled-out
- Paul shows BLOB streaming architecture
- BLOB storage engine (MyBS, heh heh, Baron especially likes this acronym)
- PBXT - streaming enabled engine or other storage engines
- HTTP-based API extension
- BLOB streaming engine
- does not provide conventional table storage (CREATE TABLE syntax only for system tables)
- built-in HTTP server (port 8080 by default)
- BLOB repository
- provides a server-side API
- Temp BLOB timeout
- BLOBs that are not referenced are deleted from the repository
- initially all BLOBs don't have references to them yet
- timeout is used (mysql_temp_blob_timeout)
- Paul shows a very interesting demo
- creates a table of type PBXT
- uploads an image into the BLOB engine directly (using curl)
- gets back a reference id for the newly inserted BLOB
- accesses the image right in the browser using a GET request with the reference id from above
- Paul does a SELECT on the BLOB engine and gets back the earlier inserted BLOB with such things as BLOB size, content type (MIME), and EXIF data
- now Paul shows what happens if you insert a BLOB and don't access it. It then gets automatically purged from the BLOB engine after the BLOB timeout (mentioned above)
- I like what I'm seeing so far. Very interesting work, Paul. Definitely worth checking out the actual slides
- client-side extensions
- JDBC already works get/setBinaryStream(), get/setBlob()
- Google Summer of Code 2008 to add connector to PHP
- still to be done
- mysqlclient library - simple extension to do HTTP GET and PUT
- other languages (Perl, Ruby, etc)
- backup
- mysqldump will dump repository names and references
- future plans
- distributed repository
- someone asks a question about BLOB repository's performance and benchmarks
- Paul has a convenient benchmark of a BLOB engine compared to xfs
- on the chart, creating and reading BLOBs stays practically the same no matter how many objects are present (in the engine or on the file system), while the file system starts high but quickly degrades almost to 0
- Why does the file system degrade to 0? Did you put all the files in the same directory? This looks very odd
- the next slide shows that when adding threads, the BLOB engine yet again performs the same and scales well while the file system starts degrading after 4-8 threads
- why is the performance better than, let's say, lighttpd pulling from a file system?
- http://www.blobstreaming.org
- http://sourceforge.net/projects/mybs
- http://pbxt.blogspot.com
MySQL Conference and Expo 2008, Day One
Today is the first day at the conference (aside from the tutorials, which were yesterday). Here’s what I went to:
New Subquery Optimizations in 6.0
By Sergey Petrunia. This was a similar session to one I went to last year. MySQL has a few cases where subqueries are badly optimized, and this session went into the details of how this is being addressed in MySQL 6.0. There are several new optimization techniques for all types of subqueries, such as inside-out subqueries, materialization, and converting to joins. The optimizations apply to scalar subqueries and subqueries in the FROM clause. Performance results are very good, depending on which data you choose to illustrate. The overall point is that the worst-case subquery nastiness should be resolved. I’m speaking of WHERE NOT IN(SELECT…) and friends. It remains to be seen how this shakes out as 6.0 matures, and what edge cases will pop up.
The Lost Art Of the Self Join
This was just great. Among many other things, Beat Vontobel showed how a Su Doku can be solved entirely with declarative queries: a very large self-join query against a table of digits and a table of the board’s initial state. I had been promoting this session because last year’s was so very good. I can’t wait to see what he comes up with for next year. Can he find another creative idea? Time will tell.
He wasn’t able to solve a 9×9 puzzle with MySQL because of the limitation on the number of joins, but PostgreSQL had no trouble doing it.
EXPLAIN Demystified
This was my session, of course. (Slides will be on the O’Reilly conference site, if they aren’t already). It went great, I thought. The room was full and people were standing in the back of the room and in the door. The questions came fast and furious; all really good questions. I think we ended up exploring a lot of the MySQL query execution method, strengths, and weaknesses by the time we were through. And I gave away all the remaining Maatkit t-shirts. Hopefully the people who took them will wear them tomorrow and the conference will be sea of deep, rich red shirts.
Someone did an audio recording of the session, but I don’t recall who it was.
Investigating InnoDB Scalability Limits
This session was given by Peter Zaitsev (disclosure: I now work for Percona, the company he co-founded). Peter and Vadim Tkachenko spent a lot of time over the last weeks and months running a dizzying array of benchmarks on MySQL 5.0.22, 5.0.51, and 5.1.24 (if I recall the versions correctly). They were able to show InnoDB’s scaling patterns for a number of different micro-benchmarks on a variety of configurations. If you didn’t attend, please look up the slides if you care about InnoDB performance. A lot of work went into the benchmarks — a lot of work. The slides should be on the conference website or on our blog, http://www.mysqlperformanceblog.com/.
Replication Tricks and Tips
Lars Thalmann and Mats Kindahl gave this session. At a high level, I’d say it was a run-down of all the different ways you can use MySQL replication. Replication is really a flexible tool, and they covered a large array of the most important ways you can use it to achieve different purposes. Many of the techniques they mentioned are implemented by various tools in Maatkit. A couple of the others are implemented in MySQL Master Master Manager and MySQL Semi Multi-Master tools. Don’t re-code these! You can save weeks of work and get quality code by using the pre-built tools. (I built Maatkit, so I know exactly how tricky it is to get some of these things right.)
BoF Sessions
I dropped in on a few BoF sessions, including the Sphinx one and the PBXT/Blob Streaming one. (Keep an eye on the PrimeBase folks — they are up to great things.) Ronald Bradford protected me from those who wanted to get me drunk. Hint: it’s really easy… I have to say, though, Monty’s black vodka was amazing.
Speaking of Blob Streaming, Paul McCullagh and I were talking earlier in the day about the project’s name, MyBS. This has been smirked about a few times. I think it’s a great name, because after all my initials are BS (I usually insert one of my four middle names in to alleviate this problem, but I digress). The conversation went like this:
Me: I like it. My initials are BS.
Paul: BS actually means British Standard, so it can’t be bad.
Me: Better than American Standard. That’s a toilet.
We also debated the merits of watching the original move The Blob. It’s a classic. It must be good.
Beat Vontobel, benchmarks, innodb, Lars Thalmann, Mats Kindahl, mysqluc2008, Paul McCullagh, pbxt, Percona, Peter Zaitsev, replication, Sergey Petrunia, Sphinx, Su Doku, The Blob, Vadim TkachenkoReplication is dead, long live Replication!
Arjen Lentz says "I think Brian is right...", and Frank Mash confirmed: "what Brian says about replication, caching and memcached is very true".
Just like Video killed the Radio Star it looks like maybe Memcached killed the Replication Hierarchy!
But of course, Brian and others are talking about replication for scaling reads.
In my session on PBXT next week at the conference I will be talking about how we plan to use synchronous replication to produce an HA solution for MySQL at the engine level.
I will also discuss how some flexibility in the PBXT architecture makes it possible to actually scale writes efficiently as mentioned by Arjen in his blog.
So don't miss it:
10:50am - 11:50am Thursday, 04/17/2008
Ballroom G
Storage Engines at the MySQL Conference
I’ll be following closely the progression of Storage Engines available in the MySQL Database server, well soon to be available when 5.1 gets to GA (hopefully by end of Q2 which is what we have been told). Tick, Tick, time is running out.
PrimeBase XT (PBXT) and Blob Streaming is obviously my clear focus, actually now working for PrimeBase Technologies, the company which I want to note for people is an Open Source company, committed at providing an open source alternative to the other commercial players. You also have at the MySQL Conference talks on the the existing InnoDB from Innobase (a subsidiary of market RDBMS leader Oracle). There is a Nitro presentation, an Infobright presentation, no Solid presentation surprisingly (the IBM news happening after submissions closed). We also have from MySQL, presentations on the internally developed storage engines Falcon and Maria, both products that won’t even be in 5.1 but 6.0, however Maria is presently a different branch of 5.1 so I don’t know how that works. Will it be in 5.1?
But what I want to seek is more news of KickFire, a Diamond Sponsor, an engine with embedded H/W, something that’s been obviously worked on in reasonable stealth. For me it’s not just interesting, it’s a competitor in our technology space, so I’ve been researching Joseph Chamdani and some of his patents.
Plenty of news in the past few weeks on Kickfire including Kickfire Update by Keith Murphy on April 3, Kickfire: stream-processing SQL queries by Baron Schwartz on April 4, Kickfire looking to push MySQL limits by Farhan Mashraqi on April 4, and Kickfire Kickfire Kickfire by Peter Zaitsev on April 4, and myself back on March 23.
So what can I make from the lack of company information and posted information to date.
- Hardware based acceleration.
- No Solid State Drive (SSD) Technology, at least not yet but C2App mentions SSD.
- Data Warehousing, lending to thinking it’s not a transactional storage engine
- A new storage engine and a new approach to data storage. I find this surprising, as it takes years to develop a feature complete storage engine, and most new 5.1 storage engines are indeed existing products, take Nitro, Solid, Infobright and Falcon. Only PBXT has been written from the ground up for MySQL 5.1, so looking to know more about it’s development
- Expensive, it’s dedicated H/W + (assuming) MySQL Enterprise + Storage Engine
Come and get a t-shirt at UC2008
Here I am at my desk sporting the PrimeBase supporters t-shirt that will be available at the exhibitors booth at the 2008 MySQL Conference. The front is rather uneventful with the official logo, but the back will be worth the experience. So everybody interested in supporting PBXT as the transactional storage engine for MySQL developed by the community and for the community, please come and see us and mention the secret password.
We have been placed way back in the right hand side of the exhibitors hall at booth 518, in front of the Open Source and OEM providers.
MySQL
databases
professional
Technologies
PBXT
primebase
mysqluc08
PrimeBase Technologies - Day 1
Today I started my new job at PrimeBase Technologies. The company that has brought you the PBXT and Blob Streaming Pluggable Storage Engines for MySQL 5.1.
My move to Germany has gone mostly without incident and now I’m settling in to different weather, language and food, plus the change in time zones +6 hours.
A smaller company from my previous, but I’m part of a larger group then expected. One of 26 people in the office. It’s good to have a desk, a big monitor (and definitely not a German keyboard) and see and talk to people on various topics and interests in comparison to either past work at home by myself, or on a new customer site each week during my consulting days.
Preparations for the upcoming MySQL Conference which includes presentations by myself and Paul McCallagh as well as being part of the Exhibitors Hall high on our agenda.
My role also entails a far greater involvement in the MySQL Community, something I’m very much looking forward to. Stay tuned, plenty of exiting news for and around the conference in the next few weeks to share.
MySQL
german
experience
databases
professional
PBXT
primebase
A day with Paul McCullagh - Architect of PBXT and BlobStreaming
Continuing on from my lightning visits with Jan Kneschke and Michael Zinner, today I got to spend a day with Paul McCullagh at his home in Hamburg Germany.
Paul is the architect of the PBXT Pluggable Storage Engine for MySQL 5.1, and also the Blob Streaming Storage Engine. His work was acknowledged with the MySQL Community contributor for the year in 2007. The successful PrimeBase product for the publishing Industry in Europe and North America also now uses PBXT for underlying data storage which is great to see a company use it’s own products.
Like each friend and expert this week, Paul develops using Mac, but at least uses the US English version & keyboard layout, not the German layout. As Paul states, “the German version is not productive for coding”.
Paul uses NetNewsWire Lite as his RSS feeder, as I observe some PlanetMySQL links. I’m more old school, I just go to the site every few days. I really should move from the dark ages, one advantage is the RSS feeder shows you what you have read.
However, there is a particular reason why I visited Paul, other then to spend some time with a good friend. I’m doing more investigation regarding making coding changes to MySQL, and I’m very out of the development cycle. I’ve forgotten more then I remember with C++ and in particular the tools and techniques used to develop, debug and deploy. Of course I know what I need to do, I just need to know what now is the most efficient means of doing today.
Paul uses Xcode to view and edit his MySQL code. The development environment provided by Apple is good for Java and C++. As an Eclipse person from my Java days, I was using CDT for my C++ environment. XCode is used for project management (i.e. looking at files in project) and for file editing. A standard terminal is used to do make. Xcode can do a quick compile for syntax checking and you get a list of errors and hot links to lines of code.
The next key part is debugging, and you can very easily define executables and enable you to run in an integrated debugger. A good option testing is to run mysql test with is –manual-gdb. This gives you the command you can run. What Paul does it copy the parameters, and add to an executable defined within XCode, the arguments from the –manual-gdb output.
The MySQL source tree was added as a project but only partial source directories such as (storage,sql,include) are required. You do need to make sure it does compile (but necessary for a build via Xcode.)
Additional debugging help is In xcode you can do jump to variable definition. So you can add breakpoints, and then run the defined executable that was build via command line make, and copy mysqld binary to initially deployed directory.
Regarding testing, we got to talk again about the issues with the MySQL Test Suite including the issues I encountered while working with Nitro. Hard coded storage engine syntax such as such as engine type=Innodb make testing more complicated then necessary. MySQL test requires some modifications for pluggable storage engines. You need to add a row to the plugins table via mysql_system_tables_data.sql to enable –default-storage-engine=pbxt as a command line argument. Paul did state that on using the MySQL testing suite, the Perl version is a great improvement in performance.
So now I understand the environment, I get onto my problem, and we are specifically looking at the thread sort_buffer and sql/filesort.cc
So when I ask about a few lines of specific code, Paul shakes his head, and says “This looks crazy”. After we spend some time on it, his comment is upgraded, “this doesn’t look crazy, it is crazy. When you look at the effect of the code, it appears it can be done in 3 lines, and it would make sense what it is doing.” This is worth further discussion in a future post.
One last tip learned. You need to be careful with multiple versions and open projects of the same, not to make changes 1/2 in one version and 1/2 in another version.
Just what are MySQL 9.x features?
Top marks to Jay Pipes for getting the Forge 2.0 finally out after quite some time, as well as in the midst of the MySQL Conference he is organizing.
I am worried however about some of the content, as shown in the screenshot below, the opening page lists Worklog tasks/features for versions 6.x or 7.x , that’s ok, but features in 9.x. Where is the practicality of thinking more then 2 releases ahead, and just having a future bucket. Indeed, we have 5.1 and 6.0 already frozen and not releases, so 6.x is already 3 releases out.
Tonight we were told at the NY PHP Meeting MySQL 5.1 is not due to late Q2, so that’s at least June 2008.
The MySQL 5.1 Release Notes reveals a history that I don’t find very flattering.
- MySQL 5.1.3 (29 November 2005)
- MySQL 5.1.9 (12 April 2006) *First reported beta via docs
- MySQL 5.1.22 (24 September 2007: Release Candidate)
I hope that Sun will take on board this very slow release cycle of producing GA products, the last version MySQL 5.0.15 (19 October 2005: Production) 2 years and 6 months ago.
I’m even more interested then previously in the ultimate release and success of MySQL 5.1 as this is a pre-requisite of my new employer’s key product the PBXT Storage Engine for MySQL.
My passion for Open Source
I am a very strong proponent of Open Source (excluding my Macbook). Joining MySQL Inc was a wonderful achievement, being part of the team behind the most popular open source database. Leaving MySQL was not an easy decision due to the people, but the Sun transition and requirements did help. However it is no surprise I am joining another open source company - Primebase Technologies in Hamburg, Germany. My association with the MySQL Community will only be strengthened with my full work and support behind the PBXT and Blob Streaming pluggable storage engines for MySQL.
It is actually poetic that I am joing Primebase for I have the auspicious recognition while an active part of the MySQL community of introducing Paul McCullagh to MySQL some 2 years ago. Only weeks later, PBXT was MySQL news in the opening CEO keynote my Marten Mickos at the 2006 MySQL Users Conference.
I expect this opportunity will increase my contributions, in particular in the lacking areas of instrumentation and memory management. As well I will now be focusing my efforts on MySQL 5.1 and ensuring this is of the highest quality for the MySQL Community. While my open source code contributions have been small to date, my first some years ago adding in JMeter support for MySQL Stored procedures and Transactions, the feeling of seeding instead of leaching is a wonderful thing. I look forward to a greater opportunity with the team at Primebase Technologies.
New version and a new home for PBXT!
Oh, which reminds me: PBXT now has a new home at http://www.primebase.org, so take a look around! I have actually found a bit of time to write some documentation. Right now the documentation describes building, installation, and the PBXT system parameters. Future additions will include information on performance tuning and a road map for PBXT development.
But there is more to the new home than just a new web-site. The PBXT project is now owned and funded by PrimeBase Technologies, an open source software development company. So altogether this is a important step forward on the road to my goal which is to make PBXT a significant contribution to the MySQL community and business/eco-system.
Besides full durability, the latest release includes the following improvements:
- Calculation of index statistics as required by the optimizer (execute FLUSH TABLES to refresh the statistics).
- New system variables: pbxt_log_cache_size, pbxt_log_file_threshold, pbxt_transaction_buffer_size and pbxt_checkpoint_frequency (details here).
- Implementation of SELECT FOR UPDATE, which performs row-level locking to prevent concurrent updates.
- Group commit: increases update throughput by committing multiple transactions concurrently.
- Support for SHOW ENGINE PBXT STATUS, which displays information about memory usage.
Even better would be to be able to specify this per table. Now if only MySQL would allow engines to specify custom table attributes...
PBXT & DBT2: Dubugging C/C++ 101
Which bug first?
Well I immediately ran into 3 bugs. Isn't it funny how bugs often come in batches, which leaves you thinking: "Oh sh.. where do I start?". Here's my advice: start with the bug that is most likely to disappear if you fix the others!
A simple example, you have 2 bugs: an unexpected exception is occurring, and you're loosing memory. First look for the memory loss, because it may disappear when you fix the exception (because you may be loosing memory in the error handler).
Take things one problem at time:
Another thing: once you have decided for one of the bugs, stick with it (no matter how hard it gets) to the bitter end! Thrashing around will build frustration!
So what happened with the DBT2 test? I started the test and immediately noticed that the engine was throwing "duplicate key" errors (it was too much to hope that this behavior was intended). Next I hit an assertion that claimed that a semaphore was not initialized (but I knew the semaphore was initialized). Finally, on restart after the assertion failed the engine crashed on recovery, in the clib memory manager (not a good sign!).
So were to start? Taking my own advice I quickly secured the state of the database before the restart, and confirmed that I could repeat the restart crash. So that one could wait for later.
The duplicate key error seemed be a fairly stable repeat, so I took a closer look at the semaphore problem. Here I noticed that the assertion was failing because the check bytes that indicate that the semaphore was initialized had been overwritten, not a happy situation!
Make the bug quick and easy to repeat:
This bug was also difficult to repeat, I had to restore a fresh environment to get it to repeat consistently. So this is where I started.
But before we go on: make sure, in such a situation, that you can repeat the bug as quickly and easily as possible. Eliminate as many manual steps as you can, it will save time in the long run. For example, in this case I wrote a line of shell commands to delete and copy in the database to provide the correct starting point for repeating the bug.
Check your last bug fix first!
Unfortunately this bug turned out to be the result of a short laps of concentration during my last bug fix. But I did not notice the error during my testing of the big fix and so I moved on to DBT2. When the error occurred during the DBT2 test, I did not relate the problem to my last bug fix.
If I had, I would have found the problem quick enough by a simple code read of the bug fix again. This has happened to me before, so my advice is: check your last bug fix, even when the new error does not seem to be related!
Debugging C/C++ 101, 3 lessons:
Conveniently for my little refresher course, each of the 3 bugs proved to touch on a different aspect of C/C++ debugging:
Bug 1. Using an uninitialized pointer.
For goodness sake, if you suspect this, then compile you program with optimization on, and the warning for "uninitialized variables" enabled. I didn't do this, and I may have saved myself a lot of time. Anyway, this does not always work (for example if you used '&'). Unfortunately, if the compiler does not help, there is no easy way to find these bugs.
Debugging method: Probing
I call the method I used to find this error "probing". The idea is to write a special piece of check code which tests for the memory overwrite. The semaphore that was being overwritten was not global, but I added some code when it was initialized to set a global pointer to the semaphore. Then I wrote a little check (or probe) function which tested to see if the check bytes were still OK.
Next I spread calls to the check function around my program, trying to close in on the point were things go wrong. When doing this you have 2 difficulties to deal with:
Finding the right thread - If you are probing the wrong thread, then you get very miss-leading results. For example, I started by adding the probes to the engine API functions. When the probe was failing on the entry point it took me a while to realize that the problem must be in the PBXT background threads. So, using the probe try to first isolate the thread(s) that are causing the corruption.
This meant removing all probes from the engine API functions and placing them in the background threads, starting with the main loops. Then by elimination I managed to narrow the problem down to one particular thread.
Dealing with disappearing repeatability - The problem with this kind of bug is that it is really shy! As soon as you start to probe it, it disappears. I mean the changes made to the program change the executions so that the bug does not repeat.
At this stage it is very tempting to leave the debug statements in the code and declare the bug as fixed! But, alas, the bug is still there, it has just moved on to overwrite some other part of memory in some quite little corner.
Here I can give the following advice: When the bug disappears always return to the last repeat point and try taking smaller steps this time.
Another thing: approach the corruption point from the bottom. By this I mean, close the probe in from a point after the corruption has occurred. This is because if the corruption is due an uninitialized stack value, then as you move the probe towards the corruption point from the top, the probe disturbs the state of the stack.
As I mentioned above, when I found this bug it turned out to be a result of the last bug fix, bummer :(
Bug 2. Overwriting memory.
Next I decided to look at the crash on startup. This bug caused a crash in the memory manager. This is most often due to a memory overwrite which has wiped out some of the management data stored per block by the memory manager.
Fortunately I have to right tools to deal with this problem.
Debugging method: Scanning Memory
Its like "don't leave home without it": don't start C/C++ program without a debug memory manager that does at least the following:
- Adds and checks headers and footers on every block of memory allocated.
- Wipes out blocks that are freed (for example set all bytes to 0xFE).
- Always moves a block of memory that is reallocated.
- Records every block allocated, and notes the line number and file where allocated.
- Checks on shutdown that all memory has been freed, and reports blocks not freed.
Now using the fact that I have a list of all allocated pointers I have implemented a function which scans all allocated pointers and checks the headers and footers to tell me if anything is corrupted.
So to find the recovery crash I added the scan call to some of the loops that do recovery and soon managed to narrow things down and find the point were the corruption was occurring.
Note that with this method it may not even be necessary to do such a search. One call to the scan routine tells me which block has been corrupted. If it was a simple overwrite of the end of the block, then my debug memory manager will tells me which block it was, and were it was allocated. This may be enough to find the problem.
In my case it turned out that I had taken a pointer to a block and then some sub-function reallocated the block. But this is why it is so important that the debug memory manager always moves blocks on realloc(). If it had not done this, I probably never would have noticed the bug until it happened in some production situation (ugh!).
Bug 3. Concurrency problems.
I was worried about the 3rd bug which was causing an unexpected "duplicate key" error, because I was afraid it might be a conceptual problems. This fear stems from the fact that there are indeed serious conceptual problems involving MVCC and SELECT FOR UPDATE (which requires locking), but fortunately, my fear was unfounded, and it turned out to be just a normal bug, whew!
I new this bug must be related to concurrency because I had tested all aspects of the row level locking in a simple controlled environment.
Debugging method: Trace it
The way to find concurrency problems is to trace them. The better your trace, the easier it is to find the bug. I think it is almost impossible to find a concurrency bug just using the debugger (unless you have a deadlock, for example). This is because in the debugger you only have a snapshot of the situation, and you don't see the interaction between the threads.
In my case the duplicate key error turned out to be the result of a SELECT FOR UPDATE that failed earlier.
There is, of course a big problem with tracing and concurrency. Sometimes the error is robust, and sticks around while you bombard it with printf() statements. Mine was more of the shy type where the timing was really critical, and it disappeared when I added print statements.
In this case, I also have the right tool for the job. It is a trace function which records the information only in RAM, in one huge block of memory which rolls over if necessary. It is worth also taking the bit of extra time to make the trace function handle printf() type syntax, so that it is as easy to using as printf() itself.
What I did was I set a breakpoint at the spot in my code where the duplicate key error is generated. At this point in the code, I built in a call to my "trace dump" function. This function dumps the trace information which I have collected so far to a file.
Then I can examine the trace to find out how I got to this point, and I can also use the debugger to examine the threads and find other information I need.
Now some advice on trace code:
- Firstly, never build in trace code that you think you might need! This is a waste of time if it is never used, and it clutters the code unnecessarily.
- Secondly, when faced with a problem that needs to be traced, do not waste to much time or thought trying to guess what information you will need. Initially, just get something out there. Examining the trace is the best way to decide what information is missing.
- Finally, when you are done, and you have found your bug, you will probably feel quite attached to you new trace code and not want to part with it. Don't worry, I understand, and I am not going now tell you that you have to remove it :) Well, not all of it, anyway.
Well in the end I was very happy with my trace code. It allowed me to pinpoint the bug in SELECT FOR UPDATE, and I added a GOTCHA to my code which you can search for as soon as I get this version released (soon I hope).
OK, so this has been quite a long post, thanks for sticking with me :)
Making PBXT Fully Durable
My first concern in making PBXT fully durable was to what extent I would have to abandon the original "write-once" design. While there are a number of ways to implement durability, the only method used by databases (as far as I know) is the write-ahead log.
The obvious advantage of this method is that all changes can be flushed at once. However, this requires that all data be written twice: once to the log and after that, to the database itself.
My solution to this problem is a compromise, but I think it is a good one. In a nutshell: short records are written twice, and long records are written once. When it comes to durability, this compromise, I believe, is a good one.
If a transaction writes only short records, then one flush will suffice to commit it. Because the records are short, contention on the write-ahead log is at a minimum. If a transaction writes any long records, most of the data will be written once to a data log (as opposed to a transaction log). Contention for writing on the data log is zero (because each writer has its own data log), but two flushes are required to commit the transaction.
By doing this I have saved other transactions having to wait while a certain transaction copies a large amount of data to the transaction log. Although the transaction log uses a double buffering system, this will still cause a hold up.
In summary: if you have a transaction which writes large records, then it will basically just hold up itself, and not everybody else.
Another innovation I have introduced to reduce contention on the transaction log is an "operation sequence number".
Normally operations must be synchronized on the transaction log to ensure consistency. For example, the allocation of a block must be written to the log before usage. But this means all threads need to lock the transaction log when performing an operation.
Instead of doing this, I issue a unique sequence number for each operation done on a table. The operations are then written to the log in batches without concern about the order.
The process that then applies the changes in the log to the database sorts the operations by sequence number before they are applied. This is also done on restart, during recovery.
New PBXT/MyBS release enables JDBC-based BLOB streaming!
For this release I have completed changes to the MySQL Connector/J 5.0.7, to allow BLOB data to be transparently stored and retrieved from the MyBS BLOB repository. The new version of the driver is called MySQL Connector/J SE (streaming enabled).
Uploading a BLOB is as simple as using setBinaryStream() or setBlob() on INSERT or UPDATE. By using getBinaryStream() or getBlob() after a SELECT you get direct access to the data stream coming from the repository. More information and some examples are provided in the documentation at: http://www.blobstreaming.org/documentation.
To try this out you need to install the latest versions of PBXT and MyBS. Both are available from: http://www.blobstreaming.org/download.
Binary versions of the storage engines are also available for MySQL 5.1.22 running on 32-bit Linux and x86 Mac OS X. The modified version of the JDBC source code is included in the MyBS source code distribution, but the driver can also be downloaded here.
I have included a small test program, TestJDBC.java, as part of the JDBC driver. So once you have installed the engines, you can test BLOB streaming as follows:
java -cp mysql-connector-java-5.0.7se-bin.jar TestJDBC
TestJDBC connects to a local MySQL server, creates a PBXT table and tests INSERT and SELECT of rows containing BLOBs. The program also serves as an example of how to do BLOB streaming with JDBC, but this is all pretty much standard stuff.
To get started quickly, the most important things to note are:
- Set EnableBlobStreaming=true in your JDBC connection URL.
- Streamable BLOBs can only be stored in LONGBLOB columns in PBXT tables.
- Use setBinaryStream(), setAsciiStream() or setBlob() and specify the length to upload a BLOB.
As usual, any comments, questions or bug reports can be sent directly to me: paul-dot-mccullagh-at-primebase-dot-com. Make sure you put the word PBXT or MyBS in the e-mail title to make it through my spam filter! :)