MyBB Community Forums

Pages: 1 2 3 4 5 6

Out of curiosity, what version of MySQL are you running??

(2010-10-28, 04:42 PM)pavemen Wrote: [ -> ]do you have phpMyAdmin? If so, open that and on the left panel, click the phpMyAdmin image at the top. the right side should then show tabs like Database, Status, Variables, etc.

Click the Variables tab and copy/paste that info here

Here is the information sir and I have no idea what it means but I am willing to learn.

Variable Session value / Global value
auto increment increment 1
auto increment offset 1
automatic sp privileges ON
back log 50
basedir /
binlog cache size 32,768
bulk insert buffer size 8,388,608
character set client utf8
(Global value) latin1
character set connection utf8
(Global value) latin1
character set database latin1
character set filesystem binary
character set results utf8
(Global value) latin1
character set server latin1
character set system utf8
character sets dir /usr/share/mysql/charsets/
collation connection utf8_general_ci
(Global value) latin1_swedish_ci
collation database latin1_swedish_ci
collation server latin1_swedish_ci
completion type 0
concurrent insert 1
connect timeout 10
datadir /var/lib/mysql/
date format %Y-%m-%d
datetime format %Y-%m-%d %H:%i:%s
default week format 0
delay key write ON
delayed insert limit 100
delayed insert timeout 300
delayed queue size 1,000
div precision increment 4
keep files on create OFF
engine condition pushdown OFF
expire logs days 0
flush OFF
flush time 0
ft boolean syntax + -><()~*:""&|
ft max word len 84
ft min word len 4
ft query expansion limit 20
ft stopword file (built-in)
group concat max len 1,024
have archive YES
have bdb NO
have blackhole engine YES
have compress YES
have community features NO
have profiling NO
have crypt YES
have csv YES
have dynamic loading YES
have example engine YES
have federated engine YES
have geometry YES
have innodb YES
have isam NO
have merge engine YES
have ndbcluster NO
have openssl NO
have ssl NO
have query cache YES
have raid NO
have rtree keys YES
have symlink YES
hostname host.ej8squad.com
init connect
init file
init slave
innodb additional mem pool size 1,048,576
innodb autoextend increment 8
innodb buffer pool awe mem mb 0
innodb buffer pool size 8,388,608
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ibdata1:10M:autoextend
innodb data home dir
innodb adaptive hash index ON
innodb doublewrite ON
innodb fast shutdown 1
innodb file io threads 4
innodb file per table OFF
innodb flush log at trx commit 1
innodb flush method
innodb force recovery 0
innodb lock wait timeout 50
innodb locks unsafe for binlog OFF
innodb log arch dir
innodb log archive OFF
innodb log buffer size 1,048,576
innodb log file size 5,242,880
innodb log files in group 2
innodb log group home dir ./
innodb max dirty pages pct 90
innodb max purge lag 0
innodb mirrored log groups 1
innodb open files 300
innodb rollback on timeout OFF
innodb support xa ON
innodb sync spin loops 20
innodb table locks ON
innodb thread concurrency 8
innodb thread sleep delay 10,000
innodb use legacy cardinality algorithm ON
interactive timeout 28,800
join buffer size 131,072
key buffer size 8,384,512
key cache age threshold 300
key cache block size 1,024
key cache division limit 100
language /usr/share/mysql/english/
large files support ON
large page size 0
large pages OFF
lc time names en_US
license GPL
local infile ON
locked in memory OFF
log OFF
log bin OFF
log bin trust function creators OFF
log error
log queries not using indexes OFF
log slave updates OFF
log slow queries OFF
log warnings 1
long query time 10
low priority updates OFF
lower case file system OFF
lower case table names 0
max allowed packet 1,048,576
max binlog cache size 4,294,963,200
max binlog size 1,073,741,824
max connect errors 10
max connections 300
max delayed threads 20
max error count 64
max heap table size 16,777,216
max insert delayed threads 20
max join size 18446744073709551615
max length for sort data 1,024
max prepared stmt count 16,382
max relay log size 0
max seeks for key 4,294,967,295
max sort length 1,024
max sp recursion depth 0
max tmp tables 1
max user connections 0
max write lock count 4,294,967,295
multi range count 256
myisam data pointer size 6
myisam max sort file size 2,146,435,072
myisam mmap size 4,294,967,295
myisam recover options OFF
myisam repair threads 1
myisam sort buffer size 8,388,608
myisam stats method nulls_unequal
net buffer length 16,384
net read timeout 30
net retry count 10
net write timeout 60
new OFF
old passwords ON
open files limit 1,500
optimizer prune level 1
optimizer search depth 62
pid file /var/lib/mysql/host.ej8squad.com.pid
plugin dir
port 3,306
preload buffer size 32,768
protocol version 10
query alloc block size 8,192
query cache limit 1,048,576
query cache min res unit 4,096
query cache size 41,943,040
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
range alloc block size 4,096
read buffer size 1,048,576
read only OFF
read rnd buffer size 524,288
relay log
relay log index
relay log info file relay-log.info
relay log purge ON
relay log space limit 0
rpl recovery rank 0
secure auth OFF
secure file priv
server id 0
skip external locking ON
skip networking OFF
skip show database OFF
slave compressed protocol OFF
slave load tmpdir /tmp/
slave net timeout 3,600
slave skip errors OFF
slave transaction retries 10
slow launch time 2
socket /var/lib/mysql/mysql.sock
sort buffer size 5,242,880
sql big selects ON
sql mode
sql notes ON
sql warnings OFF
ssl ca
ssl capath
ssl cert
ssl cipher
ssl key
storage engine MyISAM
sync binlog 0
sync frm ON
system time zone EDT
table cache 512
table lock wait timeout 50
table type MyISAM
thread cache size 8
thread stack 196,608
time format %H:%i:%s
time zone SYSTEM
timed mutexes OFF
tmp table size 33,554,432
tmpdir /tmp/
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation REPEATABLE-READ
updatable views with limit YES
version 5.0.91-community
version comment MySQL Community Edition (GPL)
version compile machine i686
version compile os pc-linux-gnu
wait timeout 28,800

