Re: InnoDB Performance

[Marcin Lewandowski]

Gary Richardson napisał(a):
> Hey,
> 
> How much load is system vs user? I found that when my company
> converted some large tables on our old server, the concurrent disk IO
> increased. Your database server is doing more in parallel and
> accessing more from your disk at one time. That would be my guess.
> 
> One option is to get a faster disk interconnect (SATA or SCSI,
> especially if you are running on IDE).

I've got IDE hdd. Is there simple way to check, if it's overloaded? 
Would RAID1 help? (I don't know if in raid1 there are parralel reads or 
maybe one disk is only a mirror)

> The other option is to increase innodb_buffer_pool_size, which can be
> tough if you are running your webserver on the same box. This is where
> InnoDB stores your database in memory. The more it can store, the less
> disk IO is required.

But if it would be too high, server would use swap, what makes more io 
calls. :(

> Without knowing how much data there is, I'd probably bump the systems
> ram up to 2 gigs (from 512) and set innodb_buffer_pool_size to 1GB.
> How big is the database? Your data file is set to autoextend, how big
> is the ibdata1 file?

700mb of innodb tables (where phpbb_posts_text contains 170000 records = 
116mb and phpbb_search_wordmatch contains about 5000000 records = 500mb, 
rest is smaller) and 200mb of myisam tables



server root # ls -l /data/mysql/ib*
-rw-rw----  1 mysql mysql    5242880 Apr 12 23:14 /data/mysql/ib_logfile0
-rw-rw----  1 mysql mysql    5242880 Apr 12 18:48 /data/mysql/ib_logfile1
-rw-rw----  1 mysql mysql 1000341504 Apr 12 23:14 /data/mysql/ibdata1


As I wrote in reply to kernel's message, I've changed settings to:

innodb_data_file_path = ibdata1:128M:autoextend
innodb_buffer_pool_size=150M
innodb_additional_mem_pool_size = 50M

and system load is "only" 2 to 3. But in my opinion, it's still to high, 
and website is growing so in month or two there would be serious problem 
if I don't fix that issue.

-- 
Marcin Lewandowski
[  mailto:www@stripped  gg:188068 jid:saepia@stripped  ]
[   http://www.nosoftwarepatents.com/pl/m/intro/index.html   ]