:~> -----Original Message-----
:~> From: Shaun Adams [mailto:[EMAIL PROTECTED]
:~> Sent: Tuesday, February 07, 2006 4:32 PM
:~> To: mysql@lists.mysql.com
:~> Subject: INNODB Questions and Optimization help
:~>
:~> Questions:
:~>
:~> 1. The ibdata1 file size is 10GB. Does that sound right? Should this
:~> file
:~> be this big?
Yes, the ibdata file contains the index and the data in the same
space (unless you use 4.1+ and use separate table spaces).
Text/blob data is doubled the actual size in most cases, so expect to see
innodb use a lot of disk space if you continue to store blob data in innodb.
:~>
:~> 2. Once a week, I have to perform HUGE insert imports into the database.
:~> What is the recommended procedure for doing this? Clearing out memory
:~> with
:~> a FLUSH before I start the import, setting
:~> innodb_flush_log_at_trx_commit to
:~> 0... any other suggestions
Use LOAD DATA INFILE increase your INNODB BUFFER POOL to about 2-3GB if you
can afford it, and disable keys on the table.
Also LOAD DATA in the ORDER that the table is ordered. INNODB supports
CLUSTERED indexes. That means if your table has a PRIMARY KEY of
A,B
The data should be sorted A,B before using LOAD DATA
:~>
:~> 3. Could you all recommend any tips you have used to increase
:~> performance
:~> using INNODB (from a system admin's perspective).
Best perf improvements is to look at your indexes and see if queries
are using them correctly. Other then that from a sysadmin point of view, use
O_DIRECT, turn on noatime, and build the drive using these options
/sbin/mkfs.ext3 -i 131072 -m 1 -L MYSQL /dev/.../ your drive
Also set swappiness to 0 in /proc/sys/vm/
:~>
:~> 4. Finally, could you take a look at my settings below as well as the
:~> system
:~> variables and recommend any changes.
Set innodb_buffer_pool_size=2G
Set innodb_thread_concurrency=32 // this is good if you do a lot of small
fast queries
Set innodb_log_file_size=512M
Your doing a bunch of table scans check you sql, and make sure your using
indexes.
:~> Handler_read_rnd 2025997
:~> Handler_read_rnd_next 487643
This is how I was able to tell.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
