Re: [sqlite] Energy consumption of SQLite queries

[Roman Fleysher]

Dear Ali,

A couple of comments. Indeed lots of energy is transferred into heat, but not 
all. Therefore, using temperature (after calibrating specific heat coefficient 
of the device ) is not a good method. Some energy is radiated as visible and 
invisible light and hard to catch it all. Some as vibration. Some energy is 
used to flip the bits on the disk. So to speak internal energy. 

Thus, monitoring input power is the only way. However, since many jobs are 
running, the results will be indeed inconsistent. Some people, therefore, 
erroneously conclude that the question is not answerable. This is not true.

A properly crafted research proposal could get funding needed to accomplish 
this fine goal. I expect 1 million US dollars for 5 years should be close to 
sufficient. Make sure you measure how long SQLIte performs the task, record 
power consumption in that period. Then run machine for the same period without 
SQLIte. Difference in power consumption is what was due to  SQLite, controlling 
for the other processes. Obviously, caching and other things already mentioned, 
will affect the numbers. Thus, you need to properly randomize these trials, 
playing with their durations. You will have to perform many of these (therefore 
5 year long project) to average out all fluctuations. 

Given complexity of the project, you should consider getting initial funding to 
design it in the first place and obtain preliminary data (and necessary 
equipment) to justify and ensure future success. It appears, given your initial 
email, that such funding is well underway towards being secured. Be sure to 
control temperature and humanity in the room, because cooling fans also consume 
energy, which depends on their speed and viscosity of the air. The tidal forces 
(of the moon) will affect friction in bearings of all moving parts (fans, 
disks). Be sure to either co-vary for them or randomize experiments for 
different phases of the moon. DO NOT MOVE computer while experiment is running. 
Coriolis force will affect friction in all rotating parts as well.

In summary, this is a perfectly doable experiment, if carefully planned and 
executed. Radio astronomy easily reaches sensitivities of 10^{-9}. You can do 
it too!

At conclusion of the 5 year research period, SQLite will be much different from 
what it is today. So will kernels of operating systems, hardware etc. 
Therefore, at conclusion of the research, you will have answered how much power 
was consumed by SQLite 5 years ago. I am sure this will be very valuable piece 
of information then, after all the money and efforts are spent. Because of this 
short delay (5 years is short on the astronomical time scale) and because of 
the experience you gained by conclusion of the project, I am rather certain you 
will be able to obtain additional funding to continue and refine the answer to 
the newer version of SQLite available then. The future is in your hands!


Roman



From: sqlite-users [sqlite-users-boun...@mailinglists.sqlite.org] on behalf of 
Ali Dorri [alidorri...@gmail.com]
Sent: Tuesday, November 21, 2017 4:49 PM
To: Robert Oeffner
Cc: SQLite mailing list
Subject: Re: [sqlite] Energy consumption of SQLite queries

Dear All,

Thanks for your comments. That was really helpful.

Regards
Ali

On Tue, Nov 21, 2017 at 11:41 PM, Robert Oeffner <rob...@oeffner.net> wrote:

> This is an interesting topic more belonging to the realms of information
> theory and statistical physics.
>
> I am not an expert in this area but from what I recall from undergraduate
> physics the moment you create order in one corner of the universe entropy
> rises in another place of the universe. If you loosely speaking equate
> information gathering such as an SQL query as creating order then that must
> have a cost in terms of increasing the entropy (heat in this case)
> elsewhere. There is a lower bound on how little entropy is generated during
> this process which comes down to the efficiency of the process (hardware
> and software in your case).
>
> One could get philosophical here and question whether mankinds computer
> modeling of climate change in itself causes the excess heat leading to
> global warming.
>
>
> Regards,
>
> Robert
>
>
> --
> Robert Oeffner, Ph.D.
> Research Associate,
> The Read Group, Department of Haematology,
> Cambridge Institute for Medical Research
> University of Cambridge
> Cambridge Biomedical Campus
> Wellcome Trust/MRC Building
> Hills Road
> Cambridge CB2 0XY
> www.cimr.cam.ac.uk/investigators/read/index.html
>
>
>
> Date: Tue, 21 Nov 2017 09:54:25 +1100
>> From: Ali Dorri <alidorri...@gmail.com>
>> To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
>> Subject: [sqlite] Energy consumption of SQLite queries
>> Message-ID:
>> 

Re: [sqlite] Energy consumption of SQLite queries

