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.
Pages: 1
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.cnfafter 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 startThe below command will allow you to see all the mysql configs:
# mysqladmin variablesAdrian 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
Offline
I don't get that config when i run the command.
nano /etc/my.cnfThis 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.pidOffline
I don't get that config when i run the command.
nano /etc/my.cnfThis 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 nanoThen check the location of my.cnf:
# whereis my.cnfIt 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
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.pidLast edited by DuttyRock (2012-09-21 03:40:36)
Offline
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
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
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
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
@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
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
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
optimize table cvideo???
Offline
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
Pages: 1