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 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.
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
[]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).
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.
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
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.