[Ali Dorri]

Dear All,

Thanks for your comments. That was really helpful.

Regards
Ali

On Tue, Nov 21, 2017 at 11:41 PM, Robert Oeffner  wrote:

> This is an interesting topic more belonging to the realms of information
> theory and statistical physics.
>
> I am not an expert in this area but from what I recall from undergraduate
> physics the moment you create order in one corner of the universe entropy
> rises in another place of the universe. If you loosely speaking equate
> information gathering such as an SQL query as creating order then that must
> have a cost in terms of increasing the entropy (heat in this case)
> elsewhere. There is a lower bound on how little entropy is generated during
> this process which comes down to the efficiency of the process (hardware
> and software in your case).
>
> One could get philosophical here and question whether mankinds computer
> modeling of climate change in itself causes the excess heat leading to
> global warming.
>
>
> Regards,
>
> Robert
>
>
> --
> Robert Oeffner, Ph.D.
> Research Associate,
> The Read Group, Department of Haematology,
> Cambridge Institute for Medical Research
> University of Cambridge
> Cambridge Biomedical Campus
> Wellcome Trust/MRC Building
> Hills Road
> Cambridge CB2 0XY
> www.cimr.cam.ac.uk/investigators/read/index.html
>
>
>
> Date: Tue, 21 Nov 2017 09:54:25 +1100
>> From: Ali Dorri 
>> To: SQLite mailing list 
>> Subject: [sqlite] Energy consumption of SQLite queries
>> Message-ID:
>> 

Re: [sqlite] Energy consumption of SQLite queries

[Robert Oeffner]

This is an interesting topic more belonging to the realms of information 
theory and statistical physics.


I am not an expert in this area but from what I recall from 
undergraduate physics the moment you create order in one corner of the 
universe entropy rises in another place of the universe. If you loosely 
speaking equate information gathering such as an SQL query as creating 
order then that must have a cost in terms of increasing the entropy 
(heat in this case) elsewhere. There is a lower bound on how little 
entropy is generated during this process which comes down to the 
efficiency of the process (hardware and software in your case).


One could get philosophical here and question whether mankinds computer 
modeling of climate change in itself causes the excess heat leading to 
global warming.



Regards,

Robert


--
Robert Oeffner, Ph.D.
Research Associate,
The Read Group, Department of Haematology,
Cambridge Institute for Medical Research
University of Cambridge
Cambridge Biomedical Campus
Wellcome Trust/MRC Building
Hills Road
Cambridge CB2 0XY
www.cimr.cam.ac.uk/investigators/read/index.html




Date: Tue, 21 Nov 2017 09:54:25 +1100
From: Ali Dorri 
To: SQLite mailing list 
Subject: [sqlite] Energy consumption of SQLite queries
Message-ID:

Re: [sqlite] Energy consumption of SQLite queries

[Paul Sanderson]

A pretty much impossible task I would think.

The power usage of SQLite compared to the power usage of different hardware
components would be miniscule. But, there are so many other tasks running
on a system, many in the background, that isolating SQLite from the rest
would be next to impossible. Just look at process on a windows system via
the task manager or a linux system using top to get a very simplistic idea
of the different tasks that are using processor time - Sort by processor
usage and the list is always changing even when you are doing nothing. Add
in variable speed fans and processor throttling to manage temperature/power
consumption etc. and you have a mammoth task.

Good luck :)

Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 21 November 2017 at 00:36, Simon Slavin  wrote:

> On 20 Nov 2017, at 10:54pm, Ali Dorri  wrote:
>
> > I am doing a research on the energy consumed by a query in SQLite. I
> have a
> > program which fills a database with blocks of data. Then, it attempts to
> > remove some data from the database. I don't know how to measure the
> energy
> > consumed from my host, i.e., my laptop which has both the SQLite and the
> > program, from the time I generated the query till the query is finished
> and
> > control returns back to my program.
>
> This is a hardware question, not anything to do with a particular piece of
> software.
>
> If you have a desktop computer, get one of those gadgets that you plug
> into the power socket and monitors how much power is passed to things that
> plug into them:
>
> 
>
> On a laptop, since the power is taken from an internal battery, and mains
> power is used to recharge it inconsistently, monitoring power usage from
> the mains is pointless.  See if the firmware provides a display or an API
> function which shows how much is going out.
>
> Then set up side-by-side comparisons, one with your computer doing those
> things in SQLite and one without.  The differences between the two power
> consumptions is how much power SQLite is using.  Unless you have really
> detailed power measurement, the results will be small and probably
> meaningless.
>
> Since you mention doing side-by-side comparisons with other databases,
> your setup should probably be comparing the same computer doing things in
> different DBMSs.  Maybe set up some procedure for doing something 10,000
> times and see how much power is used in total.
>
> Worth noting that power consumption from SQLite will be terribly
> inconsistent, based on what data is cached, how many database pages need to
> be accessed, and the state of the journal files.  This pales into
> insignificance, however, with the inconsistency of most other DBMSs, which
> perform far more internal caching and indexing.  You will get very
> different results from the same setup depending on how long the DBMS server
> has been running, not just on how long the computer has been turned on.
>
> Simon.
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Energy consumption of SQLite queries

