Author Topic: Feature Request - Database relocation  (Read 7208 times)

jondecker76

  • Alumni
  • wants to work for LinuxMCE
  • *
  • Posts: 763
    • View Profile
Feature Request - Database relocation
« on: February 06, 2008, 06:56:12 pm »
As I am building my core, I have been thinking about data integrity. Media is the first thing that comes to mind. But what about the countless hours that go into filling in metadata etc....
In my core, I have one system drive, and 3 drives in raid 5 for my media, I figured this would make it easy to do a complete reinstall on the system drive if I ever had to, without risking losing all of my media. Then I thought it would be nice if I could have RAID protection for the database itsself. Its easy to see how someone could have literally thousands of hours into tweaking their database. Doesn't that deserve to be protected as much as the media?

What does everyone think?

jondecker76

  • Alumni
  • wants to work for LinuxMCE
  • *
  • Posts: 763
    • View Profile
Re: Feature Request - Database relocation
« Reply #1 on: February 06, 2008, 10:53:50 pm »
Just to sum up what I'm proposing -
Just a setting in the admin page that lets you relocate the database to another directory/disk/partition (so you can put it on your raid array, etc...)

colinjones

  • Alumni
  • LinuxMCE God
  • *
  • Posts: 3003
    • View Profile
Re: Feature Request - Database relocation
« Reply #2 on: February 06, 2008, 11:36:27 pm »
I really wouldn't rely on RAID as your only form of protection. Frankly, from my experience of hardware RAID, it fails much more than you would expect it to, and data recovery from a RAID'd drive is much more difficult than a simple drive. Software RAID is often worse!

Check out another thread I am running at the moment on the subject of metadata for media. Under 0710b3 and up it seems that the metadata is much more transportable and less reliant on the database, which could address your concerns. Unless you are concerned about all the other stuff in the database, like your device configuration.

In which case, I would say look for a MySQL backup tool and back the DB up yourself, to your RAID drive. I am hoping also to then back up my media drives incrementally to DVDs ( which in your case would include your DB) - yet to track down a reasonable backup tool to do it...

PowrrrPlay

  • Regular Poster
  • **
  • Posts: 40
    • View Profile
Re: Feature Request - Database relocation
« Reply #3 on: February 07, 2008, 03:44:35 am »
jondecker;

I totally agree with your statement.   I would even go as far to say that since mysql is nolonger open source that the linuxmce project follow what all serious "open" source projects are doing and plan to migrate/fork to postgresql.

BTW, I have been running hot swap raid 5 for more than 4 years.   have rebuilt my array on the fly on two different occations because of a HDD failure.  Love it.   I still back up my array because if there is a "B and E" or other major hardware failure you always need to go to your backup.   In any case I like your proposal.

Frow what I am reading and witnessing the project is more concerned about simplicity rather than technical excellence.  Going to be tough competing with bill's deep pockets.  Cheers.

1audio

  • Addicted
  • *
  • Posts: 552
    • View Profile
Re: Feature Request - Database relocation
« Reply #4 on: February 07, 2008, 03:48:02 am »
I would also like a backup system that goes to DVD. I could then get real use from my Powerfile.

RichardP

  • Veteran
  • ***
  • Posts: 131
    • View Profile
Re: Feature Request - Database relocation
« Reply #5 on: February 07, 2008, 06:57:11 am »
In which case, I would say look for a MySQL backup tool and back the DB up yourself, to your RAID drive. I am hoping also to then back up my media drives incrementally to DVDs ( which in your case would include your DB) - yet to track down a reasonable backup tool to do it...

I use WebMin for a quick and easy solution to ad-hoc backups. I've heard of people using it as part of a structured backup plan, though.

Best Regards,
Richard

RichardP

  • Veteran
  • ***
  • Posts: 131
    • View Profile
Re: Feature Request - Database relocation
« Reply #6 on: February 07, 2008, 07:34:25 am »
jondecker;

I totally agree with your statement.   I would even go as far to say that since mysql is nolonger open source that the linuxmce project follow what all serious "open" source projects are doing and plan to migrate/fork to postgresql.


Why do you say mysql is no longer open source? There have been no changes, AFAIK.

Best Regards,
Richard.
Best Regards,
Richard

rrambo

  • Guru
  • ****
  • Posts: 221
    • View Profile
Re: Feature Request - Database relocation
« Reply #7 on: February 07, 2008, 03:05:04 pm »
jondecker;

I totally agree with your statement.   I would even go as far to say that since mysql is nolonger open source that the linuxmce project follow what all serious "open" source projects are doing and plan to migrate/fork to postgresql.


Why do you say mysql is no longer open source? There have been no changes, AFAIK.

Best Regards,
Richard.

http://www.reuters.com/article/mergersNews/idUSWNAS661820080116

Sun bought mysql...  but I haven't found anything saying that mysql will not remain open source.

