Input needed...

--20cf3040e53a3ab83504a28bbc33
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I have this stats: I have tuned some of it but I was wondering if someone
can give inputs regarding it:

*
__ Key ____________________________________________________________ _____
Buffer used 54.63M of 384.00M %Used: 14.23
Current 123.21M %Usage: 32.09
Write hit 99.97%
Read hit 100.00%

__ Questions ___________________________________________________________
Total 87.40M 18.4/s
QC Hits 50.63M 10.7/s %Total: 57.93
DMS 24.04M 5.1/s 27.50
Com_ 8.52M 1.8/s 9.75
COM_QUIT 4.24M 0.9/s 4.85
-Unknown 28.31k 0.0/s 0.03
Slow 10 s 9.21k 0.0/s 0.01 %DMS: 0.04 Log: OFF
DMS 24.04M 5.1/s 27.50
SELECT 21.98M 4.6/s 25.14 91.41
UPDATE 1.74M 0.4/s 1.99 7.24
INSERT 211.35k 0.0/s 0.24 0.88
DELETE 111.77k 0.0/s 0.13 0.46
REPLACE 0 0/s 0.00 0.00
Com_ 8.52M 1.8/s 9.75
set_option 4.26M 0.9/s 4.87
change_db 4.22M 0.9/s 4.83
show_table_ 9.33k 0.0/s 0.01

__ SELECT and Sort _____________________________________________________
Scan 838.37k 0.2/s %SELECT: 3.81
Range 2.12M 0.4/s 9.64
Full join 988 0.0/s 0.00
Range check 0 0/s 0.00
Full rng join 0 0/s 0.00
Sort scan 3.71M 0.8/s
Sort range 3.63M 0.8/s
Sort mrg pass 82.85k 0.0/s

__ Query Cache _________________________________________________________
Memory usage 3.75M of 32.00M %Used: 11.72
Block Fragmnt 28.39%
Hits 50.63M 10.7/s
Inserts 16.38M 3.4/s
Insrt:Prune 14.39:1 3.2/s
Hit:Insert 3.09:1

__ Table Locks _________________________________________________________
Waited 53.43k 0.0/s %Total: 0.17
Immediate 31.57M 6.6/s

__ Tables ____________________________________________________________ __
Open 512 of 512 %Cache: 100.00
Opened 3.02k 0.0/s

__ Connections _________________________________________________________
Max used 145 of 802 %Max: 18.08
Total 4.24M 0.9/s

__ Created Temp ________________________________________________________
Disk table 3.61M 0.8/s
Table 3.72M 0.8/s Size: 16.0M
File 165.71k 0.0/s

__ Threads ____________________________________________________________ _
Running 69 of 71
Cached 0 of 8 %Hit: 99.02
Created 41.74k 0.0/s
Slow 0 0/s

__ Aborted ____________________________________________________________ _
Clients 6.48k 0.0/s
Connects 25.00k 0.0/s

__ Bytes ____________________________________________________________ ___
Sent 1.18T 247.9k/s
Received 15.27G 3.2k/s*

--20cf3040e53a3ab83504a28bbc33--

Re: Input needed...

--------------enigE300D265076886D429E7DD9B
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

please post your my.cnf settings becuase stats are useless without
knowing the configuration and without knowing your hardware (memory!)
nobody can say what buffers are making sense

i would use "mysqltuner.pl" because it shows in the most cases good advi=
ses

Am 05.05.2011 20:42, schrieb J M:
> Hi,
>=20
> I have this stats: I have tuned some of it but I was wondering if so=
meone
> can give inputs regarding it:
>=20
> *
> __ Key ____________________________________________________________ ____=
_
> Buffer used 54.63M of 384.00M %Used: 14.23
> Current 123.21M %Usage: 32.09
> Write hit 99.97%
> Read hit 100.00%
>=20
> __ Questions __________________________________________________________=
_
> Total 87.40M 18.4/s
> QC Hits 50.63M 10.7/s %Total: 57.93
> DMS 24.04M 5.1/s 27.50
> Com_ 8.52M 1.8/s 9.75
> COM_QUIT 4.24M 0.9/s 4.85
> -Unknown 28.31k 0.0/s 0.03
> Slow 10 s 9.21k 0.0/s 0.01 %DMS: 0.04 Log: OFF=

