very poor db performance

@soaringeagle
8 years ago
3,304 posts

yikes
note tried using mysql tuner but what i add to /etc/my.cnf doesnt seem to help
the guy that helped reconfig my server i think has a diferent my.cnf somewhere else that icant find
and he no longer replies to messages
ps i was offered a db server on a separate server
would offloading db operations to a diferent server be a good idea will it help

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
updated by @soaringeagle: 05/05/17 12:15:03AM

@michael
8 years ago
7,772 posts

This is the guide I follow when setting up my servers:

Guides: "Mysql Optimizations for larger servers"
https://www.jamroom.net/brian/documentation/guides/1265/optional-mysql-optimizations-for-larger-servers

under
ACP -> MODULES -> CORE -> CORE -> TOOLS -> SYSTEM CHECK -> (php version number)

will take you to your phpinfo for your server:
http://yoursite.com/core/phpinfo

that might have info to help figure out where your config file is for mysql.

@soaringeagle
8 years ago
3,304 posts

https://www.dreadlockssite.com/phpinfo.php
dont seemy.cnf in there
i used your guide as a start long ago then tweaked from there

my.cnf

[mysqld]
bind-address = 127.0.0.1
skip_name_resolve
local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

key-buffer-size                = 1024M
myisam-recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 128M
wait_timeout                   = 300
max-connect-errors             = 1000000
table_cache = 1024
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M

# DATA STORAGE #
datadir                        = /var/lib/mysql/

# BINARY LOGGING #
#log-bin                        = /var/lib/mysql/mysql-bin
#expire-logs-days               = 1
#sync-binlog                    = 1

# CACHES AND LIMITS #
tmp-table-size                 = 128M
max-heap-table-size            = 128M
query_cache_size               = 128M
query_cache_limit              = 2M
query_cache_type               = 0
join_buffer_size               = 4M
max-connections                = 160
thread-cache-size              = 160
open-files-limit               = 65535
table-definition-cache         = 8096
table-open-cache               = 8096
optimizer_search_depth         = 1


# INNODB #
innodb-flush-method            = O_DIRECT
innodb-log-files-in-group      = 2
innodb-log-file-size           = 2G
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table          = 1
innodb-buffer-pool-size        = 10G
innodb_buffer_pool_instances   = 10
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

mysqltuner.pl recomendations

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Run OPTIMIZE TABLE to defragment tables for better performance
OPTIMIZE TABLE greentec_jam.jr_jrcore_cache; -- can free 1180 MB
Total freed space after theses OPTIMIZE TABLE : 1180 Mb
Restrict Host for user@% to user@SpecificDNSorIp
MySQL started within last 24 hours - recommendations may be inaccurate
Increasing the query_cache size over 128M may reduce performance
Variables to adjust:
query_cache_type (=0)
query_cache_size (> 128M) [see warning above]
innodb_log_file_size should be equals to 1/4 of buffer pool size (=2G) if possible.

note that i already set these in my.cnf but they dont seem to be taking

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities

@soaringeagle
8 years ago
3,304 posts

ps still cant get core on freedomswings.org to update either
if i gave ya my server ip can you check the jamroom.net error logs and see if theres a reason?
i did get slow transfers for some reason but the traceroute from the server was this

raceroute jamroom.net
traceroute to jamroom.net (192.155.80.32), 30 hops max, 60 byte packets
 1  gw-131-129.phlapalo.quonix.net (208.82.131.129)  0.601 ms  0.802 ms  0.788 ms
 2  ae2-854.bar1.Philadelphia1.Level3.net (4.16.124.117)  0.449 ms  0.451 ms  0.666 ms
 3  * * *
 4  10ge5-2.core1.nyc4.he.net (216.66.36.5)  2.546 ms telia-level3-10.NewYork1.Level3.net (4.68.110.82)  2.552 ms NTT-level3-90G.NewYork1.Level3.net (4.68.110.74)  3.004 ms
 5  ae-3.r24.nycmny01.us.bb.gin.ntt.net (129.250.5.61)  9.408 ms be2061.ccr42.jfk02.atlas.cogentco.com (154.54.3.69)  3.186 ms  3.420 ms
 6  ae-2.r20.sttlwa01.us.bb.gin.ntt.net (129.250.4.13)  62.564 ms  63.766 ms 10ge3-2.core3.fmt2.he.net (184.105.222.13)  78.682 ms
 7  ae-0.r21.sttlwa01.us.bb.gin.ntt.net (129.250.2.54)  61.828 ms  61.779 ms  61.769 ms
 8  be2831.ccr21.mci01.atlas.cogentco.com (154.54.42.165)  40.139 ms ae-3.r23.snjsca04.us.bb.gin.ntt.net (129.250.3.124)  78.049 ms  79.935 ms
 9  be3036.ccr22.den01.atlas.cogentco.com (154.54.31.89)  52.044 ms li566-32.members.linode.com (192.155.80.32)  73.802 ms ae-41.r02.snjsca04.us.bb.gin.ntt.net (129.250.6.119)  78.507 ms

not sure what to be looking for

related note:
on sstem update theres a dropdown with 2 options both say jamroom network
i tried the second 1 it made me re-add my system id
but still same results
i assumed they were 2 diferent servers? or same server different channel?
whats the dealio with the 2 jamroom networks to choose from

--
soaringeagle
head dreadhead at dreadlocks site
glider pilot student and member/volunteer coordinator with freedoms wings international soaring for people with disabilities
updated by @soaringeagle: 02/03/17 10:26:59PM

very poor db performance

Tags