Page 1 of 1
Tuning tips for MySQL
Posted: Fri Jul 18, 2014 10:09 pm
by mephivio
Team,
Any tips for the tuning of MySQL ?
-> my.cnf
Thanks
JP
Re: Tuning tips for MySQL
Posted: Sat Jul 19, 2014 8:30 pm
by demian
need more date
1. CPU
2. size memory
3. size all database
4. size all index tables
3-4 give output script mysqltuner
if you give root access i can tuning you mysql faster. root password no need i used for ssl key for connect.
I give you my public ssl key, you put him in /root/.ssh/known_hosts. After tuning remove him for block access
Re: Tuning tips for MySQL
Posted: Sun Jul 27, 2014 8:15 am
by dustindauncey
mephivio wrote:Team,
Any tips for the tuning of MySQL ?
-> my.cnf
Thanks
JP
I have a file I'm using happily after some performance tuning. You might want to check this out as a base to work from. Feel free of course to tweak it to your own needs, but it's relatively optimized for a low memory system.
I have a 512 MB Digital Ocean VPS, FYI.
Code: Select all
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=20
max_user_connections=20
wait_timeout=30
interactive_timeout=50
slow-query-log =1
long_query_time=5
#log-queries-not-using-indexes
slow-query-log-file = /var/log/mysql-slow.log
skip-external-locking
key_buffer_size = 8M
max_allowed_packet = 16M
table_open_cache = 4k
sort_buffer_size = 512k
read_buffer_size = 512k
read_rnd_buffer_size = 512k
myisam_sort_buffer_size = 8M
thread_cache_size = 128
query_cache_size = 8M
query_cache_limit = 8M
tmp_table_size = 16M
max_heap_table_size = 16M
join_buffer_size = 512k
#innodb_use_native_aio = 0
innodb_file_per_table
innodb_buffer_pool_size = 8M
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Re: Tuning tips for MySQL
Posted: Sun May 17, 2015 1:04 am
by bgg
Hello, My VPS has 12GB RAM and 2 core I think
Can someone please suggest a best performance configuration/settings for mysql in the server?
This is my current my.cnf:
Code: Select all
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=2000
max_user_connections=1500
wait_timeout=100
interactive_timeout=100
max_allowed_packet=100M
long_query_time=5
connect_timeout=10
#log-queries-not-using-indexes
#log-slow-queries=/var/log/mysql/log-slow-queries.log
#innodb_use_native_aio = 0
innodb_file_per_table
innodb_buffer_pool_size = 64M
key_buffer_size=128M
query_cache_size=256M
query_cache_limit = 256M
thread_cache_size=128
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Can someone please suggest a best performance configuration/settings for mysql in the server?
Thanks a lot
Re: Tuning tips for MySQL
Posted: Sun May 17, 2015 6:56 am
by joem
bgg wrote:Hello, My VPS has 12GB RAM and 2 core I think
Can someone please suggest a best performance configuration/settings for mysql in the server?
This is my current my.cnf:
Code: Select all
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
max_connections=2000
max_user_connections=1500
wait_timeout=100
interactive_timeout=100
max_allowed_packet=100M
long_query_time=5
connect_timeout=10
#log-queries-not-using-indexes
#log-slow-queries=/var/log/mysql/log-slow-queries.log
#innodb_use_native_aio = 0
innodb_file_per_table
innodb_buffer_pool_size = 64M
key_buffer_size=128M
query_cache_size=256M
query_cache_limit = 256M
thread_cache_size=128
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
Can someone please suggest a best performance configuration/settings for mysql in the server?
Thanks a lot
Check out
http://forums.jaguarpc.com/dedicated-cl ... f-vps.html
Re: Tuning tips for MySQL
Posted: Sun May 17, 2015 4:53 pm
by skurudo
bgg wrote:
Can someone please suggest a best performance configuration/settings for mysql in the server?
Try to use mysqltuner to get best performance
Re: Tuning tips for MySQL
Posted: Sat Sep 30, 2017 6:12 pm
by alexJordan
Thank you Dustin.
Re: Tuning tips for MySQL
Posted: Tue Oct 03, 2017 9:08 am
by skurudo
Closed, use tuner scripts ->
viewtopic.php?f=14&t=14148