> DMS 24.04M 5.1/s 27.50
> SELECT 21.98M 4.6/s 25.14 91.41
> UPDATE 1.74M 0.4/s 1.99 7.24
> INSERT 211.35k 0.0/s 0.24 0.88
> DELETE 111.77k 0.0/s 0.13 0.46
> REPLACE 0 0/s 0.00 0.00
> Com_ 8.52M 1.8/s 9.75
> set_option 4.26M 0.9/s 4.87
> change_db 4.22M 0.9/s 4.83
> show_table_ 9.33k 0.0/s 0.01
>=20
> __ SELECT and Sort ____________________________________________________=
_
> Scan 838.37k 0.2/s %SELECT: 3.81
> Range 2.12M 0.4/s 9.64
> Full join 988 0.0/s 0.00
> Range check 0 0/s 0.00
> Full rng join 0 0/s 0.00
> Sort scan 3.71M 0.8/s
> Sort range 3.63M 0.8/s
> Sort mrg pass 82.85k 0.0/s
>=20
> __ Query Cache ________________________________________________________=
_
> Memory usage 3.75M of 32.00M %Used: 11.72
> Block Fragmnt 28.39%
> Hits 50.63M 10.7/s
> Inserts 16.38M 3.4/s
> Insrt:Prune 14.39:1 3.2/s
> Hit:Insert 3.09:1
>=20
> __ Table Locks ________________________________________________________=
_
> Waited 53.43k 0.0/s %Total: 0.17
> Immediate 31.57M 6.6/s
>=20
> __ Tables ____________________________________________________________ _=
_
> Open 512 of 512 %Cache: 100.00
> Opened 3.02k 0.0/s
>=20
> __ Connections ________________________________________________________=
_
> Max used 145 of 802 %Max: 18.08
> Total 4.24M 0.9/s
>=20
> __ Created Temp _______________________________________________________=
_
> Disk table 3.61M 0.8/s
> Table 3.72M 0.8/s Size: 16.0M
> File 165.71k 0.0/s
>=20
> __ Threads ____________________________________________________________ =
_
> Running 69 of 71
> Cached 0 of 8 %Hit: 99.02
> Created 41.74k 0.0/s
> Slow 0 0/s
>=20
> __ Aborted ____________________________________________________________ =
_
> Clients 6.48k 0.0/s
> Connects 25.00k 0.0/s
>=20
> __ Bytes ____________________________________________________________ __=
_
> Sent 1.18T 247.9k/s
> Received 15.27G 3.2k/s*
>=20

--=20

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm


--------------enigE300D265076886D429E7DD9B
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3C8IUACgkQhmBjz394AnkimwCfaRaAYfXXO9R7Cini3fXt 52P1
1AcAninnxWuiZXGq/dOQEXZPj4qOgMPd
=QdXp
-----END PGP SIGNATURE-----

--------------enigE300D265076886D429E7DD9B--

Re: Input needed...

--20cf303f6ab64c8ef904a299c267
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Config:

Running on 8G Server.. Currently utilizing 7G.. running only mysql..

[client]
port =3D 3306
socket =3D /var/lib/mysql/mysql.sock


[mysqld]
port =3D 3306
socket =3D /var/lib/mysql/mysql.sock
skip-locking
key_buffer_size =3D 384M
max_allowed_packet =3D 1M
table_open_cache =3D 512
sort_buffer_size =3D 2M
read_buffer_size =3D 2M
read_rnd_buffer_size =3D 8M
myisam_sort_buffer_size =3D 64M
thread_cache_size =3D 8
query_cache_size =3D 32M
thread_concurrency =3D 8
max_connections =3D 802
wait_timeout =3D 15


log-bin=3Dmysql-bin

server-id =3D 1



innodb_data_home_dir =3D /var/lib/mysql/
innodb_data_file_path =3D ibdata1:2000M;ibdata2:10M:autoextend
innodb_log_group_home_dir =3D /var/lib/mysql/
innodb_buffer_pool_size =3D 384M
innodb_additional_mem_pool_size =3D 20M
innodb_log_file_size =3D 100M
innodb_log_buffer_size =3D 8M
innodb_flush_log_at_trx_commit =3D 1
innodb_lock_wait_timeout =3D 50

[mysqldump]
quick
max_allowed_packet =3D 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size =3D 256M
sort_buffer_size =3D 256M
read_buffer =3D 2M
write_buffer =3D 2M

[mysqlhotcopy]
interactive-timeout

On Thu, May 5, 2011 at 2:46 PM, Reindl Harald wrote=
:

