Results 1 to 11 of 11
  1. #1
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Database Replication with mySQL
    I've got a problem. My highest traffic site is only running on a single dedicated server, and this server is straining to stay updated. My cronjobs execute nightly and lately they've been lingering on into the morning increasing my load averages and hindering my traffic due to poor load times. I've just got too many of them and they begin to run concurrently which slows the entire process down to a crawl. I've got to put an end to this, but with my constant addition of new cronjobs and datafeeds I'm going to need a second dedicated server.

    My current server is reasonably fast at about 2.8 ghz with 1gb of ram. If I get a second I may just go for a dual zeon processor (any good experiences here?). This new server will download datafeeds, update and insert new prices and products and build my lookup table. I've just started reading about replication and it looks like a solid solution. It'll allow me to automatically sync up both databases on both servers. So the heavy duty work will be running on a separate server while my visitors won't be hindered by poor load times on the live server.

    I'm just not sure how the replication process will affect my slave/live server. Since my master server will be updating 24/7 it'll constantly have changes for the slave server. Since replication happens in real time these changes will be executed during the day on my live server. Will these updates slow it down? I may have a solution if this is the case. I can simply enable the slave at night and disable it during the day. That way the live server will only request changes from the master server at night. I'm just not sure how quickly the slave server will be able to process the queries sent over from the master server.

    Does anybody have experience with this? If so, let me know if it was helpful. I'd love to find out!

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  2. #2
    Member tsmgroup2's Avatar
    Join Date
    January 18th, 2005
    Location
    New Providence, PA USA
    Posts
    155
    Hey, scott.
    I don't have a whole lot of experience working with servers (yet) but I have learned some thru a few friends that do work with servers.

    Night time updating is best, you're right there.

    Don't servers come with some kind of buffering system that you're updates could be processed without slowing their speed or response time? I know the Dual processors will help, the newers ones are working with liquid cooled cpu's versus air cooled..

    I'll ask around. Wish you could help me master my database programming skills.
    Mark (Satchel)
    Webmaster / Sales Manager
    [url]www.tsmgroup2.biz[/url]

  3. #3
    Member johnm's Avatar
    Join Date
    August 24th, 2005
    Location
    minneapolis
    Posts
    65
    I'm using a dual xeon 3.2 ghz box w/ 4x250 raid-0+1 array & 4gb of ram running RHEL for similar tasks. I crunch through several gigs of various data files a few times a day and the load never flinches.

    With any database, mySql included, what's going to make it speedy is the fastest disk i/o you can afford (raid0+1(10), SCSI 15k drives, ex. 4x36gb SCSI, ~72gb av.), with only your database files on it. Put the OS & everything else on it's own controller and drives. Why SCSI? SATA(aka IDE) processes requests sequentially in all cases, whereas SCSI w/ tagged command queuing ("TCQ") allows multiple requests to be sent simultaneously and the controller sorts out what's fastest.

    But really, in all reality, if you have four nice SATA drives with healthy caches, a good raid controller, and separate channels for the database raid and your system drives, you will have stellar performance. I use SATA drives in my systems and I've been happy with them.

    Next, ram. One of your key measurements for MyISAM tables is your key_buffer_size value. MySQL does not cache row data for MyISAM tables, only index data. Your key_buffer_size configuration value is the size of the cache that mysql will keep frequently used index data. The best-scenario is to sum the filesizes of your .myi files in your mysql data dir and set the key_buffer_size value to that.

    CPU- mysql is a multithreaded server. If you're running windows OS, you're good to go. Give it another processor, as much cache as possible. If you're running linux, another processor will of course still help, but check to see if you're using the native posix thread library if you have a large number of connections.

    Now on to tuning and performance. Gather data:

    Check how much swap you're using. Check your IO stats (vmstat/iostat under linux).

    Enable the mysql slow query log with log long format set. That will record any query that doesn't utilize an index. Take those queries and run them through EXPLAIN.

    Install and use "mytop" which is sort of a "top" for mysql. That will let you spy on your insert/update processes that run at night to gather data such as query state, time spent, key efficiency, cache utilization, #/threads in use, etc.

    Next use SHOW STATUS. Take a look at key_reads, key_writes, key_write_requests, and key_read_requests. You want the _requests items much, much higher than their counterparts. This means the cache is big enough and doing it's job. Check your table_locks_immediate and table_locks_waited. You want table_locks_waited as close to zero as possible. Check your sort_scan value - if it's not zero, you need to add an index somewhere, check your slow queries log. If sort_merge_passes is too high, increase sort_buffer. If you've got lots of ram, set tmp_table_size bigger than the default (usually 32mb). Any temporary tables required for a query that utilize less than tmp_table_size space will be created in memory, instead of on disk. Not really necessary if you have fast drives and optimized queries. If you're using a version of mysql less than 4.0, upgrade today. 4.0 and newer has query caching.

    Check how your primary keys are set up for each table. Small values are best. For each insert and update it has to ensure uniqueness which, for large tables, can take a long time. I don't recall exactly how multi-column indexes work under MyISAM tables -- I think they ultimately reference the primary key (or internal pk if you don't specify one) which then points to the offset for the row data. So, in effect, when you change the primary key, mysql must also update the other indexes for that table.

    Regarding clustering, master/slave configuration. If your master is having trouble with the load, an identical slave would be subjected to the same amount of stress. Mysql's replication is per-query, so the slave will receive the same number of insert/update/delete queries in pretty much the same timeframe.

    One thing that came to mind for your specific problem involving inserts/updates --- I have not thought this through entirely, but you might want to give it a shot: If possible, have your application that updates the rows in your big table(s) write the SQL to a log file (assuming it doesn't depend on it's own changes previous to the current operation), then let mysql read the script instead of making many application-level calls in sequence.

    Another idea is to use UPDATE LOW PRIORITY which will only execute the updates when the table is not otherwise locked. Since your updates are probably causing locking issues given your disk setup and memory situation, low priority updates (if it's not mission-critical data) will let your main services execute first. This will not speed up your processing, but it will probably boost the performance of your other applications working with the data.

    If you are doing inserts, check out INSERT DELAYED.

    It's a lot of information, but start from the beginning and tackle them all as much as possible given your situation. It's really kind of a balancing act. Good luck,
    John

    PS Someday, separate your web and database services to their own machine(s); load balancing access to your master/slave(s) in a cluster configuration is useful (use random allocation). Cheers!

    Quote Originally Posted by Snib
    I've got a problem. My highest traffic site is only running on a single dedicated server, and this server is straining to stay updated. My cronjobs execute nightly and lately they've been lingering on into the morning increasing my load averages and hindering my traffic due to poor load times. I've just got too many of them and they begin to run concurrently which slows the entire process down to a crawl. I've got to put an end to this, but with my constant addition of new cronjobs and datafeeds I'm going to need a second dedicated server.

    My current server is reasonably fast at about 2.8 ghz with 1gb of ram. If I get a second I may just go for a dual zeon processor (any good experiences here?). This new server will download datafeeds, update and insert new prices and products and build my lookup table. I've just started reading about replication and it looks like a solid solution. It'll allow me to automatically sync up both databases on both servers. So the heavy duty work will be running on a separate server while my visitors won't be hindered by poor load times on the live server.

    I'm just not sure how the replication process will affect my slave/live server. Since my master server will be updating 24/7 it'll constantly have changes for the slave server. Since replication happens in real time these changes will be executed during the day on my live server. Will these updates slow it down? I may have a solution if this is the case. I can simply enable the slave at night and disable it during the day. That way the live server will only request changes from the master server at night. I'm just not sure how quickly the slave server will be able to process the queries sent over from the master server.

    Does anybody have experience with this? If so, let me know if it was helpful. I'd love to find out!

    - Scott

  4. #4
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Wow, thank you for such a detailed explanation John. I'll certainly have a very close look at everything you've mentioned here.

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  5. #5
    ABW Ambassador isellstuff's Avatar
    Join Date
    November 9th, 2005
    Location
    Virginia
    Posts
    1,659
    Thanks, JohnM. Your MySQL tuning tips are very helpful. I've been wondering how to best allocate system resources and how much I should shell out for drives. Definitely bookmarking this thread.

    Thanks Again,
    Jim

  6. #6
    Member johnm's Avatar
    Join Date
    August 24th, 2005
    Location
    minneapolis
    Posts
    65
    Quote Originally Posted by isellstuff
    Thanks, JohnM. Your MySQL tuning tips are very helpful. I've been wondering how to best allocate system resources and how much I should shell out for drives. Definitely bookmarking this thread.

    Thanks Again,
    Jim

    My pleasure. Even with the info I posted above, it's still sort of a balancing act. Let me know how your tweaking goes!

    Cheers,
    John

  7. #7
    Member johnm's Avatar
    Join Date
    August 24th, 2005
    Location
    minneapolis
    Posts
    65
    Quote Originally Posted by Snib
    Wow, thank you for such a detailed explanation John. I'll certainly have a very close look at everything you've mentioned here.

    - Scott
    You're welcome. Let me know how it goes. I'm always interested to hear what worked on different "real world" configurations. Cheers,
    John

  8. #8
    Newbie
    Join Date
    January 4th, 2006
    Location
    Berlin
    Posts
    41
    fastest drives you can afford and 2gb of ram is more than sufficient to run updates at the same time as running a busy http server.

    It is not the processor at all, even at peak it is rarely even touching on work. As mysql is just series of files it stores them on your disk, if your disk is only 7200 rpm then it will store your data at 7200rpm, if they are 15k then it will store at 15k. The smallest disks you can get away with, with the fastest speed will make your world rock.

  9. #9
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    John,

    I've been going through your post quite a bit lately and I've got a question for you. I've managed to get my key_reads much lower than my key_read_requests, but my key_writes and key_write_requests are looking pretty bad:

    Key_read_requests 194341531
    Key_reads 68647
    Key_write_requests 28764
    Key_writes 17252

    Now from what you said I should increase my key_buffer_size, but I've already got it set to 512M with only 1gb of memory. I'm considering purchasing an additional gb of ram and increasing this number to 750M or higher. Do you think this will improve my key_write performance?

    I've also noticed that my mysql server is eating up just about all of my 1gb of memory. This is probably due to the 512M value on my key_buffer_size. I could probably reduce this value to give more memory to apache and my other services, but this will probably reduce the performance of my key reads and writes.

    The thing is my MYI (index) files only add up to about 200-300 megs, so technically my key_buffer_size doesn't need to be much higher than that. I just don't get why my key_writes is so high.

    What do you suggest?

    Thanks again!
    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  10. #10
    Member
    Join Date
    May 12th, 2006
    Posts
    174
    Quote Originally Posted by Snib
    I've got a problem. My highest traffic site is only running on a single dedicated server, and this server is straining to stay updated. My cronjobs execute nightly and lately they've been lingering on into the morning increasing my load averages and hindering my traffic due to poor load times. I've just got too many of them and they begin to run concurrently which slows the entire process down to a crawl. I've got to put an end to this, but with my constant addition of new cronjobs and datafeeds I'm going to need a second dedicated server.

    My current server is reasonably fast at about 2.8 ghz with 1gb of ram. If I get a second I may just go for a dual zeon processor (any good experiences here?). This new server will download datafeeds, update and insert new prices and products and build my lookup table. I've just started reading about replication and it looks like a solid solution. It'll allow me to automatically sync up both databases on both servers. So the heavy duty work will be running on a separate server while my visitors won't be hindered by poor load times on the live server.

    I'm just not sure how the replication process will affect my slave/live server. Since my master server will be updating 24/7 it'll constantly have changes for the slave server. Since replication happens in real time these changes will be executed during the day on my live server. Will these updates slow it down? I may have a solution if this is the case. I can simply enable the slave at night and disable it during the day. That way the live server will only request changes from the master server at night. I'm just not sure how quickly the slave server will be able to process the queries sent over from the master server.

    Does anybody have experience with this? If so, let me know if it was helpful. I'd love to find out!

    - Scott

    scott who are you using for hosting your sites?

  11. #11
    ABW Ambassador Snib's Avatar
    Join Date
    January 18th, 2005
    Location
    Virginia
    Posts
    5,303
    Quote Originally Posted by Morethanable
    scott who are you using for hosting your sites?
    http://forum.abestweb.com/showthread.php?t=61954

    - Scott
    Hatred stirs up strife, But love covers all transgressions.

  12. Newsletter Signup

+ Reply to Thread

Similar Threads

  1. So i've got the datafeed into mysql database..
    By chillini in forum Programming / Datafeeds / Tools
    Replies: 10
    Last Post: December 2nd, 2008, 02:06 PM
  2. Uploading Datafeed to MYSQL Database
    By bluewaves1 in forum Programming / Datafeeds / Tools
    Replies: 11
    Last Post: May 6th, 2008, 02:25 PM
  3. Database of datafedds how to update - mysql
    By dflsports in forum Programming / Datafeeds / Tools
    Replies: 7
    Last Post: December 21st, 2005, 01:14 PM
  4. How can I use buyitbomb with Mysql database?
    By Amavisca in forum Programming / Datafeeds / Tools
    Replies: 0
    Last Post: July 6th, 2005, 06:43 AM
  5. MySQL database - Can I...
    By ~Michelle in forum Programming / Datafeeds / Tools
    Replies: 2
    Last Post: March 26th, 2004, 01:59 PM

Posting Permissions

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