PowrrrPlay

  • Regular Poster
  • **
  • Posts: 40
    • View Profile
Re: Feature Request - Database relocation
« Reply #8 on: February 07, 2008, 03:12:26 pm »
RichardP;

The full function mysql database is not free to the public.   

As well, SUN recently announced their acquisition of the company that owns mysql.  SUN is profit driven.  Expect more changes in the future while SUN squeezes every penny out of that puppy.

There are many companies like mysql that have an entry level product like mysql.   This is how they get you hooked on their crack.   Free to start, then as you require better crack, i mean features you have to pay up.  mysql does not fall under a true "open source" license.   Postgresql does.   It is also fact that; for enterprise computing postgresql is often compared with Oracle, DB2 and SQL Server.

tschak909

  • LinuxMCE God
  • ****
  • Posts: 5505
  • DOES work for LinuxMCE.
    • View Profile
Re: Feature Request - Database relocation
« Reply #9 on: February 07, 2008, 03:37:08 pm »
Powerrrplay: it would be a wonderful exercise to port sql2cpp, mysql_wrapper, and the various shell scripts, perl scripts, and ruby scripts to use another database, such as PostgreSQL. Would you like to try your hand at it?

-Thom

PowrrrPlay

  • Regular Poster
  • **
  • Posts: 40
    • View Profile
Re: Feature Request - Database relocation
« Reply #10 on: February 07, 2008, 04:00:47 pm »
LOL;    Thom

I will leave that one up to the project manager.   But it is good food for thought.  Replication and a pile of other features you need in a commercial DBMS are free/open source in Postgresql.   What is your favourite flavour of crack?

http://en.wikipedia.org/wiki/PostgreSQL - FOR THOSE WONDERING - http://www.posgresql.org

as well: PostgreSQL 8.3 Released 3 days ago
Posted on 2008-02-04
Posted by josh@postgresql.org

Today the PostgreSQL Global Development Group releases the long-awaited version 8.3 of the most advanced open source database, which cements our place as the best performing open source database. Among the performance features you'll be excited about in 8.3 are:

    * Heap Only Tuples
    * BGWriter Autotuning
    * Asynchronous Commit
    * Spread Checkpoints

   

    * Synchronous Scan
    * "Var-Varlena"
    * L2 Cache Protection
    * Lazy XID


8.3 also has a lot of cool features for PostgreSQL DBAs and developers, including:

    * CSV Logging
    * SQL/XML
    * MS Visual C++ support
    * ENUMs

   

    * Integrated Tsearch
    * SSPI & GSSAPI
    * Composite Type Arrays
    * pg_standby


There are many, many other features included in this release. Visit the features list and the features matrix for more information, and browse the release notes to see the more than 300 patches that went into the release. You can even visit the press page.

colinjones

  • Alumni
  • LinuxMCE God
  • *
  • Posts: 3003
    • View Profile
Re: Feature Request - Database relocation
« Reply #11 on: February 07, 2008, 09:11:28 pm »
RichardP;

The full function mysql database is not free to the public.   

As well, SUN recently announced their acquisition of the company that owns mysql.  SUN is profit driven.  Expect more changes in the future while SUN squeezes every penny out of that puppy.

There are many companies like mysql that have an entry level product like mysql.   This is how they get you hooked on their crack.   Free to start, then as you require better crack, i mean features you have to pay up.  mysql does not fall under a true "open source" license.   Postgresql does.   It is also fact that; for enterprise computing postgresql is often compared with Oracle, DB2 and SQL Server.

RichardP

Not sure what Powrrrplay is referring to either - MySQL is still open source! Perhaps (s)he is getting mixed up with MaxDB? Or MySQL Enterprise? MaxDB was a project that SAP split off from MySQL years ago as a supported DB for SAP (I use it at work), this isn't open source (at least in the GPL sense). And MySQL Enterprise is a pay-for product, but still part of the open source project - its not that different from the way Canonical offer pay-for support services for the Ubunutu family, they are still open source, and free to use, but you need to pay if you want professional/enterprise support... which is entirely reasonable if you ask me!

BTW - I'm sure that PostgreSQL is a wonderful product, however LMCE just needs a simple database for storing config and metadata, I can't see any requirement for massively high speed transaction processing, other performance enhancements and all the other features. From what I have seen performance is unlikely ever to be a problem, and the vast majority of activity is processing relatively simple SQL Select statements. MySQL is more than capable of this (and much more). I don't see any value in tying up valuable dev time porting to a new platform. Thom - I don't think the irony in your comment came across .... :)

tschak909

  • LinuxMCE God
  • ****
  • Posts: 5505
  • DOES work for LinuxMCE.
    • View Profile
Re: Feature Request - Database relocation
« Reply #12 on: February 07, 2008, 11:44:05 pm »
why are you laughing?

you're talking a big game, why don't you step up to the plate and help make it a reality? you can check out the sources and try it on your copy.

or are you more talk than action?

