SEO

Hi friends,

we're developing a new web application that works with a db around 1gb
and 30 tables.

We work with linux, and I'm evaluating the benefit of making an specific
partition (ext3) for mysql in order to have a bigger Block Size. Block
Size by default in / partition is 4096.

Do you think that could be good? Any experience?

Iñigo

--
--------
Iñigo Medina García
Librería Díaz de Santos
imedina@diazdesantos.es

Will you be using the MyISAM or InnoDB table engines?

I had heard that InnoDB uses 16k blocks internally, so that might be a good
starting point, though I'd love to have someone confirm or deny that this is
actually true.

-Aaron


On Fri, May 9, 2008 at 12:01 AM, Iñigo Medina García <
imedina@diazdesantos.es> wrote:

> Hi friends,
>
> we're developing a new web application that works with a db around 1gb
> and 30 tables.
>
> We work with linux, and I'm evaluating the benefit of making an specific
> partition (ext3) for mysql in order to have a bigger Block Size. Block
> Size by default in / partition is 4096.
>
> Do you think that could be good? Any experience?
>
> Iñigo
>
> --
> --------
> Iñigo Medina García
> Librería Díaz de Santos
> imedina@diazdesantos.es
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=aaronblew@gmail.com
>
>

I would use as large a block size as you dare, especially with InnoDB.
Makes reading and writing faster as custs down seek time as cuts down
disk fragmenation and avoids block table reads. With MyIsam you have
lots of files, but if you only have a few again might work well with a
large block size.

Also have a look at the stripe size of your raid system, might work well
aligning them if you can. This URL also gives some tips for an ext3
file system on RAID, look for 'stride':

http://insights.oetiker.ch/linux/raidoptimization.html

Ben

Iñigo Medina García wrote:
> Hi friends,
>
> we're developing a new web application that works with a db around 1gb
> and 30 tables.
>
> We work with linux, and I'm evaluating the benefit of making an specific
> partition (ext3) for mysql in order to have a bigger Block Size. Block
> Size by default in / partition is 4096.
>
> Do you think that could be good? Any experience?
>
> Iñigo
>

************************************************** ***********************
This e-mail is confidential and may be legally privileged. It is intended
solely for the use of the individual(s) to whom it is addressed. Any
content in this message is not necessarily a view or statement from Road
Tech Computer Systems Limited but is that of the individual sender. If
you are not the intended recipient, be advised that you have received
this e-mail in error and that any use, dissemination, forwarding,
printing, or copying of this e-mail is strictly prohibited. We use
reasonable endeavours to virus scan all e-mails leaving the company but
no warranty is given that this e-mail and any attachments are virus free.
You should undertake your own virus checking. The right to monitor e-mail
communications through our networks is reserved by us

Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
Registered in England No: 02017435, Registered Address: Charter Court,
Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE.
************************************************** ***********************

Thanks, Aaron.

>> Will you be using the MyISAM or InnoDB table engines?

Both, but InnoDB more.

>> I had heard that InnoDB uses 16k blocks internally, so that might be
a good starting point, though I'd love to have someone confirm or deny
that this is actually true.

Ok, that's interesting. :-) I'll check it.

Iñigo



Aaron Blew wrote:
> Will you be using the MyISAM or InnoDB table engines?
>
> I had heard that InnoDB uses 16k blocks internally, so that might be a good
> starting point, though I'd love to have someone confirm or deny that this is
> actually true.
>
> -Aaron
>
>
> On Fri, May 9, 2008 at 12:01 AM, Iñigo Medina García <
> imedina@diazdesantos.es> wrote:
>
>> Hi friends,
>>
>> we're developing a new web application that works with a db around 1gb
>> and 30 tables.
>>
>> We work with linux, and I'm evaluating the benefit of making an specific
>> partition (ext3) for mysql in order to have a bigger Block Size. Block
>> Size by default in / partition is 4096.
>>
>> Do you think that could be good? Any experience?
>>
>> Iñigo
>>
>> --
>> --------
>> Iñigo Medina García
>> Librería Díaz de Santos
>> imedina@diazdesantos.es
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=aaronblew@gmail.com
>>
>>
>


