Tuesday, March 21, 2006

Concerning: PBXT, MySQL and Mountains

Is PrimeBase XT (PBXT) just another transactional engine for MySQL? I think (hope) not! But, even if it was, it would still be justified considering the current lack of an independent (i.e. not owned by a monopolist) transactional engine. But I started developing PBXT at the beginning of last year for a much more basic reason, after all I had no idea what Oracle was about to do.

I started programming PBXT simply because it was there!

But wait, you say, "we climb mountains because they are there, we don't program software because it is there", because it isn't (before we start). Well I think many programmers can relate to that statement anyway. Programming is much like climbing a mountain. You start out with great enthusiasm, wanting to do things better and quicker than before. But as you get higher, the air gets thinner. You struggle on and as you approach the top it gets tougher and tougher (we all know how long it takes to finish that last 10%)!

But when you reach the peak, the summit, there is nothing like it! You have conquered the beast of chaos and doubt, the air is fresh and you can see for miles. Inspiring, isn't it? Well I think so.

OK, so programming is like climbing a mountain, but how can you climb a mountain that isn't there? Well, it is there, actually, isn't it? Right there, in your head.

And so it was with PBXT. After quite a few years of programming databases I had some ideas (questions actually), for example: what would it be like if the database wrote only sequentially? Must a database always write twice? Can we eliminate read locks? Is it possible to commit or rollback without doing any work? Is it possible to startup without recovery?

Using some known techniques and combining them with a few new ideas, I have found some answers to these questions. You can read about it in my White Paper at http://www.primebase.com/xt.

So have I reached the top? Of course not, nobody ever reaches the top! Seriously though, I have a few things on my to-do list, but mostly PBXT is untested. So I am thankful that testing is the first area in which I have been offered help (thanks Ronald) and also from MySQL themselves (thanks Brian). Any more help would be welcome and we'll turn this into an expedition.

I plan to keep the address above posted with the latest information, versions, etc. and we'll just take it from there, one step at a time...


Anonymous said...

Hello ...

I read your article with great interest!

I am doing support, test and QA an some kinds of coordination for the Webyog Company (delivering the SQLyog MySQL graphical client. Refer to webyog.com

I post here an anonymized discussion in our support ticket system (a question regring our DATA SYNC tool), that might interest you:


Let's say I have a contact database. One online, one for testing. If I update the First Name field online, and the Last Name field on my testing database, do both changes apply... or does one "win" over the other? In MS Access, this is called "row level syncing". As long as the same field hasn't changed, it will apply both updates.

My answer:

The answer is that 'one does "win" over the other. *soruce* overwrites *target* with rows where the PK is identical. I cannot tell what Access does, but I think it must use some logs (inside the .mdb-file) of some kind. It is simply only possible this way! I am not able to understand how else a computer should be able to 'know' which data are the correct ones.

It would theoretically be possible to use the MySQL 'binary log' for it - a least with the binary log configured for 'row-based replication'. Configuration of the binary log for 'row-based replication' (and not 'statement based replication') will be necessary if changes written by ROUTINES (SP's and FUNCTIONS), TRIGGERS and EVENTS shall be possible. Configuration of the binary log for 'row-based replication' is only possible with MySQL 5.1 and it is still an early beta.

Also there are new storage engines one their way using other log principles/methods. This one is very interesting in the respect that the log file at the same time is the data file itself: http://dev.mysql.com/tech-resources/interviews/paul-mccullagh-pbxt.html . That principle could actually be expanded a little to support something like this.

It is also the binary log that is used by MySQL replication. Refer to: http://www.webyog.com/faq/11_60_en.html

But first of all: there is no 'binary log' for the database in MySQL - only for the complete server (and that is why is it never available with shared hosting),

And second: the binary log can easily be deleted (it is written in 'chunks' of usually 10 MB and only the last one is locked by the system). It could easily give a false feeling of security!

I shall not say that we will never support row-based synch with MySQL 5.1 as it is now theretically possible. Actually it is very interesting. But there are still too many incompatible changes between MySQL 5.1 beta releases. And the binary low for a heavily burdoned server can easily be 50 or 100 GB per year in size (not at least when configured for row-based replication). We would then have to 1) read all that data (and it is (a) sequental file(s) - it cannot be read 'from end to beginning' 2) convert from binary format 3) find the 'bits' that we need for every single value in the database (or most often rather: find nothing!).

I can't help it but MySQL is not Access. MySQL has focus on performance and handling of huge amounts of data.

But new storage engines, new logging priciples are coming. Will will know much more in only 4-5 months I think (then they first dozen of third party engines prpbably will have seen day of light!)


Now consider: would it be possible to expand the data storage format of PrimeBase a little, so that it always keeps 'the last changed value' of a cell? If so we can do row-based syncronisation with it - without the use of the MySQL binary log!

Peter Laursen

(by the way: in a few weeks our program is ready to support engines like yours! As soon as we relase version 5.17 don't hesitate to download the TRIAL. And contact us if you like)

Paul McCullagh said...

Hi Peter,

Thanks for your very interresting contribution. PrimeBase XT already keeps a list of the previous versions of a row. This would allow a row-based replication system to determine exactly which fields have changed, and when. For the engine, it is just a matter of how long this information is maintained. Currently the data remains until all transactions have ended that could possibly still require this information.

For the purpose of replication, the data could be maintained a little longer, until a replication thread has read the data. In general, this method is more efficient than the current binary log method used by MySQL, because the data is only written once. What we will need in the future is an extension to the MySQL engine interface that allows the server, or other tools, to take advantage of this optimization.

I think it is really great that SQLyog MySQL GUI will soon support 3rd party storage engines. I look forward to seeing what you have done.

Best regards,