Previous Thread
Next Thread
Print Thread
Rate Thread
Why my tables produce big overhead. #284840
04/19/2005 9:48 PM
04/19/2005 9:48 PM
Joined: Sep 2000
Posts: 229
Asia
S
santana Offline OP
Member
santana  Offline OP
Member
S

Joined: Sep 2000
Posts: 229
Asia
Every day my tables, expecially w3t_Posts, w3t_Online and w3t_Users produce big overhead when I view them using phpmyadmin. If I dont optimize them or repair, then the database crashed in few days.

I am using PHP 4.x, mySQL 3.23 Apache 2.0

Thanks


...... x ......
Using UBBt 6.4.2 + Digg Ajax Mod, Trust Ajax Mod, Captcha Regristation & Login mod, Checkusername Ajax mode.
Sponsored Links
Re: Why my tables produce big overhead. [Re: often] #284841
04/20/2005 5:57 AM
04/20/2005 5:57 AM
Joined: Oct 2003
Posts: 2,409
Richmond, VA
scroungr Offline
Old Hand
scroungr  Offline
Old Hand

Joined: Oct 2003
Posts: 2,409
Richmond, VA
using only version 3.23 of Mysql? Consider upgrading that to MySQL 4.1.11

Re: Why my tables produce big overhead. [Re: 234234] #284842
04/20/2005 9:43 AM
04/20/2005 9:43 AM
Joined: Nov 2000
Posts: 235
Germany (US Citizen)
P
Pappy Offline
Member
Pappy  Offline
Member
P

Joined: Nov 2000
Posts: 235
Germany (US Citizen)
I am using version 3.23 with no problems whatsoever you might want to check to make sure that all your tables are myisam tables and post your my.ini (cnf) file contents so that others can take a look. also the database size and the aproximate number of users that are on at any given time might help to diagnose your problem.

My point is that upgrading the database software may do absolutely nothing for you. Upgrading is not always the best way to fix a problem.

Re: Why my tables produce big overhead. [Re: MercAqua] #284843
04/20/2005 10:40 AM
04/20/2005 10:40 AM
Joined: Dec 2000
Posts: 1,545
Germany
A
Astaran Offline
Addict
Astaran  Offline
Addict
A

Joined: Dec 2000
Posts: 1,545
Germany
Can you specify "big overhead"?

Re: Why my tables produce big overhead. [Re: -Fusion-] #284844
04/20/2005 10:04 PM
04/20/2005 10:04 PM
Joined: Sep 2000
Posts: 229
Asia
S
santana Offline OP
Member
santana  Offline OP
Member
S

Joined: Sep 2000
Posts: 229
Asia
Hi

Thanks for the respond.
Please take a look at the attachment to show you the 'overhead' that I have. This number is growing fast, I just optimize/repair 12 hours ago. In 2 days it should be about 200,000 Bytes and it stops the mysqld.

Number of users that are on at any given time is about 300
I have attached the screenshot of my tables including the sizes, rows, and overhead numbers.

For information, I am using Single Xeon 2.4Ghz with 1024MB (512MB x 2), SCSI DRIVE. OS Fedora Core 1, Apache 2.0, Php 4.2.2, mySQL 3.23.58

Here is my my.cnf config:
[]
[client]
port = 3306
socket = /var/lib/mysql/mysql.sock

[mysqld]
port = 3306
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

skip-locking
set-variable = max_connections=500
set-variable = myisam_sort_buffer_size=64M
#set-variable = key_buffer=16M
set-variable = key_buffer=64M
set-variable = join_buffer=4M
set-variable = record_buffer=4M
set-variable = sort_buffer=6M
#set-variable = read_buffer_size=2M
set-variable = table_cache=1024
set-variable = thread_cache_size=256
set-variable = wait_timeout=3600
set-variable = connect_timeout=10

[safe_mysqld]
open-files-limit=8192

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

My top produces
[]
10:42:46 up 7 days, 15:28, 2 users, load average: 14.33, 17.51, 16.14
191 processes: 179 sleeping, 12 running, 0 zombie, 0 stopped
CPU0 states: 93.3% user 6.2% system 0.0% nice 0.0% iowait 0.0% idle
CPU1 states: 93.3% user 6.2% system 0.0% nice 0.0% iowait 0.0% idle
Mem: 1030284k av, 978456k used, 51828k free, 0k shrd, 58532k buff
754756k actv, 64160k in_d, 37968k in_c
Swap: 1020116k av, 52048k used, 968068k free 463096k cached

PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND
16502 mysql 15 0 63012 54M 1276 S 15.2 5.4 97:51 1 mysqld
28860 apache 16 0 8160 6608 3756 S 11.0 0.6 0:06 1 httpd
28899 apache 15 0 8008 6444 3736 S 7.3 0.6 0:01 0 httpd
...
[/]

Thank you for all the helps.

Best regards

Attached Files
127255-mysql.gif (65 downloads)

...... x ......
Using UBBt 6.4.2 + Digg Ajax Mod, Trust Ajax Mod, Captcha Regristation & Login mod, Checkusername Ajax mode.
Sponsored Links
Re: Why my tables produce big overhead. [Re: 234234] #284845
04/21/2005 3:00 AM
04/21/2005 3:00 AM
Joined: Sep 2000
Posts: 229
Asia
S
santana Offline OP
Member
santana  Offline OP
Member
S

