Well, recently I've been dealing with mysql and w3t performance issues a lot.<br />I've got w3t perl installation on a linux box with apache and mysql.<br />The forum has about 250 000 posts and about 4000 users.<br />Until last weekend it run on 2 servers:<br />-httpd-modperl on pentium II 333 (160MB RAM, scsi disk) - load about 0.5 to 1<br />-mysql on dual PIII 850 (256MB RAM, scsi disk) - load about 1 to 3<br /><br />On monday I moved http and mysql to single server with one pentium III 1GHz, 256MB RAM and ide disk (ATA66, 7200rpm).<br />I've also upgraded 5.3perl to 5.4b1.<br /><br />On old servers forum run quite well, could be better, but it was acceptable.<br />On the new server it runs pretty fast...except for the day hours where there are 50-100 concurrent users.<br />Load is going to 20-50 (!), there is a lot of mysql and httpd processes and the forum hangs.<br /><br />The problem is that lots of sql queries are locked.<br />As you know standard mysql table type (myisam) does table locking, which means that when you do an update rest of queries have to wait for update to complete (they are locked because of lock on table).<br />With small tables it isn't a problem, because updates, selects are fast then.<br />The problem starts showing with large tables when queries are longer.<br />From mysql manual:<br />"Table locking is, however, not very good under the following senario: <br /><br />A client issues a SELECT that takes a long time to run. <br />Another client then issues an UPDATE on a used table. This client will wait until the SELECT is finished. <br />Another client issues another SELECT statement on the same table. As UPDATE has higher priority than SELECT, this SELECT will wait for the UPDATE to finish. It will also wait for the first SELECT to finish! "<br /><br />This is common scenario in w3t.<br />Some clients issues selects from w3t_Posts to read/find posts (or ie. count unread posts in all boards), while some other just read some post and are updating read counter for this post, which is in the same table as post.<br /><br />"Some possible solutions to this problem are: <br /><br />1)Try to get the SELECT statements to run faster. You may have to create some summary tables to do this. <br /><br />2)Start mysqld with --low-priority-updates. This will give all statements that update (modify) a table lower priority than a SELECT statement. In this case the last SELECT statement in the previous scenario would execute before the INSERT statement. <br />3)You can give a specific INSERT, UPDATE, or DELETE statement lower priority with the LOW_PRIORITY attribute. <br />4)Start mysqld with a low value for max_write_lock_count to give READ locks after a certain number of WRITE locks. <br />5)You can specify that all updates from a specific thread should be done with low priority by using the SQL command: SET SQL_LOW_PRIORITY_UPDATES=1. See section 7.33 SET Syntax. <br />6) You can specify that a specific SELECT is very important with the HIGH_PRIORITY attribute. See section 7.19 SELECT Syntax. "<br /><br />I've tried --low-priority-updates. Doesn't help. updates of b_counter are locked instead of select and situations looks the same.<br />I've tried max_write_lock_count=1. Better, but also doesn't solve the problem.<br />I've changed updates of b_counter in showthreaded.pl and showflat.pl to have low priority - updates locks again.<br /><br />I wonder what could be done to make w3t more scalable.<br />Split boards to separate tables to make queries faster?<br />Good old times :-) But searching in all forums would be harder.<br /><br />I've noticed that mainly problem is with lots of councurrent 'select count (*) from w3t_Posts' and 'update w3t_posts set b_counter=b_counter+1 where B_main=x'<br />Every hit on a post issues update on b_counter in w3t_posts, which isn't good (even though b_main is a key)<br />I wonder if b_counter could be moved to a new table (along with b_number and b_main) and if it helps.<br /><br />Again from mysql docs:<br />"Concurrent users is not a problem if one doesn't mix updates and selects that needs to examine many rows in the same table."<br /><br />"If you get speed problems with the table locks in MySQL, you may be able to solve these to convert some of your tables to BDB tables. "<br /><br />Well, I've tried BDB. <br />First - it was unstable, mysql (3.23.37max) crashed often.<br />Second - select count(*) is SLOW with berkeley db, becaue it doesn't maintain a count of the number of rows in the table (typical count was about 4 minutes, while it was <1 sec with myisam)<br />Third - w3t_posts grow from 170MB to 400MB with bdb<br /><br />I've also tried to use new innodb tables, but I didn't manage to convert w3t_posts - alter table w3t_posts type=innodb says 'row too big - can't allocate memory' <br /><br />so, do I need to change to postgresql to have row level locking or something could be done with w3t database design or maybe there is some option in mysql which could help<br />I could also buy another pentium 1G, but for how long it helps<br />Deleting old messages is not an option rather.<br /><br />Greetings,
Piotr

