Do large databases slow down MySQL?

[Ed FvC]

I ask because my forums db is at about 800MB. It was over 1.2GB last month and I pruned a good 400,000 messages, but didn't see much of a performance increase.

Does the size affect its speed (besides searches)?

[Zzed]

Originally posted by Ed FvC
I ask because my forums db is at about 800MB. It was over 1.2GB last month and I pruned a good 400,000 messages, but didn't see much of a performance increase.

Does the size affect its speed (besides searches)? You need to also optimize your post and thread tables before you notice any improvements. ;)

[Ed FvC]

Originally posted by Zzed
You need to also optimize your post and thread tables before you notice any improvements. ;) I do that about once a week with the repair.php script.

[eva2000]

Originally posted by Ed FvC
I ask because my forums db is at about 800MB. It was over 1.2GB last month and I pruned a good 400,000 messages, but didn't see much of a performance increase.

Does the size affect its speed (besides searches)? depends on how much activity to and from the large database there is

my vB forum's database is 1.3GB with 816,000+ posts, 46,000+ threads, 100,000+ private messages and no probs on the server in my sig :)

[Ed FvC]

Originally posted by eva2000
depends on how much activity to and from the large database there is

my vB forum's database is 1.3GB with 816,000+ posts, 46,000+ threads, 100,000+ private messages and no probs on the server in my sig :) The server is running two boards, one with 300,000 posts and 20-60 users at any given time. The other with 800,000 posts (the one I pruned) and 70-130 people at any time. It's on a 1ghz Duron Rackshack box with RH 7.1 and 1GB of RAM. I'm starting to think it's just the server not being able to handle the demand. :(

[eva2000]

Originally posted by Ed FvC
The server is running two boards, one with 300,000 posts and 20-60 users at any given time. The other with 800,000 posts (the one I pruned) and 70-130 people at any time. It's on a 1ghz Duron Rackshack box with RH 7.1 and 1GB of RAM. I'm starting to think it's just the server not being able to handle the demand. :( well duron cpu ain't exactly meant for web serving, particular database serving

let's see if i can figure out the problem

please provide the following

1. is this on dedicated or shared virual server
2. your server specs, such as mysql and php version
3. if possible how mysql was compiled/installed
4. your top stats
5. your mysql configuration variables located at /etc/my.cnf or c:\my.cnf if on Windows server if you don't have that file you need to log into telnet and as root user type

mysqladmin -u root -p variables

copy and paste output here

6. your mysql extended-status output either still telnet as root user type

mysqladmin -u root -p extended-status

copy and paste output here

or preferred is to installed extended-status output script which is either located

- in your vB 2.2.6 or higher vB version's zip file extra's folder, upload mysqlinfo.php script to your site or if you're on an pre vB 2.2.6 install go to
- http://vbulletin.com/forum/showthread.php?threadid=3477 and install that scrip making sure to edit $mysqllogin line with your own mysqlusername and password

and post url to that here

7. oh and is your vB the only thing on the server? or other scripts? sites?

8. how many average and max concurrent users on your vB forum ?

9. create a file named phpinfo.php and place this code in it and post the url/link to it from your web site

<?
phpinfo();
?>

i.e. yourdomain.com/phpinfo.php

10. if you run Apache and you have your own dedicated server or access to your httpd.conf (apache configuration file) can you post the values you have set for the following :

KeepAlive
MaxKeepAliveRequests
KeepAliveTimeout
MinSpareServers
MaxSpareServers
StartServers
MaxClients

11. what version of vB are you running ?

[Ed FvC]

Let's see if I can get this right...

1) Dedicated
2) 1GHz Duron / 1GB RAM / PHP 4.3.0 / MySQL 3.23.54a / PHPA 1.3.3r2
3) MySQL installed from mysql.com's RPMs
4)

4:30pm up 17:53, 0 users, load average: 4.28, 2.66, 2.11
539 processes: 538 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1015028K av, 998636K used, 16392K free, 0K shrd, 10356K buff
Swap: 265064K av, 19320K used, 245744K free 401176K cached

5)

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
skip-locking
set-variable = max_connections=512
set-variable = key_buffer=16M
set-variable = join_buffer=4M
set-variable = record_buffer=2M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=128
set-variable = wait_timeout=300

http://www.linenoyz.net/status.php

