Adult Script Pro Community Forums

The forum is here for legacy reasons. No new posts will be created. User registration is disabled! If you have any questions, please email us or check https://www.adultscriptpro.com for more details!

You are not logged in.

#1 2012-09-20 18:43:05

Mitomind
Member
Registered: 2012-06-27
Posts: 166

MySQL Database Optimization

I've found an interesting article on MySql Database Optimization. 

You can find the whole article here:  http://mysqldatabaseadministration.blog … guide.html

You must have access to a program like PuTTy to access the shell and you need to modify a file called /etc/my.cnf - we are going to do this by typing

nano /etc/my.cnf

after the

#

when you are logged into the shell account using PuTTy.

Note: using "nano" is like using a text editor.  Copy and paste the lines into the nano editor, then type control+x to save the file (it will ask you to type 'Y' to verify that you want to over-write the file.

This is what you need to add to /etc/my.cnf :

Decrease the value of connect_timeout: By default, the wait_timeout value is 28800. Unless you want MySQL to keep waiting for 28800 seconds (480 minutes or 8 hours), please decrease its value according to your application needs. On the test server, I have the wait_timeout set to 60.
wait_timeout=60

Increase connect_timeout from 5 to 10
connect_timeout=10

Decrease interactive_timeout from 28800 to 100
interactive_timeout=120

Increase join_buffer_size from 131072 to 1M
join_buffer_size=1M

Increase query_cache_size from 0 to 128M
query_cache_size=128M

Increase query cache limit from 1048576 to 2M
query_cache_limit=2M

Increase max allowed packet size from 1M to 16M
max_allowed_packet=16M

Increase table cache cache from 256 to 1024
table_cache=1024

Increase sort buffer size from 1M
sort_buffer_size=2M

Increase read buffer size from 1M
read_buffer_size=2M

Increase read_rnd_buffer_size to 4M
read_rnd_buffer_size=4M

Other settings

sort_buffer_size=8M
key_buffer = 256M
key_buffer_size=64M

After you make the changes you need to restart the MySQL service so that the changes will take effect:

# /etc/init.d/mysqld start

The below command will allow you to see all the mysql configs:

# mysqladmin variables

Adrian may have some additions to this.  The above settings I'm using on my server and queries are really fast.  It's important to note, my production server isn't running ASP... ASP is on my local development server.  So, I haven't actually used these settings with a running copy of ASP.  But the sites that I am running have become very fast.

Have fun!

Last edited by Mitomind (2012-09-20 18:52:47)

Offline

#2 2012-09-20 19:23:45

ptsguy
Member
Registered: 2012-08-06
Posts: 135

Re: MySQL Database Optimization

good post...

Offline

#3 2012-09-21 03:13:00

DuttyRock
Member
Registered: 2011-11-05
Posts: 740

Re: MySQL Database Optimization

I don't get that config when i run the command.

nano /etc/my.cnf

This is what i get.

[mysqld]
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Offline

#4 2012-09-21 03:20:30

Mitomind
Member
Registered: 2012-06-27
Posts: 166

Re: MySQL Database Optimization

DuttyRock wrote:

I don't get that config when i run the command.

nano /etc/my.cnf

This is what i get.

[mysqld]
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Check to make sure you have nano:

# whereis nano

Then check the location of my.cnf:

# whereis my.cnf

It also might be possible that you aren't allowed to modify the file... if you are on shared hosting.

Most likely you just have to find it in Fedora.  I'm running CentOS and it's located in etc/my.cnf.

Last edited by Mitomind (2012-09-21 03:21:03)

Offline

#5 2012-09-21 03:38:24

DuttyRock
Member
Registered: 2011-11-05
Posts: 740

Re: MySQL Database Optimization

i have nano, i mess with nano all day everyday Lol... but for real. Thats what i get... im on a dedicated server with root access.

[root@xxxx ~]#  whereis my.cnf
my: /etc/my.cnf
[root@xxxx ~]# nano /etc/my.cnf
  GNU nano 1.3.12                                       File: /etc/my.cnf

[mysqld]
# Settings user and group are ignored when systemd is used (fedora >= 15).
# If you need to run mysqld under different user or group,
# customize your systemd unit file for mysqld according to the
# instructions in http://fedoraproject.org/wiki/Systemd
user=mysql

datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Semisynchronous Replication
# http://dev.mysql.com/doc/refman/5.5/en/replication-semisync.html
# uncomment next line on MASTER
;plugin-load=rpl_semi_sync_master=semisync_master.so
# uncomment next line on SLAVE
;plugin-load=rpl_semi_sync_slave=semisync_slave.so

# Others options for Semisynchronous Replication
;rpl_semi_sync_master_enabled=1
;rpl_semi_sync_master_timeout=10
;rpl_semi_sync_slave_enabled=1

# http://dev.mysql.com/doc/refman/5.5/en/performance-schema.html
;performance_schema


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

Last edited by DuttyRock (2012-09-21 03:40:36)

Offline

#6 2012-09-21 03:46:49

Mitomind
Member
Registered: 2012-06-27
Posts: 166

Re: MySQL Database Optimization

DuttyRock wrote:

i have nano, i mess with nano all day everyday Lol... but for real. Thats what i get... im on a dedicated server with root access.

Ok.  That might be the file you need to modify.  Just add one of the lines to your file above and check the config with the command in my original post and see if it reflected your addition to the file (make sure to restart the mysql service after you save). 

When I opened the my.cnf on my system it only had three lines of code.  Normal logic would tell someone that when they open the file it would list all the variables that can/need to be adjusted... kinda like a php.ini file... it already has all the variables, you just need to adjust them.  The my.cnf is the opposite.  What you want to adjust you have to add.  Otherwise, MySQL will just use the default values it stores in another file.  The my.cnf over-rides default values, but you need to add those values to the file first.

Your distro is different from mine yours == fedora mine == centos so there might be some minor differences.  If you can't get it to work google "fedora adjusting my.cnf".

Last edited by Mitomind (2012-09-21 03:48:40)

Offline

#7 2013-03-27 07:41:41

DuttyRock
Member
Registered: 2011-11-05
Posts: 740

Re: MySQL Database Optimization

I don't think these settings are for tube sites, i know i had someone set those in the my.cnf and i kept getting 502 error. He had to increase the time out he said.

Offline

#8 2013-04-15 11:21:03

Jadmanx
Member
Registered: 2012-05-25
Posts: 128

Re: MySQL Database Optimization

i use the mysql tuning script tuning-primer.sh to found the best mysql settings but my cpu always over 700% in use...
some one whant to share the best settings for tube sites ?

Offline

#9 2013-06-11 12:56:44

Vasile
Member
Registered: 2013-06-06
Posts: 7

Re: MySQL Database Optimization

Jadmanx wrote:

i use the mysql tuning script tuning-primer.sh to found the best mysql settings but my cpu always over 700% in use...
some one whant to share the best settings for tube sites ?

Try to use https://tools.percona.com + apc or memcached.

Offline

#10 2013-06-11 14:04:46

symtab
Administrator
Registered: 2010-08-23
Posts: 7,501
Website

Re: MySQL Database Optimization

@Jadmax: do you have video views enabled in Admin -> Video -> Config -> Advanced? Also please enable slow query log and tell me if there are any slow queries. There shouldnt be any slow queries.


Adult Scripts: Adult Script Pro - Adult Search Script
Adult Advertising/Traffic: Plug Rush - EXOClick - PopAds

Offline

#11 2013-10-30 16:28:50

Jadmanx
Member
Registered: 2012-05-25
Posts: 128

Re: MySQL Database Optimization

i can not disable video views, i have disable but i see that it set views...

Query    2    Waiting for table level lock    UPDATE cavideo SET total_views = total_views+1, vie

Offline

#12 2013-10-30 18:41:57

symtab
Administrator
Registered: 2010-08-23
Posts: 7,501
Website

Re: MySQL Database Optimization

Is that query giving you problems? Run optimize table cvideo. That query should run instantly.


Adult Scripts: Adult Script Pro - Adult Search Script
Adult Advertising/Traffic: Plug Rush - EXOClick - PopAds

Offline

#13 2013-10-30 20:05:30

Jadmanx
Member
Registered: 2012-05-25
Posts: 128

Re: MySQL Database Optimization

optimize table cvideo???

Offline

#14 2013-10-31 07:18:07

symtab
Administrator
Registered: 2010-08-23
Posts: 7,501
Website

Re: MySQL Database Optimization

Yes, from the mysql console try and run that query.


Adult Scripts: Adult Script Pro - Adult Search Script
Adult Advertising/Traffic: Plug Rush - EXOClick - PopAds

Offline

Board footer

Powered by FluxBB