You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@trafodion.apache.org by Radu Marias <ra...@gmail.com> on 2015/07/29 19:32:10 UTC
Does trafodion caches the results for jdbc queries?
I'm using daily build *trafodion-20150726_0830.tar.gz* in *sandbox 1.1*
with several sql clients like SQuirreL, DbVisualizer and 0xDBE from
Intellij.
Noticed that in some cases when I execute a query for the first time it
takes longer than the next subsequent executions. It's not the first query
when also the connection is made, but a later one.
For example I have this query executed on 3 tables with about 1.2 million
rows each:
*select p.id <http://p.id>, p.LAST_NAME, p.FIRST_NAME, a.ADDRESS_1, a.CITY,
a.STATE_PROVINCE*
*from sna_person_addresses pa*
* join sna_persons p on (p.id <http://p.id> = pa.person_id)*
* join sna_addresses a on (a.id <http://a.id> = pa.address_id)*
*where pa.person_id=?;*
first time it takes about 200ms and after that it's bellow 50ms, never goes
higher than that.
It doesn't happen all the time, that's why I just wanted to exclude the
cache possibility in trafodion or jdbc client.
Re: Does trafodion caches the results for jdbc queries?
Posted by Radu Marias <ra...@gmail.com>.
I think we would try to install HDP (even if we would do it on a separate
environment just for testing trafodion on cluster) and will use your new
installer when it will be compatible with ubuntu.
On Thu, Jul 30, 2015 at 7:11 PM, Amanda Moran <am...@esgyn.com>
wrote:
> Hi there Radu-
>
> I have never tried that before (so I don't know for sure)... but I think
> you are making the correct assumption...that it would get wiped out... if
> we did a blind install. We should look into installing HDP or Cloudera with
> an existing HBase (special case) ... I would think that is a use case that
> is out there.
>
> Radu if you want to try installing "manually" Trafodion, we could help as
> you go. It will be a little ugly as you would need to open each script in
> the installer and see what it does and if you need to do.
>
> Thanks.
>
>
>
> On Thu, Jul 30, 2015 at 9:04 AM, Radu Marias <ra...@gmail.com> wrote:
>
>> We already have a small cluster in place with manually installed hadoop
>> and hbase and I don't know exactly what installing HDP or Cloudera would
>> mean on that cluster. I assume it would need to do a clean install and
>> erase what we have. Or this is not the case?
>>
>> On Thu, Jul 30, 2015 at 7:00 PM, Amanda Moran <am...@esgyn.com>
>> wrote:
>>
>>> Hi there Radu-
>>>
>>> Yes, like you said it is not trivial. It is something we could play
>>> around with and try... but it might not work. Is there a reason you could
>>> not install HDP or Cloudera?
>>>
>>> Thanks!
>>>
>>> On Thu, Jul 30, 2015 at 8:58 AM, Radu Marias <ra...@gmail.com>
>>> wrote:
>>>
>>>> Seen this at some point
>>>> https://wiki.trafodion.org/wiki/index.php/Troubleshooting_the_Installation
>>>> and assumed that we could manually do some steps like copying hbase-trx,
>>>> changing the hbase-site.xml and other installation steps. But I think this
>>>> is not trivial.
>>>>
>>>> On Thu, Jul 30, 2015 at 6:22 PM Amanda Moran <am...@esgyn.com>
>>>> wrote:
>>>>
>>>>> HI there Radu-
>>>>>
>>>>> I think there is a little confusion, there is no Trafodion manual
>>>>> installation. We would need to make some tweaks to the scripts to
>>>>> install
>>>>> on Ubuntu (don't worry this is a high priority!). We do need
>>>>> Hortonworks or
>>>>> Cloudera installed (not simple to remove that dependency).
>>>>>
>>>>> Thanks.
>>>>>
>>>>> Amanda
>>>>>
>>>>> On Wed, Jul 29, 2015 at 5:03 PM, Radu Marias <ra...@gmail.com>
>>>>> wrote:
>>>>>
>>>>> > But in theory it could work on Ubuntu with a manual install?
>>>>> >
>>>>> > On Thu, Jul 30, 2015, 02:49 Amanda Moran <am...@esgyn.com>
>>>>> wrote:
>>>>> >
>>>>> > > Hi thre Radu-
>>>>> > >
>>>>> > > We are currently <as of today> only able to use the installer to
>>>>> install
>>>>> > > Trafodion on RedHat 6 and on Cloudera or Hortonworks.
>>>>> > >
>>>>> > > Thanks.
>>>>> > >
>>>>> > > Amanda
>>>>> > >
>>>>> > > On Wed, Jul 29, 2015 at 4:47 PM, Radu Marias <radumarias@gmail.com
>>>>> >
>>>>> > wrote:
>>>>> > >
>>>>> > > > Yes, I agree it's not a good benchmark environment. Initially I
>>>>> got
>>>>> > > > problems installing, starting it and running any query, now I
>>>>> managed
>>>>> > to
>>>>> > > > run some test queries in sandbox with no crash and decent
>>>>> performance
>>>>> > at
>>>>> > > > least.
>>>>> > > > The next step is trying to install it on a cluster environment
>>>>> with
>>>>> > > several
>>>>> > > > nodes. But we have a small ubuntu cluster on which I manually
>>>>> installed
>>>>> > > > hdfs and hbase.
>>>>> > > > Does the installer work outside of ambari hortonworks
>>>>> environment? Or I
>>>>> > > > assume I can manually install trafodion to my cluster adding
>>>>> > dependencies
>>>>> > > > and configs as the installer does.
>>>>> > > >
>>>>> > > > On Thu, Jul 30, 2015, 02:33 Rohit Jain <ro...@esgyn.com>
>>>>> wrote:
>>>>> > > >
>>>>> > > > > If you are using a sandbox to do this testing, then that is
>>>>> not an
>>>>> > > > > environment conducive to performance testing. It will
>>>>> probably not
>>>>> > > give
>>>>> > > > > you reliable and consistent results. A sandbox is limited in
>>>>> memory
>>>>> > > and
>>>>> > > > is
>>>>> > > > > subject to limitations of the environment used to build it.
>>>>> The most
>>>>> > > > > important point is that it is a single node VM and Trafodion
>>>>> is a
>>>>> > > > scale-out
>>>>> > > > > architecture. It is best to test its performance on a
>>>>> multi-node
>>>>> > > cluster.
>>>>> > > > > I realize that may be hard to do and does involve a full
>>>>> install.
>>>>> > The
>>>>> > > > > intent of the sandbox is for you to be able to kick tires and
>>>>> get a
>>>>> > > feel
>>>>> > > > > for Trafodion. It is really not meant for extensive
>>>>> performance
>>>>> > > testing.
>>>>> > > > >
>>>>> > > > > There are smarter folks on this mailing list and I will let
>>>>> them
>>>>> > > comment
>>>>> > > > > on that.
>>>>> > > > >
>>>>> > > > > Rohit
>>>>> > > > >
>>>>> > > > >
>>>>> > > > >
>>>>> > > > >
>>>>> > > > >
>>>>> > > > > On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com>
>>>>> wrote:
>>>>> > > > >
>>>>> > > > > >Hi Radu,
>>>>> > > > > >
>>>>> > > > > >In addition, we can help verify the query plan for you to
>>>>> assure the
>>>>> > > > > queries can perform reasonably well in your environment, if
>>>>> it works
>>>>> > > for
>>>>> > > > > you.
>>>>> > > > > >
>>>>> > > > > >To produce a query plan for query <q>, do the following.
>>>>> > > > > >
>>>>> > > > > >log plan.log clear;
>>>>> > > > > >
>>>>> > > > > >prepare zzz from <q>:
>>>>> > > > > >explain zzz;
>>>>> > > > > >explain options 'f' zzz;
>>>>> > > > > >
>>>>> > > > > >--repeat the above three steps for each query
>>>>> > > > > >
>>>>> > > > > >exit;
>>>>> > > > > >
>>>>> > > > > > You can prepare multiple queries in the same sql session and
>>>>> > generate
>>>>> > > > > one plan report in the log file plan.log.
>>>>> > > > > >
>>>>> > > > > >Thanks
>>>>> > > > > >
>>>>> > > > > >-Qifan
>>>>> > > > > >
>>>>> > > > > >Sent from my iPhone
>>>>> > > > > >
>>>>> > > > > >> On Jul 29, 2015, at 4:20 PM, Hans Zeller <
>>>>> hans.zeller@esgyn.com>
>>>>> > > > wrote:
>>>>> > > > > >>
>>>>> > > > > >> Hi Radu,
>>>>> > > > > >>
>>>>> > > > > >> My suggestion would be to benchmark the system with
>>>>> warmed-up
>>>>> > > caches,
>>>>> > > > > since this will give more meaningful and repeatable numbers. It
>>>>> > > probably
>>>>> > > > > depends on the type of benchmark, but in most cases the
>>>>> benchmark
>>>>> > > itself
>>>>> > > > > will contain multiple statements, with caching going on
>>>>> between them,
>>>>> > > > > making it difficult to clean the cache every time.
>>>>> > > > > >>
>>>>> > > > > >> So, I would suggest the following, am not sure how well
>>>>> that would
>>>>> > > > work
>>>>> > > > > for you:
>>>>> > > > > >> Start from a well-defined point, with empty cache, e.g.
>>>>> after
>>>>> > > > > re-starting HBase and Trafodion
>>>>> > > > > >> Run the benchmark. This will be a bit slower initially, as
>>>>> the
>>>>> > > caches
>>>>> > > > > get filled.
>>>>> > > > > >> If the benchmark is very short, run the benchmark again,
>>>>> this time
>>>>> > > > with
>>>>> > > > > warmed-up cache. Alternatively, if the benchmark itself repeats
>>>>> > queries
>>>>> > > > > many times, show a graph of performance over time to show the
>>>>> warm-up
>>>>> > > > time.
>>>>> > > > > >> Thanks,
>>>>> > > > > >>
>>>>> > > > > >> Hans
>>>>> > > > > >>
>>>>> > > > > >>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <
>>>>> > qifan.chen@esgyn.com
>>>>> > > >
>>>>> > > > > wrote:
>>>>> > > > > >>> yes, there exists a control switch to turn off query cache
>>>>> in
>>>>> > > > compiler.
>>>>> > > > > >>>
>>>>> > > > > >>> Such switch is called query_cache and can be used in a
>>>>> control
>>>>> > > query
>>>>> > > > > default statement (CQD in short) to control how much memory
>>>>> that the
>>>>> > > > query
>>>>> > > > > cache can use. When we set it to a value of 0, the cache is
>>>>> > disabled.
>>>>> > > > To
>>>>> > > > > set the CQD, try one of the following two approaches as
>>>>> follows.
>>>>> > > > > >>>
>>>>> > > > > >>> 1. from java,
>>>>> > > > > >>>
>>>>> > > > > >>> Statement stmt = conn_.createStatement();
>>>>> > > > > >>> stmt.executeQuery("control query default query_cache '0'");
>>>>> > > > > >>>
>>>>> > > > > >>> <your query>
>>>>> > > > > >>>
>>>>> > > > > >>> 2. from sqlci
>>>>> > > > > >>> cqd query_cache '0';
>>>>> > > > > >>> prepare xx from <your_query>;
>>>>> > > > > >>>
>>>>> > > > > >>>
>>>>> > > > > >>>
>>>>> > > > > >>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <
>>>>> > > radumarias@gmail.com>
>>>>> > > > > wrote:
>>>>> > > > > >>>> These 2 caching are indeed very good for performance in
>>>>> real
>>>>> > > > > environments.
>>>>> > > > > >>>> But I'm trying to run some internal benchmarks on
>>>>> trafodion and
>>>>> > > > > collect the
>>>>> > > > > >>>> queries duration, in this case the cache generates
>>>>> inconsistency
>>>>> > > for
>>>>> > > > > my
>>>>> > > > > >>>> tests. I can drop the connection and re-run the query on
>>>>> a new
>>>>> > > > > connection
>>>>> > > > > >>>> for this benchmarks but can trafodion compiler cache be
>>>>> > disabled?
>>>>> > > > And
>>>>> > > > > can
>>>>> > > > > >>>> HBase RegionServer cache be also disabled?
>>>>> > > > > >>>>
>>>>> > > > > >>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <
>>>>> > > hans.zeller@esgyn.com
>>>>> > > > >
>>>>> > > > > wrote:
>>>>> > > > > >>>>
>>>>> > > > > >>>> > You mentioned also that sometimes you get a slower
>>>>> response
>>>>> > time
>>>>> > > > > when
>>>>> > > > > >>>> > reconnecting, Radu. As far as I understand (only from
>>>>> what
>>>>> > > others
>>>>> > > > > told me),
>>>>> > > > > >>>> > there could be two reasons. One is that your new
>>>>> connection
>>>>> > > might
>>>>> > > > > be to a
>>>>> > > > > >>>> > different mxosrvr process, which does not have the cache
>>>>> > > > established
>>>>> > > > > >>>> > earlier. The other one is that there is a change in the
>>>>> user
>>>>> > id.
>>>>> > > > If
>>>>> > > > > an
>>>>> > > > > >>>> > mxosrvr changes user ids, it invalidates its cache,
>>>>> since some
>>>>> > > of
>>>>> > > > > the cache
>>>>> > > > > >>>> > content (e.g. privilege checks) depends on the user.
>>>>> > > Reconnecting
>>>>> > > > > to the
>>>>> > > > > >>>> > same mxosrvr, using the same user id, with no other
>>>>> users
>>>>> > > > > in-between,
>>>>> > > > > >>>> > should preserve the cache, as far as I know.
>>>>> > > > > >>>> >
>>>>> > > > > >>>> > You can use the DCS web gui to see which mxosrvrs you
>>>>> are
>>>>> > > > > connecting to. I
>>>>> > > > > >>>> > think the default port is 40010.
>>>>> > > > > >>>> >
>>>>> > > > > >>>> > Hans
>>>>> > > > > >>>> >
>>>>> > > > > >>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <
>>>>> > > > qifan.chen@esgyn.com>
>>>>> > > > > wrote:
>>>>> > > > > >>>> >
>>>>> > > > > >>>> >> Another point is that if you can take the advantage of
>>>>> query
>>>>> > > > cache
>>>>> > > > > by
>>>>> > > > > >>>> >> preparing the query once and executing it multiple
>>>>> times,
>>>>> > > further
>>>>> > > > > reducing
>>>>> > > > > >>>> >> the compilation time per SQL invocation.
>>>>> > > > > >>>> >>
>>>>> > > > > >>>> >>
>>>>> > > > > >>>> >>
>>>>> > > > > >>>> >
>>>>> > > > > >>>> >
>>>>> > > > > >>>>
>>>>> > > > > >>>>
>>>>> > > > > >>>> --
>>>>> > > > > >>>> And in the end, it's not the years in your life that
>>>>> count. It's
>>>>> > > the
>>>>> > > > > life
>>>>> > > > > >>>> in your years.
>>>>> > > > > >>>
>>>>> > > > > >>>
>>>>> > > > > >>>
>>>>> > > > > >>> --
>>>>> > > > > >>> Regards, --Qifan
>>>>> > > > > >>
>>>>> > > > >
>>>>> > > > >
>>>>> > > >
>>>>> > >
>>>>> > >
>>>>> > >
>>>>> > > --
>>>>> > > Thanks,
>>>>> > >
>>>>> > > Amanda Moran
>>>>> > >
>>>>> >
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Thanks,
>>>>>
>>>>> Amanda Moran
>>>>>
>>>>
>>>
>>>
>>> --
>>> Thanks,
>>>
>>> Amanda Moran
>>>
>>
>>
>>
>> --
>> And in the end, it's not the years in your life that count. It's the life
>> in your years.
>>
>
>
>
> --
> Thanks,
>
> Amanda Moran
>
--
And in the end, it's not the years in your life that count. It's the life
in your years.
Re: Does trafodion caches the results for jdbc queries?
Posted by Amanda Moran <am...@esgyn.com>.
Hi there Radu-
I have never tried that before (so I don't know for sure)... but I think
you are making the correct assumption...that it would get wiped out... if
we did a blind install. We should look into installing HDP or Cloudera with
an existing HBase (special case) ... I would think that is a use case that
is out there.
Radu if you want to try installing "manually" Trafodion, we could help as
you go. It will be a little ugly as you would need to open each script in
the installer and see what it does and if you need to do.
Thanks.
On Thu, Jul 30, 2015 at 9:04 AM, Radu Marias <ra...@gmail.com> wrote:
> We already have a small cluster in place with manually installed hadoop
> and hbase and I don't know exactly what installing HDP or Cloudera would
> mean on that cluster. I assume it would need to do a clean install and
> erase what we have. Or this is not the case?
>
> On Thu, Jul 30, 2015 at 7:00 PM, Amanda Moran <am...@esgyn.com>
> wrote:
>
>> Hi there Radu-
>>
>> Yes, like you said it is not trivial. It is something we could play
>> around with and try... but it might not work. Is there a reason you could
>> not install HDP or Cloudera?
>>
>> Thanks!
>>
>> On Thu, Jul 30, 2015 at 8:58 AM, Radu Marias <ra...@gmail.com>
>> wrote:
>>
>>> Seen this at some point
>>> https://wiki.trafodion.org/wiki/index.php/Troubleshooting_the_Installation
>>> and assumed that we could manually do some steps like copying hbase-trx,
>>> changing the hbase-site.xml and other installation steps. But I think this
>>> is not trivial.
>>>
>>> On Thu, Jul 30, 2015 at 6:22 PM Amanda Moran <am...@esgyn.com>
>>> wrote:
>>>
>>>> HI there Radu-
>>>>
>>>> I think there is a little confusion, there is no Trafodion manual
>>>> installation. We would need to make some tweaks to the scripts to
>>>> install
>>>> on Ubuntu (don't worry this is a high priority!). We do need
>>>> Hortonworks or
>>>> Cloudera installed (not simple to remove that dependency).
>>>>
>>>> Thanks.
>>>>
>>>> Amanda
>>>>
>>>> On Wed, Jul 29, 2015 at 5:03 PM, Radu Marias <ra...@gmail.com>
>>>> wrote:
>>>>
>>>> > But in theory it could work on Ubuntu with a manual install?
>>>> >
>>>> > On Thu, Jul 30, 2015, 02:49 Amanda Moran <am...@esgyn.com>
>>>> wrote:
>>>> >
>>>> > > Hi thre Radu-
>>>> > >
>>>> > > We are currently <as of today> only able to use the installer to
>>>> install
>>>> > > Trafodion on RedHat 6 and on Cloudera or Hortonworks.
>>>> > >
>>>> > > Thanks.
>>>> > >
>>>> > > Amanda
>>>> > >
>>>> > > On Wed, Jul 29, 2015 at 4:47 PM, Radu Marias <ra...@gmail.com>
>>>> > wrote:
>>>> > >
>>>> > > > Yes, I agree it's not a good benchmark environment. Initially I
>>>> got
>>>> > > > problems installing, starting it and running any query, now I
>>>> managed
>>>> > to
>>>> > > > run some test queries in sandbox with no crash and decent
>>>> performance
>>>> > at
>>>> > > > least.
>>>> > > > The next step is trying to install it on a cluster environment
>>>> with
>>>> > > several
>>>> > > > nodes. But we have a small ubuntu cluster on which I manually
>>>> installed
>>>> > > > hdfs and hbase.
>>>> > > > Does the installer work outside of ambari hortonworks
>>>> environment? Or I
>>>> > > > assume I can manually install trafodion to my cluster adding
>>>> > dependencies
>>>> > > > and configs as the installer does.
>>>> > > >
>>>> > > > On Thu, Jul 30, 2015, 02:33 Rohit Jain <ro...@esgyn.com>
>>>> wrote:
>>>> > > >
>>>> > > > > If you are using a sandbox to do this testing, then that is not
>>>> an
>>>> > > > > environment conducive to performance testing. It will probably
>>>> not
>>>> > > give
>>>> > > > > you reliable and consistent results. A sandbox is limited in
>>>> memory
>>>> > > and
>>>> > > > is
>>>> > > > > subject to limitations of the environment used to build it.
>>>> The most
>>>> > > > > important point is that it is a single node VM and Trafodion is
>>>> a
>>>> > > > scale-out
>>>> > > > > architecture. It is best to test its performance on a multi-node
>>>> > > cluster.
>>>> > > > > I realize that may be hard to do and does involve a full
>>>> install.
>>>> > The
>>>> > > > > intent of the sandbox is for you to be able to kick tires and
>>>> get a
>>>> > > feel
>>>> > > > > for Trafodion. It is really not meant for extensive performance
>>>> > > testing.
>>>> > > > >
>>>> > > > > There are smarter folks on this mailing list and I will let them
>>>> > > comment
>>>> > > > > on that.
>>>> > > > >
>>>> > > > > Rohit
>>>> > > > >
>>>> > > > >
>>>> > > > >
>>>> > > > >
>>>> > > > >
>>>> > > > > On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com> wrote:
>>>> > > > >
>>>> > > > > >Hi Radu,
>>>> > > > > >
>>>> > > > > >In addition, we can help verify the query plan for you to
>>>> assure the
>>>> > > > > queries can perform reasonably well in your environment, if it
>>>> works
>>>> > > for
>>>> > > > > you.
>>>> > > > > >
>>>> > > > > >To produce a query plan for query <q>, do the following.
>>>> > > > > >
>>>> > > > > >log plan.log clear;
>>>> > > > > >
>>>> > > > > >prepare zzz from <q>:
>>>> > > > > >explain zzz;
>>>> > > > > >explain options 'f' zzz;
>>>> > > > > >
>>>> > > > > >--repeat the above three steps for each query
>>>> > > > > >
>>>> > > > > >exit;
>>>> > > > > >
>>>> > > > > > You can prepare multiple queries in the same sql session and
>>>> > generate
>>>> > > > > one plan report in the log file plan.log.
>>>> > > > > >
>>>> > > > > >Thanks
>>>> > > > > >
>>>> > > > > >-Qifan
>>>> > > > > >
>>>> > > > > >Sent from my iPhone
>>>> > > > > >
>>>> > > > > >> On Jul 29, 2015, at 4:20 PM, Hans Zeller <
>>>> hans.zeller@esgyn.com>
>>>> > > > wrote:
>>>> > > > > >>
>>>> > > > > >> Hi Radu,
>>>> > > > > >>
>>>> > > > > >> My suggestion would be to benchmark the system with warmed-up
>>>> > > caches,
>>>> > > > > since this will give more meaningful and repeatable numbers. It
>>>> > > probably
>>>> > > > > depends on the type of benchmark, but in most cases the
>>>> benchmark
>>>> > > itself
>>>> > > > > will contain multiple statements, with caching going on between
>>>> them,
>>>> > > > > making it difficult to clean the cache every time.
>>>> > > > > >>
>>>> > > > > >> So, I would suggest the following, am not sure how well that
>>>> would
>>>> > > > work
>>>> > > > > for you:
>>>> > > > > >> Start from a well-defined point, with empty cache, e.g. after
>>>> > > > > re-starting HBase and Trafodion
>>>> > > > > >> Run the benchmark. This will be a bit slower initially, as
>>>> the
>>>> > > caches
>>>> > > > > get filled.
>>>> > > > > >> If the benchmark is very short, run the benchmark again,
>>>> this time
>>>> > > > with
>>>> > > > > warmed-up cache. Alternatively, if the benchmark itself repeats
>>>> > queries
>>>> > > > > many times, show a graph of performance over time to show the
>>>> warm-up
>>>> > > > time.
>>>> > > > > >> Thanks,
>>>> > > > > >>
>>>> > > > > >> Hans
>>>> > > > > >>
>>>> > > > > >>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <
>>>> > qifan.chen@esgyn.com
>>>> > > >
>>>> > > > > wrote:
>>>> > > > > >>> yes, there exists a control switch to turn off query cache
>>>> in
>>>> > > > compiler.
>>>> > > > > >>>
>>>> > > > > >>> Such switch is called query_cache and can be used in a
>>>> control
>>>> > > query
>>>> > > > > default statement (CQD in short) to control how much memory
>>>> that the
>>>> > > > query
>>>> > > > > cache can use. When we set it to a value of 0, the cache is
>>>> > disabled.
>>>> > > > To
>>>> > > > > set the CQD, try one of the following two approaches as follows.
>>>> > > > > >>>
>>>> > > > > >>> 1. from java,
>>>> > > > > >>>
>>>> > > > > >>> Statement stmt = conn_.createStatement();
>>>> > > > > >>> stmt.executeQuery("control query default query_cache '0'");
>>>> > > > > >>>
>>>> > > > > >>> <your query>
>>>> > > > > >>>
>>>> > > > > >>> 2. from sqlci
>>>> > > > > >>> cqd query_cache '0';
>>>> > > > > >>> prepare xx from <your_query>;
>>>> > > > > >>>
>>>> > > > > >>>
>>>> > > > > >>>
>>>> > > > > >>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <
>>>> > > radumarias@gmail.com>
>>>> > > > > wrote:
>>>> > > > > >>>> These 2 caching are indeed very good for performance in
>>>> real
>>>> > > > > environments.
>>>> > > > > >>>> But I'm trying to run some internal benchmarks on
>>>> trafodion and
>>>> > > > > collect the
>>>> > > > > >>>> queries duration, in this case the cache generates
>>>> inconsistency
>>>> > > for
>>>> > > > > my
>>>> > > > > >>>> tests. I can drop the connection and re-run the query on a
>>>> new
>>>> > > > > connection
>>>> > > > > >>>> for this benchmarks but can trafodion compiler cache be
>>>> > disabled?
>>>> > > > And
>>>> > > > > can
>>>> > > > > >>>> HBase RegionServer cache be also disabled?
>>>> > > > > >>>>
>>>> > > > > >>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <
>>>> > > hans.zeller@esgyn.com
>>>> > > > >
>>>> > > > > wrote:
>>>> > > > > >>>>
>>>> > > > > >>>> > You mentioned also that sometimes you get a slower
>>>> response
>>>> > time
>>>> > > > > when
>>>> > > > > >>>> > reconnecting, Radu. As far as I understand (only from
>>>> what
>>>> > > others
>>>> > > > > told me),
>>>> > > > > >>>> > there could be two reasons. One is that your new
>>>> connection
>>>> > > might
>>>> > > > > be to a
>>>> > > > > >>>> > different mxosrvr process, which does not have the cache
>>>> > > > established
>>>> > > > > >>>> > earlier. The other one is that there is a change in the
>>>> user
>>>> > id.
>>>> > > > If
>>>> > > > > an
>>>> > > > > >>>> > mxosrvr changes user ids, it invalidates its cache,
>>>> since some
>>>> > > of
>>>> > > > > the cache
>>>> > > > > >>>> > content (e.g. privilege checks) depends on the user.
>>>> > > Reconnecting
>>>> > > > > to the
>>>> > > > > >>>> > same mxosrvr, using the same user id, with no other users
>>>> > > > > in-between,
>>>> > > > > >>>> > should preserve the cache, as far as I know.
>>>> > > > > >>>> >
>>>> > > > > >>>> > You can use the DCS web gui to see which mxosrvrs you are
>>>> > > > > connecting to. I
>>>> > > > > >>>> > think the default port is 40010.
>>>> > > > > >>>> >
>>>> > > > > >>>> > Hans
>>>> > > > > >>>> >
>>>> > > > > >>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <
>>>> > > > qifan.chen@esgyn.com>
>>>> > > > > wrote:
>>>> > > > > >>>> >
>>>> > > > > >>>> >> Another point is that if you can take the advantage of
>>>> query
>>>> > > > cache
>>>> > > > > by
>>>> > > > > >>>> >> preparing the query once and executing it multiple
>>>> times,
>>>> > > further
>>>> > > > > reducing
>>>> > > > > >>>> >> the compilation time per SQL invocation.
>>>> > > > > >>>> >>
>>>> > > > > >>>> >>
>>>> > > > > >>>> >>
>>>> > > > > >>>> >
>>>> > > > > >>>> >
>>>> > > > > >>>>
>>>> > > > > >>>>
>>>> > > > > >>>> --
>>>> > > > > >>>> And in the end, it's not the years in your life that
>>>> count. It's
>>>> > > the
>>>> > > > > life
>>>> > > > > >>>> in your years.
>>>> > > > > >>>
>>>> > > > > >>>
>>>> > > > > >>>
>>>> > > > > >>> --
>>>> > > > > >>> Regards, --Qifan
>>>> > > > > >>
>>>> > > > >
>>>> > > > >
>>>> > > >
>>>> > >
>>>> > >
>>>> > >
>>>> > > --
>>>> > > Thanks,
>>>> > >
>>>> > > Amanda Moran
>>>> > >
>>>> >
>>>>
>>>>
>>>>
>>>> --
>>>> Thanks,
>>>>
>>>> Amanda Moran
>>>>
>>>
>>
>>
>> --
>> Thanks,
>>
>> Amanda Moran
>>
>
>
>
> --
> And in the end, it's not the years in your life that count. It's the life
> in your years.
>
--
Thanks,
Amanda Moran
Re: Does trafodion caches the results for jdbc queries?
Posted by Radu Marias <ra...@gmail.com>.
We already have a small cluster in place with manually installed hadoop and
hbase and I don't know exactly what installing HDP or Cloudera would mean
on that cluster. I assume it would need to do a clean install and erase
what we have. Or this is not the case?
On Thu, Jul 30, 2015 at 7:00 PM, Amanda Moran <am...@esgyn.com>
wrote:
> Hi there Radu-
>
> Yes, like you said it is not trivial. It is something we could play around
> with and try... but it might not work. Is there a reason you could not
> install HDP or Cloudera?
>
> Thanks!
>
> On Thu, Jul 30, 2015 at 8:58 AM, Radu Marias <ra...@gmail.com> wrote:
>
>> Seen this at some point
>> https://wiki.trafodion.org/wiki/index.php/Troubleshooting_the_Installation
>> and assumed that we could manually do some steps like copying hbase-trx,
>> changing the hbase-site.xml and other installation steps. But I think this
>> is not trivial.
>>
>> On Thu, Jul 30, 2015 at 6:22 PM Amanda Moran <am...@esgyn.com>
>> wrote:
>>
>>> HI there Radu-
>>>
>>> I think there is a little confusion, there is no Trafodion manual
>>> installation. We would need to make some tweaks to the scripts to install
>>> on Ubuntu (don't worry this is a high priority!). We do need Hortonworks
>>> or
>>> Cloudera installed (not simple to remove that dependency).
>>>
>>> Thanks.
>>>
>>> Amanda
>>>
>>> On Wed, Jul 29, 2015 at 5:03 PM, Radu Marias <ra...@gmail.com>
>>> wrote:
>>>
>>> > But in theory it could work on Ubuntu with a manual install?
>>> >
>>> > On Thu, Jul 30, 2015, 02:49 Amanda Moran <am...@esgyn.com>
>>> wrote:
>>> >
>>> > > Hi thre Radu-
>>> > >
>>> > > We are currently <as of today> only able to use the installer to
>>> install
>>> > > Trafodion on RedHat 6 and on Cloudera or Hortonworks.
>>> > >
>>> > > Thanks.
>>> > >
>>> > > Amanda
>>> > >
>>> > > On Wed, Jul 29, 2015 at 4:47 PM, Radu Marias <ra...@gmail.com>
>>> > wrote:
>>> > >
>>> > > > Yes, I agree it's not a good benchmark environment. Initially I got
>>> > > > problems installing, starting it and running any query, now I
>>> managed
>>> > to
>>> > > > run some test queries in sandbox with no crash and decent
>>> performance
>>> > at
>>> > > > least.
>>> > > > The next step is trying to install it on a cluster environment with
>>> > > several
>>> > > > nodes. But we have a small ubuntu cluster on which I manually
>>> installed
>>> > > > hdfs and hbase.
>>> > > > Does the installer work outside of ambari hortonworks environment?
>>> Or I
>>> > > > assume I can manually install trafodion to my cluster adding
>>> > dependencies
>>> > > > and configs as the installer does.
>>> > > >
>>> > > > On Thu, Jul 30, 2015, 02:33 Rohit Jain <ro...@esgyn.com>
>>> wrote:
>>> > > >
>>> > > > > If you are using a sandbox to do this testing, then that is not
>>> an
>>> > > > > environment conducive to performance testing. It will probably
>>> not
>>> > > give
>>> > > > > you reliable and consistent results. A sandbox is limited in
>>> memory
>>> > > and
>>> > > > is
>>> > > > > subject to limitations of the environment used to build it. The
>>> most
>>> > > > > important point is that it is a single node VM and Trafodion is a
>>> > > > scale-out
>>> > > > > architecture. It is best to test its performance on a multi-node
>>> > > cluster.
>>> > > > > I realize that may be hard to do and does involve a full install.
>>> > The
>>> > > > > intent of the sandbox is for you to be able to kick tires and
>>> get a
>>> > > feel
>>> > > > > for Trafodion. It is really not meant for extensive performance
>>> > > testing.
>>> > > > >
>>> > > > > There are smarter folks on this mailing list and I will let them
>>> > > comment
>>> > > > > on that.
>>> > > > >
>>> > > > > Rohit
>>> > > > >
>>> > > > >
>>> > > > >
>>> > > > >
>>> > > > >
>>> > > > > On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com> wrote:
>>> > > > >
>>> > > > > >Hi Radu,
>>> > > > > >
>>> > > > > >In addition, we can help verify the query plan for you to
>>> assure the
>>> > > > > queries can perform reasonably well in your environment, if it
>>> works
>>> > > for
>>> > > > > you.
>>> > > > > >
>>> > > > > >To produce a query plan for query <q>, do the following.
>>> > > > > >
>>> > > > > >log plan.log clear;
>>> > > > > >
>>> > > > > >prepare zzz from <q>:
>>> > > > > >explain zzz;
>>> > > > > >explain options 'f' zzz;
>>> > > > > >
>>> > > > > >--repeat the above three steps for each query
>>> > > > > >
>>> > > > > >exit;
>>> > > > > >
>>> > > > > > You can prepare multiple queries in the same sql session and
>>> > generate
>>> > > > > one plan report in the log file plan.log.
>>> > > > > >
>>> > > > > >Thanks
>>> > > > > >
>>> > > > > >-Qifan
>>> > > > > >
>>> > > > > >Sent from my iPhone
>>> > > > > >
>>> > > > > >> On Jul 29, 2015, at 4:20 PM, Hans Zeller <
>>> hans.zeller@esgyn.com>
>>> > > > wrote:
>>> > > > > >>
>>> > > > > >> Hi Radu,
>>> > > > > >>
>>> > > > > >> My suggestion would be to benchmark the system with warmed-up
>>> > > caches,
>>> > > > > since this will give more meaningful and repeatable numbers. It
>>> > > probably
>>> > > > > depends on the type of benchmark, but in most cases the benchmark
>>> > > itself
>>> > > > > will contain multiple statements, with caching going on between
>>> them,
>>> > > > > making it difficult to clean the cache every time.
>>> > > > > >>
>>> > > > > >> So, I would suggest the following, am not sure how well that
>>> would
>>> > > > work
>>> > > > > for you:
>>> > > > > >> Start from a well-defined point, with empty cache, e.g. after
>>> > > > > re-starting HBase and Trafodion
>>> > > > > >> Run the benchmark. This will be a bit slower initially, as the
>>> > > caches
>>> > > > > get filled.
>>> > > > > >> If the benchmark is very short, run the benchmark again, this
>>> time
>>> > > > with
>>> > > > > warmed-up cache. Alternatively, if the benchmark itself repeats
>>> > queries
>>> > > > > many times, show a graph of performance over time to show the
>>> warm-up
>>> > > > time.
>>> > > > > >> Thanks,
>>> > > > > >>
>>> > > > > >> Hans
>>> > > > > >>
>>> > > > > >>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <
>>> > qifan.chen@esgyn.com
>>> > > >
>>> > > > > wrote:
>>> > > > > >>> yes, there exists a control switch to turn off query cache in
>>> > > > compiler.
>>> > > > > >>>
>>> > > > > >>> Such switch is called query_cache and can be used in a
>>> control
>>> > > query
>>> > > > > default statement (CQD in short) to control how much memory that
>>> the
>>> > > > query
>>> > > > > cache can use. When we set it to a value of 0, the cache is
>>> > disabled.
>>> > > > To
>>> > > > > set the CQD, try one of the following two approaches as follows.
>>> > > > > >>>
>>> > > > > >>> 1. from java,
>>> > > > > >>>
>>> > > > > >>> Statement stmt = conn_.createStatement();
>>> > > > > >>> stmt.executeQuery("control query default query_cache '0'");
>>> > > > > >>>
>>> > > > > >>> <your query>
>>> > > > > >>>
>>> > > > > >>> 2. from sqlci
>>> > > > > >>> cqd query_cache '0';
>>> > > > > >>> prepare xx from <your_query>;
>>> > > > > >>>
>>> > > > > >>>
>>> > > > > >>>
>>> > > > > >>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <
>>> > > radumarias@gmail.com>
>>> > > > > wrote:
>>> > > > > >>>> These 2 caching are indeed very good for performance in real
>>> > > > > environments.
>>> > > > > >>>> But I'm trying to run some internal benchmarks on trafodion
>>> and
>>> > > > > collect the
>>> > > > > >>>> queries duration, in this case the cache generates
>>> inconsistency
>>> > > for
>>> > > > > my
>>> > > > > >>>> tests. I can drop the connection and re-run the query on a
>>> new
>>> > > > > connection
>>> > > > > >>>> for this benchmarks but can trafodion compiler cache be
>>> > disabled?
>>> > > > And
>>> > > > > can
>>> > > > > >>>> HBase RegionServer cache be also disabled?
>>> > > > > >>>>
>>> > > > > >>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <
>>> > > hans.zeller@esgyn.com
>>> > > > >
>>> > > > > wrote:
>>> > > > > >>>>
>>> > > > > >>>> > You mentioned also that sometimes you get a slower
>>> response
>>> > time
>>> > > > > when
>>> > > > > >>>> > reconnecting, Radu. As far as I understand (only from what
>>> > > others
>>> > > > > told me),
>>> > > > > >>>> > there could be two reasons. One is that your new
>>> connection
>>> > > might
>>> > > > > be to a
>>> > > > > >>>> > different mxosrvr process, which does not have the cache
>>> > > > established
>>> > > > > >>>> > earlier. The other one is that there is a change in the
>>> user
>>> > id.
>>> > > > If
>>> > > > > an
>>> > > > > >>>> > mxosrvr changes user ids, it invalidates its cache, since
>>> some
>>> > > of
>>> > > > > the cache
>>> > > > > >>>> > content (e.g. privilege checks) depends on the user.
>>> > > Reconnecting
>>> > > > > to the
>>> > > > > >>>> > same mxosrvr, using the same user id, with no other users
>>> > > > > in-between,
>>> > > > > >>>> > should preserve the cache, as far as I know.
>>> > > > > >>>> >
>>> > > > > >>>> > You can use the DCS web gui to see which mxosrvrs you are
>>> > > > > connecting to. I
>>> > > > > >>>> > think the default port is 40010.
>>> > > > > >>>> >
>>> > > > > >>>> > Hans
>>> > > > > >>>> >
>>> > > > > >>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <
>>> > > > qifan.chen@esgyn.com>
>>> > > > > wrote:
>>> > > > > >>>> >
>>> > > > > >>>> >> Another point is that if you can take the advantage of
>>> query
>>> > > > cache
>>> > > > > by
>>> > > > > >>>> >> preparing the query once and executing it multiple times,
>>> > > further
>>> > > > > reducing
>>> > > > > >>>> >> the compilation time per SQL invocation.
>>> > > > > >>>> >>
>>> > > > > >>>> >>
>>> > > > > >>>> >>
>>> > > > > >>>> >
>>> > > > > >>>> >
>>> > > > > >>>>
>>> > > > > >>>>
>>> > > > > >>>> --
>>> > > > > >>>> And in the end, it's not the years in your life that count.
>>> It's
>>> > > the
>>> > > > > life
>>> > > > > >>>> in your years.
>>> > > > > >>>
>>> > > > > >>>
>>> > > > > >>>
>>> > > > > >>> --
>>> > > > > >>> Regards, --Qifan
>>> > > > > >>
>>> > > > >
>>> > > > >
>>> > > >
>>> > >
>>> > >
>>> > >
>>> > > --
>>> > > Thanks,
>>> > >
>>> > > Amanda Moran
>>> > >
>>> >
>>>
>>>
>>>
>>> --
>>> Thanks,
>>>
>>> Amanda Moran
>>>
>>
>
>
> --
> Thanks,
>
> Amanda Moran
>
--
And in the end, it's not the years in your life that count. It's the life
in your years.
Re: Does trafodion caches the results for jdbc queries?
Posted by Amanda Moran <am...@esgyn.com>.
Hi there Radu-
Yes, like you said it is not trivial. It is something we could play around
with and try... but it might not work. Is there a reason you could not
install HDP or Cloudera?
Thanks!
On Thu, Jul 30, 2015 at 8:58 AM, Radu Marias <ra...@gmail.com> wrote:
> Seen this at some point
> https://wiki.trafodion.org/wiki/index.php/Troubleshooting_the_Installation
> and assumed that we could manually do some steps like copying hbase-trx,
> changing the hbase-site.xml and other installation steps. But I think this
> is not trivial.
>
> On Thu, Jul 30, 2015 at 6:22 PM Amanda Moran <am...@esgyn.com>
> wrote:
>
>> HI there Radu-
>>
>> I think there is a little confusion, there is no Trafodion manual
>> installation. We would need to make some tweaks to the scripts to install
>> on Ubuntu (don't worry this is a high priority!). We do need Hortonworks
>> or
>> Cloudera installed (not simple to remove that dependency).
>>
>> Thanks.
>>
>> Amanda
>>
>> On Wed, Jul 29, 2015 at 5:03 PM, Radu Marias <ra...@gmail.com>
>> wrote:
>>
>> > But in theory it could work on Ubuntu with a manual install?
>> >
>> > On Thu, Jul 30, 2015, 02:49 Amanda Moran <am...@esgyn.com>
>> wrote:
>> >
>> > > Hi thre Radu-
>> > >
>> > > We are currently <as of today> only able to use the installer to
>> install
>> > > Trafodion on RedHat 6 and on Cloudera or Hortonworks.
>> > >
>> > > Thanks.
>> > >
>> > > Amanda
>> > >
>> > > On Wed, Jul 29, 2015 at 4:47 PM, Radu Marias <ra...@gmail.com>
>> > wrote:
>> > >
>> > > > Yes, I agree it's not a good benchmark environment. Initially I got
>> > > > problems installing, starting it and running any query, now I
>> managed
>> > to
>> > > > run some test queries in sandbox with no crash and decent
>> performance
>> > at
>> > > > least.
>> > > > The next step is trying to install it on a cluster environment with
>> > > several
>> > > > nodes. But we have a small ubuntu cluster on which I manually
>> installed
>> > > > hdfs and hbase.
>> > > > Does the installer work outside of ambari hortonworks environment?
>> Or I
>> > > > assume I can manually install trafodion to my cluster adding
>> > dependencies
>> > > > and configs as the installer does.
>> > > >
>> > > > On Thu, Jul 30, 2015, 02:33 Rohit Jain <ro...@esgyn.com>
>> wrote:
>> > > >
>> > > > > If you are using a sandbox to do this testing, then that is not an
>> > > > > environment conducive to performance testing. It will probably
>> not
>> > > give
>> > > > > you reliable and consistent results. A sandbox is limited in
>> memory
>> > > and
>> > > > is
>> > > > > subject to limitations of the environment used to build it. The
>> most
>> > > > > important point is that it is a single node VM and Trafodion is a
>> > > > scale-out
>> > > > > architecture. It is best to test its performance on a multi-node
>> > > cluster.
>> > > > > I realize that may be hard to do and does involve a full install.
>> > The
>> > > > > intent of the sandbox is for you to be able to kick tires and get
>> a
>> > > feel
>> > > > > for Trafodion. It is really not meant for extensive performance
>> > > testing.
>> > > > >
>> > > > > There are smarter folks on this mailing list and I will let them
>> > > comment
>> > > > > on that.
>> > > > >
>> > > > > Rohit
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > >
>> > > > > On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com> wrote:
>> > > > >
>> > > > > >Hi Radu,
>> > > > > >
>> > > > > >In addition, we can help verify the query plan for you to assure
>> the
>> > > > > queries can perform reasonably well in your environment, if it
>> works
>> > > for
>> > > > > you.
>> > > > > >
>> > > > > >To produce a query plan for query <q>, do the following.
>> > > > > >
>> > > > > >log plan.log clear;
>> > > > > >
>> > > > > >prepare zzz from <q>:
>> > > > > >explain zzz;
>> > > > > >explain options 'f' zzz;
>> > > > > >
>> > > > > >--repeat the above three steps for each query
>> > > > > >
>> > > > > >exit;
>> > > > > >
>> > > > > > You can prepare multiple queries in the same sql session and
>> > generate
>> > > > > one plan report in the log file plan.log.
>> > > > > >
>> > > > > >Thanks
>> > > > > >
>> > > > > >-Qifan
>> > > > > >
>> > > > > >Sent from my iPhone
>> > > > > >
>> > > > > >> On Jul 29, 2015, at 4:20 PM, Hans Zeller <
>> hans.zeller@esgyn.com>
>> > > > wrote:
>> > > > > >>
>> > > > > >> Hi Radu,
>> > > > > >>
>> > > > > >> My suggestion would be to benchmark the system with warmed-up
>> > > caches,
>> > > > > since this will give more meaningful and repeatable numbers. It
>> > > probably
>> > > > > depends on the type of benchmark, but in most cases the benchmark
>> > > itself
>> > > > > will contain multiple statements, with caching going on between
>> them,
>> > > > > making it difficult to clean the cache every time.
>> > > > > >>
>> > > > > >> So, I would suggest the following, am not sure how well that
>> would
>> > > > work
>> > > > > for you:
>> > > > > >> Start from a well-defined point, with empty cache, e.g. after
>> > > > > re-starting HBase and Trafodion
>> > > > > >> Run the benchmark. This will be a bit slower initially, as the
>> > > caches
>> > > > > get filled.
>> > > > > >> If the benchmark is very short, run the benchmark again, this
>> time
>> > > > with
>> > > > > warmed-up cache. Alternatively, if the benchmark itself repeats
>> > queries
>> > > > > many times, show a graph of performance over time to show the
>> warm-up
>> > > > time.
>> > > > > >> Thanks,
>> > > > > >>
>> > > > > >> Hans
>> > > > > >>
>> > > > > >>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <
>> > qifan.chen@esgyn.com
>> > > >
>> > > > > wrote:
>> > > > > >>> yes, there exists a control switch to turn off query cache in
>> > > > compiler.
>> > > > > >>>
>> > > > > >>> Such switch is called query_cache and can be used in a control
>> > > query
>> > > > > default statement (CQD in short) to control how much memory that
>> the
>> > > > query
>> > > > > cache can use. When we set it to a value of 0, the cache is
>> > disabled.
>> > > > To
>> > > > > set the CQD, try one of the following two approaches as follows.
>> > > > > >>>
>> > > > > >>> 1. from java,
>> > > > > >>>
>> > > > > >>> Statement stmt = conn_.createStatement();
>> > > > > >>> stmt.executeQuery("control query default query_cache '0'");
>> > > > > >>>
>> > > > > >>> <your query>
>> > > > > >>>
>> > > > > >>> 2. from sqlci
>> > > > > >>> cqd query_cache '0';
>> > > > > >>> prepare xx from <your_query>;
>> > > > > >>>
>> > > > > >>>
>> > > > > >>>
>> > > > > >>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <
>> > > radumarias@gmail.com>
>> > > > > wrote:
>> > > > > >>>> These 2 caching are indeed very good for performance in real
>> > > > > environments.
>> > > > > >>>> But I'm trying to run some internal benchmarks on trafodion
>> and
>> > > > > collect the
>> > > > > >>>> queries duration, in this case the cache generates
>> inconsistency
>> > > for
>> > > > > my
>> > > > > >>>> tests. I can drop the connection and re-run the query on a
>> new
>> > > > > connection
>> > > > > >>>> for this benchmarks but can trafodion compiler cache be
>> > disabled?
>> > > > And
>> > > > > can
>> > > > > >>>> HBase RegionServer cache be also disabled?
>> > > > > >>>>
>> > > > > >>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <
>> > > hans.zeller@esgyn.com
>> > > > >
>> > > > > wrote:
>> > > > > >>>>
>> > > > > >>>> > You mentioned also that sometimes you get a slower response
>> > time
>> > > > > when
>> > > > > >>>> > reconnecting, Radu. As far as I understand (only from what
>> > > others
>> > > > > told me),
>> > > > > >>>> > there could be two reasons. One is that your new connection
>> > > might
>> > > > > be to a
>> > > > > >>>> > different mxosrvr process, which does not have the cache
>> > > > established
>> > > > > >>>> > earlier. The other one is that there is a change in the
>> user
>> > id.
>> > > > If
>> > > > > an
>> > > > > >>>> > mxosrvr changes user ids, it invalidates its cache, since
>> some
>> > > of
>> > > > > the cache
>> > > > > >>>> > content (e.g. privilege checks) depends on the user.
>> > > Reconnecting
>> > > > > to the
>> > > > > >>>> > same mxosrvr, using the same user id, with no other users
>> > > > > in-between,
>> > > > > >>>> > should preserve the cache, as far as I know.
>> > > > > >>>> >
>> > > > > >>>> > You can use the DCS web gui to see which mxosrvrs you are
>> > > > > connecting to. I
>> > > > > >>>> > think the default port is 40010.
>> > > > > >>>> >
>> > > > > >>>> > Hans
>> > > > > >>>> >
>> > > > > >>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <
>> > > > qifan.chen@esgyn.com>
>> > > > > wrote:
>> > > > > >>>> >
>> > > > > >>>> >> Another point is that if you can take the advantage of
>> query
>> > > > cache
>> > > > > by
>> > > > > >>>> >> preparing the query once and executing it multiple times,
>> > > further
>> > > > > reducing
>> > > > > >>>> >> the compilation time per SQL invocation.
>> > > > > >>>> >>
>> > > > > >>>> >>
>> > > > > >>>> >>
>> > > > > >>>> >
>> > > > > >>>> >
>> > > > > >>>>
>> > > > > >>>>
>> > > > > >>>> --
>> > > > > >>>> And in the end, it's not the years in your life that count.
>> It's
>> > > the
>> > > > > life
>> > > > > >>>> in your years.
>> > > > > >>>
>> > > > > >>>
>> > > > > >>>
>> > > > > >>> --
>> > > > > >>> Regards, --Qifan
>> > > > > >>
>> > > > >
>> > > > >
>> > > >
>> > >
>> > >
>> > >
>> > > --
>> > > Thanks,
>> > >
>> > > Amanda Moran
>> > >
>> >
>>
>>
>>
>> --
>> Thanks,
>>
>> Amanda Moran
>>
>
--
Thanks,
Amanda Moran
Re: Does trafodion caches the results for jdbc queries?
Posted by Radu Marias <ra...@gmail.com>.
Seen this at some point
https://wiki.trafodion.org/wiki/index.php/Troubleshooting_the_Installation
and assumed that we could manually do some steps like copying hbase-trx,
changing the hbase-site.xml and other installation steps. But I think this
is not trivial.
On Thu, Jul 30, 2015 at 6:22 PM Amanda Moran <am...@esgyn.com> wrote:
> HI there Radu-
>
> I think there is a little confusion, there is no Trafodion manual
> installation. We would need to make some tweaks to the scripts to install
> on Ubuntu (don't worry this is a high priority!). We do need Hortonworks or
> Cloudera installed (not simple to remove that dependency).
>
> Thanks.
>
> Amanda
>
> On Wed, Jul 29, 2015 at 5:03 PM, Radu Marias <ra...@gmail.com> wrote:
>
> > But in theory it could work on Ubuntu with a manual install?
> >
> > On Thu, Jul 30, 2015, 02:49 Amanda Moran <am...@esgyn.com> wrote:
> >
> > > Hi thre Radu-
> > >
> > > We are currently <as of today> only able to use the installer to
> install
> > > Trafodion on RedHat 6 and on Cloudera or Hortonworks.
> > >
> > > Thanks.
> > >
> > > Amanda
> > >
> > > On Wed, Jul 29, 2015 at 4:47 PM, Radu Marias <ra...@gmail.com>
> > wrote:
> > >
> > > > Yes, I agree it's not a good benchmark environment. Initially I got
> > > > problems installing, starting it and running any query, now I managed
> > to
> > > > run some test queries in sandbox with no crash and decent performance
> > at
> > > > least.
> > > > The next step is trying to install it on a cluster environment with
> > > several
> > > > nodes. But we have a small ubuntu cluster on which I manually
> installed
> > > > hdfs and hbase.
> > > > Does the installer work outside of ambari hortonworks environment?
> Or I
> > > > assume I can manually install trafodion to my cluster adding
> > dependencies
> > > > and configs as the installer does.
> > > >
> > > > On Thu, Jul 30, 2015, 02:33 Rohit Jain <ro...@esgyn.com> wrote:
> > > >
> > > > > If you are using a sandbox to do this testing, then that is not an
> > > > > environment conducive to performance testing. It will probably not
> > > give
> > > > > you reliable and consistent results. A sandbox is limited in
> memory
> > > and
> > > > is
> > > > > subject to limitations of the environment used to build it. The
> most
> > > > > important point is that it is a single node VM and Trafodion is a
> > > > scale-out
> > > > > architecture. It is best to test its performance on a multi-node
> > > cluster.
> > > > > I realize that may be hard to do and does involve a full install.
> > The
> > > > > intent of the sandbox is for you to be able to kick tires and get a
> > > feel
> > > > > for Trafodion. It is really not meant for extensive performance
> > > testing.
> > > > >
> > > > > There are smarter folks on this mailing list and I will let them
> > > comment
> > > > > on that.
> > > > >
> > > > > Rohit
> > > > >
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com> wrote:
> > > > >
> > > > > >Hi Radu,
> > > > > >
> > > > > >In addition, we can help verify the query plan for you to assure
> the
> > > > > queries can perform reasonably well in your environment, if it
> works
> > > for
> > > > > you.
> > > > > >
> > > > > >To produce a query plan for query <q>, do the following.
> > > > > >
> > > > > >log plan.log clear;
> > > > > >
> > > > > >prepare zzz from <q>:
> > > > > >explain zzz;
> > > > > >explain options 'f' zzz;
> > > > > >
> > > > > >--repeat the above three steps for each query
> > > > > >
> > > > > >exit;
> > > > > >
> > > > > > You can prepare multiple queries in the same sql session and
> > generate
> > > > > one plan report in the log file plan.log.
> > > > > >
> > > > > >Thanks
> > > > > >
> > > > > >-Qifan
> > > > > >
> > > > > >Sent from my iPhone
> > > > > >
> > > > > >> On Jul 29, 2015, at 4:20 PM, Hans Zeller <hans.zeller@esgyn.com
> >
> > > > wrote:
> > > > > >>
> > > > > >> Hi Radu,
> > > > > >>
> > > > > >> My suggestion would be to benchmark the system with warmed-up
> > > caches,
> > > > > since this will give more meaningful and repeatable numbers. It
> > > probably
> > > > > depends on the type of benchmark, but in most cases the benchmark
> > > itself
> > > > > will contain multiple statements, with caching going on between
> them,
> > > > > making it difficult to clean the cache every time.
> > > > > >>
> > > > > >> So, I would suggest the following, am not sure how well that
> would
> > > > work
> > > > > for you:
> > > > > >> Start from a well-defined point, with empty cache, e.g. after
> > > > > re-starting HBase and Trafodion
> > > > > >> Run the benchmark. This will be a bit slower initially, as the
> > > caches
> > > > > get filled.
> > > > > >> If the benchmark is very short, run the benchmark again, this
> time
> > > > with
> > > > > warmed-up cache. Alternatively, if the benchmark itself repeats
> > queries
> > > > > many times, show a graph of performance over time to show the
> warm-up
> > > > time.
> > > > > >> Thanks,
> > > > > >>
> > > > > >> Hans
> > > > > >>
> > > > > >>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <
> > qifan.chen@esgyn.com
> > > >
> > > > > wrote:
> > > > > >>> yes, there exists a control switch to turn off query cache in
> > > > compiler.
> > > > > >>>
> > > > > >>> Such switch is called query_cache and can be used in a control
> > > query
> > > > > default statement (CQD in short) to control how much memory that
> the
> > > > query
> > > > > cache can use. When we set it to a value of 0, the cache is
> > disabled.
> > > > To
> > > > > set the CQD, try one of the following two approaches as follows.
> > > > > >>>
> > > > > >>> 1. from java,
> > > > > >>>
> > > > > >>> Statement stmt = conn_.createStatement();
> > > > > >>> stmt.executeQuery("control query default query_cache '0'");
> > > > > >>>
> > > > > >>> <your query>
> > > > > >>>
> > > > > >>> 2. from sqlci
> > > > > >>> cqd query_cache '0';
> > > > > >>> prepare xx from <your_query>;
> > > > > >>>
> > > > > >>>
> > > > > >>>
> > > > > >>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <
> > > radumarias@gmail.com>
> > > > > wrote:
> > > > > >>>> These 2 caching are indeed very good for performance in real
> > > > > environments.
> > > > > >>>> But I'm trying to run some internal benchmarks on trafodion
> and
> > > > > collect the
> > > > > >>>> queries duration, in this case the cache generates
> inconsistency
> > > for
> > > > > my
> > > > > >>>> tests. I can drop the connection and re-run the query on a new
> > > > > connection
> > > > > >>>> for this benchmarks but can trafodion compiler cache be
> > disabled?
> > > > And
> > > > > can
> > > > > >>>> HBase RegionServer cache be also disabled?
> > > > > >>>>
> > > > > >>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <
> > > hans.zeller@esgyn.com
> > > > >
> > > > > wrote:
> > > > > >>>>
> > > > > >>>> > You mentioned also that sometimes you get a slower response
> > time
> > > > > when
> > > > > >>>> > reconnecting, Radu. As far as I understand (only from what
> > > others
> > > > > told me),
> > > > > >>>> > there could be two reasons. One is that your new connection
> > > might
> > > > > be to a
> > > > > >>>> > different mxosrvr process, which does not have the cache
> > > > established
> > > > > >>>> > earlier. The other one is that there is a change in the user
> > id.
> > > > If
> > > > > an
> > > > > >>>> > mxosrvr changes user ids, it invalidates its cache, since
> some
> > > of
> > > > > the cache
> > > > > >>>> > content (e.g. privilege checks) depends on the user.
> > > Reconnecting
> > > > > to the
> > > > > >>>> > same mxosrvr, using the same user id, with no other users
> > > > > in-between,
> > > > > >>>> > should preserve the cache, as far as I know.
> > > > > >>>> >
> > > > > >>>> > You can use the DCS web gui to see which mxosrvrs you are
> > > > > connecting to. I
> > > > > >>>> > think the default port is 40010.
> > > > > >>>> >
> > > > > >>>> > Hans
> > > > > >>>> >
> > > > > >>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <
> > > > qifan.chen@esgyn.com>
> > > > > wrote:
> > > > > >>>> >
> > > > > >>>> >> Another point is that if you can take the advantage of
> query
> > > > cache
> > > > > by
> > > > > >>>> >> preparing the query once and executing it multiple times,
> > > further
> > > > > reducing
> > > > > >>>> >> the compilation time per SQL invocation.
> > > > > >>>> >>
> > > > > >>>> >>
> > > > > >>>> >>
> > > > > >>>> >
> > > > > >>>> >
> > > > > >>>>
> > > > > >>>>
> > > > > >>>> --
> > > > > >>>> And in the end, it's not the years in your life that count.
> It's
> > > the
> > > > > life
> > > > > >>>> in your years.
> > > > > >>>
> > > > > >>>
> > > > > >>>
> > > > > >>> --
> > > > > >>> Regards, --Qifan
> > > > > >>
> > > > >
> > > > >
> > > >
> > >
> > >
> > >
> > > --
> > > Thanks,
> > >
> > > Amanda Moran
> > >
> >
>
>
>
> --
> Thanks,
>
> Amanda Moran
>
Re: Does trafodion caches the results for jdbc queries?
Posted by Amanda Moran <am...@esgyn.com>.
HI there Radu-
I think there is a little confusion, there is no Trafodion manual
installation. We would need to make some tweaks to the scripts to install
on Ubuntu (don't worry this is a high priority!). We do need Hortonworks or
Cloudera installed (not simple to remove that dependency).
Thanks.
Amanda
On Wed, Jul 29, 2015 at 5:03 PM, Radu Marias <ra...@gmail.com> wrote:
> But in theory it could work on Ubuntu with a manual install?
>
> On Thu, Jul 30, 2015, 02:49 Amanda Moran <am...@esgyn.com> wrote:
>
> > Hi thre Radu-
> >
> > We are currently <as of today> only able to use the installer to install
> > Trafodion on RedHat 6 and on Cloudera or Hortonworks.
> >
> > Thanks.
> >
> > Amanda
> >
> > On Wed, Jul 29, 2015 at 4:47 PM, Radu Marias <ra...@gmail.com>
> wrote:
> >
> > > Yes, I agree it's not a good benchmark environment. Initially I got
> > > problems installing, starting it and running any query, now I managed
> to
> > > run some test queries in sandbox with no crash and decent performance
> at
> > > least.
> > > The next step is trying to install it on a cluster environment with
> > several
> > > nodes. But we have a small ubuntu cluster on which I manually installed
> > > hdfs and hbase.
> > > Does the installer work outside of ambari hortonworks environment? Or I
> > > assume I can manually install trafodion to my cluster adding
> dependencies
> > > and configs as the installer does.
> > >
> > > On Thu, Jul 30, 2015, 02:33 Rohit Jain <ro...@esgyn.com> wrote:
> > >
> > > > If you are using a sandbox to do this testing, then that is not an
> > > > environment conducive to performance testing. It will probably not
> > give
> > > > you reliable and consistent results. A sandbox is limited in memory
> > and
> > > is
> > > > subject to limitations of the environment used to build it. The most
> > > > important point is that it is a single node VM and Trafodion is a
> > > scale-out
> > > > architecture. It is best to test its performance on a multi-node
> > cluster.
> > > > I realize that may be hard to do and does involve a full install.
> The
> > > > intent of the sandbox is for you to be able to kick tires and get a
> > feel
> > > > for Trafodion. It is really not meant for extensive performance
> > testing.
> > > >
> > > > There are smarter folks on this mailing list and I will let them
> > comment
> > > > on that.
> > > >
> > > > Rohit
> > > >
> > > >
> > > >
> > > >
> > > >
> > > > On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com> wrote:
> > > >
> > > > >Hi Radu,
> > > > >
> > > > >In addition, we can help verify the query plan for you to assure the
> > > > queries can perform reasonably well in your environment, if it works
> > for
> > > > you.
> > > > >
> > > > >To produce a query plan for query <q>, do the following.
> > > > >
> > > > >log plan.log clear;
> > > > >
> > > > >prepare zzz from <q>:
> > > > >explain zzz;
> > > > >explain options 'f' zzz;
> > > > >
> > > > >--repeat the above three steps for each query
> > > > >
> > > > >exit;
> > > > >
> > > > > You can prepare multiple queries in the same sql session and
> generate
> > > > one plan report in the log file plan.log.
> > > > >
> > > > >Thanks
> > > > >
> > > > >-Qifan
> > > > >
> > > > >Sent from my iPhone
> > > > >
> > > > >> On Jul 29, 2015, at 4:20 PM, Hans Zeller <ha...@esgyn.com>
> > > wrote:
> > > > >>
> > > > >> Hi Radu,
> > > > >>
> > > > >> My suggestion would be to benchmark the system with warmed-up
> > caches,
> > > > since this will give more meaningful and repeatable numbers. It
> > probably
> > > > depends on the type of benchmark, but in most cases the benchmark
> > itself
> > > > will contain multiple statements, with caching going on between them,
> > > > making it difficult to clean the cache every time.
> > > > >>
> > > > >> So, I would suggest the following, am not sure how well that would
> > > work
> > > > for you:
> > > > >> Start from a well-defined point, with empty cache, e.g. after
> > > > re-starting HBase and Trafodion
> > > > >> Run the benchmark. This will be a bit slower initially, as the
> > caches
> > > > get filled.
> > > > >> If the benchmark is very short, run the benchmark again, this time
> > > with
> > > > warmed-up cache. Alternatively, if the benchmark itself repeats
> queries
> > > > many times, show a graph of performance over time to show the warm-up
> > > time.
> > > > >> Thanks,
> > > > >>
> > > > >> Hans
> > > > >>
> > > > >>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <
> qifan.chen@esgyn.com
> > >
> > > > wrote:
> > > > >>> yes, there exists a control switch to turn off query cache in
> > > compiler.
> > > > >>>
> > > > >>> Such switch is called query_cache and can be used in a control
> > query
> > > > default statement (CQD in short) to control how much memory that the
> > > query
> > > > cache can use. When we set it to a value of 0, the cache is
> disabled.
> > > To
> > > > set the CQD, try one of the following two approaches as follows.
> > > > >>>
> > > > >>> 1. from java,
> > > > >>>
> > > > >>> Statement stmt = conn_.createStatement();
> > > > >>> stmt.executeQuery("control query default query_cache '0'");
> > > > >>>
> > > > >>> <your query>
> > > > >>>
> > > > >>> 2. from sqlci
> > > > >>> cqd query_cache '0';
> > > > >>> prepare xx from <your_query>;
> > > > >>>
> > > > >>>
> > > > >>>
> > > > >>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <
> > radumarias@gmail.com>
> > > > wrote:
> > > > >>>> These 2 caching are indeed very good for performance in real
> > > > environments.
> > > > >>>> But I'm trying to run some internal benchmarks on trafodion and
> > > > collect the
> > > > >>>> queries duration, in this case the cache generates inconsistency
> > for
> > > > my
> > > > >>>> tests. I can drop the connection and re-run the query on a new
> > > > connection
> > > > >>>> for this benchmarks but can trafodion compiler cache be
> disabled?
> > > And
> > > > can
> > > > >>>> HBase RegionServer cache be also disabled?
> > > > >>>>
> > > > >>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <
> > hans.zeller@esgyn.com
> > > >
> > > > wrote:
> > > > >>>>
> > > > >>>> > You mentioned also that sometimes you get a slower response
> time
> > > > when
> > > > >>>> > reconnecting, Radu. As far as I understand (only from what
> > others
> > > > told me),
> > > > >>>> > there could be two reasons. One is that your new connection
> > might
> > > > be to a
> > > > >>>> > different mxosrvr process, which does not have the cache
> > > established
> > > > >>>> > earlier. The other one is that there is a change in the user
> id.
> > > If
> > > > an
> > > > >>>> > mxosrvr changes user ids, it invalidates its cache, since some
> > of
> > > > the cache
> > > > >>>> > content (e.g. privilege checks) depends on the user.
> > Reconnecting
> > > > to the
> > > > >>>> > same mxosrvr, using the same user id, with no other users
> > > > in-between,
> > > > >>>> > should preserve the cache, as far as I know.
> > > > >>>> >
> > > > >>>> > You can use the DCS web gui to see which mxosrvrs you are
> > > > connecting to. I
> > > > >>>> > think the default port is 40010.
> > > > >>>> >
> > > > >>>> > Hans
> > > > >>>> >
> > > > >>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <
> > > qifan.chen@esgyn.com>
> > > > wrote:
> > > > >>>> >
> > > > >>>> >> Another point is that if you can take the advantage of query
> > > cache
> > > > by
> > > > >>>> >> preparing the query once and executing it multiple times,
> > further
> > > > reducing
> > > > >>>> >> the compilation time per SQL invocation.
> > > > >>>> >>
> > > > >>>> >>
> > > > >>>> >>
> > > > >>>> >
> > > > >>>> >
> > > > >>>>
> > > > >>>>
> > > > >>>> --
> > > > >>>> And in the end, it's not the years in your life that count. It's
> > the
> > > > life
> > > > >>>> in your years.
> > > > >>>
> > > > >>>
> > > > >>>
> > > > >>> --
> > > > >>> Regards, --Qifan
> > > > >>
> > > >
> > > >
> > >
> >
> >
> >
> > --
> > Thanks,
> >
> > Amanda Moran
> >
>
--
Thanks,
Amanda Moran
Re: Does trafodion caches the results for jdbc queries?
Posted by Radu Marias <ra...@gmail.com>.
But in theory it could work on Ubuntu with a manual install?
On Thu, Jul 30, 2015, 02:49 Amanda Moran <am...@esgyn.com> wrote:
> Hi thre Radu-
>
> We are currently <as of today> only able to use the installer to install
> Trafodion on RedHat 6 and on Cloudera or Hortonworks.
>
> Thanks.
>
> Amanda
>
> On Wed, Jul 29, 2015 at 4:47 PM, Radu Marias <ra...@gmail.com> wrote:
>
> > Yes, I agree it's not a good benchmark environment. Initially I got
> > problems installing, starting it and running any query, now I managed to
> > run some test queries in sandbox with no crash and decent performance at
> > least.
> > The next step is trying to install it on a cluster environment with
> several
> > nodes. But we have a small ubuntu cluster on which I manually installed
> > hdfs and hbase.
> > Does the installer work outside of ambari hortonworks environment? Or I
> > assume I can manually install trafodion to my cluster adding dependencies
> > and configs as the installer does.
> >
> > On Thu, Jul 30, 2015, 02:33 Rohit Jain <ro...@esgyn.com> wrote:
> >
> > > If you are using a sandbox to do this testing, then that is not an
> > > environment conducive to performance testing. It will probably not
> give
> > > you reliable and consistent results. A sandbox is limited in memory
> and
> > is
> > > subject to limitations of the environment used to build it. The most
> > > important point is that it is a single node VM and Trafodion is a
> > scale-out
> > > architecture. It is best to test its performance on a multi-node
> cluster.
> > > I realize that may be hard to do and does involve a full install. The
> > > intent of the sandbox is for you to be able to kick tires and get a
> feel
> > > for Trafodion. It is really not meant for extensive performance
> testing.
> > >
> > > There are smarter folks on this mailing list and I will let them
> comment
> > > on that.
> > >
> > > Rohit
> > >
> > >
> > >
> > >
> > >
> > > On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com> wrote:
> > >
> > > >Hi Radu,
> > > >
> > > >In addition, we can help verify the query plan for you to assure the
> > > queries can perform reasonably well in your environment, if it works
> for
> > > you.
> > > >
> > > >To produce a query plan for query <q>, do the following.
> > > >
> > > >log plan.log clear;
> > > >
> > > >prepare zzz from <q>:
> > > >explain zzz;
> > > >explain options 'f' zzz;
> > > >
> > > >--repeat the above three steps for each query
> > > >
> > > >exit;
> > > >
> > > > You can prepare multiple queries in the same sql session and generate
> > > one plan report in the log file plan.log.
> > > >
> > > >Thanks
> > > >
> > > >-Qifan
> > > >
> > > >Sent from my iPhone
> > > >
> > > >> On Jul 29, 2015, at 4:20 PM, Hans Zeller <ha...@esgyn.com>
> > wrote:
> > > >>
> > > >> Hi Radu,
> > > >>
> > > >> My suggestion would be to benchmark the system with warmed-up
> caches,
> > > since this will give more meaningful and repeatable numbers. It
> probably
> > > depends on the type of benchmark, but in most cases the benchmark
> itself
> > > will contain multiple statements, with caching going on between them,
> > > making it difficult to clean the cache every time.
> > > >>
> > > >> So, I would suggest the following, am not sure how well that would
> > work
> > > for you:
> > > >> Start from a well-defined point, with empty cache, e.g. after
> > > re-starting HBase and Trafodion
> > > >> Run the benchmark. This will be a bit slower initially, as the
> caches
> > > get filled.
> > > >> If the benchmark is very short, run the benchmark again, this time
> > with
> > > warmed-up cache. Alternatively, if the benchmark itself repeats queries
> > > many times, show a graph of performance over time to show the warm-up
> > time.
> > > >> Thanks,
> > > >>
> > > >> Hans
> > > >>
> > > >>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <qifan.chen@esgyn.com
> >
> > > wrote:
> > > >>> yes, there exists a control switch to turn off query cache in
> > compiler.
> > > >>>
> > > >>> Such switch is called query_cache and can be used in a control
> query
> > > default statement (CQD in short) to control how much memory that the
> > query
> > > cache can use. When we set it to a value of 0, the cache is disabled.
> > To
> > > set the CQD, try one of the following two approaches as follows.
> > > >>>
> > > >>> 1. from java,
> > > >>>
> > > >>> Statement stmt = conn_.createStatement();
> > > >>> stmt.executeQuery("control query default query_cache '0'");
> > > >>>
> > > >>> <your query>
> > > >>>
> > > >>> 2. from sqlci
> > > >>> cqd query_cache '0';
> > > >>> prepare xx from <your_query>;
> > > >>>
> > > >>>
> > > >>>
> > > >>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <
> radumarias@gmail.com>
> > > wrote:
> > > >>>> These 2 caching are indeed very good for performance in real
> > > environments.
> > > >>>> But I'm trying to run some internal benchmarks on trafodion and
> > > collect the
> > > >>>> queries duration, in this case the cache generates inconsistency
> for
> > > my
> > > >>>> tests. I can drop the connection and re-run the query on a new
> > > connection
> > > >>>> for this benchmarks but can trafodion compiler cache be disabled?
> > And
> > > can
> > > >>>> HBase RegionServer cache be also disabled?
> > > >>>>
> > > >>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <
> hans.zeller@esgyn.com
> > >
> > > wrote:
> > > >>>>
> > > >>>> > You mentioned also that sometimes you get a slower response time
> > > when
> > > >>>> > reconnecting, Radu. As far as I understand (only from what
> others
> > > told me),
> > > >>>> > there could be two reasons. One is that your new connection
> might
> > > be to a
> > > >>>> > different mxosrvr process, which does not have the cache
> > established
> > > >>>> > earlier. The other one is that there is a change in the user id.
> > If
> > > an
> > > >>>> > mxosrvr changes user ids, it invalidates its cache, since some
> of
> > > the cache
> > > >>>> > content (e.g. privilege checks) depends on the user.
> Reconnecting
> > > to the
> > > >>>> > same mxosrvr, using the same user id, with no other users
> > > in-between,
> > > >>>> > should preserve the cache, as far as I know.
> > > >>>> >
> > > >>>> > You can use the DCS web gui to see which mxosrvrs you are
> > > connecting to. I
> > > >>>> > think the default port is 40010.
> > > >>>> >
> > > >>>> > Hans
> > > >>>> >
> > > >>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <
> > qifan.chen@esgyn.com>
> > > wrote:
> > > >>>> >
> > > >>>> >> Another point is that if you can take the advantage of query
> > cache
> > > by
> > > >>>> >> preparing the query once and executing it multiple times,
> further
> > > reducing
> > > >>>> >> the compilation time per SQL invocation.
> > > >>>> >>
> > > >>>> >>
> > > >>>> >>
> > > >>>> >
> > > >>>> >
> > > >>>>
> > > >>>>
> > > >>>> --
> > > >>>> And in the end, it's not the years in your life that count. It's
> the
> > > life
> > > >>>> in your years.
> > > >>>
> > > >>>
> > > >>>
> > > >>> --
> > > >>> Regards, --Qifan
> > > >>
> > >
> > >
> >
>
>
>
> --
> Thanks,
>
> Amanda Moran
>
Re: Does trafodion caches the results for jdbc queries?
Posted by Amanda Moran <am...@esgyn.com>.
Hi thre Radu-
We are currently <as of today> only able to use the installer to install
Trafodion on RedHat 6 and on Cloudera or Hortonworks.
Thanks.
Amanda
On Wed, Jul 29, 2015 at 4:47 PM, Radu Marias <ra...@gmail.com> wrote:
> Yes, I agree it's not a good benchmark environment. Initially I got
> problems installing, starting it and running any query, now I managed to
> run some test queries in sandbox with no crash and decent performance at
> least.
> The next step is trying to install it on a cluster environment with several
> nodes. But we have a small ubuntu cluster on which I manually installed
> hdfs and hbase.
> Does the installer work outside of ambari hortonworks environment? Or I
> assume I can manually install trafodion to my cluster adding dependencies
> and configs as the installer does.
>
> On Thu, Jul 30, 2015, 02:33 Rohit Jain <ro...@esgyn.com> wrote:
>
> > If you are using a sandbox to do this testing, then that is not an
> > environment conducive to performance testing. It will probably not give
> > you reliable and consistent results. A sandbox is limited in memory and
> is
> > subject to limitations of the environment used to build it. The most
> > important point is that it is a single node VM and Trafodion is a
> scale-out
> > architecture. It is best to test its performance on a multi-node cluster.
> > I realize that may be hard to do and does involve a full install. The
> > intent of the sandbox is for you to be able to kick tires and get a feel
> > for Trafodion. It is really not meant for extensive performance testing.
> >
> > There are smarter folks on this mailing list and I will let them comment
> > on that.
> >
> > Rohit
> >
> >
> >
> >
> >
> > On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com> wrote:
> >
> > >Hi Radu,
> > >
> > >In addition, we can help verify the query plan for you to assure the
> > queries can perform reasonably well in your environment, if it works for
> > you.
> > >
> > >To produce a query plan for query <q>, do the following.
> > >
> > >log plan.log clear;
> > >
> > >prepare zzz from <q>:
> > >explain zzz;
> > >explain options 'f' zzz;
> > >
> > >--repeat the above three steps for each query
> > >
> > >exit;
> > >
> > > You can prepare multiple queries in the same sql session and generate
> > one plan report in the log file plan.log.
> > >
> > >Thanks
> > >
> > >-Qifan
> > >
> > >Sent from my iPhone
> > >
> > >> On Jul 29, 2015, at 4:20 PM, Hans Zeller <ha...@esgyn.com>
> wrote:
> > >>
> > >> Hi Radu,
> > >>
> > >> My suggestion would be to benchmark the system with warmed-up caches,
> > since this will give more meaningful and repeatable numbers. It probably
> > depends on the type of benchmark, but in most cases the benchmark itself
> > will contain multiple statements, with caching going on between them,
> > making it difficult to clean the cache every time.
> > >>
> > >> So, I would suggest the following, am not sure how well that would
> work
> > for you:
> > >> Start from a well-defined point, with empty cache, e.g. after
> > re-starting HBase and Trafodion
> > >> Run the benchmark. This will be a bit slower initially, as the caches
> > get filled.
> > >> If the benchmark is very short, run the benchmark again, this time
> with
> > warmed-up cache. Alternatively, if the benchmark itself repeats queries
> > many times, show a graph of performance over time to show the warm-up
> time.
> > >> Thanks,
> > >>
> > >> Hans
> > >>
> > >>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <qi...@esgyn.com>
> > wrote:
> > >>> yes, there exists a control switch to turn off query cache in
> compiler.
> > >>>
> > >>> Such switch is called query_cache and can be used in a control query
> > default statement (CQD in short) to control how much memory that the
> query
> > cache can use. When we set it to a value of 0, the cache is disabled.
> To
> > set the CQD, try one of the following two approaches as follows.
> > >>>
> > >>> 1. from java,
> > >>>
> > >>> Statement stmt = conn_.createStatement();
> > >>> stmt.executeQuery("control query default query_cache '0'");
> > >>>
> > >>> <your query>
> > >>>
> > >>> 2. from sqlci
> > >>> cqd query_cache '0';
> > >>> prepare xx from <your_query>;
> > >>>
> > >>>
> > >>>
> > >>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <ra...@gmail.com>
> > wrote:
> > >>>> These 2 caching are indeed very good for performance in real
> > environments.
> > >>>> But I'm trying to run some internal benchmarks on trafodion and
> > collect the
> > >>>> queries duration, in this case the cache generates inconsistency for
> > my
> > >>>> tests. I can drop the connection and re-run the query on a new
> > connection
> > >>>> for this benchmarks but can trafodion compiler cache be disabled?
> And
> > can
> > >>>> HBase RegionServer cache be also disabled?
> > >>>>
> > >>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <hans.zeller@esgyn.com
> >
> > wrote:
> > >>>>
> > >>>> > You mentioned also that sometimes you get a slower response time
> > when
> > >>>> > reconnecting, Radu. As far as I understand (only from what others
> > told me),
> > >>>> > there could be two reasons. One is that your new connection might
> > be to a
> > >>>> > different mxosrvr process, which does not have the cache
> established
> > >>>> > earlier. The other one is that there is a change in the user id.
> If
> > an
> > >>>> > mxosrvr changes user ids, it invalidates its cache, since some of
> > the cache
> > >>>> > content (e.g. privilege checks) depends on the user. Reconnecting
> > to the
> > >>>> > same mxosrvr, using the same user id, with no other users
> > in-between,
> > >>>> > should preserve the cache, as far as I know.
> > >>>> >
> > >>>> > You can use the DCS web gui to see which mxosrvrs you are
> > connecting to. I
> > >>>> > think the default port is 40010.
> > >>>> >
> > >>>> > Hans
> > >>>> >
> > >>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <
> qifan.chen@esgyn.com>
> > wrote:
> > >>>> >
> > >>>> >> Another point is that if you can take the advantage of query
> cache
> > by
> > >>>> >> preparing the query once and executing it multiple times, further
> > reducing
> > >>>> >> the compilation time per SQL invocation.
> > >>>> >>
> > >>>> >>
> > >>>> >>
> > >>>> >
> > >>>> >
> > >>>>
> > >>>>
> > >>>> --
> > >>>> And in the end, it's not the years in your life that count. It's the
> > life
> > >>>> in your years.
> > >>>
> > >>>
> > >>>
> > >>> --
> > >>> Regards, --Qifan
> > >>
> >
> >
>
--
Thanks,
Amanda Moran
Re: Does trafodion caches the results for jdbc queries?
Posted by Radu Marias <ra...@gmail.com>.
Yes, I agree it's not a good benchmark environment. Initially I got
problems installing, starting it and running any query, now I managed to
run some test queries in sandbox with no crash and decent performance at
least.
The next step is trying to install it on a cluster environment with several
nodes. But we have a small ubuntu cluster on which I manually installed
hdfs and hbase.
Does the installer work outside of ambari hortonworks environment? Or I
assume I can manually install trafodion to my cluster adding dependencies
and configs as the installer does.
On Thu, Jul 30, 2015, 02:33 Rohit Jain <ro...@esgyn.com> wrote:
> If you are using a sandbox to do this testing, then that is not an
> environment conducive to performance testing. It will probably not give
> you reliable and consistent results. A sandbox is limited in memory and is
> subject to limitations of the environment used to build it. The most
> important point is that it is a single node VM and Trafodion is a scale-out
> architecture. It is best to test its performance on a multi-node cluster.
> I realize that may be hard to do and does involve a full install. The
> intent of the sandbox is for you to be able to kick tires and get a feel
> for Trafodion. It is really not meant for extensive performance testing.
>
> There are smarter folks on this mailing list and I will let them comment
> on that.
>
> Rohit
>
>
>
>
>
> On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com> wrote:
>
> >Hi Radu,
> >
> >In addition, we can help verify the query plan for you to assure the
> queries can perform reasonably well in your environment, if it works for
> you.
> >
> >To produce a query plan for query <q>, do the following.
> >
> >log plan.log clear;
> >
> >prepare zzz from <q>:
> >explain zzz;
> >explain options 'f' zzz;
> >
> >--repeat the above three steps for each query
> >
> >exit;
> >
> > You can prepare multiple queries in the same sql session and generate
> one plan report in the log file plan.log.
> >
> >Thanks
> >
> >-Qifan
> >
> >Sent from my iPhone
> >
> >> On Jul 29, 2015, at 4:20 PM, Hans Zeller <ha...@esgyn.com> wrote:
> >>
> >> Hi Radu,
> >>
> >> My suggestion would be to benchmark the system with warmed-up caches,
> since this will give more meaningful and repeatable numbers. It probably
> depends on the type of benchmark, but in most cases the benchmark itself
> will contain multiple statements, with caching going on between them,
> making it difficult to clean the cache every time.
> >>
> >> So, I would suggest the following, am not sure how well that would work
> for you:
> >> Start from a well-defined point, with empty cache, e.g. after
> re-starting HBase and Trafodion
> >> Run the benchmark. This will be a bit slower initially, as the caches
> get filled.
> >> If the benchmark is very short, run the benchmark again, this time with
> warmed-up cache. Alternatively, if the benchmark itself repeats queries
> many times, show a graph of performance over time to show the warm-up time.
> >> Thanks,
> >>
> >> Hans
> >>
> >>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <qi...@esgyn.com>
> wrote:
> >>> yes, there exists a control switch to turn off query cache in compiler.
> >>>
> >>> Such switch is called query_cache and can be used in a control query
> default statement (CQD in short) to control how much memory that the query
> cache can use. When we set it to a value of 0, the cache is disabled. To
> set the CQD, try one of the following two approaches as follows.
> >>>
> >>> 1. from java,
> >>>
> >>> Statement stmt = conn_.createStatement();
> >>> stmt.executeQuery("control query default query_cache '0'");
> >>>
> >>> <your query>
> >>>
> >>> 2. from sqlci
> >>> cqd query_cache '0';
> >>> prepare xx from <your_query>;
> >>>
> >>>
> >>>
> >>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <ra...@gmail.com>
> wrote:
> >>>> These 2 caching are indeed very good for performance in real
> environments.
> >>>> But I'm trying to run some internal benchmarks on trafodion and
> collect the
> >>>> queries duration, in this case the cache generates inconsistency for
> my
> >>>> tests. I can drop the connection and re-run the query on a new
> connection
> >>>> for this benchmarks but can trafodion compiler cache be disabled? And
> can
> >>>> HBase RegionServer cache be also disabled?
> >>>>
> >>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <ha...@esgyn.com>
> wrote:
> >>>>
> >>>> > You mentioned also that sometimes you get a slower response time
> when
> >>>> > reconnecting, Radu. As far as I understand (only from what others
> told me),
> >>>> > there could be two reasons. One is that your new connection might
> be to a
> >>>> > different mxosrvr process, which does not have the cache established
> >>>> > earlier. The other one is that there is a change in the user id. If
> an
> >>>> > mxosrvr changes user ids, it invalidates its cache, since some of
> the cache
> >>>> > content (e.g. privilege checks) depends on the user. Reconnecting
> to the
> >>>> > same mxosrvr, using the same user id, with no other users
> in-between,
> >>>> > should preserve the cache, as far as I know.
> >>>> >
> >>>> > You can use the DCS web gui to see which mxosrvrs you are
> connecting to. I
> >>>> > think the default port is 40010.
> >>>> >
> >>>> > Hans
> >>>> >
> >>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <qi...@esgyn.com>
> wrote:
> >>>> >
> >>>> >> Another point is that if you can take the advantage of query cache
> by
> >>>> >> preparing the query once and executing it multiple times, further
> reducing
> >>>> >> the compilation time per SQL invocation.
> >>>> >>
> >>>> >>
> >>>> >>
> >>>> >
> >>>> >
> >>>>
> >>>>
> >>>> --
> >>>> And in the end, it's not the years in your life that count. It's the
> life
> >>>> in your years.
> >>>
> >>>
> >>>
> >>> --
> >>> Regards, --Qifan
> >>
>
>
Re: Does trafodion caches the results for jdbc queries?
Posted by Rohit Jain <ro...@esgyn.com>.
If you are using a sandbox to do this testing, then that is not an environment conducive to performance testing. It will probably not give you reliable and consistent results. A sandbox is limited in memory and is subject to limitations of the environment used to build it. The most important point is that it is a single node VM and Trafodion is a scale-out architecture. It is best to test its performance on a multi-node cluster. I realize that may be hard to do and does involve a full install. The intent of the sandbox is for you to be able to kick tires and get a feel for Trafodion. It is really not meant for extensive performance testing.
There are smarter folks on this mailing list and I will let them comment on that.
Rohit
On 7/30/15, 7:07 AM, "Qifan Chen" <qi...@esgyn.com> wrote:
>Hi Radu,
>
>In addition, we can help verify the query plan for you to assure the queries can perform reasonably well in your environment, if it works for you.
>
>To produce a query plan for query <q>, do the following.
>
>log plan.log clear;
>
>prepare zzz from <q>:
>explain zzz;
>explain options 'f' zzz;
>
>--repeat the above three steps for each query
>
>exit;
>
> You can prepare multiple queries in the same sql session and generate one plan report in the log file plan.log.
>
>Thanks
>
>-Qifan
>
>Sent from my iPhone
>
>> On Jul 29, 2015, at 4:20 PM, Hans Zeller <ha...@esgyn.com> wrote:
>>
>> Hi Radu,
>>
>> My suggestion would be to benchmark the system with warmed-up caches, since this will give more meaningful and repeatable numbers. It probably depends on the type of benchmark, but in most cases the benchmark itself will contain multiple statements, with caching going on between them, making it difficult to clean the cache every time.
>>
>> So, I would suggest the following, am not sure how well that would work for you:
>> Start from a well-defined point, with empty cache, e.g. after re-starting HBase and Trafodion
>> Run the benchmark. This will be a bit slower initially, as the caches get filled.
>> If the benchmark is very short, run the benchmark again, this time with warmed-up cache. Alternatively, if the benchmark itself repeats queries many times, show a graph of performance over time to show the warm-up time.
>> Thanks,
>>
>> Hans
>>
>>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <qi...@esgyn.com> wrote:
>>> yes, there exists a control switch to turn off query cache in compiler.
>>>
>>> Such switch is called query_cache and can be used in a control query default statement (CQD in short) to control how much memory that the query cache can use. When we set it to a value of 0, the cache is disabled. To set the CQD, try one of the following two approaches as follows.
>>>
>>> 1. from java,
>>>
>>> Statement stmt = conn_.createStatement();
>>> stmt.executeQuery("control query default query_cache '0'");
>>>
>>> <your query>
>>>
>>> 2. from sqlci
>>> cqd query_cache '0';
>>> prepare xx from <your_query>;
>>>
>>>
>>>
>>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <ra...@gmail.com> wrote:
>>>> These 2 caching are indeed very good for performance in real environments.
>>>> But I'm trying to run some internal benchmarks on trafodion and collect the
>>>> queries duration, in this case the cache generates inconsistency for my
>>>> tests. I can drop the connection and re-run the query on a new connection
>>>> for this benchmarks but can trafodion compiler cache be disabled? And can
>>>> HBase RegionServer cache be also disabled?
>>>>
>>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <ha...@esgyn.com> wrote:
>>>>
>>>> > You mentioned also that sometimes you get a slower response time when
>>>> > reconnecting, Radu. As far as I understand (only from what others told me),
>>>> > there could be two reasons. One is that your new connection might be to a
>>>> > different mxosrvr process, which does not have the cache established
>>>> > earlier. The other one is that there is a change in the user id. If an
>>>> > mxosrvr changes user ids, it invalidates its cache, since some of the cache
>>>> > content (e.g. privilege checks) depends on the user. Reconnecting to the
>>>> > same mxosrvr, using the same user id, with no other users in-between,
>>>> > should preserve the cache, as far as I know.
>>>> >
>>>> > You can use the DCS web gui to see which mxosrvrs you are connecting to. I
>>>> > think the default port is 40010.
>>>> >
>>>> > Hans
>>>> >
>>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <qi...@esgyn.com> wrote:
>>>> >
>>>> >> Another point is that if you can take the advantage of query cache by
>>>> >> preparing the query once and executing it multiple times, further reducing
>>>> >> the compilation time per SQL invocation.
>>>> >>
>>>> >>
>>>> >>
>>>> >
>>>> >
>>>>
>>>>
>>>> --
>>>> And in the end, it's not the years in your life that count. It's the life
>>>> in your years.
>>>
>>>
>>>
>>> --
>>> Regards, --Qifan
>>
Re: Does trafodion caches the results for jdbc queries?
Posted by Qifan Chen <qi...@esgyn.com>.
Hi Radu,
In addition, we can help verify the query plan for you to assure the queries can perform reasonably well in your environment, if it works for you.
To produce a query plan for query <q>, do the following.
log plan.log clear;
prepare zzz from <q>:
explain zzz;
explain options 'f' zzz;
--repeat the above three steps for each query
exit;
You can prepare multiple queries in the same sql session and generate one plan report in the log file plan.log.
Thanks
-Qifan
Sent from my iPhone
> On Jul 29, 2015, at 4:20 PM, Hans Zeller <ha...@esgyn.com> wrote:
>
> Hi Radu,
>
> My suggestion would be to benchmark the system with warmed-up caches, since this will give more meaningful and repeatable numbers. It probably depends on the type of benchmark, but in most cases the benchmark itself will contain multiple statements, with caching going on between them, making it difficult to clean the cache every time.
>
> So, I would suggest the following, am not sure how well that would work for you:
> Start from a well-defined point, with empty cache, e.g. after re-starting HBase and Trafodion
> Run the benchmark. This will be a bit slower initially, as the caches get filled.
> If the benchmark is very short, run the benchmark again, this time with warmed-up cache. Alternatively, if the benchmark itself repeats queries many times, show a graph of performance over time to show the warm-up time.
> Thanks,
>
> Hans
>
>> On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <qi...@esgyn.com> wrote:
>> yes, there exists a control switch to turn off query cache in compiler.
>>
>> Such switch is called query_cache and can be used in a control query default statement (CQD in short) to control how much memory that the query cache can use. When we set it to a value of 0, the cache is disabled. To set the CQD, try one of the following two approaches as follows.
>>
>> 1. from java,
>>
>> Statement stmt = conn_.createStatement();
>> stmt.executeQuery("control query default query_cache '0'");
>>
>> <your query>
>>
>> 2. from sqlci
>> cqd query_cache '0';
>> prepare xx from <your_query>;
>>
>>
>>
>>> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <ra...@gmail.com> wrote:
>>> These 2 caching are indeed very good for performance in real environments.
>>> But I'm trying to run some internal benchmarks on trafodion and collect the
>>> queries duration, in this case the cache generates inconsistency for my
>>> tests. I can drop the connection and re-run the query on a new connection
>>> for this benchmarks but can trafodion compiler cache be disabled? And can
>>> HBase RegionServer cache be also disabled?
>>>
>>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <ha...@esgyn.com> wrote:
>>>
>>> > You mentioned also that sometimes you get a slower response time when
>>> > reconnecting, Radu. As far as I understand (only from what others told me),
>>> > there could be two reasons. One is that your new connection might be to a
>>> > different mxosrvr process, which does not have the cache established
>>> > earlier. The other one is that there is a change in the user id. If an
>>> > mxosrvr changes user ids, it invalidates its cache, since some of the cache
>>> > content (e.g. privilege checks) depends on the user. Reconnecting to the
>>> > same mxosrvr, using the same user id, with no other users in-between,
>>> > should preserve the cache, as far as I know.
>>> >
>>> > You can use the DCS web gui to see which mxosrvrs you are connecting to. I
>>> > think the default port is 40010.
>>> >
>>> > Hans
>>> >
>>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <qi...@esgyn.com> wrote:
>>> >
>>> >> Another point is that if you can take the advantage of query cache by
>>> >> preparing the query once and executing it multiple times, further reducing
>>> >> the compilation time per SQL invocation.
>>> >>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>> --
>>> And in the end, it's not the years in your life that count. It's the life
>>> in your years.
>>
>>
>>
>> --
>> Regards, --Qifan
>
Re: Does trafodion caches the results for jdbc queries?
Posted by Hans Zeller <ha...@esgyn.com>.
Hi Radu,
My suggestion would be to benchmark the system with warmed-up caches, since
this will give more meaningful and repeatable numbers. It probably depends
on the type of benchmark, but in most cases the benchmark itself will
contain multiple statements, with caching going on between them, making it
difficult to clean the cache every time.
So, I would suggest the following, am not sure how well that would work for
you:
- Start from a well-defined point, with empty cache, e.g. after
re-starting HBase and Trafodion
- Run the benchmark. This will be a bit slower initially, as the caches
get filled.
- If the benchmark is very short, run the benchmark again, this time
with warmed-up cache. Alternatively, if the benchmark itself repeats
queries many times, show a graph of performance over time to show the
warm-up time.
Thanks,
Hans
On Wed, Jul 29, 2015 at 12:43 PM, Qifan Chen <qi...@esgyn.com> wrote:
> yes, there exists a control switch to turn off query cache in compiler.
>
> Such switch is called query_cache and can be used in a control query
> default statement (CQD in short) to control how much memory that the query
> cache can use. When we set it to a value of 0, the cache is disabled. To
> set the CQD, try one of the following two approaches as follows.
>
> 1. from java,
>
> Statement stmt = conn_.createStatement();
>
> stmt.executeQuery("control query default query_cache '0'");
>
>
> <your query>
>
>
> 2. from sqlci
>
> cqd query_cache '0';
>
> prepare xx from <your_query>;
>
>
>
>
> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <ra...@gmail.com> wrote:
>
>> These 2 caching are indeed very good for performance in real environments.
>> But I'm trying to run some internal benchmarks on trafodion and collect
>> the
>> queries duration, in this case the cache generates inconsistency for my
>> tests. I can drop the connection and re-run the query on a new connection
>> for this benchmarks but can trafodion compiler cache be disabled? And can
>> HBase RegionServer cache be also disabled?
>>
>> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <ha...@esgyn.com>
>> wrote:
>>
>> > You mentioned also that sometimes you get a slower response time when
>> > reconnecting, Radu. As far as I understand (only from what others told
>> me),
>> > there could be two reasons. One is that your new connection might be to
>> a
>> > different mxosrvr process, which does not have the cache established
>> > earlier. The other one is that there is a change in the user id. If an
>> > mxosrvr changes user ids, it invalidates its cache, since some of the
>> cache
>> > content (e.g. privilege checks) depends on the user. Reconnecting to the
>> > same mxosrvr, using the same user id, with no other users in-between,
>> > should preserve the cache, as far as I know.
>> >
>> > You can use the DCS web gui to see which mxosrvrs you are connecting
>> to. I
>> > think the default port is 40010.
>> >
>> > Hans
>> >
>> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <qi...@esgyn.com>
>> wrote:
>> >
>> >> Another point is that if you can take the advantage of query cache by
>> >> preparing the query once and executing it multiple times, further
>> reducing
>> >> the compilation time per SQL invocation.
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>> --
>> And in the end, it's not the years in your life that count. It's the life
>> in your years.
>>
>
>
>
> --
> Regards, --Qifan
>
>
Re: Does trafodion caches the results for jdbc queries?
Posted by Rohit Jain <ro...@esgyn.com>.
I am not sure that a sandbox is really good to assess performance and
whether it will give you reliable consistent performance. The sandbox is
limited to the environment it was built on and there are limitations on the
amount of memory available to it. The sandbox was built only to kick tires
with Trafodion and to get a sense for how Trafodion works. If you are
assessing performance you need to use a cluster. Trafodion is a scale out
solution and performs a lot better in a multi node environment. The sandbox
is a single node implementation and is not the ideal platform for
performance testing. At least, that is my understanding. There are smarter
people on this DL who can validate that.
On Thursday, July 30, 2015, Qifan Chen <qi...@esgyn.com> wrote:
> yes, there exists a control switch to turn off query cache in compiler.
>
> Such switch is called query_cache and can be used in a control query
> default statement (CQD in short) to control how much memory that the query
> cache can use. When we set it to a value of 0, the cache is disabled. To
> set the CQD, try one of the following two approaches as follows.
>
> 1. from java,
>
> Statement stmt = conn_.createStatement();
>
> stmt.executeQuery("control query default query_cache '0'");
>
>
> <your query>
>
>
> 2. from sqlci
>
> cqd query_cache '0';
>
> prepare xx from <your_query>;
>
>
>
>
> On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <radumarias@gmail.com
> <javascript:;>> wrote:
>
> > These 2 caching are indeed very good for performance in real
> environments.
> > But I'm trying to run some internal benchmarks on trafodion and collect
> the
> > queries duration, in this case the cache generates inconsistency for my
> > tests. I can drop the connection and re-run the query on a new connection
> > for this benchmarks but can trafodion compiler cache be disabled? And can
> > HBase RegionServer cache be also disabled?
> >
> > On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <hans.zeller@esgyn.com
> <javascript:;>>
> > wrote:
> >
> > > You mentioned also that sometimes you get a slower response time when
> > > reconnecting, Radu. As far as I understand (only from what others told
> > me),
> > > there could be two reasons. One is that your new connection might be
> to a
> > > different mxosrvr process, which does not have the cache established
> > > earlier. The other one is that there is a change in the user id. If an
> > > mxosrvr changes user ids, it invalidates its cache, since some of the
> > cache
> > > content (e.g. privilege checks) depends on the user. Reconnecting to
> the
> > > same mxosrvr, using the same user id, with no other users in-between,
> > > should preserve the cache, as far as I know.
> > >
> > > You can use the DCS web gui to see which mxosrvrs you are connecting
> to.
> > I
> > > think the default port is 40010.
> > >
> > > Hans
> > >
> > > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <qifan.chen@esgyn.com
> <javascript:;>>
> > wrote:
> > >
> > >> Another point is that if you can take the advantage of query cache by
> > >> preparing the query once and executing it multiple times, further
> > reducing
> > >> the compilation time per SQL invocation.
> > >>
> > >>
> > >>
> > >
> > >
> >
> >
> > --
> > And in the end, it's not the years in your life that count. It's the life
> > in your years.
> >
>
>
>
> --
> Regards, --Qifan
>
--
Rohit Jain
CTO
Esgyn
Re: Does trafodion caches the results for jdbc queries?
Posted by Qifan Chen <qi...@esgyn.com>.
yes, there exists a control switch to turn off query cache in compiler.
Such switch is called query_cache and can be used in a control query
default statement (CQD in short) to control how much memory that the query
cache can use. When we set it to a value of 0, the cache is disabled. To
set the CQD, try one of the following two approaches as follows.
1. from java,
Statement stmt = conn_.createStatement();
stmt.executeQuery("control query default query_cache '0'");
<your query>
2. from sqlci
cqd query_cache '0';
prepare xx from <your_query>;
On Wed, Jul 29, 2015 at 2:13 PM, Radu Marias <ra...@gmail.com> wrote:
> These 2 caching are indeed very good for performance in real environments.
> But I'm trying to run some internal benchmarks on trafodion and collect the
> queries duration, in this case the cache generates inconsistency for my
> tests. I can drop the connection and re-run the query on a new connection
> for this benchmarks but can trafodion compiler cache be disabled? And can
> HBase RegionServer cache be also disabled?
>
> On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <ha...@esgyn.com>
> wrote:
>
> > You mentioned also that sometimes you get a slower response time when
> > reconnecting, Radu. As far as I understand (only from what others told
> me),
> > there could be two reasons. One is that your new connection might be to a
> > different mxosrvr process, which does not have the cache established
> > earlier. The other one is that there is a change in the user id. If an
> > mxosrvr changes user ids, it invalidates its cache, since some of the
> cache
> > content (e.g. privilege checks) depends on the user. Reconnecting to the
> > same mxosrvr, using the same user id, with no other users in-between,
> > should preserve the cache, as far as I know.
> >
> > You can use the DCS web gui to see which mxosrvrs you are connecting to.
> I
> > think the default port is 40010.
> >
> > Hans
> >
> > On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <qi...@esgyn.com>
> wrote:
> >
> >> Another point is that if you can take the advantage of query cache by
> >> preparing the query once and executing it multiple times, further
> reducing
> >> the compilation time per SQL invocation.
> >>
> >>
> >>
> >
> >
>
>
> --
> And in the end, it's not the years in your life that count. It's the life
> in your years.
>
--
Regards, --Qifan
Re: Does trafodion caches the results for jdbc queries?
Posted by Radu Marias <ra...@gmail.com>.
These 2 caching are indeed very good for performance in real environments.
But I'm trying to run some internal benchmarks on trafodion and collect the
queries duration, in this case the cache generates inconsistency for my
tests. I can drop the connection and re-run the query on a new connection
for this benchmarks but can trafodion compiler cache be disabled? And can
HBase RegionServer cache be also disabled?
On Wed, Jul 29, 2015 at 9:42 PM, Hans Zeller <ha...@esgyn.com> wrote:
> You mentioned also that sometimes you get a slower response time when
> reconnecting, Radu. As far as I understand (only from what others told me),
> there could be two reasons. One is that your new connection might be to a
> different mxosrvr process, which does not have the cache established
> earlier. The other one is that there is a change in the user id. If an
> mxosrvr changes user ids, it invalidates its cache, since some of the cache
> content (e.g. privilege checks) depends on the user. Reconnecting to the
> same mxosrvr, using the same user id, with no other users in-between,
> should preserve the cache, as far as I know.
>
> You can use the DCS web gui to see which mxosrvrs you are connecting to. I
> think the default port is 40010.
>
> Hans
>
> On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <qi...@esgyn.com> wrote:
>
>> Another point is that if you can take the advantage of query cache by
>> preparing the query once and executing it multiple times, further reducing
>> the compilation time per SQL invocation.
>>
>>
>>
>
>
--
And in the end, it's not the years in your life that count. It's the life
in your years.
Re: Does trafodion caches the results for jdbc queries?
Posted by Hans Zeller <ha...@esgyn.com>.
You mentioned also that sometimes you get a slower response time when
reconnecting, Radu. As far as I understand (only from what others told me),
there could be two reasons. One is that your new connection might be to a
different mxosrvr process, which does not have the cache established
earlier. The other one is that there is a change in the user id. If an
mxosrvr changes user ids, it invalidates its cache, since some of the cache
content (e.g. privilege checks) depends on the user. Reconnecting to the
same mxosrvr, using the same user id, with no other users in-between,
should preserve the cache, as far as I know.
You can use the DCS web gui to see which mxosrvrs you are connecting to. I
think the default port is 40010.
Hans
On Wed, Jul 29, 2015 at 11:34 AM, Qifan Chen <qi...@esgyn.com> wrote:
> Another point is that if you can take the advantage of query cache by
> preparing the query once and executing it multiple times, further reducing
> the compilation time per SQL invocation.
>
>
>
Re: Does trafodion caches the results for jdbc queries?
Posted by Qifan Chen <qi...@esgyn.com>.
Another point is that if you can take the advantage of query cache by
preparing the query once and executing it multiple times, further reducing
the compilation time per SQL invocation.
Re: Does trafodion caches the results for jdbc queries?
Posted by Dave Birdsall <da...@esgyn.com>.
Hi,
I suspect this is normal behavior.
The Trafodion SQL compiler caches query plans and metadata. So if you
execute the same query multiple times in a session, most of the compilation
phases are bypassed after the first time.
Also, HBase itself caches data in its RegionServers. So, the first time a
query is executed the data will be loaded into the RegionServer cache. If
the same data is requested on subsequent queries, HBase will serve it from
its cache.
Dave
On Wed, Jul 29, 2015 at 10:32 AM, Radu Marias <ra...@gmail.com> wrote:
> I'm using daily build *trafodion-20150726_0830.tar.gz* in *sandbox 1.1*
> with several sql clients like SQuirreL, DbVisualizer and 0xDBE from
> Intellij.
>
> Noticed that in some cases when I execute a query for the first time it
> takes longer than the next subsequent executions. It's not the first query
> when also the connection is made, but a later one.
>
> For example I have this query executed on 3 tables with about 1.2 million
> rows each:
>
>
> *select p.id <http://p.id>, p.LAST_NAME, p.FIRST_NAME, a.ADDRESS_1,
> a.CITY,
> a.STATE_PROVINCE*
> *from sna_person_addresses pa*
> * join sna_persons p on (p.id <http://p.id> = pa.person_id)*
> * join sna_addresses a on (a.id <http://a.id> = pa.address_id)*
> *where pa.person_id=?;*
>
> first time it takes about 200ms and after that it's bellow 50ms, never goes
> higher than that.
>
> It doesn't happen all the time, that's why I just wanted to exclude the
> cache possibility in trafodion or jdbc client.
>