...
| Note |
|---|
InnoDB tables are highly recommended - uPortal relies on database transactional support, in MySQL this typically means InnoDB. This is *NOT* the default |
Assuming you've got the needed statements for InnoDB tables in your my.cnf file, you can change already-created MyISAM tables to Innodb tables with an ALTER TABLE statement - see the documentation for the exact syntax. Or, you can drop all your tables and re-create them with "TYPE=InnoDB" on your CREATE TABLE statements. If you're not sure about using TST (Transaction Safe Tables), do some reading on the MySQL site (http://www.mysql.com) about some of the companies and organizations that are currently using them - for instance, Slashdot.org uses MySQL InnoDB tables to handle their more than 100,000 hits per week.
Case-Sensitivity
MySQL table names are case-sensitive depending on the filesystem of the server. e.g. insensitive on Windows & Mac HFS+, Case sensitive on Unix. To prevent issues when moving between platforms it is recommended that you set:
...
Note: If you change this setting on a Unix system, you may have to rename some of your tables and entries – there is a note in the MySQL manual.
Indexing
Another thing that is really needed to use MySQL with uPortal effectively is to index your tables - this way, uPortal queries will have to scan fewer records and set fewer locks - and this can speed up authentication into the portal tremendously. UP_USER, UP_USER_ATTS, ans UP_USER_PARM need to be indexed with a primary key - check out the documentation for your version of MySQL for the exact syntax.
Query Cache
You may be much better performance by enabling the MySQL query cache.
uPortal Configuration
Before using MySQL with uPortal, several uPortal properties files need to be altered for MySQL. These include:
...
When the MySQL tables are created, they will be the MySQL default type - as of this writing (5/23/05), that's MyISAM. These tables are NOT transaction safe, which means that if an error occurs, like a communications/network error, the tables cannot be rolled back to their previous state. There are places in the uPortal code where if such a problem occurs, the transaction is programmed to roll back to its previous state - but if the tables cannot accomodate this, an error about not being able to roll back the tables will be entered into portal.log, and the account will be unusable after that point. To avoid this, use transaction safe tables, which MySQL provides support for, in terms of either BDB (Berkeley database) or InnoDB tables. As of 4.0.5, support for InnoDB tables is included with MySQL; previous to that, it was an extra download.
SOME OTHER NOTES
Assuming you've got the needed statements for InnoDB tables in your my.cnf file, you can change already-created MyISAM tables to Innodb tables with an ALTER TABLE statement - see the documentation for the exact syntax. Or, you can drop all your tables and re-create them with "TYPE=InnoDB" on your CREATE TABLE statements. If you're not sure about using TST (Transaction Safe Tables), do some reading on the MySQL site (http://www.mysql.com) about some of the companies and organizations that are currently using them - for instance, Slashdot.org uses MySQL InnoDB tables to handle their more than 100,000 hits per week.
...
Deadlock troubleshooting
One thing that will most likely come up in working with uPortal and MySQL (or any database for that matter) is the issue of deadlocks - when a record is needed to be accessed by two different queries at the same time. The MySQL site has an EXCELLENT chapter on dealing with these. Overall, the on-line reference guide for MySQL is an EXCELLENT resource.
...