Previous Thread
Next Thread
Print Thread
Rate Thread
Joined: Sep 2000
Posts: 129
Member
Member
Offline
Joined: Sep 2000
Posts: 129
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
Joined: Oct 2003
Posts: 2,305
Old Hand
Old Hand
Joined: Oct 2003
Posts: 2,305
using only version 3.23 of Mysql? Consider upgrading that to MySQL 4.1.11

Joined: Nov 2000
Posts: 210
Member
Member
Offline
Joined: Nov 2000
Posts: 210
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.

Joined: Dec 2000
Posts: 1,471
Addict
Addict
Offline
Joined: Dec 2000
Posts: 1,471
Can you specify "big overhead"?

Joined: Sep 2000
Posts: 129
Member
Member
Offline
Joined: Sep 2000
Posts: 129
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
Attachments
127255-mysql.gif (0 Bytes, 65 downloads)


...... x ......
Using UBBt 6.4.2 + Digg Ajax Mod, Trust Ajax Mod, Captcha Regristation & Login mod, Checkusername Ajax mode.
Sponsored Links
Joined: Sep 2000
Posts: 129
Member
Member
Offline
Joined: Sep 2000
Posts: 129
[]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.
Joined: Nov 2001
Posts: 10,369
I type Like navaho
I type Like navaho
Joined: Nov 2001
Posts: 10,369
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.

Joined: May 2001
Posts: 550
Code Monkey
Code Monkey
Offline
Joined: May 2001
Posts: 550
> 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.

Joined: Sep 2000
Posts: 129
Member
Member
Offline
Joined: Sep 2000
Posts: 129
[]
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.

Link Copied to Clipboard
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
bluehost
InterServer
Visit us on Facebook
Member Spotlight
Ruben Rocha
Ruben Rocha
Lutz,FL,USA
Posts: 253
Joined: January 2000
Forum Statistics
Forums63
Topics37,570
Posts293,915
Members13,848
Most Online5,166
Sep 15th, 2019
Today's Statistics
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
Top Posters
AllenAyres 21,079
JoshPet 10,369
LK 7,394
Lord Dexter 6,708
Gizmo 5,829
Greg Hard 4,625
Top Posters(30 Days)
Top Likes Received
isaac 81
Gizmo 20
Brett 7
WebGuy 2
Morgan 2
Top Likes Received (30 Days)
None yet
The UBB.Developers Network (UBB.Dev/Threads.Dev) is ©2000-2022 VNC Web Services

 
Powered by UBB.threads™ PHP Forum Software 8.0.0
(Preview build 20221129)