Two vBs are the primary resource hogs on the server (both licensed -- fordvschevy.com and rajahwwf.com). There are a few smaller PHP/MySQL scripts but they are tiny.

Average users total on both boards are 100-120, max around 350.

http://www.linenoyz.net/phpinfo.php

Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 512

vB 2.2.8

[Coreace]

I would change your 1GHz # Duron / 1GB RAM #. Since you are on a Duron I assume you use IDE? Switch to SCSI.

[eva2000]

Originally posted by Ed FvC
Let's see if I can get this right...

1) Dedicated
2) 1GHz Duron / 1GB RAM / PHP 4.3.0 / MySQL 3.23.54a / PHPA 1.3.3r2
3) MySQL installed from mysql.com's RPMs
4)

4:30pm up 17:53, 0 users, load average: 4.28, 2.66, 2.11
539 processes: 538 sleeping, 1 running, 0 zombie, 0 stopped
Mem: 1015028K av, 998636K used, 16392K free, 0K shrd, 10356K buff
Swap: 265064K av, 19320K used, 245744K free 401176K cached

5)

[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
skip-locking
set-variable = max_connections=512
set-variable = key_buffer=16M
set-variable = join_buffer=4M
set-variable = record_buffer=2M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=128
set-variable = wait_timeout=300

http://www.linenoyz.net/status.php

Two vBs are the primary resource hogs on the server (both licensed -- fordvschevy.com and rajahwwf.com). There are a few smaller PHP/MySQL scripts but they are tiny.

Average users total on both boards are 100-120, max around 350.

http://www.linenoyz.net/phpinfo.php

Timeout 300
KeepAlive On
MaxKeepAliveRequests 100
KeepAliveTimeout 15
MinSpareServers 5
MaxSpareServers 10
StartServers 5
MaxClients 512

vB 2.2.8 it could be duron cpu, but try these changes

1. change my.cnf to below and restart mysql


[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql
skip-locking
set-variable = max_connections=450
set-variable = key_buffer=16M
set-variable = myisam_sort_buffer_size=64M
set-variable = join_buffer=1M
set-variable = record_buffer=1M
set-variable = sort_buffer=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=128
set-variable = wait_timeout=14400
set-variable = connect_timeout=10
set-variable = max_allowed_packet=16M
set-variable = max_connect_errors=10

[safe_mysqld]
open_files_limit=8192

[mysqldump]
quick
set-variable = max_allowed_packet=16M

[myisamchk]
set-variable = key_buffer=64M
set-variable = sort_buffer=64M
set-variable = read_buffer=16M
set-variable = write_buffer=16M


2. reduce maxclients in httpd.conf from 512 to 150 and restart apache

[Ed FvC]

Reduce it to 150?? There are another 100 users on the site at any moment that AREN'T using the boards. Wouldn't that make the load skyrocket?

:confused:

[Ed FvC]

bump

[eva2000]

Originally posted by Ed FvC
Reduce it to 150?? There are another 100 users on the site at any moment that AREN'T using the boards. Wouldn't that make the load skyrocket?

:confused: nope... have you tried it yet ?

[Ed FvC]

Originally posted by eva2000
nope... have you tried it yet ? Yeah, I did...it worked okay for a little while, but after last night's WWE Pay Per View, the server just got owned. :( I have it set to MaxClients 256 and KeepAlive Off. This seems to be keeping it from dying.

[Tungsten]

Let's see if I can get this right...
http://www.linenoyz.net/status.php


Um... www.linenoyz.net (http://www.linenoyz.net) appears to have been hacked? Check the pop-ups. :D

[eva2000]

Yeah, I did...it worked okay for a little while, but after last night's WWE Pay Per View, the server just got owned. :( I have it set to MaxClients 256 and KeepAlive Off. This seems to be keeping it from dying.
looks like you need a cpu/server upgrade ;)

[Ed FvC]

looks like you need a cpu/server upgrade ;)
Well, we did that last month. It's now on a nice new server with a 2ghz Pentium 4 with 1.5GB of RAM. Wow, what a difference that made! The last PPV was this past weekend and the server didn't even flinch. :D

[eva2000]

Well, we did that last month. It's now on a nice new server with a 2ghz Pentium 4 with 1.5GB of RAM. Wow, what a difference that made! The last PPV was this past weekend and the server didn't even flinch. :D
good to hear :D