171.4 mysql queries/second!

[George L]

Wow vB's reindexing of searchindex is very server intensive... with my forum closed and reindexing of searchindex for min 3 and max 19 characters for 29,000 threads or 452,000+ posts

it's taken hours but look at the mysql queries per second load average is constant 1.50 - 1.85 right now still reindexing overnight LOL

Key Reads/Key Read Requests = 0.000297 (Cache hit = 99.999703%)
Key Writes/Key Write Requests = 0.926374
Connections/second = 0.171 (/hour = 615.664)
KB received/second = 32.741 (/hour = 117866.280)
KB sent/second = 46.061 (/hour = 165821.030)
Temporary Tables Created/second = 13.776 (/hour = 49592.023)
Opened Tables/second = 0.059 (/hour = 211.022)
Slow Queries/second = 0.003 (/hour = 10.007)
% of slow queries = 0.002%
Queries/second = 163.397 (/hour = 588228.861)

update 6000+ threads to index left

Key Reads/Key Read Requests = 0.000838 (Cache hit = 99.999162%)
Key Writes/Key Write Requests = 0.932576
Connections/second = 0.163 (/hour = 588.229)
KB received/second = 34.730 (/hour = 125027.018)
KB sent/second = 47.269 (/hour = 170168.610)
Temporary Tables Created/second = 14.168 (/hour = 51004.372)
Opened Tables/second = 0.045 (/hour = 163.117)
Slow Queries/second = 0.002 (/hour = 7.735)
% of slow queries = 0.001%
Queries/second = 168.125 (/hour = 605251.682)

finally finished 171.4 mysql queries/sec to reindex

Key Reads/Key Read Requests = 0.001563 (Cache hit = 99.998437%)
Key Writes/Key Write Requests = 0.938126
Connections/second = 0.167 (/hour = 600.551)
KB received/second = 36.706 (/hour = 132142.555)
KB sent/second = 48.523 (/hour = 174683.824)
Temporary Tables Created/second = 14.356 (/hour = 51683.272)
Opened Tables/second = 0.037 (/hour = 133.732)
Slow Queries/second = 0.002 (/hour = 6.342)
% of slow queries = 0.001%
Queries/second = 171.489 (/hour = 617360.018)

[Hotte]

load average is constant 1.50 - 1.85

hu?
what for a CPU, how much RAM etc has your server?

[George L]

Originally posted by Hotte

hu?
what for a CPU, how much RAM etc has your server?

the load is for a dual cpu server - server specs in my sig

Dual P3 866 - 768MB - 18GB 10k SCSI - PHP4.06 - MySQL 3.23.39 - APC Cache 1.1.0pl1 - mod_gzip enabled

[Martin]

good god

I hope I never have to reindex!

[George L]

Originally posted by Martin
good god

I hope I never have to reindex!

yeah it took me from start of index first try yesterday 7:30 pm until today mid-day - 3rd attempt to get it reindexed!

it's one reason i always backup mysql with the searchindex rather than without it

some interesting searchindex size stats showing number of records and size occupied on defaults prior to vB 2.2.0 upgrade and after upgrade with reindex for min 3 instead of 4 characters

min 4 - max 20 - default badwords
searchindex 7,368,429 - 179.5 MB
word 512,619 - 37.4 MB
total database size: 551.7 MB

min 3 - max 19 - default badwords
searchindex 7,770,838 - 190.8 MB
word 426,764 - 32.2 MB
total database size: 569.1 MB

Registered Members: 3,813
Total Posts: 452,041
Total Threads: 28,586
Total Thread Views: 3,832,810

[chrispadfield]

i wonder what the load would have been had you kept the forum open

[George L]

Originally posted by chrispadfield
i wonder what the load would have been had you kept the forum open

normal load when forum is open is between 0.45 - 0.95 with spikes to 1.5 when my cronjob's are running mysql backup the load goes to 3.00 - 4.5 backing up 1.6gb of mysql databases and gzipping while the forum is still running and open