Tango database - TANGO Controls

# 9 years ago
claudio	After the interesting presetation by Gwenaelle at the last Tango meeting and some discussion on the mailing list, we have staterd to test some techniques which may solve, or at least mitigate, the timeout probelms on particular operations on the Tangpo database. We have the impression that the timeouts may be due to the LOCK TABLE statements used in tha Dabaseds server. Table locks are used because the originale MyISAM MySQL storage enrgine did not support real SQL transactions. The idea is the to use the InnoDb storage enagine START TRANSACTION / COMMIT statements to guarantee the consistency of the tango database. The first step is to convert the dtango databse schem to use Innodb. The attached script convertdb.sql performs this chanage. Stop the Dabaseds server and then run convertdb.sql ( you can paste the script in the myslq shell) with a user with sufficnet privilegs (e.g. root) Then you can start again the Tango databseds. The second step is to modify the Database server in oredr to use transaction instead of locks. I modified just tree files from the ltates trunk in the svn reposiory and recompiled the dataabse server. You can find the modified file here: dbasemods.tar.gz This is just a test, no much care has been payed to code style and othe possible optimizations. Test are under way to see if the modified server works correctly in terms of dab management and if the timeout problems are still there. Does any body has a a stress test recipe for the Tango Dabase ? Edited 9 years ago Attachments: 155b 55Kb

# 9 years ago

After the interesting presetation by Gwenaelle at the last Tango meeting and some discussion on the mailing list, we have staterd to test some techniques which may solve, or at least mitigate, the timeout probelms on particular operations on the Tangpo database.
We have the impression that the timeouts may be due to the LOCK TABLE statements used in tha Dabaseds server. Table locks are used because the originale MyISAM MySQL storage enrgine did not support real SQL transactions.

The idea is the to use the InnoDb storage enagine START TRANSACTION / COMMIT statements to guarantee the consistency of the tango database.

The first step is to convert the dtango databse schem to use Innodb.
The attached script convertdb.sql performs this chanage.
Stop the Dabaseds server and then run convertdb.sql ( you can paste the script in the myslq shell) with a user with sufficnet privilegs (e.g. root)
Then you can start again the Tango databseds.

The second step is to modify the Database server in oredr to use transaction instead of locks. I modified just tree files from the ltates trunk in the svn reposiory and recompiled the dataabse server. You can find the modified file here: dbasemods.tar.gz
This is just a test, no much care has been payed to code style and othe possible optimizations.
Test are under way to see if the modified server works correctly in terms of dab management and if the timeout problems are still there. Does any body has a a stress test recipe for the Tango Dabase ?

Edited 9 years ago

# 9 years ago
jan	Hi, I've just made some first tests comparing MyISAM and innoDB and I do not see to many differences in performance. I've used tango 8.1.2 version with an adopted patch from Claudio. (The concurrency problem appears only when I set to many threads comparing to my cpu cores, e.g. innodb_thread_concurrency=20 ). What I see what over-kills db engines is when Database is asked to update only one property in the property_attribute_device table it removes all properties for this attribute and inserts them back again. Could someone remind me why all properties for one attribute has to be kept together? Maybe it is not needed for for innoDB? Other option could be to have separate tables for each device. Bests, Jan

# 9 years ago

jan

Hi,

I've just made some first tests comparing MyISAM and innoDB and I do not see to many differences in performance.
I've used tango 8.1.2 version with an adopted patch from Claudio.
(The concurrency problem appears only when I set to many threads comparing to my cpu cores, e.g.
innodb_thread_concurrency=20 ).

What I see what over-kills db engines is when Database is asked to update only one property in
the property_attribute_device table it removes all properties for this attribute and inserts them back again.

Could someone remind me why all properties for one attribute has to be kept together?
Maybe it is not needed for for innoDB?

Other option could be to have separate tables for each device.

Bests,
Jan

# 9 years ago
jan	Hello, I attach a few plots from my stress tests. They show an execution time of read_Position (in simulation mode which writes also 2 others memorized attributes), command_inout("State") and state() for OmsVme58 motors versus the local time. One plot is for myisam engine and another for innodb (and versions with the log scale). I attached also the test sources. It seems that performence of the myisam engine with the current settings is better that innodb. For innodb I used and probably one can tune it better (also the current Database server is more tuned to the myisam engine ) innodb_buffer_pool_size = 1G innodb_flush_method = O_DIRECT innodb_log_files_in_group = 2 innodb_file_per_table = 1 innodb_flush_log_at_trx_commit=2 innodb_doublewrite=0 innodb_support_xa=0 innodb_thread_concurrency=1 For both engines I get spikes in the plot with similar maximal high (for innodb are more frequent). The higher spikes are caused by insert and delete attribute property queries. Bests, Jan Edited 9 years ago

# 9 years ago

jan

Hello,

I attach a few plots from my stress tests. They show an execution time of read_Position (in simulation mode which writes also 2 others memorized attributes), command_inout("State") and state() for OmsVme58 motors versus the local time. One plot is for myisam engine and another for innodb (and versions with the log scale). I attached also the test sources. It seems that performence of the myisam engine with the current settings is better that innodb.
For innodb I used and probably one can tune it better (also the current Database server is more tuned to the myisam engine )

innodb_buffer_pool_size = 1G
innodb_flush_method = O_DIRECT
innodb_log_files_in_group = 2
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit=2
innodb_doublewrite=0
innodb_support_xa=0
innodb_thread_concurrency=1

For both engines I get spikes in the plot with similar maximal high (for innodb are more frequent).
The higher spikes are caused by insert and delete attribute property queries.

Bests,
Jan

Edited 9 years ago

# 9 years ago
jan	attachments Attachments: 463Kb

# 9 years ago
jan	attachments Attachments: 524Kb

# 9 years ago
jan	attachments

# 9 years ago
jan	sources Attachments: 1Kb

# 9 years ago
jan	I was not able to attached all my files. It seems that it is not possible to do it with files which are larger than 1MB.

# 9 years ago
Andy	Jan, thanks for these interesting results. We will comment them in a later post but in the meantime here are the images in jpg format for those of you on cannot read them easily: Edited 9 years ago Attachments:

# 9 years ago
Andy	Hi Jan, indeed InnoDB tables seem to perform much worse than MyISAM. For a simulated device the answer should be immediate as shown on the plot with MyISAM tables. I do not understand the large number of slow calls on InnoDB. Looks like MyISAM is a better choice in our case except for the spikes. We have had another idea here (actually Emmanuel had the idea) to replace the LOCK/DELETE/INSERT/UNLOCK call with UPDATE for memorised attributes. If someone has time to try that please post the patch so everyone can try to stress test it. We will try to do the change ourselves as soon as we have time. Thanks for this interesting result. Andy

# 9 years ago

Andy

Hi Jan,

indeed InnoDB tables seem to perform much worse than MyISAM. For a simulated device the answer should be immediate as shown on the plot with MyISAM tables. I do not understand the large number of slow calls on InnoDB. Looks like MyISAM is a better choice in our case except for the spikes. We have had another idea here (actually Emmanuel had the idea) to replace the LOCK/DELETE/INSERT/UNLOCK call with UPDATE for memorised attributes. If someone has time to try that please post the patch so everyone can try to stress test it. We will try to do the change ourselves as soon as we have time.

Thanks for this interesting result.

Andy