Is your current version of mysql locking tables by default? From the mysql manual:<br /><br />Normally, you don't have to lock tables, as all single UPDATE statements are atomic; no other thread can interfere with any other currently executing SQL statement.<br /><br />They suggest the only time to lock a table in these cases:<br /><br />If you are going to run many operations on a bunch of tables, it's much faster to lock the tables you are going to use. The downside is, of course, that no other thread can update a READ-locked table and no other thread can read a WRITE-locked table. <br />If you are using a table handler in MySQL that doesn't support transactions, you must use LOCK TABLES if you want to ensure that no other thread comes between a SELECT and an UPDATE. The example shown below requires LOCK TABLES in order to execute safely: <br /><br />Neither of these cases really apply to W3T.<br /><br />I don't think there would be any way to change the scripts if tables are always locked from and insert,select,update etc. as your forums grow the amount of traffic will keep tables almost continuosly locked.<br /><br />-------------------<br />Scream<br />WWWThreads

I am expecting like 100 concurrent users soon.. Should i use postgres 7.1 instead of mysql then?<br /><br /><br />

I am at 135 users now during peak times and ran into this situation just today on Win2k and mySQL. all though I had it up to as high as 189 users concurrently and no dramatic problems like today ??? seemed to be a lot of posters. We have 1500 users and growing my db is about 70k posts and about 55mb in size. <br /><br />single box dual xeon pII 450's 1 gig ram, raid 5 array. I was thinking about seperating DB from APP's any suggestions guys. Thanks.<br /><br />

At some point it is a very good idea to split the db into a separate machine. Probably the busiest site running W3T is Stratics.com. They have anywhere from 400-500+ users online most of the time and they went with 2 servers, one for the db and one for the webserver.<br /><br />-------------------<br />Scream<br />WWWThreads

> Is your current version of mysql locking tables by default? <br /><br />mysql uses table locking - it isn't an option, that's the way mysql works when you write to table (see chapters 13.2.8-9 and I.4)<br />locking doesn't appear with selects and sometimes with inserts (new mysql versions use versioning to do concurrent inserts)<br /><br />> Normally, you don't have to lock tables, <br /><br />I didn't mean locking table by sql command. I meant this atomic locking which mysql does when you write to table.<br />When you do show processlist you can watch state field for queries. If you issue a long update query and a concurrent select query on the same table you can see that select query has state "locked", because it waits for update to complete.<br />You can also watch "show status" which in version 3.23.33+ shows Table_locks_waited and Table_locks_immediate.<br />Typically when you have short queries you will have locks_waited much smaller than locks_immediate (ie. 30 waited and 60000 immediate on my system during single evening hour), which means that only few queries had to wait for other queries to complete.<br />On busy hours on my forum waited/immediated is about 1/50 (compare with above 1/2000). Also slow_queries (that is queries longer than long_query_time variable, default 10sec) is getting large (normally <10, it grows >1000)<br /><br />> I don't think there would be any way to change the scripts if <br />> tables are always locked from and insert,select,update etc. <br />> as your forums grow the amount of traffic will keep tables <br />> almost continuosly locked<br /><br />If you move b_counter to new table updates of b_counter won't be locking w3t_posts for selects.<br />New table would be smaller so updates would also run quicker.<br />I will turn off read counter for a while to see if it's the main reason of locks.<br /><br /><br />A few tips from mysql manual which I think could be helpful:<br /><br />-Try to avoid complex SELECT queries on tables that are updated a lot. This is to avoid problems with table locking<br /><br />-Get Your Data as Small as Possible<br /><br />-If you very often need to calculate things based on information from a lot of rows (like counts of things), it's probably much better to introduce a new table and update the counter in real time. An update of type UPDATE table set count=count+1 where index_column=constant is very fast! This is really important when you use databases like MySQL that only have table locking (multiple readers / single writers). <br /><br />- Use HEAP tables to get more speed when possible. (I've changed w3t_Online to heap - it doesn't help a lot, but why not, heap tables are created for this kind of tasks)<br /><br />-In some circumstances it can be beneficial to split into two a table that is scanned very often. This is especially true if it is a dynamic format table and it is possible to use a smaller static format table that can be used to find the relevant rows when scanning the table. <br /><br />Greetings,
Piotr