(2010-10-28, 04:48 PM)MattRogowski Wrote: [ -> ]It's not even just posting that's slow, a lot of the site is slow. Even if some things weren't fully indexed, that wouldn't affect the entire site. Loading newreply.php just took 25 seconds for me, there's no way an indexing issue would make that page take 25 seconds to load.

Is it time to look for a new hosting company or can I repair whatever is wrong?

(2010-10-28, 05:02 PM)pavemen Wrote: [ -> ]you are not caching your CSS either, the page is calling css.php three times and having to parse that every page view.

Researching on how to go about caching my CSS.

(2010-10-28, 07:40 PM)- G33K - Wrote: [ -> ]Out of curiosity, what version of MySQL are you running??

According the the variables that pavemen requested I am running 5.0.91.

Tracert and ping latency is ok. So it's probably the database. Maybe you can check in advanced debug which queries take so much time?

I am guessing it's either the posts or threads table. The lasts posts on index is a heavy one for the database. If someone is doing a search at the same time, you're in trouble fast.

Which plan are you on?

Thank for verifying that the ping and tracrt are ok. Out of my 21 queries, #16 takes the longest

(Plugin Hook: index_start)
#16 - Select Query
SELECT subject,username,uid,pid,tid FROM mybb_posts WHERE visible='1' ORDER BY dateline DESC LIMIT 0,10
table type possible_keys key key_len ref rows Extra
mybb_posts range visible visible 4 261667 Using where; Using filesort
Query Time: 2.85105204582

If it is the posts or threads table what can I do to fix this? Members are starting to notice and getting frustrated.

I am on Knownhost's VS2 (VPS) plan.
-512MB
-30GB HD space
-750GB bandwidth

pid, tid, uid, visible and dateline (5 out of the 7 columns mentioned in that query) are indexed. That query shouldn't be taking anywhere near 2.8 seconds. They said it'll have to copy into a temp table if there are incorrect indexes, but everything that needs to be indexed here is indexed.

Leaning towards another case of 'Host BS'.

If it's another case of 'Host BS' what can I tell them in my next support ticket? Can I fix this with the help of this community or am I at the mercy of whoever reads my support ticket?

Not necessarily host BS..

512 Mb isn't much for a forum with a 200-250 Mb database.

Also.. depending on how much cpu you get it just might not be enough to have those cpu intensive plugins (overview and shoutbox) AND have a fast forum.

Disabling certain features for guests might help (shoutbox, search)

Ask them why a query on the posts table is taking 2.8 seconds, when it is indexed, after they said that the slowness could be caused by incorrect indexing.

Again, the pid, tid, visible and dateline columns are all indexed. You can optimize the database via phpMyAdmin but it might not have much effect. Past that there's little more you can really do. Did you tell them that the table is fully indexed already??

Quote:Please optimize the database ej8squad****** queries to solve the issue."

You can't really optimize the queries in this post or the last one, they're not badly written queries.

Pages: 1 2 3 4 5 6

- G33K -

gimmezell

Disturbed

gimmezell

Matt

gimmezell

Disturbed

Matt

gimmezell

Matt