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.<br /><br />I am using PHP 4.x, mySQL 3.23 Apache 2.0<br /><br />Thanks

using only version 3.23 of Mysql? Consider upgrading that to MySQL 4.1.11

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.<br /><br />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.

Can you specify "big overhead"?

Hi<br /><br />Thanks for the respond.<br />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.<br /><br />Number of users that are on at any given time is about 300<br />I have attached the screenshot of my tables including the sizes, rows, and overhead numbers.<br /><br />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 <br /><br />Here is my my.cnf config:<br />[]<br />[client]<br />port = 3306<br />socket = /var/lib/mysql/mysql.sock<br /><br />[mysqld]<br />port = 3306<br />datadir=/var/lib/mysql<br />socket=/var/lib/mysql/mysql.sock<br /><br />skip-locking<br />set-variable = max_connections=500<br />set-variable = myisam_sort_buffer_size=64M<br />#set-variable = key_buffer=16M<br />set-variable = key_buffer=64M<br />set-variable = join_buffer=4M<br />set-variable = record_buffer=4M<br />set-variable = sort_buffer=6M<br />#set-variable = read_buffer_size=2M<br />set-variable = table_cache=1024<br />set-variable = thread_cache_size=256<br />set-variable = wait_timeout=3600<br />set-variable = connect_timeout=10<br /><br />[safe_mysqld]<br />open-files-limit=8192<br /><br />[myisamchk]<br />set-variable = key_buffer=256M<br />set-variable = sort_buffer=256M<br />set-variable = read_buffer=16M<br />set-variable = write_buffer=16M<br />[/] <br /><br />My top produces<br />[]<br />10:42:46 up 7 days, 15:28, 2 users, load average: 14.33, 17.51, 16.14<br />191 processes: 179 sleeping, 12 running, 0 zombie, 0 stopped<br />CPU0 states: 93.3% user 6.2% system 0.0% nice 0.0% iowait 0.0% idle<br />CPU1 states: 93.3% user 6.2% system 0.0% nice 0.0% iowait 0.0% idle<br />Mem: 1030284k av, 978456k used, 51828k free, 0k shrd, 58532k buff<br /> 754756k actv, 64160k in_d, 37968k in_c<br />Swap: 1020116k av, 52048k used, 968068k free 463096k cached<br /><br />PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND<br />16502 mysql 15 0 63012 54M 1276 S 15.2 5.4 97:51 1 mysqld<br />28860 apache 16 0 8160 6608 3756 S 11.0 0.6 0:06 1 httpd<br />28899 apache 15 0 8008 6444 3736 S 7.3 0.6 0:01 0 httpd<br />...<br />[/]<br /><br />Thank you for all the helps.<br /><br />Best regards

[]scroungr said: <br />using only version 3.23 of Mysql? Consider upgrading that to MySQL 4.1.11 [/] <br /> <br />Hello <br /> <br />I have a small machine (PII 400, 96MB) (fedora core 1) on my room for experimenting things. <br /> <br />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. <br /> <br />Then I downloaded mysql-4.0.24 rpm package and did an upgrade. <br />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). <br /> <br />I don't know what's going on here.

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.<br /><br />Hope that explaination helps a bit. <img src="http://www.ubbdev.com/forum/images/graemlins/smile.gif" alt="" />

> In 2 days it should be about 200,000 Bytes and it stops the mysqld.<br /><br />200,000 Bytes is ~200 kilobytes which is nothing for a table that is 33 megabytes.<br />I don't think your mysql is crashing because of this.

[]<br /> 11:58:16 up 5 days, 20:06, 1 user, load average: 1.69, 1.60, 3.90<br />385 processes: 384 sleeping, 1 running, 0 zombie, 0 stopped<br />CPU0 states: 39.4% user 4.1% system 0.0% nice 0.0% iowait 55.3% idle<br />CPU1 states: 43.1% user 4.4% system 0.0% nice 0.0% iowait 51.3% idle<br />Mem: 1030284k av, 1005520k used, 24764k free, 0k shrd, 89664k buff<br /> 713968k actv, 103000k in_d, 21692k in_c<br />Swap: 1020116k av, 198712k used, 821404k free 591440k cached<br /><br />[/]<br /><br />My top after deleting junk files.<br />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.<br /><br />I am using Single Xeon 2.4HT with 1GB memory. Apache 2.0 and mySQL 4