RE: innodb and vbulletin 1.1
I work for vBulletin and have some input on this. First off, you really should look into upgrading to version 2.2.x, as not only are there many new features, but there have been a number of performance enhancements done that may help your situation. You also might want to take a look at the vBulletin forums ( http://www.vbulletin.com/forums/ ) for MySQL Server Configuration, where we might be able to give some more tuning help specific to vBulletin. I know a few sites that are using InnoDB, or have tested using InnoDB tables. vBulletin will run just fine in this configuration, however there is one issue many users have run into: InnoDB does not store table counts, so select count(*) from table-name statements can take a while, and there three of these statements on the main index.php page for the total number of users, threads and posts. There are some workarounds you could look into, read this thread for more information on that: http://www.vbulletin.com/forum/showthread.php?s=threadid=36219 From the InnoDB manual: InnoDB does not keep internally a count of rows in a table, which would actually be somewhat complicated because of multiversioning. To answer a query SELECT COUNT(*) FROM T InnoDB has to scan one index of the table, which will take some time if the table is not entirely in the buffer pool. To get a fast count you have to use a counter table you create yourself, and let your application update it according to the inserts and deletes it does. A way to eliminate the bottleneck posed by lock waits for the counter is to create a whole set of counters. The application can choose one at random each time. To get the count, just sum the counters: SELECT SUM(counter_column) FROM your_counter_table. Hope this helps, Chris Schreiber -Original Message- From: Byron Albert [mailto:[EMAIL PROTECTED]] Sent: Friday, January 19, 2001 3:03 AM To: [EMAIL PROTECTED] Subject: innodb and vbulletin 1.1 Hello, I have a few questions. First I work on a very high traffic site that uses vbulletin 1.1 to run its bb. The bb is very high traffic around 60-200 concurrent users. We are starting to run into some serious locking issues, and I am thinking about converting the high use tables(maby all) to innodb. My first question is will this break anything in the application layer? Second we may be moving this to a new serve where I could have 6+ disks. I have done some testing and found that after all the importing into innodb all the data is around 1gb. Would it be helpful to add these extra disks creating 1+gb raw partition on each one to spread the io across the disks and controllers. And how does innodb distribute the data through the table spaces? Thanks for any help Byron [EMAIL PROTECTED] - 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 - 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
RE: ERROR 1006: Can't create database 'mynewdb'. (errno: 28)
Error code 28: No space left on device You're out of disk space on the drive where your databases are stored. Chris Schreiber -Original Message- From: John Lepone [mailto:[EMAIL PROTECTED]] Sent: Monday, December 17, 2001 3:20 PM To: '[EMAIL PROTECTED]' Subject: ERROR 1006: Can't create database 'mynewdb'. (errno: 28) I have recently installed MYSQL on my Mandrake 7.1 Linux box. I connect to the DB as root. When I try to create a new DB, I get the following error: [jlepone@mandrake jlepone]$ su Password: [root@mandrake jlepone]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 3.23.46-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE DATABASE mynewdb; ERROR 1006: Can't create database 'mynewdb'. (errno: 28) mysql Likewise, if I try to add a table to an existing DB I get the following: mysql use test; Database changed mysql CREATE TABLE mynewtable (mycolumn int); ERROR 3: Error writing file './test/mynewtable.frm' (Errcode: 28) mysql can anyone help? - 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 - 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
RE: [OT] A News Group Perhaps.
Actually I setup a vBulletin forum for MySQL over at www.mysqlforums.com and I am the moderator of the MySQL and Server Configuration forums over at www.vbulletin.com/forum/ I've been keeping an archive of the mailing lists, mainly for my own purposes to search through old questions and answers from these lists, but I certainly would like to offer it to others that would find it useful for searching for older threads and for those that prefer a web-based solution over email lists. Chris Schreiber -Original Message- From: Kelly Firkins [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 12, 2001 8:22 AM To: [EMAIL PROTECTED] Subject: Re: [OT] A News Group Perhaps. I would vote in favor of using vbulletin (www.vbulletin.com) it's got forums for talk like this, web-based, and runs off of the MySQL server as a back-end. An example in action is vbulletin itself or community.installshield.com. Very slick indeed. Kelly FYI, I'm somewhat biased in favor of it because I have set up this program and know how well it works. It's a great example of PHP and MySQL working together. Hi Matt, I am sure this has been said before so my apologies if I bore! Most mail clients let you setup rules. For the mySQL list I automatically redirect all messages into a separate folder based on the [EMAIL PROTECTED] email address. This creates a fabulous resource that can be searched (title and text) and can be read or ignored by choice. I am not a great fan of Newsgroups - they get spammed too much and messages get lost or archived. Some ISPs carry them, some don't. All too hit and miss for a resource on which I rely and am very grateful for. Tony - Original Message - From: Matthew Darcy [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, December 12, 2001 10:58 AM Subject: RE: A News Group Perhaps. Hi, I was wondering if the mysql list had any plans to be put onto a news group. I have only been a memeber a short time but I have found %50 of the information to be usefull. This does however mean that %50 is does not apply to me at this time. Due to this I get a lot of emails that are of no use to me at the moment. I would be keen to talk about hosting this list as a news group to make it browsable so myself and others could pick out infromation that is usefull to me at this time. The email list could stay for mass contributions but I would rather just browse for information I need. Any thoughts on this ? Thanks, Matt. - 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 - 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 -- Kelly Firkins [EMAIL PROTECTED] _ Chat with friends online, try MSN Messenger: http://messenger.msn.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 - 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
RE: Horrible performance degredation on Solaris environment -vs- FreeBSD environment ...
I've had the same experience myself. I had a client running a large MySQL database with 500+ concurrent users on a Sun E420R quad processor machine with 2GB RAM and mirrored SCSI drives. Performance was acceptable, but there were some performance problems during peak usage times, that shouldn't have been happening with the amount of hardware he was using. I ported the databases to a dual-CPU Linux box with 1GB of RAM, and with some tuning had things running faster and more consistently on Linux then the Sun machine, and the monthly hardware cost savings were over $3000USD. I know certain databases (Oracle for example) seem to run much better on Sun machines, but as far as MySQL goes, I've seen the best performance when running on Linux over the other OSes that are available. Hope this helps, Chris Schreiber -Original Message- From: Matthew McHugh [mailto:[EMAIL PROTECTED]] Sent: Tuesday, December 11, 2001 12:27 AM To: [EMAIL PROTECTED] Subject: Horrible performance degredation on Solaris environment -vs- FreeBSD environment ... Hello All, I am running a mysql DB that is a little under 8Gb in size. I have this same DB running in two separate environments (1 a FreeBSD environment running on a single Pentium III 900 Mhz CPU w/128 Mb of RAM, and the 2nd one on a Sun (sparc) solaris 8 ultra enterprise 420R with 4 Gb of RAM and 4 x 450 Mhz CPU's). The FreeBSD PC outperforms the Sun box 100% or better. I perform a load on the FreeBSD box and it completes on 13 hours, the Sun box takes 21.5 hours or longer. I also do run other jobs on this DB and in all cases, the PC significantly outperforms the Sun box. The data is the same (an exact copy). I am using the same versions of the mysql distribution (obviously I don't just copy of the binaries. I have tried the Solaris 8 mysql install from source and I also tried the mysql binary for Solaris 8, but neither of the two showed any improvement in performance. The Sun box is also on a EMC symmetric with 8 Gb of cache and the filesystems are stripped across several disks. The PC is a simple 5400 RPM ide drive. I tried to rule out the OS and hardware of this 420 by installing the same mysql database on a Sun solaris 2.6 ultra enterprise 450 with internal disks (not on a EMC RAID), but that is even longer. I expected the 2.6 load to take over 40 hours so I eventually killed the process after 24 hours. I also ran the Solaris 8 environment in both 32 bit and 64 bit with 0 change in performance. The documentation states that Solaris 7 and Solaris 8 are the best OS environments in which to run mysql, however this does not seem to be the case. Any help on what I should do here would be much appreciated. The Sun boxes are not sweating at all. Their cpu's are idle 74% of the time, their is no memory issue (shown via vmstat), no i/o issue shown via vxstat (I'm running Veritas' filesystems and volume manager on these boxes), nor Sun's iostat utility. Thanks, Matt - 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 - 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
RE: Hang???
Sure, open up a new MySQL session, and use SHOW PROCESSLIST; to monitor the query status. It should report what it's doing and how long the process has been running. Chris Schreiber -Original Message- From: Karl Stubsjoen [mailto:[EMAIL PROTECTED]] Sent: Monday, December 10, 2001 8:38 PM To: [EMAIL PROTECTED] Subject: Hang??? Hello, How can I tell if a process is hanging? I am performing a large (fairly) insert from one table to another (insert into... select from) I have been looking at a non-MySQL prompt for 20 minutes or more (non, meaning: after you execute a command you are between prompts for awhile until the command is finished, at that time you get a prompt again). Thanks! Karl www.excelbus.com/info-m ..opportunity knocking.. - 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 - 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
RE: innodb won't allocate the memory i ask it to
If you are running FreeBSD, there is an inner limit, which doesn't allow malloc() calls greater then 500MB. You need to reconfigure the kernel sources and recompile (or change your InnoDB parameters to use less then 500MB of RAM). Chris Schreiber -Original Message- From: Heikki Tuuri [mailto:[EMAIL PROTECTED]] Sent: Saturday, December 08, 2001 4:58 AM To: [EMAIL PROTECTED] Subject: Re: innodb won't allocate the memory i ask it to Rich, I have a system with 1gig of ram and i am trying to get the innodb buffer to be about 750megs. Whenever I set the value in the database config file it won't start with an error along the lines of, 011207 21:02:42 mysqld started InnoDB: Fatal error: cannot allocate 67108864 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 484667027 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. 011207 21:02:44 mysqld ended I have 1gig of ram and a 1gig swap file. Any ideas why it won't assign more than roughly 450megs of memory to mysql? do you have other big processes running at the same time in your computer? If not, then this is probably a problem in Linux or glibc. Some Linux versions seem to refuse to allocate big amounts of memory. You could try another Linux kernel version, or write to your Linux vendor about the problem. Rich Regards, Heikki http://www.innodb.com -- Order commercial MySQL/InnoDB support at https://order.mysql.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 - 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
