MySQL schema changes for performance

[mikeyp@…]

We have been dealing with extremenly poor Trac performance when using MySQL as the backend database. The root cause is the use of 'text' fields in many columns that should preferably be varchar.

We finally decided to make some schema data changes to improve performance. As a result of these changes, we have substantial performance improvements. For an example ,the following query is executed very often by Trac:

SELECT DISTINCT s.sid, n.value, e.value 
FROM session AS s  
LEFT JOIN session_attribute AS n ON (n.sid=s.sid   and n.authenticated=1 AND n.name = 'name')  
LEFT JOIN session_attribute AS e ON (e.sid=s.sid   AND e.authenticated=1 AND e.name = 'email') 
WHERE s.authenticated=1 ORDER BY s.sid; 

Our execution time dropped from 8 seconds for this query to approximately 0.1 seconds. When timing this, there were about 6000 rows in the session table, and 20k rows in session_attribute. The only data type changes me made were to convert several columns from text to varchar, primarily colums used often in joins and where clauses. We also added indexes on ticket.{priority,milestone,component}

I am attaching three files:

• trac_schema_before is a MySQL dump of our table definitions before the changes (Trac 0.10.4 bas schema)
• trac_schema_after is a MySQL dump of our table definitions after the changes.
• trac_schema_diffs shows the changes we made.

I recommend that these changes or similar ones be merged into a future Trac version.

Thanks,

mike

[mikeyp@…]

Trac 0.10 schema in MySQL dump format

[mikeyp@…]

Trac 0.10.4 schema with dataype changes

[mikeyp@…]

Summary of trac/MySQL datatype changes

[cboos]

Thanks, we'll try to take those advices into account.

[dx@…]

As requested in comment:ticket:7080:2, i'll explain here the 500 internal server errors i got before applying this changes.

Some months after migrating to mysql, our trac started to become slower, only on POST requests (when submitting a comment or opening a ticket). That delay often finished after a while, up to one minute, with a 500 internal server error page, meaning an obvious timeout. Trac logs didn't include meaningful errors, but IIRC there were a few "broken pipe" tracebacks (fastcgi)

After getting one of those errors, the server often remained busy, making other requests wait - those that were waiting for too much finished with 500 too. But all requests were processed, all tickets or comments were added to the database. Some users, of course, were unaware of this detail, and considered that reposting (F5) that page until they get a non-error page. Unfortunately they didn't get that, never, but they left lots of nice duplicate tickets around.

Dealing with that was, uhm, annoying. So I changed the 500 internal server error page to the following, removing the word "error", but some users still insisted in DoS'ing our trac.

500
If this is the result of a form submission, please don't repost or refresh this page.
Your request will be processed in a few seconds

Anyway, last wednesday, some people from our hosting (dreamhost) noticed that trac was taking up too much mysql resources, and renamed the corresponding (session) tables, which had 20k and 60k rows each one, leaving trac broken for a while.

The guy-who-pays-the-hosting googled a bit and got this ticket. He applied this changes, cleared both tables, and reduced the session expiration time from 90 days to 5 days. Now trac works unbeliabely well. That's it.

[brad@…]

I have been using Trac for only a week now. So I'm no Trac expert. So far I really like it. I have been using Sqlite due to the warnings on this website, but today I decided I'd give it a go with MySQL. I have been using MySQL for years. I am using FreeBSD.

I used the script here  http://trac-hacks.org/wiki/SqliteToMySqlScript to export into MySQL. That was pretty painless.

A few notes to remember:

• Collation of all tables is utf8_general_ci as per the instructions on this website. I didn't try anything else.
• I changed all tables to InnoDB because this is more reliable and more like Sqlite functionality. I added the innodb_file_per_table option to my.cnf because I prefer it.
• After I installed py-MySQLdb, I had to restart Apache to be able to load this new module.

I inspected the MySQL schema and notices all the 'text' fields. That makes sense considering that this schema was designed for Sqlite, which has limited field types. However, in MySQL this makes an enormous difference to performance.

Along the lines of the comments before me, I went about changing the types of my fields. Attached is the SQL required to modify the Trac 0.11rc1 schema for use in MySQL.

Please note: I chose my own field types based on a quick look at the content in the fields. Remember my installation is very new, and thus fairly empty. Therefore, this isn't optimised, which won't matter for small/medium installations, but might need tweaking for large/huge installations. Never-the-less, the new schema reduced both database disk size and response times by orders of magnitude.

I didn't bother altering the indexes, as I assume they are probably ok. I don't have time to read the source code to optimise them.

You should be able to just execute the sql file on your MySQL installation to fix the schema. The attached file is above and named TracMysqlSchemaChange.sql

[brad@…]

SQL to fix the 'text' fields for MySQL installations

[diroussel+trac@…]

This seems like a great change, but in order to incorporate it into the core of trac we can't rely on manual changes to the schema. Any plugin can extend the schema of trac, so the only way to fix this properly is in source:/trunk/trac/db/mysql_backend.py

In source:/trunk/trac/db/schema.py the default column type is text. This needs to be translated in the mysql backend into a varchar, somewhere in to_sql(). That sounds do-able. But how do we know the correct column width?

Also some of the upgrade script, e.g. source:/trunk/trac/upgrades/db3.py, create new tables with direct SQL. How do we solve that?

[diroussel+trac@…]

Possible fix for the database adapter

[info@…]

Our  Trac was also getting really slow, it took around 70 secs to create or update a ticket with Trac 0.11.3, so we applied the TracMysqlSchemaChange.sql changes and it now takes 30 secs instead, which is an improvement but no way as fast as other Trac sites I use, like twistedmatrix.com, which presumably use SQLite. So we're now thinking of switching to SQLite.

[anonymous]

Turns out the TracMysqlSchemaChange.sql script incorrectly converted the timestamp fields for the milestones from int(11) to tinyint(4) causing all milestones to show up as closed on 1/1/1970, so I removed these following lines:

 ALTER TABLE `milestone` CHANGE `due` `due` TINYINT( 4 ) NULL DEFAULT NULL ,
CHANGE `completed` `completed` TINYINT( 4 ) NULL DEFAULT NULL ;

See updated attachment that works with 0.11.3 (so far).

[info@…]

The id field in the attachment table was also converted from text to int, which screwed up the attachments, and removing that line fixed it again:

CHANGE `id` `id` INT NOT NULL ,

See updated attachment for the latest version that works for us (so far).

[mikeyp@…]

Just reminder for anyone following this , the original changes I submitted were for 0.10.x, and there are probably changes in 0.11 I didn't account for.

[cboos]

Note that the database schema is the same since Trac 0.10.4 and all 0.11.x versions (even trunk so far, but no guarantees for 0.12).

[Woody Gilk <woody.gilk@…>]

All of the utf8_general_ci collations in TracMysqlSchemaChange?-3.sql should be changed to utf8_bin because utf8_general_ci is not case sensitive, and sqlite is. Without using utf8_bin two attachments: foo.txt and FOO.txt are considered the same, and will produce errors when migrating from sqlite to MySQL.