[Simon Slavin]

On 20 Nov 2017, at 10:54pm, Ali Dorri  wrote:

> I am doing a research on the energy consumed by a query in SQLite. I have a
> program which fills a database with blocks of data. Then, it attempts to
> remove some data from the database. I don't know how to measure the energy
> consumed from my host, i.e., my laptop which has both the SQLite and the
> program, from the time I generated the query till the query is finished and
> control returns back to my program.

This is a hardware question, not anything to do with a particular piece of 
software.

If you have a desktop computer, get one of those gadgets that you plug into the 
power socket and monitors how much power is passed to things that plug into 
them:



On a laptop, since the power is taken from an internal battery, and mains power 
is used to recharge it inconsistently, monitoring power usage from the mains is 
pointless.  See if the firmware provides a display or an API function which 
shows how much is going out.

Then set up side-by-side comparisons, one with your computer doing those things 
in SQLite and one without.  The differences between the two power consumptions 
is how much power SQLite is using.  Unless you have really detailed power 
measurement, the results will be small and probably meaningless.

Since you mention doing side-by-side comparisons with other databases, your 
setup should probably be comparing the same computer doing things in different 
DBMSs.  Maybe set up some procedure for doing something 10,000 times and see 
how much power is used in total.

Worth noting that power consumption from SQLite will be terribly inconsistent, 
based on what data is cached, how many database pages need to be accessed, and 
the state of the journal files.  This pales into insignificance, however, with 
the inconsistency of most other DBMSs, which perform far more internal caching 
and indexing.  You will get very different results from the same setup 
depending on how long the DBMS server has been running, not just on how long 
the computer has been turned on.

Simon.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Energy consumption of SQLite queries

[Keith Medcalf]

>Considering how total energy of computation is entirely liberated as heat

Human technology at its current primitive state converts ALL energy usage into 
heat.  

At scale you call this is called "global warming" and it is an inherent process 
-- at least until humans figure out how to convert energy back into matter.

---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Energy consumption of SQLite queries

[petern]

Energy measurement can be carried out with a recording electric power meter
provided the power otherwise consumed by the operating system and other
programs can be controlled for.   Total energy used by the computer during
one trial is computed by the integral of the recorded power reading over
the time of the experiment.  Do you have a time recording power meter to
connect between the computer and power source?  A sensitive Coulomb
counting power meter such as those used for characterizing batteries would
be ideal.  Often such meters will have a direct readout of the integrated
energy in Joules or Watt Hours.

Considering how total energy of computation is entirely liberated as heat
from the computer's electronics, another technique would be to put the
computer inside a well insulated and air tight cabinet for which you've
determined the specific heat of the system.  Specific heat might be
experimentally determined by measuring the temperature rise of the system
relative to the energy expended from a precise energy standard.   Such a
standard is merely a carefully measured amount of pure chemicals which
produce a well defined exothermic reaction that doesn't destroy the
apparatus.

Generally I would suggest finding a small DC powered single board computer
which runs only the SQLite program during the experiment.  That would
eliminate controlling for energy use of programs and irrelevant system
components like power supply, graphics card, and so on. You will also have
to design your experiment around the choice for secondary storage.  For
example, a solid state disk will have different energy characteristics
compared to a mechanical disk.  Separating the factors of energy for
storage versus energy from computation would be an important dimension for
an energy study of DBMS's.

Peter

On Mon, Nov 20, 2017 at 2:54 PM, Ali Dorri  wrote:

