Re: [sqlite] SQLite to MySQL
First, WHY move off sqlite. Hosting companies are a dime-a-dozen. sqlite is a library, it's part of your code, you are not asking the provider to set up or maintain another RDBMS engine. Option one in my opinion is to find a cooperative hosting company and move you app unmodified. IF your app is currently running on your PC and IF it's currently running sqlite and IF your design and SQL code are reasonably optimized I highly doubt you'll find anything that cane come close speed-wise. IF you do have a poor design and are forced to fix it in order to migrate it may be faster once you are done, but that doesn't count. ;-). I'm an Oracle DBA I make good money doing it, but I'm not beyond using a more appropriate database for a given situation. Just to muddy the waters a bit more... Oracle has a free version for windows and linux that is good for a 1 GB or smaller database if I recall. DB2 has just been released for free on windows and linux for machines with up to 2 dual Core processors and 4 Gig RAM. No limits of database size. Oracle just changed the way they license multi-core processors but List price for the enterprise edition for a 2 CPU dual core machine it still $100,000 list. I currently support about 20 Oracle instances driving 5 commercial applications which cost about 150 million dollars to develop. From what I've seen so far this free version of DB2 running on good linux hardware would outrun the sun/oracle systems for about 1/5th the cost. Jim C. Nasby wrote: See also http://sql-info.de/mysql/gotchas.html. About the only downsides I can think of with PostgreSQL is that it's out-of-the-box configuration is meant for like a 486 and that not quite as many hosting providers offer it. That url has about 100 downsides to MySQL (many of which are rather serious). PostgreSQL is also strives to stay as close to ANSI SQL as possible and makes it nearly impossible to configure your database in such a way that it's only a matter of time and luck before you end up with corrupted data. Granted, MySQL added a lot of features in 5.0, but they still focus much less on doing things the right way than PostgreSQL does. And remember: Feb 31st isn't a date. :) On Thu, Feb 09, 2006 at 07:47:47AM +1100, John Stanton wrote: PostgreSQL implements standard SQL as well as the features of an enterprise DBMS. On that basis if you are changing it makes sense to change to the fuller-featured product, one in the same class as Oracle and DB2. In the short term Mysql could be as good as PostgreSQL. Fanda Vacek wrote: I'm not sure, if Postgres is better choice than MySQL. I have used both of them to find out which is the better one. Both of them can do almost anything. The choice is a matter of taste and person. We are free to choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1. Sorry for writing this to SQLite mail-list. Fanda On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]> wrote: Jim C. Nasby wrote: On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote: Hi there, I use SQLite on my website for 2 years now. I do like SQLite a lot and will use it for a lot of new web projects but, because I got more and more traffic, I consider to move this one to MySQL in order to reduce the over load of my computer (I host it @ home). Do you know if there is a way to convert easily an SQLite database into a MySQL one ? (I'm especially thinking about the escapestring and other "create table [myTable] {" issues...) FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is to MySQL, so it might me easier to migrate that direction... If you are migrating to an enterprise level DBMS, PostgreSQL is a better choice than Mysql. It is fully featured with all the qualities of DB2 and Oracle but without the expense. Note that you will need considerably more machine resources to run a "heavier" DBMS than Sqlite. For example on our 166MHz, 256MB RS/6000 PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series it is lightning fast. Sqlite runs fast on the 166MHz machine.
Re: [sqlite] SQLite to MySQL
On Fri, Feb 10, 2006 at 04:44:30PM +0100, Fanda Vacek wrote: > You can choose what to use. An Elephant or a Dolphin :)) Or you could use both! :P http://commandprompt.com/images/mammoth_versus_dolphin_500.jpg
Re: [sqlite] SQLite to MySQL
You can choose what to use. An Elephant or a Dolphin :)) Fanda On Thu, 09 Feb 2006 02:43:13 +0100, Jim C. Nasby <[EMAIL PROTECTED]> wrote: See also http://sql-info.de/mysql/gotchas.html. About the only downsides I can think of with PostgreSQL is that it's out-of-the-box configuration is meant for like a 486 and that not quite as many hosting providers offer it. That url has about 100 downsides to MySQL (many of which are rather serious). PostgreSQL is also strives to stay as close to ANSI SQL as possible and makes it nearly impossible to configure your database in such a way that it's only a matter of time and luck before you end up with corrupted data. Granted, MySQL added a lot of features in 5.0, but they still focus much less on doing things the right way than PostgreSQL does. And remember: Feb 31st isn't a date. :) On Thu, Feb 09, 2006 at 07:47:47AM +1100, John Stanton wrote: PostgreSQL implements standard SQL as well as the features of an enterprise DBMS. On that basis if you are changing it makes sense to change to the fuller-featured product, one in the same class as Oracle and DB2. In the short term Mysql could be as good as PostgreSQL. Fanda Vacek wrote: >I'm not sure, if Postgres is better choice than MySQL. I have used both >of them to find out which is the better one. Both of them can do >almost anything. The choice is a matter of taste and person. We are >free to choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1. > >Sorry for writing this to SQLite mail-list. > >Fanda > >On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]> >wrote: > >>Jim C. Nasby wrote: >> >>>On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote: >>> Hi there, I use SQLite on my website for 2 years now. I do like SQLite a lot and will use it for a lot of new web projects but, because I got more and more traffic, I consider to move this one to MySQL in order to reduce the over load of my computer (I host it @ home). Do you know if there is a way to convert easily an SQLite database into a MySQL one ? (I'm especially thinking about the escapestring and other "create table [myTable] {" issues...) >>> >>> FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it >>>is to >>>MySQL, so it might me easier to migrate that direction... >> >> >>If you are migrating to an enterprise level DBMS, PostgreSQL is a >>better choice than Mysql. It is fully featured with all the >>qualities of DB2 and Oracle but without the expense. >> >>Note that you will need considerably more machine resources to run a >>"heavier" DBMS than Sqlite. For example on our 166MHz, 256MB RS/6000 >>PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series >>it is lightning fast. Sqlite runs fast on the 166MHz machine. >> > >
Re: [sqlite] SQLite to MySQL
See also http://sql-info.de/mysql/gotchas.html. About the only downsides I can think of with PostgreSQL is that it's out-of-the-box configuration is meant for like a 486 and that not quite as many hosting providers offer it. That url has about 100 downsides to MySQL (many of which are rather serious). PostgreSQL is also strives to stay as close to ANSI SQL as possible and makes it nearly impossible to configure your database in such a way that it's only a matter of time and luck before you end up with corrupted data. Granted, MySQL added a lot of features in 5.0, but they still focus much less on doing things the right way than PostgreSQL does. And remember: Feb 31st isn't a date. :) On Thu, Feb 09, 2006 at 07:47:47AM +1100, John Stanton wrote: > PostgreSQL implements standard SQL as well as the features of an > enterprise DBMS. On that basis if you are changing it makes sense to > change to the fuller-featured product, one in the same class as Oracle > and DB2. In the short term Mysql could be as good as PostgreSQL. > > Fanda Vacek wrote: > >I'm not sure, if Postgres is better choice than MySQL. I have used both > >of them to find out which is the better one. Both of them can do > >almost anything. The choice is a matter of taste and person. We are > >free to choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1. > > > >Sorry for writing this to SQLite mail-list. > > > >Fanda > > > >On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]> > >wrote: > > > >>Jim C. Nasby wrote: > >> > >>>On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote: > >>> > Hi there, > > I use SQLite on my website for 2 years now. I do like SQLite a lot and > will use it for a lot of new web projects but, because I got more and > more traffic, I consider to move this one to MySQL in order to reduce > the over load of my computer (I host it @ home). > > Do you know if there is a way to convert easily an SQLite database > into a MySQL one ? > (I'm especially thinking about the escapestring and other "create > table [myTable] {" issues...) > >>> > >>> FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it > >>>is to > >>>MySQL, so it might me easier to migrate that direction... > >> > >> > >>If you are migrating to an enterprise level DBMS, PostgreSQL is a > >>better choice than Mysql. It is fully featured with all the > >>qualities of DB2 and Oracle but without the expense. > >> > >>Note that you will need considerably more machine resources to run a > >>"heavier" DBMS than Sqlite. For example on our 166MHz, 256MB RS/6000 > >>PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series > >>it is lightning fast. Sqlite runs fast on the 166MHz machine. > >> > > > > > -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
Re: [sqlite] SQLite to MySQL
PostgreSQL implements standard SQL as well as the features of an enterprise DBMS. On that basis if you are changing it makes sense to change to the fuller-featured product, one in the same class as Oracle and DB2. In the short term Mysql could be as good as PostgreSQL. Fanda Vacek wrote: I'm not sure, if Postgres is better choice than MySQL. I have used both of them to find out which is the better one. Both of them can do almost anything. The choice is a matter of taste and person. We are free to choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1. Sorry for writing this to SQLite mail-list. Fanda On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]> wrote: Jim C. Nasby wrote: On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote: Hi there, I use SQLite on my website for 2 years now. I do like SQLite a lot and will use it for a lot of new web projects but, because I got more and more traffic, I consider to move this one to MySQL in order to reduce the over load of my computer (I host it @ home). Do you know if there is a way to convert easily an SQLite database into a MySQL one ? (I'm especially thinking about the escapestring and other "create table [myTable] {" issues...) FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is to MySQL, so it might me easier to migrate that direction... If you are migrating to an enterprise level DBMS, PostgreSQL is a better choice than Mysql. It is fully featured with all the qualities of DB2 and Oracle but without the expense. Note that you will need considerably more machine resources to run a "heavier" DBMS than Sqlite. For example on our 166MHz, 256MB RS/6000 PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series it is lightning fast. Sqlite runs fast on the 166MHz machine.
Re: [sqlite] SQLite to MySQL
I'm not sure, if Postgres is better choice than MySQL. I have used both of them to find out which is the better one. Both of them can do almost anything. The choice is a matter of taste and person. We are free to choose:)) I'm talking about MySQL 5 and PostgreSQL 8.1. Sorry for writing this to SQLite mail-list. Fanda On Tue, 07 Feb 2006 22:35:09 +0100, John Stanton <[EMAIL PROTECTED]> wrote: Jim C. Nasby wrote: On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote: Hi there, I use SQLite on my website for 2 years now. I do like SQLite a lot and will use it for a lot of new web projects but, because I got more and more traffic, I consider to move this one to MySQL in order to reduce the over load of my computer (I host it @ home). Do you know if there is a way to convert easily an SQLite database into a MySQL one ? (I'm especially thinking about the escapestring and other "create table [myTable] {" issues...) FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is to MySQL, so it might me easier to migrate that direction... If you are migrating to an enterprise level DBMS, PostgreSQL is a better choice than Mysql. It is fully featured with all the qualities of DB2 and Oracle but without the expense. Note that you will need considerably more machine resources to run a "heavier" DBMS than Sqlite. For example on our 166MHz, 256MB RS/6000 PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series it is lightning fast. Sqlite runs fast on the 166MHz machine.
Re: [sqlite] SQLite to MySQL
Jim C. Nasby wrote: On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote: Hi there, I use SQLite on my website for 2 years now. I do like SQLite a lot and will use it for a lot of new web projects but, because I got more and more traffic, I consider to move this one to MySQL in order to reduce the over load of my computer (I host it @ home). Do you know if there is a way to convert easily an SQLite database into a MySQL one ? (I'm especially thinking about the escapestring and other "create table [myTable] {" issues...) FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is to MySQL, so it might me easier to migrate that direction... If you are migrating to an enterprise level DBMS, PostgreSQL is a better choice than Mysql. It is fully featured with all the qualities of DB2 and Oracle but without the expense. Note that you will need considerably more machine resources to run a "heavier" DBMS than Sqlite. For example on our 166MHz, 256MB RS/6000 PostgreSQL is sluggish but on our dual processor 1.2GHz, 2GB P-Series it is lightning fast. Sqlite runs fast on the 166MHz machine.
Re: [sqlite] SQLite to MySQL
On Mon, Feb 06, 2006 at 05:30:39PM +0100, Laurent Goussard wrote: > Hi there, > > I use SQLite on my website for 2 years now. I do like SQLite a lot and > will use it for a lot of new web projects but, because I got more and > more traffic, I consider to move this one to MySQL in order to reduce > the over load of my computer (I host it @ home). > > Do you know if there is a way to convert easily an SQLite database > into a MySQL one ? > (I'm especially thinking about the escapestring and other "create > table [myTable] {" issues...) FWIW, I believe SQLite's syntax is closer to PostgreSQL's than it is to MySQL, so it might me easier to migrate that direction... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
RE: [sqlite] SQLite to MySQL
HELP!! I have a problem with a SqLite install. I've been charged with installing PolicyPage, a web site compliance checking tool with uses PHP4 and Sqlite. I've got the web/php content installed such that Apache will serve it. I have verified that PHP is working on the machine (FC4). I used pear to install SqLite as far as I can tell the install worked. When I hit a page that attempts to tap the database, I am graced with the following error: Unable to load dynamic library /usr/lib/php4/sqlite.so Cannot open shared object file: Permission denied on Line 0... Any help anyone could give would be great. Thanks PCS
Re: [sqlite] SQLite to MySQL
On 2/6/06, Laurent Goussard <[EMAIL PROTECTED]> wrote: > I don't know, I suppose my queries are not as optimized as I thought > (even if this optimization was my leitmotiv for all the development > part), or perhaps it's an apache2+php5 issue on my windows computer... > > But the fact is since the database has grown (like my traffic : 6000 > visitors/day and 22Mb db file), I've got more and more "maximum > execution time" errors at the peak hours. I've monitored them, and it > seems a lot of simultaneous queries are freezing the server and > finally generates this error. > > The interresting point is the same queries sent a testing mysql db > while the sqlite part is not responding anymore are working very well, > So that's the reason why I consider to switch on a mysql solution for > this website. > > Do you got clues concerning conversion ? Herbert posted a good link to a converter. That will get your data, but I don't know how well the queries will translate though. I would think debugging your existing code would be much less work than a rewrite to a new database. If there's a flaw in your design a new database won't fix that.
RE: [sqlite] SQLite to MySQL
Are you using SQLite that is built into PHP5? That SQLite version is 2.8.17, or at least it is on my pc. Anyway, you might want to consider switching to a newer version of SQLite and using PHP's PDO functions which will allow you to connect to a SQLite3 database. In my application, I experienced a dramatic increase in speed by switching to version 3.2.8. -Original Message- From: Laurent Goussard [mailto:[EMAIL PROTECTED] Sent: Monday, February 06, 2006 12:05 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQLite to MySQL I don't know, I suppose my queries are not as optimized as I thought (even if this optimization was my leitmotiv for all the development part), or perhaps it's an apache2+php5 issue on my windows computer... But the fact is since the database has grown (like my traffic : 6000 visitors/day and 22Mb db file), I've got more and more "maximum execution time" errors at the peak hours. I've monitored them, and it seems a lot of simultaneous queries are freezing the server and finally generates this error. The interresting point is the same queries sent a testing mysql db while the sqlite part is not responding anymore are working very well, So that's the reason why I consider to switch on a mysql solution for this website. Do you got clues concerning conversion ? 2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>: > > Hi there, > > > > I use SQLite on my website for 2 years now. I do like SQLite a lot and > > will use it for a lot of new web projects but, because I got more and > > more traffic, I consider to move this one to MySQL in order to reduce > > the over load of my computer (I host it @ home). > > How is this going to reduce load? > > sqlite = mysql - server code > > You're adding server code. More code = More load. >
Re: [sqlite] SQLite to MySQL
There might be two possible causes for this to happen: - query optimization - for example, complex queries are better optimized by MS SQL Server. I don't know about MySql. Could you post some problematic queries? - concurency. SQLite is not that great about concurency. But... there was before the issue of writer starvation and it was fixed. I don't know in which version. Maybe you should give a try to SQLite 3.3.3 and see if the problems persist On Mon, 6 Feb 2006, Laurent Goussard wrote: I don't know, I suppose my queries are not as optimized as I thought (even if this optimization was my leitmotiv for all the development part), or perhaps it's an apache2+php5 issue on my windows computer... But the fact is since the database has grown (like my traffic : 6000 visitors/day and 22Mb db file), I've got more and more "maximum execution time" errors at the peak hours. I've monitored them, and it seems a lot of simultaneous queries are freezing the server and finally generates this error. The interresting point is the same queries sent a testing mysql db while the sqlite part is not responding anymore are working very well, So that's the reason why I consider to switch on a mysql solution for this website. Do you got clues concerning conversion ? 2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>: Hi there, I use SQLite on my website for 2 years now. I do like SQLite a lot and will use it for a lot of new web projects but, because I got more and more traffic, I consider to move this one to MySQL in order to reduce the over load of my computer (I host it @ home). How is this going to reduce load? sqlite = mysql - server code You're adding server code. More code = More load.
Re: [sqlite] SQLite to MySQL
Quoting Jay Sprenkle ([EMAIL PROTECTED]): > > Hi there, > > > > I use SQLite on my website for 2 years now. I do like SQLite a lot and > > will use it for a lot of new web projects but, because I got more and > > more traffic, I consider to move this one to MySQL in order to reduce > > the over load of my computer (I host it @ home). > > How is this going to reduce load? > > sqlite = mysql - server code > > You're adding server code. More code = More load. For applications that are updating and querying at the same time, the locking on sqlite can impose horrible delays. I've got a database that's currently on mysql. There are three main types of interaction: - interactive web applications that both query and update (hundreds per day) - long running "batch" applications that do queries lasting 10 minutes or more (dozens per day) - very long running batch update applications that can take hours to run, but only have to be done once a month. In MySQL, all three types of applications happen simultaneously. In SQLIte, the way locking is implemented means that the interactive web applications can't run in a reasonable time because the batch applications won't allow the updater to commit until the batch job is finished. -- Paul Tomblin <[EMAIL PROTECTED]> http://xcski.com/blogs/pt/ OTOH, the general theme is that lusers should not be allowed to have computers, cars, guns or genitalia. -- Anthony DeBoer
Re: [sqlite] SQLite to MySQL
Ho, thank you Herbet ! But, hmmm, is there a chance you know an opensource or free solution instead of this expensive one ? 2006/2/6, Laurent Goussard <[EMAIL PROTECTED]>: > I don't know, I suppose my queries are not as optimized as I thought > (even if this optimization was my leitmotiv for all the development > part), or perhaps it's an apache2+php5 issue on my windows computer... > > But the fact is since the database has grown (like my traffic : 6000 > visitors/day and 22Mb db file), I've got more and more "maximum > execution time" errors at the peak hours. I've monitored them, and it > seems a lot of simultaneous queries are freezing the server and > finally generates this error. > The interresting point is the same queries sent a testing mysql db > while the sqlite part is not responding anymore are working very well, > So that's the reason why I consider to switch on a mysql solution for > this website. > > Do you got clues concerning conversion ? > > > 2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>: > > > Hi there, > > > > > > I use SQLite on my website for 2 years now. I do like SQLite a lot and > > > will use it for a lot of new web projects but, because I got more and > > > more traffic, I consider to move this one to MySQL in order to reduce > > > the over load of my computer (I host it @ home). > > > > How is this going to reduce load? > > > > sqlite = mysql - server code > > > > You're adding server code. More code = More load. > > >
Re: [sqlite] SQLite to MySQL
I don't know, I suppose my queries are not as optimized as I thought (even if this optimization was my leitmotiv for all the development part), or perhaps it's an apache2+php5 issue on my windows computer... But the fact is since the database has grown (like my traffic : 6000 visitors/day and 22Mb db file), I've got more and more "maximum execution time" errors at the peak hours. I've monitored them, and it seems a lot of simultaneous queries are freezing the server and finally generates this error. The interresting point is the same queries sent a testing mysql db while the sqlite part is not responding anymore are working very well, So that's the reason why I consider to switch on a mysql solution for this website. Do you got clues concerning conversion ? 2006/2/6, Jay Sprenkle <[EMAIL PROTECTED]>: > > Hi there, > > > > I use SQLite on my website for 2 years now. I do like SQLite a lot and > > will use it for a lot of new web projects but, because I got more and > > more traffic, I consider to move this one to MySQL in order to reduce > > the over load of my computer (I host it @ home). > > How is this going to reduce load? > > sqlite = mysql - server code > > You're adding server code. More code = More load. >
Re: [sqlite] SQLite to MySQL
Hi, with dbCopy you can copy the SQLite-tables directly to MySQL. "w_w_w.dbcopy.de" Laurent Goussard schrieb: Hi there, I use SQLite on my website for 2 years now. I do like SQLite a lot and will use it for a lot of new web projects but, because I got more and more traffic, I consider to move this one to MySQL in order to reduce the over load of my computer (I host it @ home). Do you know if there is a way to convert easily an SQLite database into a MySQL one ? (I'm especially thinking about the escapestring and other "create table [myTable] {" issues...) Thanks, Laurent
Re: [sqlite] SQLite to MySQL
> Hi there, > > I use SQLite on my website for 2 years now. I do like SQLite a lot and > will use it for a lot of new web projects but, because I got more and > more traffic, I consider to move this one to MySQL in order to reduce > the over load of my computer (I host it @ home). How is this going to reduce load? sqlite = mysql - server code You're adding server code. More code = More load.
[sqlite] SQLite to MySQL
Hi there, I use SQLite on my website for 2 years now. I do like SQLite a lot and will use it for a lot of new web projects but, because I got more and more traffic, I consider to move this one to MySQL in order to reduce the over load of my computer (I host it @ home). Do you know if there is a way to convert easily an SQLite database into a MySQL one ? (I'm especially thinking about the escapestring and other "create table [myTable] {" issues...) Thanks, Laurent