Tango database
avatar par défaut
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).
Attachments:
avatar par défaut
Manu
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
avatar par défaut
jan
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
avatar par défaut
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
avatar par défaut
Manu
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
avatar par défaut
jan
Hi Emmanuel,

It is fine for me. I will do the test.

Cheers,
Jan
avatar par défaut
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
avatar par défaut
Manu
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
avatar par défaut
jan
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
avatar par défaut
Manu
Jan,

Which version of MySQL are you using?
I have 5.6.19

Manu
 
Register or login to create to post a reply.