OK, this means that the package tango_db does not use the script create_db.sql as is.
Cheers
Manu
Manu,
No. It use the script create_db.sql as it is (copying it from the tango sources) and that’s the problem. One have to change the script either in the debian package or in the tango sources.
Cheers,
Jan
Jan,
I am lost! How is it possible to run this script several times if the MySQL tango database is not dropped between each run?
This script aborts after its first line if the database is already there and therefore could not add entries in
XXX_id tables.
Cheers
Manu
Manu,
Ah, yes. You are right. In the package script from debian (tango-db 8.1.2c+dfsg-4~bpo70+1)
this two first lines are missing.
CREATE DATABASE tango;
USE tango;
Cheers,
Jan
Jan,
OK, I understand now. This morning I have sent a mail to Fred (Our Soleil colleague who did the debian packaging) to ask him
his opinion on this subject. May be there is may be a good reason why these two lines have been removed
Cheers
Manu
Hi,
I attach the current state plot from my PC with all my patches.
I can correlate groups of the highest spikes with some processes (which e.g. use my harddisk)
Top 5 are:
- TSM backup
- rsyslogd
- serverdump.sh (dumping all tango db properties)
- login after screensaver
- puppet
and other cron jobs.
What I’ve learnt is that it is better to keep tango DB separately on dedicated host with big amounts of memory and a large number of cores where no other program is using host resources.
Cheers,
Jan
Hello,
you are right, I am using the create_db.sql script but not only and with some modifications.
here the patch applyed to the
diff --git a/cppserver/database/create_db.sql.in b/cppserver/database/create_db.sql.in
index 210a335..292d005 100644
— a/cppserver/database/create_db.sql.in
+++ b/cppserver/database/create_db.sql.in
@@ -1,12 +1,8 @@
-CREATE DATABASE @TANGO_DB_NAME@;
-USE @TANGO_DB_NAME@;
Create all database tables
-source create_db_tables.sql
+source /usr/share/tango-db/create_db_tables.sql
Init the history identifiers
@@ -126,5 +122,5 @@ INSERT INTO property_class VALUES (‘TangoAccessControl’,‘AllowedAccessCmd’,6,'Ge
Load the stored procedures
-source stored_proc.sql
+source /usr/share/tango-db/stored_proc.sql
In fact this script is used during the package installation so I fixed the path.
Now If I remember correctly (I did this years ago with almost zero knowledge about databases…), I removed the first line of the script in order to be able to re-install the package wihtout error during the installation.
On Debian/Ubuntu/Mint… I am using the dbconfig-common[1] system to deal with all the database creation and upgrade from one version to the other.
In the debian/rules file you will see an sql target which create the script used by dbconfig-common to do the database upgrade.
sql:
./configure $(CONFIGURE_OPTS)
# use this target to generate the dbconfig-common upgrade script once the debian/changelog is ok
cat cppserver/database/stored_proc.sql
cppserver/database/update_db.sql > debian/mysql/$(FULLDEBVERSION)
$(MAKE) distclean
this way during an upgrade the upgrade script take care to create the right tables and fill them with the default values.
I regenerate also the stored_proc in the same time.
Now I need your help to rethink all this and tune the tango sources script in order to make it work properly.
the ideal solution for the packaging woud be to have by default the right scripts expected by dbconfig-common in the tango source.
I also need your help to propose a script which fix the databases generated with the Debian packages.
In order to sanitize them.
Let’s speak about all this.
cheers
Fred
[1] https://people.debian.org/~seanius/policy/dbconfig-common.html/
Hello Fred,
I see your problem. The dbconfig tool creates DB by itself so “two first lines” are executed by dbconfig and you cannot check if DB was created or it was before.
Therefore, you have to check in the sql sript if the rows from tango tables exist.
(For creating of tables it is done inside create_db_tables.sql so create_db_tables.sql is safe).
My first solution I’ve send in one of my previous posts (about a revision counter from NeXus Configuraton Server). Then, one has to add into each insert query a check if row was already inserted.
The second solution is to group all insert queries into one sql block with an if statement in front of it. The if command can check whether one of the table rows was added or not.
Unfortunately, the if statement in sql scripts can be only used in an sql procedure or a function so one has to create a procedure with insert queries and call it after.
One has to remember that an sql procedure cannot contain any ‘source’ command ( which is executed by an sql client interpreter).
So createdb.sql could look like
source create_db_tables.sql
delimiter |
drop procedure if exists populate_tango_rows |
create procedure populate_tango_rows()
begin
if not exists (select id from device_history_id limit 1) then
begin
INSERT INTO device_history_id VALUES (0);
INSERT INTO device_attribute_history_id VALUES (0);
INSERT INTO class_history_id VALUES (0);
/* here there should be all INSERT statements from create_db.sql */
INSERT INTO property_class VALUES ('TangoAccessControl','AllowedAccessCmd',5,'GetAllowedCommands',NULL,NULL,NULL);
INSERT INTO property_class VALUES ('TangoAccessControl','AllowedAccessCmd',6,'GetAllowedCommandClassList',NULL,NULL,NULL);
end;
end |
delimiter ;
call populate_tango_rows();
drop procedure if exists populate_tango_rows;
source stored_proc.sql
Actually, such a procedure could be put into the stored_proc.sql file.
To avoid changing of paths in the source commands you can put create_db.sql into /usr/share/tango-db/
and in your ‘mysql’ file source it as
source /usr/share/tango-db/create_db.sql
Finally, also those “two first lines” could be put into a separate file, i.e.
CREATE DATABASE @TANGO_DB_NAME@;
USE @TANGO_DB_NAME@;
source create_db.sql
If none of the changes are performed in the tango sources
the second solution is easier to perform (INSERT statments stay as there are).
Cheers,
Jan
Hi,
I’ve found a bug in my innodb-patch which causes a memory leak. To fix it
diff --git a/cppserver/database/DataBase.cpp b/cppserver/database/DataBase.cpp
index b7d779c..0bff3a1 100644
--- a/cppserver/database/DataBase.cpp
+++ b/cppserver/database/DataBase.cpp
@@ -6625,6 +6625,7 @@ void DataBase::db_put_device_attribute_property2(const Tango::DevVarStringArray
DEBUG_STREAM << "DataBase::PutAttributeProperty(): sql_query " << sql_query_stream.str() << endl;
result = query(sql_query_stream.str(),"db_put_device_attribute_property2()",al.get_con_nb());
my_ulonglong count = mysql_num_rows(result);
+ mysql_free_result(result);
if (count == 1)
{
This fix improves a situation on the second part of my last plot.
One can only see a spike caused by TSM.
Bests,
Jan
Getting better and better! Nice result - well done Jan!
finally I’ve cleaned my DB with duplicated rows.
Also we’ve excluded mysql db files from TSM backup (replacing it by mysqldump) and I cannot see any higher spikes.
Now the results of my tests with the innodb-patch look as follows
which seems to be acceptable (max < 0.25 sec).
Hi Jan,
Because you have everything already set-up, if I send you a patch file for the DB server with still MyISAM tables but with a
specialized treatment for creation/update of attribute properties used for memorized attribute, will it be possible for you to run your test?
Cheers
Emmanuel
Hi Emmanuel,
yes sure. I can switch back to myisam and perform the test. It would be good to have a patch for tango 8.1.2. (In other cases I can adopt it by myself)
Cheers,
Jan
Hi,
I just started to run my test for myisam engine (without any patch) and I need longer tests but it seems that after removing duplicated rows from my db my test plots look much better. Comparing innodb with myisam:
- average time for writing innodb (with patch) properties is lower than for myisam (without patch) properties
- for innodb spikes are higher and more frequent (for innodb I had more printouts so I have to check if that is not the reason)
It is also consistent with Claudio’s numbers from his previous post.
Bests,
Jan
Hi Jan,
Sorry not to send you a real patch file!
Could you add the following method in your DataBase class with the associated method declaration in the .h file
void DataBase::create_update_mem_att(const Tango::DevVarStringArray *argin)
{
const char *tmp_device = (*argin)[0];
const char *tmp_attribute = (*argin)[2];
//
// First the update
//
stringstream sql_query_stream;
sql_query_stream << "UPDATE property_attribute_device SET value=\"" << (*argin)[6]
<< "\" WHERE device=\"" << tmp_device << "\" AND attribute=\"" << tmp_attribute
<< "\" AND name=\"__value\" AND count=1";
DEBUG_STREAM << "DataBase::PutAttributeProperty2(): sql_query " << sql_query_stream.str() << endl;
int con_nb = get_connection();
string sql_query = sql_query_stream.str();
if (mysql_real_query(conn_pool[con_nb].db, sql_query.c_str(),sql_query.length()) != 0)
{
stringstream o;
WARN_STREAM << "DataBase::db_put_device_attribute_property2() failed to query TANGO database:" << endl;
WARN_STREAM << " query = " << sql_query << endl;
WARN_STREAM << " (SQL error=" << mysql_error(conn_pool[con_nb].db) << ")" << endl;
o << "Failed to query TANGO database (error=" << mysql_error(conn_pool[con_nb].db) << ")";
o << "\n.The query was: " << sql_query << ends;
release_connection(con_nb);
Tango::Except::throw_exception((const char *)DB_SQLError,o.str(),"Database::db_put_device_attribute_property2()");
}
my_ulonglong nb_rows = mysql_affected_rows(conn_pool[con_nb].db);
if (nb_rows == 0)
{
//
// The update hasn't changed anything in DB (0 rows affected). This means that the property is not yet
// created in DB. Therefore, create it now
//
sql_query_stream.str("");
sql_query_stream << "INSERT INTO property_attribute_device SET device=\'"
<< tmp_device << "\',attribute=\'" << tmp_attribute
<< "\',name=\'__value\',count=1,value=\'" << (*argin)[6] << "\',updated=NULL,accessed=NULL";
DEBUG_STREAM << "DataBase::PutAttributeProperty(): sql_query " << sql_query_stream.str() << endl;
sql_query = sql_query_stream.str();
if (mysql_real_query(conn_pool[con_nb].db, sql_query.c_str(),sql_query.length()) != 0)
{
stringstream o;
WARN_STREAM << "DataBase::db_put_device_attribute_property2() failed to query TANGO database:" << endl;
WARN_STREAM << " query = " << sql_query << endl;
WARN_STREAM << " (SQL error=" << mysql_error(conn_pool[con_nb].db) << ")" << endl;
o << "Failed to query TANGO database (error=" << mysql_error(conn_pool[con_nb].db) << ")";
o << "\n.The query was: " << sql_query << ends;
release_connection(con_nb);
Tango::Except::throw_exception((const char *)DB_SQLError,o.str(),"Database::db_put_device_attribute_property2()");
}
}
release_connection(con_nb);
}
and in the method DataBase::db_put_device_attribute_property2() after the call to GetTime(before)
if (argin->length() == 7 &&
::strcmp((*argin)[1].in(),"1") == 0 &&
::strcmp((*argin)[3].in(),"1") == 0 &&
::strcmp((*argin)[4].in(),"__value") == 0 &&
::strcmp((*argin)[5].in(),"1") == 0)
{
create_update_mem_att(argin);
}
else
{
..... // Previous code
}
GetTime(after);
Thank’s a lot for your help and your time
Cheers
Emmanuel
Hi Emmanuel,
It is fine for me. I will do the test.
Cheers,
Jan
Hi Emmanuel,
Your patch needs an improvment. When I run the test new rows are added to the property_attribute_device table (which is wrong).
MySQL is clever enough to see if modification is done or not, i.e.
mysql_affected_rows(…) return 0 if row has not been changed (but update has been executed).
So it is better to check if the insert query should be executed in another way, e.g. by a select query.
Cheers,
Jan
Jan,
What we have to do is :
- Create the entry in the table for the first time the memorized attribute is written and then only update the stored value
each time the attribute is written.
What the change is doing (or try to do) is:
- First UPDATE the value even if it does not exist yet. We do this first because as soon as the entry is created, it is the only
db operation required - If the update hasn’t modified anything in db (which means that the entry was not already created in DB) then create
the entry with one INSERT
The idea is to not do any SELECT
This is not what you are experiencing?
What are the new row(s) created?
Cheers
Manu
Hi Manu,
the problem is that in newer versions of mysql if you call update query to set some value but the value is still the same (has not been changed ) then mysql_affected_rows(…) returns 0 (but in the patch you expect to be 1).
After short run my tango db is four times bigger with additional duplicated rows for memorized attributes.
Stackoverflow
Cheers,
Jan
Jan,
Which version of MySQL are you using?
I have 5.6.19
Manu