Joined: Sep 2000
Posts: 229
Asia
[]scroungr said:
using only version 3.23 of Mysql? Consider upgrading that to MySQL 4.1.11 [/]

Hello

I have a small machine (PII 400, 96MB) (fedora core 1) on my room for experimenting things.

I loaded 350MB (450K rows of data in w3t_Posts) of data to mysql 3.23. Using Ubbthreads 6.4.2, I tried to search the word 'Nokia' with the option '5 years'. The average was 430 seconds when I tried to search on the same keyword.

Then I downloaded mysql-4.0.24 rpm package and did an upgrade.
After it was done I tried to search the same keyword 'nokia' with the same amount of data. The result was 545 seconds in average (never reached below 500 seconds).

I don't know what's going on here.


...... x ......
Using UBBt 6.4.2 + Digg Ajax Mod, Trust Ajax Mod, Captcha Regristation & Login mod, Checkusername Ajax mode.
Re: Why my tables produce big overhead. [Re: often] #284846
04/28/2005 10:26 PM
04/28/2005 10:26 PM
Joined: Nov 2001
Posts: 11,330
Charlotte, NC
JoshPet Offline
I type Like navaho
JoshPet  Offline
I type Like navaho

Joined: Nov 2001
Posts: 11,330
Charlotte, NC
The overhead you're seeing in phpMyAdmin isn't anything to do with the MySQL settings. When records are deleted in MySQL, the space is never reclaimed until you optimize the table. Thus phpMyAdmin shows you how much space is "wasted" due to stuff that has been deleted. The user's database frequently gets stuff deleted from it, (if you are using the email verification, alot of signups never get verified and automatically deleted. The online table and search results table hold tempoary info and so they bloat quite regularly. The busier the site, the more this will happen. That's why optimizing those tables with high overhead frequently is important.

Hope that explaination helps a bit.

Re: Why my tables produce big overhead. [Re: often] #284847
04/30/2005 9:05 PM
04/30/2005 9:05 PM
Joined: May 2001
Posts: 562
Austria
A
Anno Offline
Code Monkey
Anno  Offline
Code Monkey
A

Joined: May 2001
Posts: 562
Austria
> In 2 days it should be about 200,000 Bytes and it stops the mysqld.

200,000 Bytes is ~200 kilobytes which is nothing for a table that is 33 megabytes.
I don't think your mysql is crashing because of this.

Re: Why my tables produce big overhead. [Re: domain123] #284848
05/18/2005 11:17 PM
05/18/2005 11:17 PM
Joined: Sep 2000
Posts: 229
Asia
S
santana Offline OP
Member
santana  Offline OP
Member
S

Joined: Sep 2000
Posts: 229
Asia
[]
11:58:16 up 5 days, 20:06, 1 user, load average: 1.69, 1.60, 3.90
385 processes: 384 sleeping, 1 running, 0 zombie, 0 stopped
CPU0 states: 39.4% user 4.1% system 0.0% nice 0.0% iowait 55.3% idle
CPU1 states: 43.1% user 4.4% system 0.0% nice 0.0% iowait 51.3% idle
Mem: 1030284k av, 1005520k used, 24764k free, 0k shrd, 89664k buff
713968k actv, 103000k in_d, 21692k in_c
Swap: 1020116k av, 198712k used, 821404k free 591440k cached

[/]

My top after deleting junk files.
A tremendeous improvement! Yesterday the load average was about 45-60, and today we found out that one of my banner scripts (revsense), is taking too much of the space. 16GB for the cache only. We have deleted these files and the load average down to 0.9 to 1.7 in average for more processes.

I am using Single Xeon 2.4HT with 1GB memory. Apache 2.0 and mySQL 4


...... x ......
Using UBBt 6.4.2 + Digg Ajax Mod, Trust Ajax Mod, Captcha Regristation & Login mod, Checkusername Ajax mode.

Donate Today!
Donate via PayPal

Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.

Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
Recommended Hosts
We have personally worked with, and recommend, the following Web Hosts:
· Stable Host
· Blue Host
· Interserver.net
Visit us on Facebook
Member Spotlight
badfrog
badfrog
somewhere on the coast of Maine
Posts: 94
Joined: March 2007
Show All Member Profiles 
Forum Statistics
Forums64
Topics37,467
Posts293,523
Members13,802
Most Online1,498
Mar 17th, 2017
Top Posters(All Time)
AllenAyres 25,587
JoshPet 11,330
Rick 8,373
LK 7,396
Lord Dexter 6,503
Gizmo 5,947
Greg Hard 5,533
Top Posters(30 Days)
isaac 1
Today's Statistics
Currently Online 932
Topics Created 0
Posts Made 0
Users Online 2
Birthdays 14
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2018 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 7.7.0
(Snapshot build 20181115.dev)
PHP: 5.4.45 Page Time: 0.039s Queries: 16 (0.012s) Memory: 3.2267 MB (Peak: 3.4067 MB) Data Comp: Zlib Server Time: 2018-12-14 12:01:18 UTC