Eric, "Eric Mayers" wrote in message ... >I have an application that stores a large (up to 6gb) database in an >InnoDB table. I'm using an InnoDB table to have concurrent inserts and >selects and to get beyond the 2gb limitation. > >I have to allow read-only remote database connections and cannot enforce >that users use any kind of special query to access the database (e.g., >implementing my own fulltext-like index and using the index at an >application layer). The content consists of log entries which are each >under 255 characters long.
use the VARCHAR type instead of CHAR type. Otherwise your columns will take a full fixed length of space and the table will be bigger and slower. >I'd like to know what I can do to improve the response time of the >database under these restrictions. So I have a few questions: > >Would adding ram help? I can't imagine that an operation that requires >searching 6gb (a disk bandwidth or processing bottleneck problem) would >be improved much by adding ram.. ? If every query needs to scan the whole 6 GB table, or access it totally at random, then adding RAM does not help. But usually queries have a restricted working set, and a much smaller buffer pool can serve most queries quickly. Adding RAM and incresing the buffer pool size helps in most cases. >Can I do anything (short of hardware changes) to increase performance? The usual way is to use EXPLAIN SELECT, and in very problematic cases use the STRAIGHT JOIN and USE INDEX (index1, index2, ...) clauses of MySQL to force a query plan. The MySQL 'slow query log' can be used to track slow queries. The Unix 'top' and the Windows Task Manager are the best way to determine if the load is CPU-bound or I/O-bound. innodb_monitor prints very detailed statistics on the internal working of InnoDB, and you can also innodb_lock_monitor to track lock waits, if they are a problem. >And long term question: >I've noticed that a fulltext index feature doesn't appear on the InnoDB >todo list. From my (limited) research, it looks like this is a feature >a lot of people would find very useful. Is this a long term goal, or >has it been excluded for some reason? There has been demand of a fulltext indexing capacity on InnoDB type tables. If there are enough paying customers requesting it, we can assign a developer to port the MyISAM fulltext search on InnoDB. Fulltext indexes will work also on a transactional database. >Thanks > >Eric Mayers >Software Engineer Best regards, Heikki Tuuri Innobase Oy --- Order technical MySQL/InnoDB support at https://order.mysql.com/ See http://www.innodb.com for the online manual and latest news on InnoDB --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
