Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- performance question
April 13, 2006, 8:08 am
rate this thread
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
Many thanks in advance
Re: performance question
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.
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
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.
Re: performance question
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.
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
- » Americanas.com SALDO de TVs Com at 80% de Desconto (29798)
- — Newest thread in » MySQL Database Forum
- » ssh on command line: force using a group size (prime size) of 1024 (and no...
- — The site's Newest Thread. Posted in » Secure Shell Forum