(Jan 22) Release 0.9.8-19 with bug fixes available now. Run this command for update v-update-sys-vesta-all

Tuning tips for MySQL Topic is solved

Questions regarding the Database Server
MySQL, PostgreSQL, MariaDB, Percona Server, phpMyAdmin, phpPgAdmin
Forum rules
Before creating a new topic or reply on the forum you should fill out additional fields "Os" and "Web" in your profile section.
In case of violation, the topic can be closed or response from the support will not be received.
mephivio
Posts: 175
Joined: Thu Mar 27, 2014 7:35 am

Os: Debian 9x
Web: nginx + php-fpm

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: 458
Joined: Thu May 16, 2013 5:19 am

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
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: 376
Joined: Thu Nov 13, 2014 8:33 am
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: 7808
Joined: Fri Dec 26, 2014 2:23 pm
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: 7808
Joined: Fri Dec 26, 2014 2:23 pm
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 1 guest

cron