--
--------
Iñigo Medina García
Librería Díaz de Santos Madrid (Spain)
imedina@diazdesantos.es facilmejor@gmail.com

Thanks Ben.

>> I would use as large a block size as you dare, especially with InnoDB.
>> Makes reading and writing faster as custs down seek time as cuts down
>> disk fragmenation and avoids block table reads. With MyIsam you have
>> lots of files, but if you only have a few again might work well with
>> a large block size.

Perfect. That sounds the way Aaron explain about 16k blocks in InnoDb.
And actually we have more InnoDB tables tan MyISAM.

>> Also have a look at the stripe size of your raid system, might work
>> well aligning them if you can. This URL also gives some tips for an
>> ext3 file system on RAID, look for 'stride':
>> http://insights.oetiker.ch/linux/raidoptimization.html

Perfect. I'll check it.

Iñigo



Ben Clewett wrote:
>
>
> I would use as large a block size as you dare, especially with InnoDB.
> Makes reading and writing faster as custs down seek time as cuts down
> disk fragmenation and avoids block table reads. With MyIsam you have
> lots of files, but if you only have a few again might work well with a
> large block size.
>
> Also have a look at the stripe size of your raid system, might work well
> aligning them if you can. This URL also gives some tips for an ext3
> file system on RAID, look for 'stride':
>
> http://insights.oetiker.ch/linux/raidoptimization.html
>
> Ben
>
> Iñigo Medina García wrote:
>> Hi friends,
>>
>> we're developing a new web application that works with a db around 1gb
>> and 30 tables.
>>
>> We work with linux, and I'm evaluating the benefit of making an specific
>> partition (ext3) for mysql in order to have a bigger Block Size. Block
>> Size by default in / partition is 4096.
>>
>> Do you think that could be good? Any experience?
>>
>> Iñigo
>>
>
> ************************************************** ***********************
> This e-mail is confidential and may be legally privileged. It is intended
> solely for the use of the individual(s) to whom it is addressed. Any
> content in this message is not necessarily a view or statement from Road
> Tech Computer Systems Limited but is that of the individual sender. If
> you are not the intended recipient, be advised that you have received
> this e-mail in error and that any use, dissemination, forwarding,
> printing, or copying of this e-mail is strictly prohibited. We use
> reasonable endeavours to virus scan all e-mails leaving the company but
> no warranty is given that this e-mail and any attachments are virus free.
> You should undertake your own virus checking. The right to monitor e-mail
> communications through our networks is reserved by us
>
> Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
> Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
> Registered in England No: 02017435, Registered Address: Charter Court,
> Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE.
> ************************************************** ***********************
>


--
--------
Iñigo Medina García
Librería Díaz de Santos Madrid (Spain)
imedina@diazdesantos.es facilmejor@gmail.com

I'm seeing that architecture has almost allways the limit in 4 kb (block
size -> page). Theoretically architecture of 64 bits would offer up to 8
kb, but it seems to be that it usually has 4 kb too because of
compatibiliry issues with its i386 ancestors.

Any idea about that? We run Intel Core 2 Duo on kernel 2.6.21-1 and i386
by now. Do you think it could be better to change the architecture in
order to get the advantages of 8 kb blocks size?

Iñigo


Iñigo Medina García wrote:
> Hi friends,
>
> we're developing a new web application that works with a db around 1gb
> and 30 tables.
>
> We work with linux, and I'm evaluating the benefit of making an specific
> partition (ext3) for mysql in order to have a bigger Block Size. Block
> Size by default in / partition is 4096.
>
> Do you think that could be good? Any experience?
>
> Iñigo
>


--
--------
Iñigo Medina García
Librería Díaz de Santos Madrid (Spain)
imedina@diazdesantos.es facilmejor@gmail.com

