Let's see if I can give you some ideas. > -----Original Message----- > From: RV Tec [mailto:[EMAIL PROTECTED] > Sent: Tuesday, May 18, 2004 8:28 AM > To: [EMAIL PROTECTED] > Subject: MySQL limits. > > We have a database with approximately 135 tables (MyISAM). > Most of them are small, but we have 5 tables, with 8.000.000 > records. And that number is to increase at least 1.000.000 > records per month (until the end of the year, the growing rate > might surpass 2.000.000 records/month). So, today our database > size is 6GB.
That's an average size for most applications. > > The server handles about 35-40 concurrent connections. We have > a lot of table locks, but that does not seem to be a problem. > Most of the time it works really well. Table locks in my opinion are bad. Especially with 35 concurrent connections. On one of my servers we currently have 1498 threads running, we are averaging 2044.431 queries per second, and 1 slow query for the past month. I restarted mysql on the wrong box on accident. But I would still consider these numbers to be nothing compared to some others around here. > From time to time (2 weeks uptime or so), we have to face a > Signal 11 crash (which is pretty scary, since we have to run a > myisamchk that takes us offline for at least 1 hour). We > believe this signal 11 is related to the MySQL server load > (since we have changed OS's and hardware -- RAM mostly). What does it say in the mysql_error_log when this happens? Mysql will usually dump the reason out in the error log and it's pretty easy to solve after that. Have you considered using the binary version of MySQL instead of compiling from source? > > Our server is one P4 3GHz, 2GB RAM (400mhz), SCSI Ultra160 > 36GB disks (database only) running on OpenBSD 3.5. We are > aware that OpenBSD might not be the best OS for this > application... at first, it was chosen by it's security. Now > we are looking (if that helps) to a OS with LinuxThreads > (FreeBSD perharps?). Sorry, can't help you with BSD. Linux for me all of the way. > > The fact is that we are running MySQL on a dedicated server, > that keeps the load between 0.5 and 1.5. CPU definitively > is not a problem. The memory could be a problem... our > key_buffer is set to 384M, according to the recommendations at > my-huge.cnf. So, it seems we have a lot of free memory. We > have already tried to increase key_buffer (along with the > other settings), but it does not seem to hurt or to improve > our performance (although, the memory use increases). 384 for key_buffer is probably fine with 2gigs of memory. Some will say that you can go up to 1/2 of the memory, but I like to stay around 400 myself. But it really varies based on what you are doing. We had to do a lot of testing of our application to find the right number. > > To track down this signal 11, we have just compiled MySQL with > debug and returned to the original my-huge.cnf > recommendations. Now it seems we are running on a overclocked > 486 66mhz. That's what debug does. Use the binary, that's my recommendation. > > Is there any way to prevent this signal 11 to happen or is it > a message that we have exceeded MySQL capability? Exceeded MySQL's capability? I don't think you have scratched the surface yet. Error messages are just that, an error of some type. Without knowing the version of MySQL you are running, it's even harder to know. > > Is MySQL able to handle such load with no problems/turbulences > at all? If so, what would be the best hardware/OS > configuration? For me, I buy dual proc xeons with hyperthreading. 2 or 4 gigs of memory. Fedora Linux, RPM install of mysql 4.1.1 (4.1.2 is getting close!) Apache 2.x, and php. I install apache and php on all of our servers no matter what, because you never know when you need them. I know many people will tell you to buy opteron's, we just haven't bought one yet, since our vendor of choice doesn't offer them yet. > > What is the largest DB known to MySQL community? I've heard that cox communications is fairly large, at least according to this: http://www.mysql.com/news-and-events/press-release/release_2003_21.html It says theirs is about 600 gigs. But I am sure there are larger ones around. On one server we have about 170 gigs right now of databases. Donny > > If it's needed, I can provide DMESG, MySQL error log, compile > options and some database statistics. > > Thanks a lot for your help! > > Best regards, > RV Tec > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
