performance question

[tarscher@gmail.com]

Hi all,

I have 2 questions regarding performance:

1) I'm building a monitoring system that has to store lots of sensor
data that I 'll have to query from time to time. I have pressure and
temperature. Since we sample every 500 ms we will get lots of data
after some time. Will my performance increase by making 2 tables; one
with pressure and one with temperature? Thus when querying (eg
pressure) mysql will only need to look in the pressure table that
contains half the data opposed to when querying a table that contains
pressure and temperature (and is double the size)?

2) I have read that when querying the server in read mode opposed to
read/write mode you can get a performance increase. Can this be done
with php?

Many thanks in advance
Stijn

[Bill Karwin]

Re: performance question

tarscher@gmail. com wrote:[color=blue]
> Will my performance increase by making 2 tables; one
> with pressure and one with temperature?[/color]

I doubt it. Besides, I assume it would often be the case that you'd
want both figures, so you'd eliminate any potential gains anyway when
you join the two tables.

It sounds like you're going to be producing 2 records per second. Even
after a year of continuous measurement 24 hours per day, you'll have 63
million records. This may be reaching the upper bounds of what MySQL is
best suited for, but it can handle it. If you use indexes properly,
queries won't be too slow (certainly slower than after 1 day of
measurements, but that's to be expected).

There is a chapter in the MySQL docs about improving performance.

http://dev.mysql.com/doc/refman/5.0/en/optimization.html

There are also several articles on the MySQL web site in the "white
papers" section on achieving even greater scalability (larger databases,
faster performance) with other features of the product, like clustering
and partitioning. http://www.mysql.com/why-mysql/white-papers/

There are many companies using MySQL for high volume applications. See
http://www.mysql.com/why-mysql/case-studies/.
[color=blue]
> 2) I have read that when querying the server in read mode opposed to
> read/write mode you can get a performance increase. Can this be done
> with php?[/color]

I have not heard of a feature called "read mode". Can you cite a reference?

You may be thinking of the REPEATABLE READ transaction isolation mode.
This doesn't increase performance, but reduces the likelihood that two
concurrent queries will block one another.

Regards,
Bill K.

[Gordon Burditt]

Re: performance question

>1) I'm building a monitoring system that has to store lots of sensor[color=blue]
>data that I 'll have to query from time to time. I have pressure and
>temperature. Since we sample every 500 ms we will get lots of data
>after some time. Will my performance increase by making 2 tables; one
>with pressure and one with temperature? Thus when querying (eg
>pressure) mysql will only need to look in the pressure table that
>contains half the data opposed to when querying a table that contains
>pressure and temperature (and is double the size)?[/color]

Double the size? Surely you're storing something other than
temperature and pressure in the table, like date, time, which
temperature/pressure sensor, etc. Assuming you always take temperature
and pressure readings together and put them in the same table, the
other info wouldn't have to be duplicated.

If the temperature and pressure readings need DIFFERENT time stamps
(a single one for both isn't sufficiently accurate) you probably
want separate tables.

Do your queries often need pressure alone, or do they often want
temperature and pressure together?

At this rate of data generation, you should worry not only about
the QUERIES, but the data insertion as well. Using two tables means
maintaining two indexes of the data. More indexes => faster reading
data but slower inserting it.
[color=blue]
>2) I have read that when querying the server in read mode opposed to
>read/write mode you can get a performance increase. Can this be done
>with php?[/color]

What is "querying in read mode"?

It is often true that reading a single record is faster than inserting
a single record (due to locking contention and updating indexes) but
two such queries do not substitute for each other. You cannot usually
replace reading a record with inserting one.

Also remember that any program can run infinitely fast and with zero
storage if it doesn't have to produce the correct result.

Gordon L. Burditt

[Bill Karwin]

Re: performance question

Gordon Burditt wrote:[color=blue]
> Also remember that any program can run infinitely fast and with zero
> storage if it doesn't have to produce the correct result.[/color]

Heh! Someone on comp.graphics once asked for a command to give the
absolute best compression for images. I suggested "rm". >:-)

Regards,
Bill K.

[tarscher@gmail.com]

Re: performance question

Thanks for the answers. I must say though that I don't have line of
sight. The path is blocked by several buildings and trees. I guess this
makes it much harder?

regards
Stijn