Tuning tips for MySQL Topic is solved

Questions regarding the Database Server
MySQL, PostgreSQL, MariaDB, Percona Server, phpMyAdmin, phpPgAdmin
mephivio
Posts: 161
Joined: Thu Mar 27, 2014 7:35 am
Location: France

Tuning tips for MySQL

Postby mephivio » Fri Jul 18, 2014 10:09 pm

Team,

Any tips for the tuning of MySQL ?
-> my.cnf

Thanks
JP

demian
Posts: 455
Joined: Thu May 16, 2013 5:19 am
Location: Russua, Germany, UK, Ireland, Tuvalu

Re: Tuning tips for MySQL

Postby demian » Sat Jul 19, 2014 8:30 pm

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

dustindauncey
Posts: 26
Joined: Sat Jul 26, 2014 7:32 am
Location: Canada
Contact:

Re: Tuning tips for MySQL

Postby dustindauncey » Sun Jul 27, 2014 8:15 am

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

bgg
Posts: 8
Joined: Sun May 17, 2015 1:01 am

Re: Tuning tips for MySQL

Postby bgg » Sun May 17, 2015 1:04 am

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

joem
Posts: 373
Joined: Thu Nov 13, 2014 8:33 am
Location: New York
Contact:

Re: Tuning tips for MySQL

Postby joem » Sun May 17, 2015 6:56 am

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

skurudo
VestaCP Team
Posts: 7798
Joined: Fri Dec 26, 2014 2:23 pm
Location: Moscow
Contact:

Re: Tuning tips for MySQL

Postby skurudo » Sun May 17, 2015 4:53 pm

bgg wrote:Can someone please suggest a best performance configuration/settings for mysql in the server?


Try to use mysqltuner to get best performance

alexJordan
Posts: 2
Joined: Thu Sep 28, 2017 10:01 am

Re: Tuning tips for MySQL

Postby alexJordan » Sat Sep 30, 2017 6:12 pm

Thank you Dustin.

skurudo
VestaCP Team
Posts: 7798
Joined: Fri Dec 26, 2014 2:23 pm
Location: Moscow
Contact:

Re: Tuning tips for MySQL  Topic is solved

Postby skurudo » Tue Oct 03, 2017 9:08 am

Closed, use tuner scripts -> viewtopic.php?f=14&t=14148
-> DigitalOcean competition - please, support us
-> fix for phpmyadmin - nice and sweet now


Return to “Database Server”



Who is online

Users browsing this forum: No registered users and 3 guests

cron