> Dear All,
>
> I am doing a research on the energy consumed by a query in SQLite. I have a
> program which fills a database with blocks of data. Then, it attempts to
> remove some data from the database. I don't know how to measure the energy
> consumed from my host, i.e., my laptop which has both the SQLite and the
> program, from the time I generated the query till the query is finished and
> control returns back to my program.
>
> Any help is highly appreciated.
>
> Regards
> Ali
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Energy consumption of SQLite queries

[Ali Dorri]

Dear Ryan,

Thanks for your response.
As you said the energy completely depends on the machine, but that is fine
for our experiment. We are not comparing SQLite with other databases. I am
a researcher and I've developed a system which uses a database. My aim is
to see how much time and energy my method incurred on the host node that is
running the program. I measured the time consumed by measuring the time
difference before and after running the query and I need to do the same
(somehow) for the energy consumption. I just need to give a rough
estimation. Of course, the exact values depend on many options, but I need
to show something.

Hope this helps.

Many thanks
Ali

On Tue, Nov 21, 2017 at 10:26 AM, R Smith  wrote:

> Dear All,
>
>> I am doing a research on the energy consumed by a query in SQLite. I have
>> a
>> program which fills a database with blocks of data. Then, it attempts to
>> remove some data from the database. I don't know how to measure the energy
>> consumed from my host, i.e., my laptop which has both the SQLite and the
>> program, from the time I generated the query till the query is finished
>> and
>> control returns back to my program.
>>
>> Any help is highly appreciated.
>>
>
> I'm sure you have a good reason to want to do this, but I think it's not a
> worthy pursuit.
>
> It's a bit like trying to measure the amount of Energy a road uses. The
> answer is simple: It depends very much on which vehicles drive on it. The
> road doesn't use energy, it's just a venue or method used by machines who
> themselves consume the energy based on how efficient THEY are. Sure a
> longer or more curvy road will require more energy than a shorter road, so
> you can get COMPARATIVE or scaled energy ratios for different length roads
> for similar vehicles perhaps, but in the end quantifying that "road A uses
> X energy" will be patently false.
>
> Similarly, SQLite's energy usage depends on the TDP and energy
> coefficients of the machine it is running on.
>
> If you want to know how SQLite stacks up against another database or
> storage mechanism (possibly in terms of who uses more battery power from
> the phone it runs on) then you can simply measure the cycles/FPO's of both
> test subjects and there's programs out there to do that with for every
> platform. (SQLite will likely beat anything else by a mile for equal data
> handling ops).
>
> This will give you a great ratio of energy usage against whatever else it
> is measured, but measuring quantified physical energy usage is merely an
> arbitration of the machine it is running on, it has no intrinsic value as a
> stand-alone figure.
>
>
> Tell us more of your specific goals, maybe someone here has a way...
>
> Cheers,
> Ryan
>
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Energy consumption of SQLite queries

[R Smith]

Dear All,

I am doing a research on the energy consumed by a query in SQLite. I have a
program which fills a database with blocks of data. Then, it attempts to
remove some data from the database. I don't know how to measure the energy
consumed from my host, i.e., my laptop which has both the SQLite and the
program, from the time I generated the query till the query is finished and
control returns back to my program.

Any help is highly appreciated.


I'm sure you have a good reason to want to do this, but I think it's not 
a worthy pursuit.


It's a bit like trying to measure the amount of Energy a road uses. The 
answer is simple: It depends very much on which vehicles drive on it. 
The road doesn't use energy, it's just a venue or method used by 
machines who themselves consume the energy based on how efficient THEY 
are. Sure a longer or more curvy road will require more energy than a 
shorter road, so you can get COMPARATIVE or scaled energy ratios for 
different length roads for similar vehicles perhaps, but in the end 
quantifying that "road A uses X energy" will be patently false.


Similarly, SQLite's energy usage depends on the TDP and energy 
coefficients of the machine it is running on.


If you want to know how SQLite stacks up against another database or 
storage mechanism (possibly in terms of who uses more battery power from 
the phone it runs on) then you can simply measure the cycles/FPO's of 
both test subjects and there's programs out there to do that with for 
every platform. (SQLite will likely beat anything else by a mile for 
equal data handling ops).


This will give you a great ratio of energy usage against whatever else 
it is measured, but measuring quantified physical energy usage is merely 
an arbitration of the machine it is running on, it has no intrinsic 
value as a stand-alone figure.



Tell us more of your specific goals, maybe someone here has a way...

Cheers,
Ryan

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users