The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Flat File To MYSQL - EVEN WORSE

Discussion in 'General Discussion' started by jaymc, Jul 31, 2005.

  1. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    OK so i have a very busy website, which used to use a flat file database system for members

    i have just covnerted it to run completely from MYSQL, however now with 200 users online MYSQL is going crazy

    Im fairlly new to MYSQL, so im not aware of common programming issues that may cause crashes or lock ups

    Load = 20, 30, 40..... i look at TOP and see 279 Processes


    CPU states: cpu user nice system irq softirq iowait idle
    total 78.2% 0.0% 20.3% 0.5% 0.7% 0.0% 0.0%


    I also see a lot of mysql <defunct> in TOP


    Here is an example of the type of commands that are being run by each user every time they click a link, so its 200 X whats below near enough

    At first i thoguht it might be because i never had any 'LIMIT 0,1' its the commands, but as you can see ive added them now

    the DATABASE in questions is the members one, its 4.5MB and has 26,000 users

    // fullname
    $query="SELECT * FROM members WHERE username='$page' LIMIT 0,1";
    $result=mysql_query($query);
    $fullname=mysql_result($result,'id',"fullname");


    // username
    $query="SELECT * FROM members WHERE username='$page' LIMIT 0,1";
    $result=mysql_query($query);
    $usered=mysql_result($result,'id',"username");

    // nickname
    $query="SELECT * FROM members WHERE username='$page' LIMIT 0,1";
    $result=mysql_query($query);
    $nickname=mysql_result($result,'id',"nickname");


    // visits
    $query="SELECT * FROM members WHERE username='$page' LIMIT 0,1";
    $result=mysql_query($query);
    $visits=mysql_result($result,'id',"visits");


    $browsin="UPDATE members SET browsing = '$rowed' WHERE username = '$usered'";
    mysql_query($browsin) or die(mysql_error());

    $lastact="UPDATE members SET lastact = '$dated' WHERE username = '$usered'";
    mysql_query($lastact) or die(mysql_error());


    $song="UPDATE members SET lastsongs = '$lastsongsdone' WHERE username = '$usered'";
    mysql_query($song) or die(mysql_error());


    mysql_close();




    Any help would be greatly appreciate, my site is basically dying, takes 15 seconds for each page to load....
     
  2. stoo2000

    stoo2000 Active Member

    Joined:
    Jul 26, 2003
    Messages:
    43
    Likes Received:
    0
    Trophy Points:
    6
    Location:
    Leicestershire, UK
    cPanel Access Level:
    Root Administrator
    Code:
    // fullname
    	$query="SELECT * FROM members WHERE username='$page' LIMIT 0,1";
    	$result=mysql_query($query);
    	$fullname = $result['fullname'];
                    $username = $result['username'];
                    $nickname = $result['nickname'];
                    $visits = $result['visits'];
    
    this would cut down the amount of mysql queries, you could also save a couple lines of code and just echo the $result var where needed.
    
    
    	$browsin="UPDATE members SET browsing = '$rowed', lastact = '$dated', lastsongs = '$lastsongsdone' WHERE username = '$usered'";
    	mysql_query($browsin) or die(mysql_error());
    
    
    	mysql_close();
    That should also work and cut down the amount of mysql queries.

    hope that helps :)
     
  3. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
  4. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    I followed your advice stoo, it seems to have reduced the load by about 50-60%

    before i was getting loads of 10,20,30,40,50

    now its 3,5,7,9,14,20

    im using MYSQL mysql (4.0.25-standard)

    i see the option in WHM tp upgrade to 4.1, just wondering if in doing so, will it mess up any current database... in other words is the conversation process 100% clean, i dont want to be losing any info..

    Also, i take a look at that CONF you posted, what is the configuration aimed at... is it for lots of consistant querys.... because the site im working with is doing about 200 querys a second

    any kind of conf needs to be aimed at dealing with that... and as im not to good with the MYSQL conf set up can some one please explain...

    Thank you
     
  5. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    Using the conf I posed above for MySQL 4.1 with slightly modified settings from the comments gives me way more than 200 queries/sec. The changes from the one in the link (the 4.1 version) are the following:
    query_cache_size=128M
    key_buffer=128M
    thread_concurrency=8


    The rest of the settings are left as is, and the server hardware: Dual Xeon 2.8GHz w/ HT enabled, 1GB RAM, 73GB SCSI 10K RPM.

    Code:
    root@diamond [~]# mysql
    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 4265040 to server version: 4.1.13-standard
    
    Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
    
    mysql> status
    --------------
    mysql  Ver 14.7 Distrib 4.1.13, for pc-linux-gnu (i686) using readline 4.3
    
    Connection id:          4265040
    Current database:
    Current user:           root@localhost
    SSL:                    Not in use
    Current pager:          stdout
    Using outfile:          ''
    Using delimiter:        ;
    Server version:         4.1.13-standard
    Protocol version:       10
    Connection:             Localhost via UNIX socket
    Server characterset:    latin1
    Db     characterset:    latin1
    Client characterset:    latin1
    Conn.  characterset:    latin1
    UNIX socket:            /var/lib/mysql/mysql.sock
    Uptime:                 4 days 22 hours 38 min 34 sec
    
    Threads: 5  Questions: 362953171  Slow queries: 35  Opens: 136  Flush tables: 1  Open tables: 125  Queries per second avg: 849.781
    --------------
     
  6. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    ok i have just went to try that, and in my cnf file is this

    [mysqld]
    set-variable = max_connections=500
    safe-show-database



    thats it, ive also tried locating my.cnf in every other location, an the contents is no more than the above

    am i missing something :s
     
  7. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    That's the VERY generic, default my.cnf file used for cPanel servers. It's very lacking as you can tell. Use the config from the appropriate link and you should see a fair amount of improvement.
     
  8. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    hi, ive just tried the 4.0 conf since im using that versions, edit my.cnf and then uploaded it back into /etc

    restart mysql, then apache, and the load is still going wild

    its hitting 10,20,30,40 now...

    Any ideas, or perhaps find what script is causing the load, dont forget all this has came about when i converted a very busy website database from flat file to MYSQL, so i know its that site, just need to find out what script or perhaps what query is causing this

    ?

    Maybe this will be of help ?



    Top Process %CPU 55.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.jmchost.net.pid --skip-locking
    Top Process %CPU 40.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.jmchost.net.pid --skip-locking
    Top Process %CPU 33.0 /usr/sbin/mysqld --basedir/ --datadir/var/lib/mysql --usermysql --pid-file/var/lib/mysql/server.jmchost.net.pid --skip-locking
     
    #8 jaymc, Aug 1, 2005
    Last edited: Aug 1, 2005
  9. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    In the my.cnf file, increase both key_buffer and query_cache_size to 128M or higher if you have the RAM for it. Setting these to higher values will help put more MySQL data into RAM, reducing disk I/O.

    Are you running IDE or SCSI drive(s)?
     
  10. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    I'm running a 300GIG SATA

    this is a big problem, the site in questions is extreemly popular, 3000 unqiue+ visiters a day

    usually about 500 people online at any one given time, so im desperate to get this fixed

    at the moment, after Apache & MYSQL optimization the load has decrease slightly, but its always 10+, and some times the load its 70 an memory 90%, so their is a major problem

    this is my first time ive made a MYSQL database, so i have no idea what it could be

    alls i can say is that everytime some once clicks a link, these querys are beng executed


    $result = array();
    $query="SELECT fullname, username, nickname, username, visits, points FROM members WHERE username = '$page' LIMIT 0,1";
    $resulted = mysql_query($query) or die(mysql_error());
    $resulto = mysql_fetch_assoc($resulted) or die(mysql_error());

    $query="SELECT * FROM members WHERE username = '$cookiename' LIMIT 0,1";
    $result=mysql_query($query);
    $lastsongs = mysql_result($result,'id',"lastsongs");

    $updatefew="UPDATE `members` SET `browsing` = '".($rowed)."', `lastact` = '".$dated."', `lastsongs` = '".htmlspecialchars($lastsongsdone)."' WHERE `username` = '".$cookiename."' LIMIT 1";
    mysql_query($updatefew) or die(mysql_error());



    Now im told MYSQL is designed for dealing with info better than a flat file database could be, but as it looks its just not

    the database in questions has 25 fields, and their are 28,000 thousand rows, every user has their own row, total size of the database is 4.6MB

    im just wondering if the load is so high because of the time it takes to search, find, then insert/update info on a database of that magnitude

    but then again, i think of other websites which must have Mysql database 5 times as large as mine.... and they dont have a problem

    so really it must be the way i have my database laid out, the amount of querys being processed a second, or bad code that takes longer to get/insert info from the database.....

    Really looking for someone who has a lot of MYSQ experience to step in and just check through my previous posts and see if they can isolate the problem

    Im running MYSQYl version 4.0 at the moment, will upgrading to version 4.1 better handle the load ?
     
    #10 jaymc, Aug 1, 2005
    Last edited: Aug 1, 2005
  11. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    Do the tables in your database have indexes?
     
  12. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    No

    here is the datbase layout, maybe this will be of help

    [​IMG]
     
  13. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    Ok i modified to that in my MYSQL 4.0 conf

    just wondering tho, for

    thread_cache_size=128
    key_buffer=128M


    it says to use 32M for every 1 Gig of Ram

    i have 1.5GIG so shouldnt it be about 50M..... or isnt it an issue

    also, thread_concurrency=8, i have 1 CPU, so should i leave it as 2 or 8 ?

    Thanks
     
  14. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    sorry to keep posting just i keep find things while googling


    My database is MyISAM, in PHPMYADMIN i have options to change to HEAP, MEMORY, ISAM or MERGE

    A Database is no fun when you are locked out
    The default table type for MySQL is MyISAM. It has table level locking, which means during an UPDATE, nobody can access any other record of the same table. BDB uses Page level locking, and during an UPDATE, nobody can access any other record residing in the same database page of that table, until the locking transaction issues a COMMIT.

    InnoDB however, uses Row level locking. Row level locking ensures that during an UPDATE, nobody can access that particular row, until the locking transaction issues a COMMIT. Any of the above table types will probably be fine for a web server, but in a LAN application can cause unecessary issues.



    From that its basically saying that if 200 users run a query at the same time, 199 are qued untill the previous one completes, and so on... thats for MyISAM way any way, do you think that could be what the problem is, or am i going down the wrong road

    also, if i was to upgrade using PHPMYADMIN from MyISAM to HEAP or MEMORY or what ever is best, would it wreck my database, or would everything still be fine just using a diff method ?
     
  15. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    You should leave it as MyISAM. The different setting values are OK. I just added those comments in there as a general rule of thumb, but in some scenarios, it's best to up the settings a bit.

    I think it might be in your interest to upgrade to MySQL 4.1 for some performance increases. Although, I would recommend backing up the database with phpMyAdmin (Export) just in case. Follow the instructions in WHM Tweak settings to upgrade to MySQL 4.1.

    Also, Can you post the output for the following commands?
    hdparm /dev/sda
    hdparm -tT /dev/sda
     
  16. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    /dev/sda:
    readonly = 0 (off)
    geometry = 36483/255/63, sectors = 586114704, start = 0


    /dev/sda:
    Timing buffer-cache reads: 616 MB in 2.00 seconds = 308.00 MB/sec
    Timing buffered disk reads: 76 MB in 3.07 seconds = 24.76 MB/sec



    Here you go
     
  17. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    Well the harddrive speeds don't look too bad. The buffer-cache reads a little low, but relatively good. So the bottleneck isn't really with your drives (atleast not completely).

    I really don't know else can be done without having a look at it myself and troubleshooting more things.

    Did you/are you going to upgrade to MySQL 4.1?
     
  18. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    i went to upgrade to MYSQL yesterday

    in doing so, APAChE wouldnt started, i updated apache 3 times to try and fix, but it wouldnt work

    Cpanel have said they will do it so i may try tonight....

    I am interested in changing table type from MYISAM, the write up sort if makes sense where it says it will look the table until query completes, where as InodeDB will only lock the row...... surely that is worth looking into.... or maybe not ?

    If you have the time i will give you access to take a look at it your self, up to you
     
  19. Blue|Fusion

    Blue|Fusion Well-Known Member

    Joined:
    Sep 12, 2004
    Messages:
    378
    Likes Received:
    0
    Trophy Points:
    16
    Location:
    Cleveland, Ohio
    Personally, I've never used InnoDB, and infact, many people disable it completely on their MySQL. For the exact reasons? I don't know. But I do know that using InnoDB instead of MyISAM, you will need to recode some of the PHP stuff for the different stuff.

    If you trust me, you can contact me on AIM, MSN, or (despite the sig), PM and talk to me about it there and I can check a few things. No guarantees I can fix anything, but I'll check some things out and see what I can do.
     
  20. jaymc

    jaymc Well-Known Member

    Joined:
    Jan 5, 2005
    Messages:
    100
    Likes Received:
    0
    Trophy Points:
    16
    Cheers ive added you to MSN
     
Loading...

Share This Page