Interestingly enough, although mySQL seems to be the defacto standard in regards to openSource DBs, it appears that PostgreSQL (its opensource competitor) is 4 to 5 times faster. Here is a link to an independent testing that was done with mySQL, PostgreSQL, Oracle, and some other 3rd party DBs. <br /><br />It looks like Postgre was the only opensource DB that could hang with its commercial competitors.<br /><br />http://apachetoday.com/news_story.php3?ltsn=2000-08-14-008-01-PR-MR-SW<br /><br />Is anyone currently using Postgre as their backend, and what are their experiences with this product? I am just curious as to whether it'd be worth the change or not.<br /><br /><br />= Jim =

Interesting. It seems like on very large forums things would totally choke, so this is why I don't really understand how this works. I know on some forums with serveral hundred users online it seems that there would always be queries waiting to be processed. I keep refering to stratics.com, but that is a good example on this. With 400-500+ users online at one time it seems like this wouldn't work hardly at all. So, I guess I'll need to read up a bit more on mysql.<br /><br />Moving the B_Counter field into another table would be ok for writes, but this means an extra query would need to be done for each post displayed to grab the B_Counter field from the new table.<br /><br />I've tried before to use smaller tables or use another table to hold things for total posts, etc so the COUNT(*) doesn't need to be used so much, but this can't be done as the view is different depending on the user, so everything has to stay dynamic to get a true view of everything.<br /><br />-------------------<br />Scream<br />WWWThreads

It would be interesting to compare two *identical* setups - one using Mysql and the other Postgres. That way we would know for sure. Anybody feel like doing it?<br /><br />[]http://www.wopr.com/w3tuserpics/Eileen-sig.gif[/]

Another note in regards to performance -<br /><br /> If you're running the php version, you have a few options (I haven't researched the Perl version other than mod_perl). There are a couple of php "precompilers" that will compile each individual script and force the web server to run the compiled script, instead of parsing it each time. It has increased efficiency (according to Zend.com) as much as 400%, which would be well worth the trouble to looking into. There is the Zend caching system, which is quite expensive, which is about $800. That can be found at :<br /><br />http://www.zend.com<br /><br />Or, there is an opensource php caching system being worked on, though I haven't heard much as far as its reliability or performance. It can be found at :<br /><br />http://apc.communityconnect.com/<br /><br />I am looking to explore the latter of the two options (and also exploring the conversion of mySQL to PostgreSQL). Hopefully it will help me to 'stretch' my hardware before being forced to upgrade to compensate for the usage demand.<br /><br />Hope this helps.<br /><br /><br />= Jim =

> If you're running the php version<br /><br />I'm running perl version, but will move to php with 5.4 release.<br /><br />> There is the Zend caching system, which is quite <br />> expensive, which is about $800<br /><br />I use free zend-optimizer (on other systems) from the same company.<br />But I've never checked if things run quicker :-)<br /><br />Greetings,
Piotr

