>Hi, >I'm running mysql 3.23.42 on a 1.4ghz athlon with 512mb of ram for my >database server but this machine doesn't seem to be able to handle the load, >which makes me think i must be doing something wrong. The primary job of >this database is radius authentication for our 30k or so customers so it is >imperative that we have a well functioning database, but the cpu load on the >machine is generally around the 30 mark and it occasionally dies altogehter. >Could anyone shed any light on what could be causing this problem? >If it makes any difference,the tables are in innodb format as table based >locking could spell disaster in a situation such as this. >Thanks, >Daniel
Hello Daniel, That should be more than enough hardware to handle accounting logs. I do something like you are doing, and my setup (e4500 4x400mhx, 4gigs ram) handles a several million customer userbase with ease. So, you are probably right in assuming you have something set up wrong. There are several places you can really increase performance. I would start with your my.cnf settings. I suspect that since you are using InnoDB that you have at least seen the inside of my.cnf. These settings can really help you get more performance out of your system. One thing to look at especially is the innodb_flush_method Depending on how important each row of data is, is how you should set this. The riskier you get with the data, the faster it runs. After digging around in my own my.cnf settings, here is what I have.. this is not really a benchmark by any means, just some quick comments I threw into the my.cnf to remember what is what. innodb_flush_method=0_DSYNC slowest, and most reliable for storing data. 44% cpu usage. innodb_flush_method=littlelesync a bit faster, might lose data. 30% cpu usage. innodb_flush_method=nosync real fast. 15% cpu usage. I am not expert on innodb, or mysql either. I started playing with those values after reading the performance guide on Innodb.com. The other thing you can get performance out of is your database table itself. With InnoDB you don't want to set its size any larger than you need, with a slight margin of safety. The larger you set the database size, the longer each insert/select is going to take. Also watch your indexes.. each indexed column will take longer to insert than a non-indexed column. The price of this is a much faster SELECT when you are including that column in a WHERE clause. On average, our radius servers can authenticate around 400 users a second. That is authenticating and recording accounting records. So a 1.4 that is just recording authentication records should be able to push quite a lot more than will be usually required of it for a 30,000 user base. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Aaron Williams [EMAIL PROTECTED] Black Raven.com http://www.backraven.com =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