> please post your my.cnf settings becuase stats are useless without
> knowing the configuration and without knowing your hardware (memory!)
> nobody can say what buffers are making sense
>
> i would use "mysqltuner.pl" because it shows in the most cases good
> advises
>
> Am 05.05.2011 20:42, schrieb J M:
> > Hi,
> >
> > I have this stats: I have tuned some of it but I was wondering if
> someone
> > can give inputs regarding it:
> >
> > *
> > __ Key ____________________________________________________________ ____=
_
> > Buffer used 54.63M of 384.00M %Used: 14.23
> > Current 123.21M %Usage: 32.09
> > Write hit 99.97%
> > Read hit 100.00%
> >
> > __ Questions __________________________________________________________=
_
> > Total 87.40M 18.4/s
> > QC Hits 50.63M 10.7/s %Total: 57.93
> > DMS 24.04M 5.1/s 27.50
> > Com_ 8.52M 1.8/s 9.75
> > COM_QUIT 4.24M 0.9/s 4.85
> > -Unknown 28.31k 0.0/s 0.03
> > Slow 10 s 9.21k 0.0/s 0.01 %DMS: 0.04 Log: OFF
> > DMS 24.04M 5.1/s 27.50
> > SELECT 21.98M 4.6/s 25.14 91.41
> > UPDATE 1.74M 0.4/s 1.99 7.24
> > INSERT 211.35k 0.0/s 0.24 0.88
> > DELETE 111.77k 0.0/s 0.13 0.46
> > REPLACE 0 0/s 0.00 0.00
> > Com_ 8.52M 1.8/s 9.75
> > set_option 4.26M 0.9/s 4.87
> > change_db 4.22M 0.9/s 4.83
> > show_table_ 9.33k 0.0/s 0.01
> >
> > __ SELECT and Sort ____________________________________________________=
_
> > Scan 838.37k 0.2/s %SELECT: 3.81
> > Range 2.12M 0.4/s 9.64
> > Full join 988 0.0/s 0.00
> > Range check 0 0/s 0.00
> > Full rng join 0 0/s 0.00
> > Sort scan 3.71M 0.8/s
> > Sort range 3.63M 0.8/s
> > Sort mrg pass 82.85k 0.0/s
> >
> > __ Query Cache ________________________________________________________=
_
> > Memory usage 3.75M of 32.00M %Used: 11.72
> > Block Fragmnt 28.39%
> > Hits 50.63M 10.7/s
> > Inserts 16.38M 3.4/s
> > Insrt:Prune 14.39:1 3.2/s
> > Hit:Insert 3.09:1
> >
> > __ Table Locks ________________________________________________________=
_
> > Waited 53.43k 0.0/s %Total: 0.17
> > Immediate 31.57M 6.6/s
> >
> > __ Tables ____________________________________________________________ _=
_
> > Open 512 of 512 %Cache: 100.00
> > Opened 3.02k 0.0/s
> >
> > __ Connections ________________________________________________________=
_
> > Max used 145 of 802 %Max: 18.08
> > Total 4.24M 0.9/s
> >
> > __ Created Temp _______________________________________________________=
_
> > Disk table 3.61M 0.8/s
> > Table 3.72M 0.8/s Size: 16.0M
> > File 165.71k 0.0/s
> >
> > __ Threads ____________________________________________________________ =
_
> > Running 69 of 71
> > Cached 0 of 8 %Hit: 99.02
> > Created 41.74k 0.0/s
> > Slow 0 0/s
> >
> > __ Aborted ____________________________________________________________ =
_
> > Clients 6.48k 0.0/s
> > Connects 25.00k 0.0/s
> >
> > __ Bytes ____________________________________________________________ __=
_
> > Sent 1.18T 247.9k/s
> > Received 15.27G 3.2k/s*
> >
>
> --
>
> Mit besten Grüßen, Reindl Harald
> the lounge interactive design GmbH
> A-1060 Vienna, Hofmühlgasse 17
> CTO / software-development / cms-solutions
> p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
> icq: 154546673, http://www.thelounge.net/
>
> http://www.thelounge.net/signature.asc.what.htm
>
>

--20cf303f6ab64c8ef904a299c267--

RE: Input needed...

--_6514b32e-34b7-4a3f-9e2d-304817265815_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


Harald-

could you re-iterate the details of the malady or error you are experiencin=
g?

danke=2C
Martin=20
______________________________________________=20
Verzicht und Vertraulichkeitanmerkung
=20
Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaeng=
er sein=2C so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiter=
leitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient l=
ediglich dem Austausch von Informationen und entfaltet keine rechtliche Bin=
dungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen w=
ir keine Haftung fuer den Inhalt uebernehmen.






