Marco, it would be more helpful if you could post the table structures involved ("SHOW CREATE TABLE <tablename>") as well as the SQL query that is giving you this problem.
Off the top of my head: - InnoDB is designed to solve this exact problem; maybe re-visit why you cannot use it, given that it solves your problem. - Increase your MySQL cache settings to allow holding more of your data in RAM - Move older posts into an archive table to keep your "live" or more recent posts more responsive. The MERGE feature may be very helpful in this area. Dan On 8/21/06, Marco Simon <[EMAIL PROTECTED]> wrote:
Hi everybody, I've got a little problem with a web and mysql based bulleting-board-application. The board is quite well visited and users are writing aprox. 1 new post per second. In total the db gets aprox. 250 queries/sec. The webserver and mysql-server are running on different hosts, the db server is running on Linux db 2.6.14.2 #4 SMP Thu Nov 17 09:54:44 CET 2005 x86_64 GNU/Linux model name : Intel(R) Pentium(R) D CPU 3.00GHz stepping : 4 cpu MHz : 3000.229 cache size : 1024 KB 4GB RAM. My problem is that I get a lot and very long locks (30 sec and more) on the table which is holding the user's posts. There are aprox. 3.3 Mio records in that table which consumes a size of 1.5 GB. The table-format is myisam. So far switching to innodb mostly soluted my problems with table locks. But because of the table size and the limited Memory I can't switch to inno-db in this case. Are there any other conceptional or technical ideas how to reduce the (long lasting) table locks ? Thanks for any idea in advance ! Best regards, Marco
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]