No announcement yet.

13. Search

  • Filter
  • Time
  • Show
Clear All
new posts

  • 13. Search

    Search. mySQL. Queries. Database. Backups

    Reply below with URLs to the most frequently discussed requests (and answers) for this topic grouping. Click here to see all 14 "FAQ Topic Categories".

    Please Note: This is a completely open, volunteer effort and a work-in-progress. It will become useful only to the extent you take a few minutes, now and then, to post URLs here to help build these lists into a comprehensive resource. They'll be edited periodically. vB's official FAQ forum. "How do I...?" Hack Requests. Troubleshooting. Customizing templates.

    Last edited by theflow; Tue 4th Sep '01, 3:53am.

  • #2
    Table of Contents:
    Later, we may link these up after there's enough stuff to organize. For now, scroll down to match post number. Remember, there are 15 posts per page.

    2. (this Table of Contents)
    3. mySQL Database size
    4. Backing up, Restoring, Re-installing Database
    5. More Backup & Restore Questions
    6. mySQL Data files
    7. High Volume Traffic & Performance
    8. Running SQL queries
    10. Getting your site listed by Search Engines


    Last edited by theflow; Tue 2nd Oct '01, 5:25pm.


    • #3

      mySQL Database size:

      What is the size of a heavy-traffic site's mySQL data dump?
      For example, how big is's SQL dump?

      UBB data vs. vB database size:
      My message board had 143 members and about 43,000 posts total. I seem to remember that when I imported from UBB (with fewer posts at the time, of course), that it was a lot bigger, like maybe 75 or 100 MB. My vB database file was only 22 MB.

      Is this normal, though, for the database file size to be so small? Does 22 MB seem right for a board of this size?

      JimF replies:
      Yeah, 22MB sounds just about right. A text file dump of your database will give you a rough estimate of the overall size, but it doesn't account for some things that would increase the size. It is safe to assume that your database is approximately 22MB - maybe a little more.

      UBB stores every thread as an HTML file, whereas vB stores the HTML for threads in the database, so it's only stored once, instead of 15,000 times. That's one major reason for the difference in size.

      Last edited by theflow; Wed 5th Sep '01, 12:38am.


      • #4

        Backing up, Restoring, Re-installing Database

        How do I install a recent backup of my DB ?? I have moved to a new host.
        Reply to answer: "Could someone explain All this JARGON to me please in a clear simple manner?"

        And the answers... (tubedogg)

        Step One - Backup current database:
        Telnet into your system
        you will need to login to your system via telnet. Your host can tell you exactly how they expect you to do it.

        from the root directory, type mysqldump -uUSERNAME -pPASSWORD databasename > /path/to/dump.sql
        type this once you are logged in, replacing username, password, databasename, and /path/to/
        this should only take about than 30 seconds to a few minutes, depending on your database size.
        when it is completed, it will bring you back to the prompt
        verify that you have a dump.sql in the /path/to area you specified

        Step Two - Transfer to new server
        telnet into machine1, and from the command prompt type the following:
        same as in step 1, telnet into your system.
        machine1$ cd /path/to/yourbackupdirectory

        type the command (machine$ is a representation of your prompt)
        machine1$ ftp machine2 (IP or host) - this should open a connection and ask for your username/password

        same thing, type 'ftp' and then ''
        ftp> bin

        again ftp> is the prompt, type what's after it
        ftp> cd /path/to/newdirectory

        same thing, type in a path to the new directory you are putting the dump file in
        ftp> put dump.sql

        type the command
        ftp> close

        type the command
        ftp> quit

        type the command
        verify dump.sql is in the /path/to/new area on the new server make sure the file is on your server

        Step Three - Install vBulletin on new server creating a dummy
        create a new database and install vBulletin as you would do for a clean install

        Step Four - Restore to new server
        telnet into machine
        telnet into the second machine
        create new database on the mysql (either via phpMyAdmin or telnet)
        create a second new database
        from the root, type
        mysql -uUSERNAME -pPASSWORD newdbname < /path/to/dump.sql
        type this command

        this takes a couple of minutes depending on your database size, but you can track the progress either by FTP (checking the /mysql/data/newdbname and see the files appearing) or by phpMyAdmin (you should see the files appearing there)
        when it is completed, it will bring you back to the prompt

        Step Five - Bringing it back online

        Go into your config.php and change the database name to the database name you created in Step 4.

        upload config.php
        Go into your Admin CP and then click on "options" and change the URLs and verify the options and click "submit"

        Last edited by theflow; Wed 5th Sep '01, 12:38am.


        • #5

          More Backup & Restore Questions:

          Restoring a backup - onto local W2k drive (server):

          Refer to step 4 here:

          mysql dump to local machine

          How does the speed of a mysql dump grow as the database grows?

          Last edited by theflow; Wed 5th Sep '01, 12:39am.


          • #6

            mySQL Data files:

            HUGE MySQL data files eating my HD!
            I thought my forum was eating up all 7 gigs of my hard drive, but when I did a dump it's only 260MB. THEN, I noticed like 8 files in /var/lib/mysql, which range in size from 900MB to 2 GIGS! I opened some of the smaller ones, and they seem to be filled with half binary jumble and half data from my forum database (text from posts, announcements, etc.) Is it safe to delete these files? What the heck are these things???
            Thanks! Freed up 5 gigs, and server loads seem to be much lower now with all the logging off.

            Last edited by theflow; Wed 5th Sep '01, 12:39am.


            • #7

              High Volume Traffic & Performance

              Extremely high volume forums? URLs of some. Can vB handle 10,000,000 daily page views? Can mySQL? Will vB support Oracle & Sybase in future?

              A few replies:
              my vB only does between 45,000 - 75,000 pageviews/day and i believe a few vBs i have read about on this forum do up to 250,000 pageviews/day but 1 - 10 million/day

              I'm sure if vBull could handle 5-10 million hits or page views a day with the right backend and hardware. I don't know what MySQL would do though. Might throw rocks at you. (Could be wrong though. Never seen that many page views in a day.)

              High traffic boards:

              I've never seen anyone switch back to UBB after vBulletin but here is an example: HTF:

              Ed Sullivan:
              Actually, I've been working with a guy on that forum -- they're running into strange MySQL problems where queries that usually take .01 seconds would take 6+ seconds to run. Tables would lock in the "opening tables" part. Strange stuff.

              Here's a large vB forum that basically yawns at its traffic:
              With 451 users online in the last 15 minutes, the MySQL server's load is .23 and the PHP server's is 2.13 (with gzip on too I might add).

              Q: What hardware are they running to handle that particular community?
              A: Dual P733 with 1 gig of RAM for each server: one for MySQL and one for PHP.

              Q: I too heard about that site, (HTF), switching back to UBB because they stated VB couldn't handle the traffic and was too difficult to maintain reliable because of other factors like PHP and MySQL backend. That makes me wonder if others with high traffic will have the same results? Having worked with VB I really like it better, but since the other site is having such trouble it causes me some concern. Can anyone tell me if this could be site specific? They claim to have enough hardware to handle it and no problems when using UBB due to no PHP or MySQL backend. But, since the other site mentioned here with high traffic seems to be doing ok with it that makes for somewhat of a puzzle. Any guess as to if the other site has better hardware, isp, etc. or the like? Just wondering about VB limits and what's needed for real high traffic use?

              A: one factor also seems to be the OS. We ran into horrible performance problems with PHP/MySQL on a sun/solaris machine, but now on DualPentium/Linux (redhat) it's all good...

              A: (roy7) Yes, HTF's problems are, I believe, operating system related. Even when the site is off, sometimes we'd see an update take 3 seconds that should take .1 seconds. Also, sometimes 150 threads would all be in the "Opening tables" state for a long time before it'd finally flush and the site start working again.

              Optimizing for high volume traffic: (roy7)(excerpt: see thread for details)
              I've been doing lots of work helping Home Theater Forum. I'm not sure if vBulletin can scale to this size in any "simple" way... vB 2.x can handle large sites as Ed mentioned, but there are certain choices you should make which would go a long way to help:
              - First, use Linux 2.4 or Solaris for the MySQL machine.
              - On the web side, be sure to get Zend Cache to go along with the Zend Optimizer. There are other free caches but they can't cache the result of the Optimizier. The Optimizer alone vs a free cache alone doesn't seem to be a major difference either way. But caching the optimizer output is a big win.
              - There are many other improvements/etc you can make, like optimizing MySQL itself and having the MySQL databases on a seperate drive from the main system, or even spreading the tables out on individual drives.
              - I'd use at least 2 or 3 web servers to handle the PHP side of things. Zend Cache, Zend Optimizer, mod_gzip.
              - On the MySQL side it gets more complex. I'm not sure even with a dual 1Ghz machine (I know there are quad CPU x86 machines out there but I have no experiance with them and the hardware vendor I plan to use doesn't sell them) you'd be able to keep up with the traffic. So you'll want to use multiple MySQL servers. MySQL has it's own built in master/slave setup now. The way I'd set this up is using slaves in a 'read only' mode, and the master as a 'write only' machine. This gets rid of read/write conflicts and one machine should do fine with all of the writes/updates that vbulletin generates. The reads won't always be 100% up to date if a slave falls a little behind from the master, but it shouldn't cause any real problems. When you 'edit' a post, etc, it should read from the master of course.
              -Some of our problems I'm almost certain are OS related and moving to 4.3
              -I'm also considering the Squid httpd-accelerator mode to help speed up the site further, so Apache doesn't need to mess with images at all.
              -I'm not a vbulletin expert like Ed, but I've done quite a bit to try and make HTF fast on a buggy box which doesn't even let MySQL use both CPUs.
              -FanForum leaving vbulletin because of MySQL corruption sounds like admin error. MySQL in terms of basic operation is very reliable. It doesn't just corrupt the database. There had to be something causing it, from incorrect configuration or power outages without a UPS causing the database to get screwed up, etc. Any time there is a corruption not caused by that though, the MySQL dev team is very good about debugging it and looking over a copy of your bad data files, etc.
              -MySQL does love dual processors. If you are on a big Sun box with 10 processors, it'll love that even more.
              -MySQL is fully multi-threaded and has been since the dawn of time. Okay, well, not that long, but still.
              -The only time you'll have problems with MySQL and not being able to use CPUs is if you use an OS which doesn't let one process use more than one CPU, like FreeBSD. If you use Linux or Solaris, then MySQL will use all of the CPUs you have fully. It's the reason why we're moving to Debian linux for our database box. I love FreeBSD in general and it's fast and stable for web servers, etc, but for multithreaded software it doesn't do multiple CPUs.
              -Apache has no problem using both CPUs on FreeBSD because it uses seperate processes, not one process with seperate threads. Some FreeBSD people run two MySQL servers on the same machine, so each uses a cpu, but that's too much of a hack for me. BTW, the 2.4 linux kernel has significant threading library improvements, so for maximum performance 2.4 is the way to go.
              -MySQL only does table locks in all of the "default" table types. That can cause performance problems, esp. with things like vbulletin under heavy usage. But it does support the same sort of row locking PostGres does in InnoDB, and also does page locking in BDB. (PostGres and MySQL's InnoDB seem to be related since they both had the same 8K data per row restriction, and some other identical features.)
              -PostGres is a fine database system and works great for many people. But please don't claim MySQL won't use dual CPUs, that's false.

              eva2000's list of other high-volume vB boards:
              not sure how much 's vB does

              Last edited by theflow; Wed 5th Sep '01, 12:39am.


              • #8

                Running SQL queries:

                Run a SQL query directly from your admin control panel (Hack - firefly)

                Running a Query using phpMyAdmin (plus installing phpMyAdmin) (tubedogg)

                Last edited by theflow; Wed 5th Sep '01, 12:40am.


                • #9



                  • #10

                    Getting your site listed by Search engines:


                    Last edited by theflow; Tue 2nd Oct '01, 5:43pm.


                    widgetinstance 262 (Related Topics) skipped due to lack of content & hide_module_if_empty option.