> Date: Fri=2C 6 May 2011 07:26:20 -0400
> Subject: Re: Input needed...
> From: jerome.m@gmail.com
> To: h.reindl@thelounge.net
> CC: mysql@lists.mysql.com
>=20
> Config:
>=20
> Running on 8G Server.. Currently utilizing 7G.. running only mysql..
>=20
> [client]
> port =3D 3306
> socket =3D /var/lib/mysql/mysql.sock
>=20
>=20
> [mysqld]
> port =3D 3306
> socket =3D /var/lib/mysql/mysql.sock
> skip-locking
> key_buffer_size =3D 384M
> max_allowed_packet =3D 1M
> table_open_cache =3D 512
> sort_buffer_size =3D 2M
> read_buffer_size =3D 2M
> read_rnd_buffer_size =3D 8M
> myisam_sort_buffer_size =3D 64M
> thread_cache_size =3D 8
> query_cache_size =3D 32M
> thread_concurrency =3D 8
> max_connections =3D 802
> wait_timeout =3D 15
>=20
>=20
> log-bin=3Dmysql-bin
>=20
> server-id =3D 1
>=20
>=20
>=20
> innodb_data_home_dir =3D /var/lib/mysql/
> innodb_data_file_path =3D ibdata1:2000M=3Bibdata2:10M:autoextend
> innodb_log_group_home_dir =3D /var/lib/mysql/
> innodb_buffer_pool_size =3D 384M
> innodb_additional_mem_pool_size =3D 20M
> innodb_log_file_size =3D 100M
> innodb_log_buffer_size =3D 8M
> innodb_flush_log_at_trx_commit =3D 1
> innodb_lock_wait_timeout =3D 50
>=20
> [mysqldump]
> quick
> max_allowed_packet =3D 16M
>=20
> [mysql]
> no-auto-rehash
>=20
> [myisamchk]
> key_buffer_size =3D 256M
> sort_buffer_size =3D 256M
> read_buffer =3D 2M
> write_buffer =3D 2M
>=20
> [mysqlhotcopy]
> interactive-timeout
>=20
> On Thu=2C May 5=2C 2011 at 2:46 PM=2C Reindl Harald et>wrote:
>=20
> > please post your my.cnf settings becuase stats are useless without
> > knowing the configuration and without knowing your hardware (memory!)
> > nobody can say what buffers are making sense
> >
> > i would use "mysqltuner.pl" because it shows in the most cases good
> > advises
> >
> > Am 05.05.2011 20:42=2C schrieb J M:
> > > Hi=2C
> > >
> > > I have this stats: I have tuned some of it but I was wondering if
> > someone
> > > can give inputs regarding it:
> > >
> > > *
> > > __ Key ____________________________________________________________ __=
___
> > > Buffer used 54.63M of 384.00M %Used: 14.23
> > > Current 123.21M %Usage: 32.09
> > > Write hit 99.97%
> > > Read hit 100.00%
> > >
> > > __ Questions ________________________________________________________=
___
> > > Total 87.40M 18.4/s
> > > QC Hits 50.63M 10.7/s %Total: 57.93
> > > DMS 24.04M 5.1/s 27.50
> > > Com_ 8.52M 1.8/s 9.75
> > > COM_QUIT 4.24M 0.9/s 4.85
> > > -Unknown 28.31k 0.0/s 0.03
> > > Slow 10 s 9.21k 0.0/s 0.01 %DMS: 0.04 Log: O=
FF
> > > DMS 24.04M 5.1/s 27.50
> > > SELECT 21.98M 4.6/s 25.14 91.41
> > > UPDATE 1.74M 0.4/s 1.99 7.24
> > > INSERT 211.35k 0.0/s 0.24 0.88
> > > DELETE 111.77k 0.0/s 0.13 0.46
> > > REPLACE 0 0/s 0.00 0.00
> > > Com_ 8.52M 1.8/s 9.75
> > > set_option 4.26M 0.9/s 4.87
> > > change_db 4.22M 0.9/s 4.83
> > > show_table_ 9.33k 0.0/s 0.01
> > >
> > > __ SELECT and Sort __________________________________________________=
___
> > > Scan 838.37k 0.2/s %SELECT: 3.81
> > > Range 2.12M 0.4/s 9.64
> > > Full join 988 0.0/s 0.00
> > > Range check 0 0/s 0.00
> > > Full rng join 0 0/s 0.00
> > > Sort scan 3.71M 0.8/s
> > > Sort range 3.63M 0.8/s
> > > Sort mrg pass 82.85k 0.0/s
> > >
> > > __ Query Cache ______________________________________________________=
___
> > > Memory usage 3.75M of 32.00M %Used: 11.72
> > > Block Fragmnt 28.39%
> > > Hits 50.63M 10.7/s
> > > Inserts 16.38M 3.4/s
> > > Insrt:Prune 14.39:1 3.2/s
> > > Hit:Insert 3.09:1
> > >
> > > __ Table Locks ______________________________________________________=
___
> > > Waited 53.43k 0.0/s %Total: 0.17
> > > Immediate 31.57M 6.6/s
> > >
> > > __ Tables ___________________________________________________________=
___
> > > Open 512 of 512 %Cache: 100.00
> > > Opened 3.02k 0.0/s
> > >
> > > __ Connections ______________________________________________________=
___
> > > Max used 145 of 802 %Max: 18.08
> > > Total 4.24M 0.9/s
> > >
> > > __ Created Temp _____________________________________________________=
___
> > > Disk table 3.61M 0.8/s
> > > Table 3.72M 0.8/s Size: 16.0M
> > > File 165.71k 0.0/s
> > >
> > > __ Threads __________________________________________________________=
___
> > > Running 69 of 71
> > > Cached 0 of 8 %Hit: 99.02
> > > Created 41.74k 0.0/s
> > > Slow 0 0/s
> > >
> > > __ Aborted __________________________________________________________=
___
> > > Clients 6.48k 0.0/s
> > > Connects 25.00k 0.0/s
> > >
> > > __ Bytes ____________________________________________________________ =
___
> > > Sent 1.18T 247.9k/s
> > > Received 15.27G 3.2k/s*
> > >
> >
> > --
> >
> > Mit besten Grüßen=2C Reindl Harald
> > the lounge interactive design GmbH
> > A-1060 Vienna=2C Hofmühlgasse 17
> > CTO / software-development / cms-solutions
> > p: +43 (1) 595 3999 33=2C m: +43 (676) 40 221 40
> > icq: 154546673=2C http://www.thelounge.net/
> >
> > http://www.thelounge.net/signature.asc.what.htm
> >
> >
=

