Tango database - TANGO Controls

# 9 years ago
claudio	Weird indeed. I look at ot our databases: the "oldest, installed in 2005, has two rows in the _history_id tables; the newer, installed in 2010 an dlaterhave 1 row in _history_id tables. Jan, would you post your modified Databseds sources? Regards

# 9 years ago
jan	Hi, To get the idea what I'm doing now I attach my version od Databaseds for Tango 8.1.2. Since I'm debugging it now it is full of printouts and dirty hacks which shouldn't be used in standard code. The main modifications are in DataBase::db_put_device_attribute_property2(…). Bests, Jan Attachments: 51Kb

# 9 years ago
claudio	jan Hi, To get the idea what I'm doing now I attach my version od Databaseds for Tango 8.1.2. Since I'm debugging it now it is full of printouts and dirty hacks which shouldn't be used in standard code. The main modifications are in DataBase::db_put_device_attribute_property2(…). Bests, Jan Thanks a lot. I will try it next week.

# 9 years ago
jan	I've just realized that if all attribute property transactions block each other by get_id(). So updating history_id should be put into separate transaction or history_id should be removed from mysql db. Otherwise it is no point to use row locking from innodb.

# 9 years ago
Andy	Hi Jan, very interesting. I don't know what exactly device_attribute_history_id is used for nor why it grows. In my test installation I see it has 5 rows and all have a value of zero. Strange. I thought it was intended to have a unique id for all history entries in the database but I don't know why this is needed. And you are right it is a bottleneck for all requests. No need to parallelise them if they all get re-serialised by one table. I will ask the designers of the history feature of the database to explain why this is necessary and how to get around the limitations. Anyway the fact that you don't have any spikes is excellent news. It would be great to get a clean version of your patches for others to try out. Cheers Andy

# 9 years ago

Andy

Hi Jan,

very interesting. I don't know what exactly device_attribute_history_id is used for nor why it grows. In my test installation I see it has 5 rows and all have a value of zero. Strange. I thought it was intended to have a unique id for all history entries in the database but I don't know why this is needed. And you are right it is a bottleneck for all requests. No need to parallelise them if they all get re-serialised by one table. I will ask the designers of the history feature of the database to explain why this is necessary and how to get around the limitations.

Anyway the fact that you don't have any spikes is excellent news. It would be great to get a clean version of your patches for others to try out.

Cheers

Andy

# 9 years ago
Jean-Luc	Hi, The history ids are used for indexing history of resources and should be unique only for a single property. If i remember well, at the beginning, I implemented this features without locking the table. In fact, this is not a problem if 2 history entries of 2 different properties get the same id. But someone (not me) has serialized this. The only problem that can happen with an unserialized get_id(), is when 2 different database servers write the same property at the same time. In that case you can have a wrong order in the history for this particular update (or in worst case a mixed value for arrays). Jean-Luc

# 9 years ago

Jean-Luc

Hi,

The history ids are used for indexing history of resources and should be unique only for a single property. If i remember well, at the beginning, I implemented this features without locking the table. In fact, this is not a problem if 2 history entries of 2 different properties get the same id. But someone (not me) has serialized this. The only problem that can happen with an unserialized get_id(), is when 2 different database servers write the same property at the same time. In that case you can have a wrong order in the history for this particular update (or in worst case a mixed value for arrays).

Jean-Luc

# 9 years ago
jan	Hi Claudio, I attach a newer version of my files. (In the previous there was a bug in a query). In this version I put get_id into a separate transaction which seems to improve things. Bests, Jan Attachments: 51Kb

# 9 years ago
jan	The spikes still exists but they are much smaller (up to 0.1 without TSM). They come mostly from updating id, delete/insert history, SELECT … order by or commit. The changes work only for setting attribute properties with innodb. So to change to innodb reviewing of Databaseds code and much more tests is needed. Bests, Jan

# 9 years ago
claudio	jan Hi Claudio, I attach a newer version of my files. (In the previous there was a bug in a query). In this version I put get_id into a separate transaction which seems to improve things. Bests, Jan Thanks a lot. Test are under way with your version Databeseds on my development machine. Best regards Claudio

# 9 years ago
claudio	Update on performace tests: I compiled the patched source that Jan posted on the forums and reapetd my tests (see my other post): `test average min max inno+trans 4.207 0.523 140.678 isam+lock 8.31 0.54 175.253 patch+inno+trans 0.779 0.626 245.3` Please notice that durin the last tests I turned on loggoing of slow transactions , which may have slowed a little the db engine. Seems that there is some average improvement. Regarding: UPDATE device_attribute_history_id SET id=LAST_INSERT_ID(id+1) It may be possible to get rid of the _history_id tables and declare the corresponding id column of the history table as AUTO_INCREMENT. It requires some extra work on the server code but may result in a more compact code (may even fatser). What do you think? Regards

# 9 years ago

claudio

Update on performace tests: I compiled the patched source that Jan posted on the forums and reapetd my tests (see my other post):


test           average		min		max		
inno+trans	 4.207		0.523		140.678		
isam+lock	 8.31		0.54		175.253
patch+inno+trans 0.779          0.626           245.3

Please notice that durin the last tests I turned on loggoing of slow transactions , which may have slowed a little the db engine.
Seems that there is some average improvement.

Regarding:

UPDATE device_attribute_history_id SET id=LAST_INSERT_ID(id+1)

It may be possible to get rid of the *_history_id tables and declare the corresponding id column of the *history table as AUTO_INCREMENT. It requires some extra work on the server code but may result in a more compact code (may even fatser).
What do you think?

Regards