> Will you be using the MyISAM or InnoDB table engines?
>
> I had heard that InnoDB uses 16k blocks internally, so that might be a good
> starting point, though I'd love to have someone confirm or deny that this is
> actually true.

Yep, Aaron. Look at: http://www.innodb.com/innodb/features/
and expand the *wspifmd* link "read more" of InnoDB Features:
"database page sizes configurable, from 4 to 64 kB, default 16 kB".

Iñigo


> -Aaron
>
>
> On Fri, May 9, 2008 at 12:01 AM, Iñigo Medina García <
> imedina@diazdesantos.es> wrote:
>
>> Hi friends,
>>
>> we're developing a new web application that works with a db around 1gb
>> and 30 tables.
>>
>> We work with linux, and I'm evaluating the benefit of making an specific
>> partition (ext3) for mysql in order to have a bigger Block Size. Block
>> Size by default in / partition is 4096.
>>
>> Do you think that could be good? Any experience?
>>
>> Iñigo
>>
>> --
>> --------
>> Iñigo Medina García
>> Librería Díaz de Santos
>> imedina@diazdesantos.es
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=aaronblew@gmail.com
>>
>>
>


--
--------
Iñigo Medina García
Librería Díaz de Santos Madrid (Spain)
imedina@diazdesantos.es facilmejor@gmail.com

That's true in some workloads, but with InnoDB you'll usually run into data
file fragmentation before filesystem fragmentation (unless it's a shared
system). This is especially true if your application runs a lot of updates
or deletes as random chunks of space will become free at different points
within the InnoDB data file. In a business intelligence application where
there's not much deleting this probably isn't a concern...

I think the best way to approach it may be to look at your average row size
and base your InnoDB and filesystem block sizes around that.

-Aaron

On Fri, May 9, 2008 at 12:43 AM, Ben Clewett <ben@roadrunner.uk.com> wrote:

>
>
> I would use as large a block size as you dare, especially with InnoDB.
> Makes reading and writing faster as custs down seek time as cuts down disk
> fragmenation and avoids block table reads. With MyIsam you have lots of
> files, but if you only have a few again might work well with a large block
> size.
>
> Also have a look at the stripe size of your raid system, might work well
> aligning them if you can. This URL also gives some tips for an ext3 file
> system on RAID, look for 'stride':
>
> http://insights.oetiker.ch/linux/raidoptimization.html
>
> Ben
>
> Iñigo Medina García wrote:
>
>> Hi friends,
>>
>> we're developing a new web application that works with a db around 1gb
>> and 30 tables.
>>
>> We work with linux, and I'm evaluating the benefit of making an specific
>> partition (ext3) for mysql in order to have a bigger Block Size. Block
>> Size by default in / partition is 4096.
>>
>> Do you think that could be good? Any experience?
>>
>> Iñigo
>>
>>
> ************************************************** ***********************
> This e-mail is confidential and may be legally privileged. It is intended
> solely for the use of the individual(s) to whom it is addressed. Any
> content in this message is not necessarily a view or statement from Road
> Tech Computer Systems Limited but is that of the individual sender. If
> you are not the intended recipient, be advised that you have received
> this e-mail in error and that any use, dissemination, forwarding,
> printing, or copying of this e-mail is strictly prohibited. We use
> reasonable endeavours to virus scan all e-mails leaving the company but
> no warranty is given that this e-mail and any attachments are virus free.
> You should undertake your own virus checking. The right to monitor e-mail
> communications through our networks is reserved by us
>
> Road Tech Computer Systems Ltd. Shenley Hall, Rectory Lane, Shenley,
> Radlett, Hertfordshire, WD7 9AN. - VAT Registration No GB 449 3582 17
> Registered in England No: 02017435, Registered Address: Charter Court,
> Midland Road, Hemel Hempstead, Hertfordshire, HP2 5GE.
> ************************************************** ***********************
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=aaronblew@gmail.com
>
>