From: Reindl Harald
Date: February 20 2012 7:42am
Subject: Re: can anyone tell me the best my.cnf Configuration for dedicated
 MySQL DB server
List-Archive: http://lists.mysql.com/mysql/226819
Message-Id: <4F41F974.2070206@thelounge.net>
MIME-Version: 1.0
Content-Type: multipart/signed; micalg=pgp-sha1;
 protocol="application/pgp-signature";
 boundary="------------enigFA5DF07F59B3174D4DB66DE7"

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



Am 20.02.2012 06:57, schrieb Nayan Darekar:
> hi members,
>=20
> I want install, configure my 8GB Ram 4 core CPU hardware server for
> dedicated MySQL  DB Server with stable version of MySQL. So which versi=
on i
> should use and can anyone help me for best my.cnf Configuration.

there does no best exist and will never be!

* how much memory?
* what other processes are running on the machine?
* how much RAM will they cosume?

this is the base to calculate how much memory
is available for mysqld

so and now you need a application profile

* myisam / innodv / both
* many parallel connections
* how many different and how large results do you expect

______________________________________

this is one of TEN different production configs here
but be careful - with low memory it will blow away your
machine and "myisam_use_mmap" will cause instability
before MySQL 5.5.20

[--] Data in MyISAM tables: 635M (Tables: 4376)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 314

-------- Security Recommendations  --------------------------------------=
-----
[OK] All database users have passwords assigned

-------- Performance Metrics --------------------------------------------=
-----
[--] Up for: 7d 12h 1m 2s (20M q [31.984 qps], 954K conn, TX: 37B, RX: 4B=
)
[--] Reads / Writes: 67% / 33%
[--] Total buffers: 2.4G global + 3.2M per thread (200 max threads)
[OK] Maximum possible memory usage: 3.0G (37% of installed RAM)
[OK] Slow queries: 0% (5/20M)
[OK] Highest usage of available connections: 13% (27/200)
[OK] Key buffer size / total MyISAM indexes: 256.0M/130.8M
[OK] Key buffer hit rate: 99.8% (169M cached / 268K reads)
[OK] Query cache efficiency: 88.8% (14M cached / 16M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 175K sorts)
[!!] Joins performed without indexes: 3777
[OK] Temporary tables created on disk: 25% (61K on disk / 245K total)
[OK] Thread cache hit rate: 99% (27 created / 954K connections)
[OK] Table cache hit rate: 25% (4K open / 19K opened)
[OK] Open file limit used: 30% (9K/30K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)

----------------------------

socket                                  =3D /var/lib/mysql/mysql.sock
character-set-server                    =3D latin1
collation-server                        =3D latin1_german1_ci
default-time-zone                       =3D "Europe/Vienna"
default-storage-engine                  =3D myisam
lower_case_table_names                  =3D 1
port                                    =3D 3306
old_passwords                           =3D 0
local-infile                            =3D 0
thread_concurrency                      =3D 16

delay-key-write                         =3D ALL
concurrent_insert                       =3D 2
open-files-limit                        =3D 20000
myisam-recover                          =3D "FORCE"
myisam_use_mmap                         =3D 1

wait_timeout                            =3D 300
interactive_timeout                     =3D 300

max_allowed_packet                      =3D 200M
max_connections                         =3D 200
max_tmp_tables                          =3D 150
max_connect_errors                      =3D 250
max_delayed_threads                     =3D 32

flush_time                              =3D 0

query_cache_limit                       =3D 220K
query_cache_min_res_unit                =3D 2K
query_cache_size                        =3D 1536M
query_cache_type                        =3D 1

table_cache                             =3D 15000
thread_cache                            =3D 200
table_definition_cache                  =3D 768

tmp_table_size                          =3D 640M
max_heap_table_size                     =3D 640M

key_buffer_size                         =3D 256M
sort_buffer_size                        =3D 512K
myisam_sort_buffer_size                 =3D 5M
join_buffer_size                        =3D 2M
preload_buffer_size                     =3D 256K
read_buffer_size                        =3D 256K
read_rnd_buffer_size                    =3D 256K

large-pages
low-priority-updates
safe-user-create
skip-federated
skip-innodb
skip-name-resolve
skip-partition
skip-archive
skip-blackhole
skip-symbolic-links
slave_compressed_protocol
______________________________________


--------------enigFA5DF07F59B3174D4DB66DE7
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.12 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEUEARECAAYFAk9B+XQACgkQhmBjz394AnmG3wCVE5KVBYAmL/2tf/7Jd2uDZu2M
uACdHHsh44rss0Ea7ORhYJMuahq8LB8=
=PPCL
-----END PGP SIGNATURE-----

--------------enigFA5DF07F59B3174D4DB66DE7--