Results 1 to 4 of 4

Thread: MySQL Abusers - New MySQL Hostnames - Optimize Your Database

  1. #1
    PowWeb Staff
    Join Date
    May 2006
    Location
    Planet Earth - for now!
    Posts
    1,297
    Rep Power
    14

    MySQL Abusers

    To reduce some necessity for speculation:
    1) The MySQL problem can occur on any of the servers -- they are all essentially configured identically. Some people have found this by moving databases from one server to another ...

    2) Backups essentially run continuously, since the volume of databases/accounts is such if we stopped for more than an hour or so, we would not have any prospect of a "daily" backup. This load is kept minimal ...

    3) There are still very occasional issues with CGI (scripting) which are generally caused by the same sites that cause the MySQL issues... but, these are very, very, rare.

    4) To identify a problem we usually have to identify the sites with too many connections -- once we shut the sites with 20-30(or more) connections open down, things usually return to something more normal within a few minutes -- sometimes, we need to "bounce" the box since it is also possible for a MySQL task to so completely tie things up that a service restart is going to resolve things more quickly than wrestling the offenders into line ... that happens sometimes, not frequently.

    5) As we noted since the very first announcements of the upgrade to CGI/MySQL -- this is a 3 stage process -- 1) Upgrade CGI platform 2) Upgrade MySQL platform 3) Add additional modification to system to reduce possibility of a site tying up too many connections --- essentially, moving toward a system where the MySQL resources will be limited based on the account (or username subdomain reference) to be able to identify and/or limit concurrent connections and/or possibly other relevant metrics that would limit the impact of any site on the others...

    As Yvette notes, there are many, many sites which are working reasonably well --- but, essentially any site using MySQL could be affected at any point -- if we shut some sites down, things may remain slower for a period until some jobs complete ... if we "bounce" the server, things will come up blazingly fast for a brief period, until additional jobs and/or website hits return the site to a typical load condition (or above).

    6) We issue multiple suspensions every day -- I've sent out suspension notices sometimes in batches of 12-13 in an hour (and I'm just one support person) --- and, we're getting better at identifying the problems more quickly --- the problem is, sometimes things go wrong with a site (database corruption, change in script or job, change in load when some other sites stop being a limit on resources) such that a site that "was never a problem before", all of a sudden is --- either because something changed on the site, or some other jobs or sites that were limiting the site have now been removed.

    7) Every support person wishes they knew "when" this would be done --- but we don't, and, having discussed this with people right up to the top -- they aren't certain "when" it will be completed either --- when the solutions currently being tested are ready (when we're sure they are the best possible solution with the least interruption to service and/or required changes to hosted sites), then they will be introduced --- in the interim, the hardware and tuning changes and some "rough in" continue, so that when the "software side" changes are finalized, they can be deployed with all speed.
    Last edited by Croc Hunter; 3-16-08 at 07:32 AM.
    Well, that's the way I see it -- for the moment!

  2. #2
    joshuamc's Avatar
    Join Date
    Oct 2003
    Location
    Boston, MA
    Posts
    2,511
    Rep Power
    18

    New MySQL Hostnames

    As of today, you no longer need to remember your MySQL hostname as mysqlxx.powweb.com. From now on, just use username.powwebmysql.com in your scripts and we'll take care of the rest.

    Don't worry -- scripts using the old explicit format will continue to work. We just ask that over the course of the next few weeks you update your code to use the new hostname format (username.powwebmysql.com). This way, when we do decide to stop supporting the old format, you won't run into any issues.

    If you want to see the new format of your MySQL hostname, it is available under the Manage MySQL page.

    Here are a couple of questions you might be asking:

    Q) Can I access this hostname from an outside source?
    A) Nope. We still limit database access to only inside our network, and these hostnames are only routable from inside our network (i.e. a DNS lookup won't even find them).

    Q) Do I need to use this new hostname style?
    A) Not immediately, but eventually, yes. We recommend using it for a few reasons, but the best reason is that it'll make it much easier for us to move databases to new servers with no disruption in service. This means we can add new MySQL servers without having to take down customer sites.

  3. #3
    YvetteKuhns's Avatar
    Join Date
    Feb 2003
    Location
    Allentown, PA USA
    Posts
    15,244
    Rep Power
    34

    How to optimize a database

    Login into OPS, click Services tab, click MySQL in menu under tabs, click Administer under the database table name for your username. You will be logged into phpmyadmin.

    Click the name of your database and wait for it to load. (It is usually fast, but not for you.) You will see a list of tables. At the bottom, you will see Check All and other links. Click Check All if you want to check all tables at once.

    There is a drop down box where you can select Check Table, Analyze Table, Optimize Table, Repair Table and other things. You can do any and all of these. You can also choose to check one table at a time. This is recommended if you want to test this first or view each separately.

    Be sure to make backups BEFORE doing any of this. The Repair option can fix minor repairs, but it won't remove spam and magically fix any problem.
    Last edited by Croc Hunter; 3-16-08 at 07:31 AM.
    Yvette Kuhns
    Power Pages Web Design
    Customized Internet Advertising Solutions

  4. #4
    Custom User Title entrecon's Avatar
    Join Date
    Aug 2006
    Location
    Michigan
    Posts
    2,742
    Rep Power
    17
    From now on, just use username.powwebmysql.com in your scripts and we'll take care of the rest.
    Most scripts will use localhost as the default. On PowWeb, you will need to use the username.powwebmysql.com reference for the database.
    ________________________________
    Find me on twitter: @entrecon

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •