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