FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum » How To » fud_index
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
fud_index [message #160090] Thu, 23 July 2009 10:51 Go to next message
remek is currently offline  remek   Poland
Messages: 34
Registered: August 2005
Location: Poland
Karma: 0
Member
add to buddy list
ignore all messages by this user
Hi,
Today I had problems with forum again. It did not work - many problems with performance. I got an information from myhost provider that capacity of my server (regarding database) is not enough to run such big forum (database is too large). My biggest table is fud_index (over 1GB). Can I truncate it? What would be the impact on forum?

Cheers,
Remek
Re: fud_index [message #160091 is a reply to message #160090] Thu, 23 July 2009 11:22 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
It will probably break your forum's search function. Try the following and let us know if it helps:

OPTIMIZE TABLE fud28_index;

Re: fud_index [message #160092 is a reply to message #160091] Thu, 23 July 2009 13:12 Go to previous messageGo to next message
remek is currently offline  remek   Poland
Messages: 34
Registered: August 2005
Location: Poland
Karma: 0
Member
add to buddy list
ignore all messages by this user
Hi,
I decided to switch off search on forum and delete records from fud_index. For me, after 2h, forum works far, far better. I have table dump but ... do not know if I come back to previous setting.

I had many problems with posting (timeouts came out).


Best regards
Remek
Re: fud_index [message #160093 is a reply to message #160090] Thu, 23 July 2009 13:20 Go to previous messageGo to next message
remek is currently offline  remek   Poland
Messages: 34
Registered: August 2005
Location: Poland
Karma: 0
Member
add to buddy list
ignore all messages by this user
Unfortunately ... problem again:

(/forum/index.php:81
/forum/index.php:177
/forum/index.php:1070
/forum/theme/default/post.php:1626
/forum/theme/default/post.php:3854
/forum/index.php:1176
) 1053: Server shutdown in progress
Query: SELECT MAX(id),count(*) FROM fud_msg WHERE poster_id=265 AND apr=1
_GET: array ( 't' => 'post', )
_POST: array ( 'msg_subject' => 'xxxxxxxxxxxx', 'msg_icon' => '', 'fnt_size' => '', 'fnt_color' => '', 'fnt_face' => '', 'msg_body' => 'xxxxxxxxx', 'tmp_f_val' => '1', 'msg_show_sig' => 'Y', 'minimsg_pager_switch' => '0', 'SQ' => 'f86d30d6ef60cf4bf2ec0ec636d4c2a8', 'submitted' => '1', 'reply_to' => '150024', 'th_id' => '9754', 'frm_id' => '3', 'start' => '0', 'msg_id' => '0', 'pl_id' => '0', 'old_subject' => 'xxxxxxxxx', 'prev_loaded' => '1', 'btn_submit' => 1, )
Server Version:
[Referring Page] http://www.xxxx.xxx/forum/index.php?t=post&reply_to=150024&


And again slow ....
Re: fud_index [message #160094 is a reply to message #160093] Thu, 23 July 2009 15:07 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
I do understand that you have a problem, but this is not FUDforum's fault. "1053: Server shutdown in progress" is a MySQL error. Complain to your web host about this or move to a host that don't overcommit their servers.
Re: fud_index [message #160095 is a reply to message #160094] Thu, 23 July 2009 16:53 Go to previous messageGo to next message
remek is currently offline  remek   Poland
Messages: 34
Registered: August 2005
Location: Poland
Karma: 0
Member
add to buddy list
ignore all messages by this user
I am looking for help only. I do not think that that this is fudforum problem. I want to use it -> I have been using it for 4 years so far.

Fudforum I use -> 2.7.2

Unfortunately I am not an database expert Sad and can not set up server variables. They told me that this is sql query problem. They execute too long - they have some timeouts set up (3 sec) on database. After this time message "server shutdown ..." is shown.

Variable Session value / Global value
auto increment increment 1
auto increment offset 1
automatic sp privileges ON
back log 128
basedir /
binlog cache size 32,768
bulk insert buffer size 8,388,608
character set client latin1
(Global value) latin2
character set connection utf8
(Global value) latin2
character set database latin2
character set filesystem binary
character set results latin1
(Global value) latin2
character set server latin2
character set system utf8
character sets dir /usr/local/share3/mysql/charsets/
collation connection utf8_unicode_ci
(Global value) latin2_general_ci
collation database latin2_general_ci
collation server latin2_general_ci
completion type 0
concurrent insert 0
connect timeout 10
datadir /var/
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 3
ft query expansion limit 20
ft stopword file (built-in)
group concat max len 1,024
have archive NO
have bdb NO
have blackhole engine NO
have compress YES
have crypt YES
have csv NO
have dynamic loading NO
have example engine NO
have federated engine NO
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 xxxx.xxxx.xxx
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 67,108,864
innodb checksums ON
innodb commit concurrency 0
innodb concurrency tickets 500
innodb data file path ib_data:16M: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 ON
innodb flush log at trx commit 0
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
interactive timeout 300
join buffer size 1,048,576
key buffer size 268,435,456
key cache age threshold 300
key cache block size 1,024
key cache division limit 100
language /usr/local/share3/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 /var/m101.err
log queries not using indexes ON
log slave updates OFF
log slow queries ON
log warnings 1
long query time 3
low priority updates ON
lower case file system OFF
lower case table names 0
max allowed packet 4,194,304
max binlog cache size 4,294,963,200
max binlog size 1,073,741,824
max connect errors 4,096
max connections 1,024
max delayed threads 0
max error count 64
max heap table size 16,777,216
max insert delayed threads 0
max join size 4,294,967,295
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 32
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 recover options OFF
myisam repair threads 1
myisam sort buffer size 8,388,608
myisam stats method nulls_unequal
net buffer length 65,536
net read timeout 30
net retry count 1,000,000
net write timeout 60
new OFF
old passwords OFF
open files limit 7,408
optimizer prune level 1
optimizer search depth 62
pid file /var/m101.pid
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 67,108,864
query cache type ON
query cache wlock invalidate OFF
query prealloc size 8,192
range alloc block size 4,096
read buffer size 262,144
read only OFF
read rnd buffer size 262,144
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 /var/tmp/
slave net timeout 3,600
slave skip errors OFF
slave transaction retries 10
slow launch time 2
socket /usr/run/mysql
sort buffer size 262,144
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 CEST
table cache 3,187
table lock wait timeout 50
table type MyISAM
thread cache size 16
thread stack 196,608
time format %H:%i:%s
time zone SYSTEM
timed mutexes OFF
tmp table size 67,108,864
tmpdir /var/tmp/
transaction alloc block size 8,192
transaction prealloc size 4,096
tx isolation REPEATABLE-READ
updatable views with limit YES
version 5.0.66a-log
version comment xxxxx.xxx
version compile machine i386
version compile os unknown-freebsd6.2
wait timeout 120

[Updated on: Thu, 23 July 2009 16:57]

Report message to a moderator

Re: fud_index [message #160099 is a reply to message #160095] Fri, 24 July 2009 01:40 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
That query should should only take a few milliseconds to run (the table is indexed). It can only take 3 seconds if MySQL or the server is too busy (over committed).
Re: fud_index [message #160122 is a reply to message #160090] Tue, 28 July 2009 07:07 Go to previous messageGo to next message
remek is currently offline  remek   Poland
Messages: 34
Registered: August 2005
Location: Poland
Karma: 0
Member
add to buddy list
ignore all messages by this user
Next observations came to me Smile

SELECT SUM(length) FROM fud_pmsg WHERE duser_id=782

execution time -> 4.7708

In MyPhpAdmin I noticed:


PRIMARY PRIMARY 63533
- id

fud_pmsg_i_dfr INDEX 63533
- duser_id
- fldr
- read_stamp

fud_pmsg_i_dfi INDEX 63533
- duser_id
- fldr
- id

for column `INDEX` was created many indexes

Should I fix it?



Re: fud_index [message #160128 is a reply to message #160122] Tue, 28 July 2009 11:10 Go to previous message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
If you can bring the execution time down by recreating indexes (without negatively impacting other queries), I would be very interested in your solution.

So, go ahead and try it. Just remember to backup your forum before you start!
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: The availability of IP addresses to regular users
Next Topic: Is it possible to block users from posting
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Wed Oct 18 11:00:03 EDT 2017

Total time taken to generate the page: 0.00801 seconds