From: Reindl Harald
Date: May 6 2011 11:50am
Subject: Re: Input needed...
List-Archive: http://lists.mysql.com/mysql/224963
Message-Id: <4DC3E07F.50308@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigA591C472F19E3DAF7224A51B"

--------------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/

iEYEARECAAYFAk3D4H8ACgkQhmBjz394Annl2ACfeP7cIHE7J1NQXuYDFQwwidz4
NSgAmweNn3qtVMC6QCPX5HxtO97aMmbg
=NGfQ
-----END PGP SIGNATURE-----

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