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
