Energy consumption of SQLite queries

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

Energy consumption of SQLite queries

Ali Dorri
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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Energy consumption of SQLite queries

petern
In reply to this post by Ali Dorri
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 <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Energy consumption of SQLite queries

Simon Slavin-3
In reply to this post by Ali Dorri
On 20 Nov 2017, at 10:54pm, Ali Dorri <[hidden email]> 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:

<https://www.amazon.co.uk/Plug-In-Power-and-Energy-Monitor/dp/B000Q7PJGW>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> wrote:

> On 20 Nov 2017, at 10:54pm, Ali Dorri <[hidden email]> 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:
>
> <https://www.amazon.co.uk/Plug-In-Power-and-Energy-Monitor/dp/B000Q7PJGW>
>
> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Energy consumption of SQLite queries

Robert Oeffner
In reply to this post by Ali Dorri
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 <[hidden email]>
> To: SQLite mailing list <[hidden email]>
> Subject: [sqlite] Energy consumption of SQLite queries
> Message-ID:
> <CAOB3cEGoh=[hidden email]>
> Content-Type: text/plain; charset="UTF-8"
>
> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 <[hidden email]> 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 <[hidden email]>
>> To: SQLite mailing list <[hidden email]>
>> Subject: [sqlite] Energy consumption of SQLite queries
>> Message-ID:
>>         <CAOB3cEGoh=[hidden email]
>> ail.com>
>> Content-Type: text/plain; charset="UTF-8"
>>
>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: 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 [[hidden email]] on behalf of Ali Dorri [[hidden email]]
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 <[hidden email]> 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 <[hidden email]>
>> To: SQLite mailing list <[hidden email]>
>> Subject: [sqlite] Energy consumption of SQLite queries
>> Message-ID:
>>         <CAOB3cEGoh=[hidden email]
>> ail.com>
>> Content-Type: text/plain; charset="UTF-8"
>>
>> 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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users