--_6514b32e-34b7-4a3f-9e2d-304817265815_--

Re: Input needed...

--------------enigA591C472F19E3DAF7224A51B
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Am 06.05.2011 13:26, schrieb J M:
> Config:
>=20
> Running on 8G Server.. Currently utilizing 7G.. running only mysql..

> [mysqld]
> port =3D 3306
> socket =3D /var/lib/mysql/mysql.sock
> skip-locking
> key_buffer_size =3D 384M

depends on the size of all yur keys

> max_allowed_packet =3D 1M

is verly low if you import a bigger dump or BLOB

> table_open_cache =3D 512

> sort_buffer_size =3D 2M

512K should be enough, this is per connection and does not help most case=
s

> read_buffer_size =3D 2M
> read_rnd_buffer_size =3D 8M

try a becnhmark with both lowered to 256k
this could maybe much faster in real life because lower memory-allocation=


> myisam_sort_buffer_size =3D 64M

should be verified by a "repair table" on your biggest myisam-table
because iz hurts if it would get corrupt later and a repair fails because=

to low value

> thread_cache_size =3D 8

should be as high as the most time connections
we allow 200 connections on the webserver and thread cache is also 200

> query_cache_size =3D 32M

hm, this can be too low
reduce some too high buffers and give the memory to the cache
we are using 1.5 GB query_cache_size resuling in 500.000 queries
in the cache some days after start without preuns

> thread_concurrency =3D 8

with mysql >=3D 5.5 we use 16 on a 5-core-VM

> max_connections =3D 802

this can be dangerous with per connection-buffers, see calculation below

> wait_timeout =3D 15

read_rnd_buffer_size 8M x max_connections 802 =3D 6.416 MB
sort_buffer_size 2M x max_connections 802 =3D 1604 MB
be aware of to big per-connection-buffers!

> innodb_data_home_dir =3D /var/lib/mysql/
> innodb_data_file_path =3D ibdata1:2000M;ibdata2:10M:autoextend

bad - you get one flat storage-pool which will grow
and never can be rudced with "optimize table"

innodb_file_per_table =3D 1

> innodb_buffer_pool_size =3D 384M

if you are using innodb highly you should tune htis as high
as possible, perfect would be as big as all innodb-tables