You might be able to use a merge table (available in MySQL 3.23.25) to split the data tables down without having to do much surgery to W3T. I've never used them myself, but from the description it sounds like it allows you to SELECT and DELETE (but not INSERT) from a collection of tables that have the same column definitions as if they were one big table.<br /><br />Here is a very rough idea (without much thought about the details of the W3T internals--I'm not enough of an expert). You could create two tables: new_posts, and old_posts, and a merge table called all_posts. All new posts get put into new_posts which is a small table (you would periodically move its contents over to old_posts to keep it small) so inserts would be fast and your locking problem is (hopefully) gone. When users navigate through the forum it reads from all_posts so everything behaves as it currently does (since new_posts is small there shouldn't be much performance hit for having two tables instead of one). Probably the only change needed to W3T would be to have the code able to INSERT and SELECT from two different tables (new_posts and all_posts respectively).<br /><br />Bill Dimm, SaveTheFreeWeb.com

Scream -<br /><br /> What would be the overhead involved with having an individual table represent each board? I understand that there would have to be some sort of conversion program written, to convert from the current db structure, so this would be a major overhaul.<br /><br /> Stroker, the fact is that we are in the minority and these changes wouldn't even be noticed by most running w3t. I'd be very suprised that Scream would be interested in doing these changes; it just doesn't serve most of his userbase. <br /><br /><br />= Jim =

I get it PostgreSQL 7.1 or Oracle not helping enough for mySQL your performance problems caused by table locking? If you have not tried i suggest trying those databases first before making any table structure modifications. Thats just how i would try it next There is conversion tools for mysql -> postgres and oracle.<br /><br /><br /><br /><br /><br /><br />

Well, I'm not Scream but I'll try an answer anyway []/testimages/icons/smile.gif[/].<br /><br />I read somewhere or other (and I can't find the reference now...) that if you divide a table into lots of tables you can hinder MySQL's ability to buffer things (and I suppose you can run out of file descriptors too). I don't know how to quantify the impact though. <br /><br />I think I've read before that W3T used to use a separate table for each board but it was changed because some shared hosting accounts were not allowed to have so many tables (probably for the reasons above).<br /><br />One problem with this approach is that if it fixes the problem it only does so temporarily. As the number of posts increases you will eventually hit the point where each board is individually as large as the entire forum is right now, and then you're out of luck--no way to further subdivide it. In contrast, with the method I proposed (cutting into "new" vs. "old" posts) the table that the insertions go into doesn't grow as the board gets older because you keep emptying it out (into the "old" table) periodically. Also, if you divide up the posts table by board and you have 10 boards you have reduced the size of the table where the insertions go by roughly a factor of 10 (assuming all boards are about the same size) whereas doing the new-old split allows you to make the insertion table arbitrarily small by increasing the frequency of sweeping the new posts into the old table (you probably have to shut down the forum when you do this, so once a day is probably the most that is practical). If your forum is 1 year old and you sweep every day, the insertion table will be roughly 365 times smaller than it is now.<br /><br />P.S. Does anybody know why this says that insertion time goes like N*log(N)? To me it seems like it should just be log(N) (i.e. the amount of time it takes to work through the tree to find the node where the insertion happens). Maybe they are talking about the time it takes to insert N items rather than the time it takes to insert 1 item when there are already N of them in the table (although that's really not the relevant thing in this context)?<br /><br />Bill Dimm, SaveTheFreeWeb.com

Well, I think I've found real reason of slow down<br />"Error in accept: Too many open files in system" - I didn't notice this in logs earlier and thought that problem is with mysql. <br />I don't now yet what is causing this, but I know where to look.<br /><br />Anyway, this was interesting discussion - thanks everyone for participation :-)<br /><br />Greetings,
Piotr

Maybe you are running out of file descriptors. In /usr/bin/safe_mysqld there is a chunk that looks like:<br /><br />#if type ulimit | grep "shell builtin" > /dev/null<br />#then<br /># ulimit -n 256 > /dev/null 2>&1 # Fix for BSD and FreeBSD systems<br />#fi <br /><br />Bill Dimm, SaveTheFreeWeb.com

Guess what, problem was with apache.<br />File descriptors are also used by sockets, so I checked netstat -an<br />There were a few thousand of connections with dns servers,<br />which disappeared after killing apache.<br />I solved the problem temporary with hostnamelookups off in httpd.conf.<br /><br />Greetings,
Piotr

We have a database to run a bulletin board. There are a bunch of reads to read posts, a few writes to write new posts.<br /><br />Almost all database action is about cute, but unnecessary accessories, like kepping count of user data, like last access time, read counts, etc.<br /><br />I suggest we get an option to shut these server hogs off. Alternatively, there might be a database organisation that improves performance. I could care less about data security of write counts and user visit times. I would not mind if that stuff stayed cached for a long time and only occasionally got written to disk. <br /><br /><br /><br /><br /><br />