-Thom


LOL;    Thom

I will leave that one up to the project manager.   But it is good food for thought.  Replication and a pile of other features you need in a commercial DBMS are free/open source in Postgresql.   What is your favourite flavour of crack?

http://en.wikipedia.org/wiki/PostgreSQL - FOR THOSE WONDERING - http://www.posgresql.org

as well: PostgreSQL 8.3 Released 3 days ago
Posted on 2008-02-04
Posted by josh@postgresql.org

Today the PostgreSQL Global Development Group releases the long-awaited version 8.3 of the most advanced open source database, which cements our place as the best performing open source database. Among the performance features you'll be excited about in 8.3 are:

    * Heap Only Tuples
    * BGWriter Autotuning
    * Asynchronous Commit
    * Spread Checkpoints

   

    * Synchronous Scan
    * "Var-Varlena"
    * L2 Cache Protection
    * Lazy XID


8.3 also has a lot of cool features for PostgreSQL DBAs and developers, including:

    * CSV Logging
    * SQL/XML
    * MS Visual C++ support
    * ENUMs

   

    * Integrated Tsearch
    * SSPI & GSSAPI
    * Composite Type Arrays
    * pg_standby


There are many, many other features included in this release. Visit the features list and the features matrix for more information, and browse the release notes to see the more than 300 patches that went into the release. You can even visit the press page.

RichardP

  • Veteran
  • ***
  • Posts: 131
    • View Profile
Re: Feature Request - Database relocation
« Reply #13 on: February 08, 2008, 03:39:12 am »
LOL;    Thom

I will leave that one up to the project manager.   But it is good food for thought.  Replication and a pile of other features you need in a commercial DBMS are free/open source in Postgresql.   What is your favourite flavour of crack?

What a can of worms this seems to have opened - seems to have hit a nerve somewhere. When it comes to LAMP-type projects, MySQL is (arguably) the most popular choice even though PostgreSQL is available. This is usually because MySQL is smaller, quicker (in development and runtime) and simpler. That choice comes with a tradeoff - you are giving up the scalability and other features. Put yourself in the shoes of someone starting out on a project and weighing up the possibilities. PostgreSQL has good support for transactions and MySQL has only basic support, but if I use PostgreSQL and use transactions, the project will take 20% longer. It's like you say - what flavour of crack do you prefer. In most cases, the Project Leader knows exactly what he is giving up, but is happy to give them up in return for what he is gaining. However, if the application an enterprise-wide commercial application that absolutely must have the sort of features that SQL-Server and Oracle offer, then MySQL goes out the window and PostgreSQL sits higher on the list of candidates.

When a project is under way, though, like this one, changing comes at a very high cost. Changing DB means months of work, only to find at the end of it that you haven't moved an inch in terms of new features.

Best Regards,
Richard.
Best Regards,
Richard

colinjones

  • Alumni
  • LinuxMCE God
  • *
  • Posts: 3003
    • View Profile
Re: Feature Request - Database relocation
« Reply #14 on: February 09, 2008, 08:52:05 am »
LOL;    Thom

I will leave that one up to the project manager.   But it is good food for thought.  Replication and a pile of other features you need in a commercial DBMS are free/open source in Postgresql.   What is your favourite flavour of crack?

What a can of worms this seems to have opened - seems to have hit a nerve somewhere. When it comes to LAMP-type projects, MySQL is (arguably) the most popular choice even though PostgreSQL is available. This is usually because MySQL is smaller, quicker (in development and runtime) and simpler. That choice comes with a tradeoff - you are giving up the scalability and other features. Put yourself in the shoes of someone starting out on a project and weighing up the possibilities. PostgreSQL has good support for transactions and MySQL has only basic support, but if I use PostgreSQL and use transactions, the project will take 20% longer. It's like you say - what flavour of crack do you prefer. In most cases, the Project Leader knows exactly what he is giving up, but is happy to give them up in return for what he is gaining. However, if the application an enterprise-wide commercial application that absolutely must have the sort of features that SQL-Server and Oracle offer, then MySQL goes out the window and PostgreSQL sits higher on the list of candidates.

When a project is under way, though, like this one, changing comes at a very high cost. Changing DB means months of work, only to find at the end of it that you haven't moved an inch in terms of new features.

Best Regards,
Richard.


Richardp - I think the point is moot. Powrrrplay is incorrect saying that MySQL is not open source. It is, it is GNU GPL. Both Community and Enterprise - and they share a common code-base, Enterprise server is more about support and guaranteed updates, moving to another database isn't even relevant. As to the other comment about now that Sun own it, look out for coming changes. This is unnecessarily cynical and unrealistic anyway. Even if Sun decided to close the source and start charging for the product - highly unlikely, this isn't necessary for the company to make money, they already are from the support subscription - the existing and previous versions are still GNU GPL, with millions of installations worldwide and many more devs involved, the existing version would simply be forked and continue independent development.