> innodb_log_file_size =3D 100M

on heavy writes maybe too small and not easy to change in production


--------------enigA591C472F19E3DAF7224A51B
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: OpenPGP digital signature
Content-Disposition: attachment; filename="signature.asc"

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Fedora - http://enigmail.mozdev.org/

iEYEARECAAYFAk3D4H8ACgkQhmBjz394Annl2ACfeP7cIHE7J1NQXuYDFQww idz4
NSgAmweNn3qtVMC6QCPX5HxtO97aMmbg
=NGfQ
-----END PGP SIGNATURE-----

--------------enigA591C472F19E3DAF7224A51B--

RE: Input needed...

--_c2301347-39fa-42c5-93b3-90ae6a325b8a_
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable


> From: h.reindl@thelounge.net
> To: mysql@lists.mysql.com
> Subject: Re: Input needed...
>=20
> Am 06.05.2011 13:26=2C schrieb J M:
> > Config:
> >=20
> > Running on 8G Server.. Currently utilizing 7G.. running only mysql..
>=20
> > [mysqld]
> > innodb_data_home_dir =3D /var/lib/mysql/
> > innodb_data_file_path =3D ibdata1:2000M=3Bibdata2:10M:autoextend
>=20
> bad - you get one flat storage-pool which will grow
> and never can be rudced with "optimize table"
MG>so the datafile could never be reduced with OPTIMIZE TABLE
MG>http://www.pythian.com/news/1067/difference-between-innod b_data_file_pat=
h-and-innodb_file_per_table/
MG>would you suggest using innodb_file_per_table for more granular control?
MG>is it worth the effort to export the database and reimport the data into=
new db using innodb_file_per_table enabled in my.cnf?
MG>http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters. html#sysvar_inn=
odb_file_per_table
>=20
danke=2C
Martin
=

--_c2301347-39fa-42c5-93b3-90ae6a325b8a_--

Re: Input needed...

Hi,

My 2 cents about this inline.

Peter Boros

On 5/6/11 1:50 PM, Reindl Harald wrote:
> Am 06.05.2011 13:26, schrieb J M:
>> Config:
>>
>> Running on 8G Server.. Currently utilizing 7G.. running only mysql..
>
>> [mysqld]
>> port = 3306
>> socket = /var/lib/mysql/mysql.sock
>> skip-locking
>> key_buffer_size = 384M
>
> depends on the size of all yur keys
>
>> max_allowed_packet = 1M
>
> is verly low if you import a bigger dump or BLOB
>
>> table_open_cache = 512
>> sort_buffer_size = 2M
>
> 512K should be enough, this is per connection and does not help most cases
>
>> read_buffer_size = 2M
>> read_rnd_buffer_size = 8M
>
> try a becnhmark with both lowered to 256k
> this could maybe much faster in real life because lower memory-allocation
>
>> myisam_sort_buffer_size = 64M
>
> should be verified by a "repair table" on your biggest myisam-table
> because iz hurts if it would get corrupt later and a repair fails because
> to low value
>
>> thread_cache_size = 8
>
> should be as high as the most time connections
> we allow 200 connections on the webserver and thread cache is also 200
>
>> query_cache_size = 32M
>
> hm, this can be too low
> reduce some too high buffers and give the memory to the cache
> we are using 1.5 GB query_cache_size resuling in 500.000 queries
> in the cache some days after start without preuns
For high concurrency environments query cache can actually hurt because
of the coarse invalidation. Oprofiling the system can show you if you
spend too much time waiting for the query cache mutex.
>> thread_concurrency = 8
>
> with mysql>= 5.5 we use 16 on a 5-core-VM
This is a Solaris only parameter. I don't understand why people are
setting this on Linux.
>> max_connections = 802
>
> this can be dangerous with per connection-buffers, see calculation below
>
>> wait_timeout = 15
>
> read_rnd_buffer_size 8M x max_connections 802 = 6.416 MB
> sort_buffer_size 2M x max_connections 802 = 1604 MB
> be aware of to big per-connection-buffers!
>
>> innodb_data_home_dir = /var/lib/mysql/
>> innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
>
> bad - you get one flat storage-pool which will grow
> and never can be rudced with "optimize table"
>
> innodb_file_per_table = 1
>
>> innodb_buffer_pool_size = 384M
>
> if you are using innodb highly you should tune htis as high
> as possible, perfect would be as big as all innodb-tables
>
>> innodb_log_file_size = 100M
>
> on heavy writes maybe too small and not easy to change in production
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org