Re: [Engine-devel] Move SQL out of stored procedures
- Original Message - > From: "Eli Mesika" > To: engine-devel@ovirt.org > Sent: Monday, April 1, 2013 11:35:03 PM > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > - Original Message - > > From: "Laszlo Hornyak" > > To: "Libor Spevak" > > Cc: "Juan Hernandez" , engine-devel@ovirt.org > > Sent: Thursday, March 28, 2013 5:31:34 PM > > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > > > > > - Original Message - > > > From: "Libor Spevak" > > > To: "Itamar Heim" > > > Cc: "Juan Hernandez" , engine-devel@ovirt.org > > > Sent: Thursday, March 28, 2013 4:04:20 PM > > > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > > > Hi, > > > > > > apart from SQL vs. stored procedures discussion, I am trying to > > > understand what we can get if we support more databases... > > Sorry for joining this discussion so late (I was in a vacation) > anyway > two points missing from SQL VS. SP are > 1) security - With plain SQL we will have to handle SQL Injection I do not understand this. What's wrong with PreparedStatement? > 2) It is more economic to pass a call to SP than the full SQL on the wire... Ah that is not actually happening with postgresql :) I don't know about all the specific DB's but I am quite sure most other DB does not do that either. If you have a DataSource, like commons-dbcp, it is caching the PreparedStatements in the background. A PreparedStatement executes a 'PREPARE' command in postgresql http://www.postgresql.org/docs/9.2/static/sql-prepare.html After that it will only send over the name of the query plan and the parameters. I believe it usually does not save a lot on bandwidth, for example engine's SQL statements fit in a single tcp/ip frame, but the query parser and planner needs to run only once, when you create the query plan and that is a big win. I wrote a testfor this once, quite long ago but I remember something around 10% win if the query execution was simple enough. But of course it does not matter much if you have a pile of seqscan in your query plan. Anyway, this is kind of cool in PostgreSQL :) > > > > > > > > Some points: > > > 1. Is there a real need by end-users/customers to run it on e.g. > > > Oracle > > > only? (performance, stability, easier administration). > > > > Usually companies have one database and they are trying to stick to that > > one. > > Having two doubles the resource needs, you need one more DBA team, care for > > mirrors, backups. So it almost doubles the costs. > > Generally, I agree with Alon B L , if you have to support X DBs you are not > doubling the effort by X > Actually, we had already experience with that when we supported both MS SQL & > Postgres > I believe that as we have some customers with large installations, > performance counts and the best way (and sometimes teh only way) id the DB > layer Ok, then let's tell MySQL/MariaDB users to use PostgerSQL and see what happens. > > > This is why I frequently hear people asking if we plan to support XyDB in > > the > > future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they > > just do not want one more. > > > > > What is the future of PostgreSQL? > > > > > > 2. Is it decided by architectural board, what kind of databases we > > > would > > > like to support? (cannot support any db) > > > > With a JPA we could support most mainstream relational databases, but in my > > opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So > > maybe we do not have to think in big number of database engines. > > This is theoretical since JPA is still on wishlist :( > > > > > > > > 3. Are we talking about the Engine only, or there will be a need to > > > rewrite ETL mappings and upgrade DWH database, or maybe modify > > > JasperReports templates (simply, some DB types behave differently)? > > > Maybe we can look at JasperSoft solution, they support more > > > databases. > > IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported > by Jasper > > > > > > > 4. Current full/incremental upgrade process of PostgreSQL is IMHO > > > very > > > good tuned (it is similar to dbmaintain.org tool - Java > > > implementation - > > > I used successfully on one project - after some changes of course). I > > > do > > > not believe we can use or easily develop general upgrade/migration > > > tool, > > > and XML based (I am sorry Alissa, not sure about Liquibase, I haven't > > > studied it deeply, but there is a need to incrementally change db > > > objects, but sometimes also to migrate data to new structures, the > > > most > > > flexible and quickest is to do it using native SQL, but yes, it > > > depends > > > on the project needs...). > > I had evaluated Liquibase and I think that managing your DB upgrades via XML > is very unfriendly and very limited as you reach complex upgrades as we had > in the past. > Just think of the tables in which we change the k
Re: [Engine-devel] [RFC] new power management protocol "libvirtp"
On Tue, Apr 02, 2013 at 10:15:44AM +0800, Shu Ming wrote: > Hi, > > In oVirt environment, power manager should be enabled for the host to > support VM high availability in the cluster. Various kinds of > physical power management protocol are supported, ie., ALOM, ILO&etc. > However, when the oVirt is running on a nested KVM > environment, there is no feasible way to do the power management of the > VDSM host(also a KVM virtual machine). A new protocol > will be based on libvirt to achieve the power management of a virtual > host. The new protocol can be named as "libvirtp". > > In oVirt engine, a new type will be added to > > power management---> type libvirtp > power management--->address it will be the IP of the physical host where > the virtual VDSM host is on when "libvirtp" is selected > power management--->user name it will be the user name to the libvirtd > service > power management--->password it will be the password to the libvirtd service > power management--->port it will be the port to the libvirtd service Have you looked into fence_virsh or fence_virt ? Don't they provide what you want? ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
Hi Laszlo, I'm currently in the process of adding a caching layer on top of JdbcTemplate, which would greatly reduce the number of database activities we have, so that would solve the last item you raised. I didn't mean the ORM performance is caused by the mapping. I think the problem lies in the fact that we will modify our code to have batch updates for most insert activities - a thing that is impossible in JPA/Hibernate. So, if we'll have some code in SQL and some in ORM - I prefer we stick all code to SQL… On Apr 2, 2013, at 9:34 AM, Laszlo Hornyak wrote: > Hi Liran, > > I agree that ORM tools in general have to add some mapping overhead, but that > overhead is very small compared to the time needed by the database > interaction. > ORM tools sometimes generate SQL statements that we could imagine being > better, I do not think they are as hard for the DB as for example the ones > generated by searchbackend. Also, we can do rdbms specific optimizations when > needed. > Plus we could finally have some caching in ovirt engine and the code would > not have to read e.g. the DC record again and again. There are some more like > that. > > Therefore having a JPA could improve the performance in engine. > > Laszlo > > - Original Message - >> From: "Liran Zelkha" >> To: "Eli Mesika" >> Cc: engine-devel@ovirt.org >> Sent: Tuesday, April 2, 2013 7:24:08 AM >> Subject: Re: [Engine-devel] Move SQL out of stored procedures >> >> I also apologize for jumping in late... >> I think concerning SQL injection we'll be covered by using >> PreparedStatements. Since we're using SpringJDBC, most of our code uses >> PreparedStatements anyway. >> Concerning ORM - I feel it won't really be beneficial to us. I know of very >> few projects who can actually be cross-database, and just maintaining schema >> creation scripts for different databases can be too difficult to maintain. >> Also, from a performance perspective, ORM performs worse than regular SQL >> (or stored procedures), so it wouldn't be the direction I choose. >> I think we should keep using SpringJDBC with either SQL or stored procedures >> (doesn't really matter, whatever is easier to maintain and performs faster) >> and maybe add a better, more generic, RowMapper class. >> >> - Original Message - >> From: "Eli Mesika" >> To: engine-devel@ovirt.org >> Sent: Tuesday, April 2, 2013 12:35:03 AM >> Subject: Re: [Engine-devel] Move SQL out of stored procedures >> >> >> >> - Original Message - >>> From: "Laszlo Hornyak" >>> To: "Libor Spevak" >>> Cc: "Juan Hernandez" , engine-devel@ovirt.org >>> Sent: Thursday, March 28, 2013 5:31:34 PM >>> Subject: Re: [Engine-devel] Move SQL out of stored procedures >>> >>> >>> >>> - Original Message - From: "Libor Spevak" To: "Itamar Heim" Cc: "Juan Hernandez" , engine-devel@ovirt.org Sent: Thursday, March 28, 2013 4:04:20 PM Subject: Re: [Engine-devel] Move SQL out of stored procedures Hi, apart from SQL vs. stored procedures discussion, I am trying to understand what we can get if we support more databases... >> >> Sorry for joining this discussion so late (I was in a vacation) >> anyway >> two points missing from SQL VS. SP are >> 1) security - With plain SQL we will have to handle SQL Injection >> 2) It is more economic to pass a call to SP than the full SQL on the wire... >> >> Some points: 1. Is there a real need by end-users/customers to run it on e.g. Oracle only? (performance, stability, easier administration). >>> >>> Usually companies have one database and they are trying to stick to that >>> one. >>> Having two doubles the resource needs, you need one more DBA team, care for >>> mirrors, backups. So it almost doubles the costs. >> >> Generally, I agree with Alon B L , if you have to support X DBs you are not >> doubling the effort by X >> Actually, we had already experience with that when we supported both MS SQL & >> Postgres >> I believe that as we have some customers with large installations, >> performance counts and the best way (and sometimes teh only way) id the DB >> layer >> >>> This is why I frequently hear people asking if we plan to support XyDB in >>> the >>> future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they >>> just do not want one more. >>> What is the future of PostgreSQL? 2. Is it decided by architectural board, what kind of databases we would like to support? (cannot support any db) >>> >>> With a JPA we could support most mainstream relational databases, but in my >>> opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So >>> maybe we do not have to think in big number of database engines. >>> This is theoretical since JPA is still on wishlist :( >>> 3. Are we talking about the Engine only, or there will be a need to rewrite ETL mappings and upgrade DWH database, or maybe mo
Re: [Engine-devel] Move SQL out of stored procedures
Hi Liran, I agree that ORM tools in general have to add some mapping overhead, but that overhead is very small compared to the time needed by the database interaction. ORM tools sometimes generate SQL statements that we could imagine being better, I do not think they are as hard for the DB as for example the ones generated by searchbackend. Also, we can do rdbms specific optimizations when needed. Plus we could finally have some caching in ovirt engine and the code would not have to read e.g. the DC record again and again. There are some more like that. Therefore having a JPA could improve the performance in engine. Laszlo - Original Message - > From: "Liran Zelkha" > To: "Eli Mesika" > Cc: engine-devel@ovirt.org > Sent: Tuesday, April 2, 2013 7:24:08 AM > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > I also apologize for jumping in late... > I think concerning SQL injection we'll be covered by using > PreparedStatements. Since we're using SpringJDBC, most of our code uses > PreparedStatements anyway. > Concerning ORM - I feel it won't really be beneficial to us. I know of very > few projects who can actually be cross-database, and just maintaining schema > creation scripts for different databases can be too difficult to maintain. > Also, from a performance perspective, ORM performs worse than regular SQL > (or stored procedures), so it wouldn't be the direction I choose. > I think we should keep using SpringJDBC with either SQL or stored procedures > (doesn't really matter, whatever is easier to maintain and performs faster) > and maybe add a better, more generic, RowMapper class. > > - Original Message - > From: "Eli Mesika" > To: engine-devel@ovirt.org > Sent: Tuesday, April 2, 2013 12:35:03 AM > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > - Original Message - > > From: "Laszlo Hornyak" > > To: "Libor Spevak" > > Cc: "Juan Hernandez" , engine-devel@ovirt.org > > Sent: Thursday, March 28, 2013 5:31:34 PM > > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > > > > > - Original Message - > > > From: "Libor Spevak" > > > To: "Itamar Heim" > > > Cc: "Juan Hernandez" , engine-devel@ovirt.org > > > Sent: Thursday, March 28, 2013 4:04:20 PM > > > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > > > Hi, > > > > > > apart from SQL vs. stored procedures discussion, I am trying to > > > understand what we can get if we support more databases... > > Sorry for joining this discussion so late (I was in a vacation) > anyway > two points missing from SQL VS. SP are > 1) security - With plain SQL we will have to handle SQL Injection > 2) It is more economic to pass a call to SP than the full SQL on the wire... > > > > > > > > Some points: > > > 1. Is there a real need by end-users/customers to run it on e.g. > > > Oracle > > > only? (performance, stability, easier administration). > > > > Usually companies have one database and they are trying to stick to that > > one. > > Having two doubles the resource needs, you need one more DBA team, care for > > mirrors, backups. So it almost doubles the costs. > > Generally, I agree with Alon B L , if you have to support X DBs you are not > doubling the effort by X > Actually, we had already experience with that when we supported both MS SQL & > Postgres > I believe that as we have some customers with large installations, > performance counts and the best way (and sometimes teh only way) id the DB > layer > > > This is why I frequently hear people asking if we plan to support XyDB in > > the > > future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they > > just do not want one more. > > > > > What is the future of PostgreSQL? > > > > > > 2. Is it decided by architectural board, what kind of databases we > > > would > > > like to support? (cannot support any db) > > > > With a JPA we could support most mainstream relational databases, but in my > > opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So > > maybe we do not have to think in big number of database engines. > > This is theoretical since JPA is still on wishlist :( > > > > > > > > 3. Are we talking about the Engine only, or there will be a need to > > > rewrite ETL mappings and upgrade DWH database, or maybe modify > > > JasperReports templates (simply, some DB types behave differently)? > > > Maybe we can look at JasperSoft solution, they support more > > > databases. > > IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported > by Jasper > > > > > > > 4. Current full/incremental upgrade process of PostgreSQL is IMHO > > > very > > > good tuned (it is similar to dbmaintain.org tool - Java > > > implementation - > > > I used successfully on one project - after some changes of course). I > > > do > > > not believe we can use or easily develop general upgrade/migration > > > tool, > > > and XML based (I am sorr
Re: [Engine-devel] Move SQL out of stored procedures
I also apologize for jumping in late... I think concerning SQL injection we'll be covered by using PreparedStatements. Since we're using SpringJDBC, most of our code uses PreparedStatements anyway. Concerning ORM - I feel it won't really be beneficial to us. I know of very few projects who can actually be cross-database, and just maintaining schema creation scripts for different databases can be too difficult to maintain. Also, from a performance perspective, ORM performs worse than regular SQL (or stored procedures), so it wouldn't be the direction I choose. I think we should keep using SpringJDBC with either SQL or stored procedures (doesn't really matter, whatever is easier to maintain and performs faster) and maybe add a better, more generic, RowMapper class. - Original Message - From: "Eli Mesika" To: engine-devel@ovirt.org Sent: Tuesday, April 2, 2013 12:35:03 AM Subject: Re: [Engine-devel] Move SQL out of stored procedures - Original Message - > From: "Laszlo Hornyak" > To: "Libor Spevak" > Cc: "Juan Hernandez" , engine-devel@ovirt.org > Sent: Thursday, March 28, 2013 5:31:34 PM > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > - Original Message - > > From: "Libor Spevak" > > To: "Itamar Heim" > > Cc: "Juan Hernandez" , engine-devel@ovirt.org > > Sent: Thursday, March 28, 2013 4:04:20 PM > > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > Hi, > > > > apart from SQL vs. stored procedures discussion, I am trying to > > understand what we can get if we support more databases... Sorry for joining this discussion so late (I was in a vacation) anyway two points missing from SQL VS. SP are 1) security - With plain SQL we will have to handle SQL Injection 2) It is more economic to pass a call to SP than the full SQL on the wire... > > > > Some points: > > 1. Is there a real need by end-users/customers to run it on e.g. > > Oracle > > only? (performance, stability, easier administration). > > Usually companies have one database and they are trying to stick to that one. > Having two doubles the resource needs, you need one more DBA team, care for > mirrors, backups. So it almost doubles the costs. Generally, I agree with Alon B L , if you have to support X DBs you are not doubling the effort by X Actually, we had already experience with that when we supported both MS SQL & Postgres I believe that as we have some customers with large installations, performance counts and the best way (and sometimes teh only way) id the DB layer > This is why I frequently hear people asking if we plan to support XyDB in the > future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they > just do not want one more. > > > What is the future of PostgreSQL? > > > > 2. Is it decided by architectural board, what kind of databases we > > would > > like to support? (cannot support any db) > > With a JPA we could support most mainstream relational databases, but in my > opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So > maybe we do not have to think in big number of database engines. > This is theoretical since JPA is still on wishlist :( > > > > > 3. Are we talking about the Engine only, or there will be a need to > > rewrite ETL mappings and upgrade DWH database, or maybe modify > > JasperReports templates (simply, some DB types behave differently)? > > Maybe we can look at JasperSoft solution, they support more > > databases. IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported by Jasper > > > > 4. Current full/incremental upgrade process of PostgreSQL is IMHO > > very > > good tuned (it is similar to dbmaintain.org tool - Java > > implementation - > > I used successfully on one project - after some changes of course). I > > do > > not believe we can use or easily develop general upgrade/migration > > tool, > > and XML based (I am sorry Alissa, not sure about Liquibase, I haven't > > studied it deeply, but there is a need to incrementally change db > > objects, but sometimes also to migrate data to new structures, the > > most > > flexible and quickest is to do it using native SQL, but yes, it > > depends > > on the project needs...). I had evaluated Liquibase and I think that managing your DB upgrades via XML is very unfriendly and very limited as you reach complex upgrades as we had in the past. Just think of the tables in which we change the key from long to UUID , there is no way to do that in such tools > > > > 5. As a developer, with every new column I need to write upgrade > > scripts, prepare test environments and test all scenarios several > > times > > on different databases, so time-consuming. Did it also , again , since our SQL is 90% simple , the effort of writing a SP for more than one DB is not so high (and you have free converters you can use for that) Finally, embedded SQL in the Java code is not a good idea, it will be hard to maintai
[Engine-devel] [RFC] new power management protocol "libvirtp"
Hi, In oVirt environment, power manager should be enabled for the host to support VM high availability in the cluster. Various kinds of physical power management protocol are supported, ie., ALOM, ILO&etc. However, when the oVirt is running on a nested KVM environment, there is no feasible way to do the power management of the VDSM host(also a KVM virtual machine). A new protocol will be based on libvirt to achieve the power management of a virtual host. The new protocol can be named as "libvirtp". In oVirt engine, a new type will be added to power management---> type libvirtp power management--->address it will be the IP of the physical host where the virtual VDSM host is on when "libvirtp" is selected power management--->user name it will be the user name to the libvirtd service power management--->password it will be the password to the libvirtd service power management--->port it will be the port to the libvirtd service -- --- 舒明 Shu Ming Open Virtualization Engineerning; CSTL, IBM Corp. Tel: 86-10-82451626 Tieline: 9051626 E-mail: shum...@cn.ibm.com or shum...@linux.vnet.ibm.com Address: 3/F Ring Building, ZhongGuanCun Software Park, Haidian District, Beijing 100193, PRC ___ Engine-devel mailing list Engine-devel@ovirt.org http://lists.ovirt.org/mailman/listinfo/engine-devel
Re: [Engine-devel] Move SQL out of stored procedures
- Original Message - > From: "Laszlo Hornyak" > To: "Libor Spevak" > Cc: "Juan Hernandez" , engine-devel@ovirt.org > Sent: Thursday, March 28, 2013 5:31:34 PM > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > - Original Message - > > From: "Libor Spevak" > > To: "Itamar Heim" > > Cc: "Juan Hernandez" , engine-devel@ovirt.org > > Sent: Thursday, March 28, 2013 4:04:20 PM > > Subject: Re: [Engine-devel] Move SQL out of stored procedures > > > > Hi, > > > > apart from SQL vs. stored procedures discussion, I am trying to > > understand what we can get if we support more databases... Sorry for joining this discussion so late (I was in a vacation) anyway two points missing from SQL VS. SP are 1) security - With plain SQL we will have to handle SQL Injection 2) It is more economic to pass a call to SP than the full SQL on the wire... > > > > Some points: > > 1. Is there a real need by end-users/customers to run it on e.g. > > Oracle > > only? (performance, stability, easier administration). > > Usually companies have one database and they are trying to stick to that one. > Having two doubles the resource needs, you need one more DBA team, care for > mirrors, backups. So it almost doubles the costs. Generally, I agree with Alon B L , if you have to support X DBs you are not doubling the effort by X Actually, we had already experience with that when we supported both MS SQL & Postgres I believe that as we have some customers with large installations, performance counts and the best way (and sometimes teh only way) id the DB layer > This is why I frequently hear people asking if we plan to support XyDB in the > future. PostgreSQL is cool, but those who already use MySQL/MariaDB, they > just do not want one more. > > > What is the future of PostgreSQL? > > > > 2. Is it decided by architectural board, what kind of databases we > > would > > like to support? (cannot support any db) > > With a JPA we could support most mainstream relational databases, but in my > opinion 99 percent of people run oracle, mysql/mariadb or postgresql. So > maybe we do not have to think in big number of database engines. > This is theoretical since JPA is still on wishlist :( > > > > > 3. Are we talking about the Engine only, or there will be a need to > > rewrite ETL mappings and upgrade DWH database, or maybe modify > > JasperReports templates (simply, some DB types behave differently)? > > Maybe we can look at JasperSoft solution, they support more > > databases. IMHO , ETL & DWH are perfect candidates for NO SQL which is already supported by Jasper > > > > 4. Current full/incremental upgrade process of PostgreSQL is IMHO > > very > > good tuned (it is similar to dbmaintain.org tool - Java > > implementation - > > I used successfully on one project - after some changes of course). I > > do > > not believe we can use or easily develop general upgrade/migration > > tool, > > and XML based (I am sorry Alissa, not sure about Liquibase, I haven't > > studied it deeply, but there is a need to incrementally change db > > objects, but sometimes also to migrate data to new structures, the > > most > > flexible and quickest is to do it using native SQL, but yes, it > > depends > > on the project needs...). I had evaluated Liquibase and I think that managing your DB upgrades via XML is very unfriendly and very limited as you reach complex upgrades as we had in the past. Just think of the tables in which we change the key from long to UUID , there is no way to do that in such tools > > > > 5. As a developer, with every new column I need to write upgrade > > scripts, prepare test environments and test all scenarios several > > times > > on different databases, so time-consuming. Did it also , again , since our SQL is 90% simple , the effort of writing a SP for more than one DB is not so high (and you have free converters you can use for that) Finally, embedded SQL in the Java code is not a good idea, it will be hard to maintain it and it is not advancing us in supporting more than one database. We have already SQL generated in the Java code on the search engine and IMHO this is one of the parts in the applications that needs a rewrite ... > > > > > > On 27.3.2013 13:53, Itamar Heim wrote: > > > On 03/26/2013 08:39 PM, Alon Bar-Lev wrote: > > >> > > >> > > >> - Original Message - > > >>> From: "Juan Hernandez" > > >>> To: engine-devel@ovirt.org > > >>> Sent: Tuesday, March 26, 2013 7:34:04 PM > > >>> Subject: [Engine-devel] Move SQL out of stored procedures > > >>> > > >>> Hello, > > >>> > > >>> I would like to start a discussion about the subject. I think > > >>> this is > > >>> something we need to do if one day we want to be able to use any > > >>> database other than PostgreSQL. > > >> > > >> Hello, > > >> > > >> I think that database layer is a software interface like any other > > >> software interface, if done properly, a dba can convert the stored >
