UBB.Dev
Posted By: santana Why my tables produce big overhead. - 04/20/2005 5:48 AM
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
Posted By: scroungr Re: Why my tables produce big overhead. - 04/20/2005 1:57 PM
using only version 3.23 of Mysql? Consider upgrading that to MySQL 4.1.11
Posted By: Pappy Re: Why my tables produce big overhead. - 04/20/2005 5:43 PM
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.
Posted By: Astaran Re: Why my tables produce big overhead. - 04/20/2005 6:40 PM
Can you specify "big overhead"?
Posted By: santana Re: Why my tables produce big overhead. - 04/21/2005 6:04 AM
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 picture 127255-mysql.gif
Posted By: santana Re: Why my tables produce big overhead. - 04/21/2005 11:00 AM
[]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.
Posted By: JoshPet Re: Why my tables produce big overhead. - 04/29/2005 6:26 AM
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.
Posted By: Anno Re: Why my tables produce big overhead. - 05/01/2005 5:05 AM
> 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.
Posted By: santana Re: Why my tables produce big overhead. - 05/19/2005 7:17 AM
[]
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
© UBB.Developers