Edgewall Software
Modify

Opened 11 years ago

Last modified 3 years ago

#6986 new enhancement

MySQL schema changes for performance

Reported by: mikeyp@… Owned by:
Priority: normal Milestone: next-major-releases
Component: general Version: 0.11rc1
Severity: major Keywords: performance mysql database patch
Cc: dx@…, info@…, peter@… Branch:
Release Notes:
API Changes:

Description

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

Attachments (7)

trac_schema_before.sql (6.7 KB ) - added by mikeyp@… 11 years ago.
Trac 0.10 schema in MySQL dump format
trac_schema_after.sql (7.0 KB ) - added by mikeyp@… 11 years ago.
Trac 0.10.4 schema with dataype changes
trac_schema_diffs.txt (3.1 KB ) - added by mikeyp@… 11 years ago.
Summary of trac/MySQL datatype changes
TracMysqlSchemaChange.sql (5.8 KB ) - added by brad@… 11 years ago.
SQL to fix the 'text' fields for MySQL installations
ticket_6986_mysql_support.diff (588 bytes ) - added by diroussel+trac@… 10 years ago.
Possible fix for the database adapter
TracMysqlSchemaChange-2.sql (5.7 KB ) - added by info@… 10 years ago.
TracMysqlSchemaChange-3.sql (5.6 KB ) - added by info@… 10 years ago.

Download all attachments as: .zip

Change History (26)

Changed 11 years ago by mikeyp@…

Attachment: trac_schema_before.sql added

Trac 0.10 schema in MySQL dump format

Changed 11 years ago by mikeyp@…

Attachment: trac_schema_after.sql added

Trac 0.10.4 schema with dataype changes

Changed 11 years ago by mikeyp@…

Attachment: trac_schema_diffs.txt added

Summary of trac/MySQL datatype changes

comment:1 Changed 11 years ago by Christian Boos

Milestone: 0.12
Severity: normalmajor

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

comment:2 Changed 11 years ago by dx@…

Cc: dx@… added

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.

comment:3 Changed 11 years ago by brad@…

Milestone: 0.120.11.1
Version: 0.10.40.11rc1

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 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

Last edited 4 years ago by Ryan J Ollos (previous) (diff)

Changed 11 years ago by brad@…

Attachment: TracMysqlSchemaChange.sql added

SQL to fix the 'text' fields for MySQL installations

comment:4 Changed 10 years ago by 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?

Changed 10 years ago by diroussel+trac@…

Possible fix for the database adapter

comment:5 Changed 10 years ago by info@…

Cc: info@… added

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.

Changed 10 years ago by info@…

Attachment: TracMysqlSchemaChange-2.sql added

comment:6 Changed 10 years ago by 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).

Changed 10 years ago by info@…

Attachment: TracMysqlSchemaChange-3.sql added

comment:7 Changed 10 years ago by 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).

comment:8 Changed 10 years ago by 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.

comment:9 Changed 10 years ago by Christian Boos

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).

comment:10 Changed 10 years ago by Marcus Lindblom <macke@…>

Cc: macke@… added

comment:11 Changed 10 years ago by 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.

comment:12 Changed 9 years ago by anonymous

Be warned!

the attachment table uses a field named "id" this is a VARCHAR or TEXT Field and MUST BE!

its wrong in the script, if you run, you will destroy your wiki attachment associations!

in trac attachments, the field contains the Ticket ID but in Wiki Attachment it contains the Wikipage Name!

comment:13 Changed 9 years ago by macke@…

Cc: macke@… removed

comment:14 Changed 8 years ago by Christian Boos

Milestone: next-minor-0.12.xnext-major-0.1X
Owner: Jonas Borgström deleted

The next iterations of db improvements should really take this text vs. varchar distinction into consideration.

The TracMysqlSchemaChange* attachments here probably don't apply to recent versions of Trac, and from some of the comments above, I think we should really warn people that they're not at all supported and also that they've never been tested by us.

Last edited 8 years ago by Christian Boos (previous) (diff)

comment:15 Changed 8 years ago by alex@…

Cc: alex@… added

comment:16 Changed 7 years ago by peter@…

Cc: peter@… added

I just did this manually for a trac instance, and I'll probably do it again for another. For that I will cobble up a script that does the conversion for 0.12 databases.

comment:17 in reply to:  16 Changed 7 years ago by garyb@…

Replying to peter@…:

I just did this manually for a trac instance, and I'll probably do it again for another. For that I will cobble up a script that does the conversion for 0.12 databases.

That would be great. We are running Trac as the ticket system and wiki in conjunction with dotProject, and would like (someday) to integrate things like priorities between the two. Having them both use MySQL would greatly simplify that, as either one could have queries that pull from both databases.

comment:18 Changed 4 years ago by Alex Willmer <alex@…>

Cc: alex@… removed

comment:19 Changed 3 years ago by figaro

Keywords: patch added

Modify Ticket

Change Properties
Set your email in Preferences
Action
as new The ticket will remain with no owner.
The ticket will be disowned.
as The resolution will be set.
The owner will be changed from (none) to anonymous.

Add Comment


E-mail address and name can be saved in the Preferences .
 
Note: See TracTickets for help on using tickets.