Results 1 to 17 of 17
  1. #1
    Join Date
    Jan 2006
    Location
    Toronto, Canada
    Posts
    317

    mysql for a large website

    I have a website has about 500k UV per day.
    I use 3 servers for nginx, and 1 server for mysql.
    the mysql server has high load, sometime up to 100.
    I have to set up a crontab job to restart mysql twice a day. it's still fine now, kind of slow during peak time.

    I am wondering how to use cloud server for mysql.
    Has anybody try http://xeround.com/ ?

  2. #2
    Join Date
    Dec 2011
    Location
    Hertfordshire
    Posts
    455
    Although I've never tried Xeround, from what I can gather it's a solid product.

    I do have experience with Amazon RDS (Relational Database Service). Its a similar DBaaS product to xeround, and allows you to scale either manually or programmatically according to load. Very simple, very flexible and very stable.

  3. #3
    Join Date
    Mar 2003
    Location
    New York City
    Posts
    7,406
    The simpler solution might be to just upgrade your 1 mysql server, setup SSD drives on it with faster CPU and see if that does the trick?
    █• Taskade - To-Do List & Tasks All-in-One To-Do List & Mind Map App for Remote Teams
    █• Simple and shareable to-do lists for web, mobile, and desktop
    █• To-Do List Templates 300+ shareable templates and productivity workflows
    █• Get things done, faster and smarter! I eat penguins for breakfast ...

  4. #4
    Join Date
    Jan 2006
    Location
    Toronto, Canada
    Posts
    317
    Quote Originally Posted by jcsrv View Post
    Although I've never tried Xeround, from what I can gather it's a solid product.

    I do have experience with Amazon RDS (Relational Database Service). Its a similar DBaaS product to xeround, and allows you to scale either manually or programmatically according to load. Very simple, very flexible and very stable.
    I tried xeround.
    but it's difficult to upload a large database file (700M).

  5. #5
    Join Date
    Jan 2006
    Location
    Toronto, Canada
    Posts
    317
    Quote Originally Posted by Cirtex View Post
    The simpler solution might be to just upgrade your 1 mysql server, setup SSD drives on it with faster CPU and see if that does the trick?
    it worths a try!

  6. #6
    Join Date
    Aug 2010
    Location
    Prague, Czech Republic
    Posts
    404
    I would start with MySQL optimizing (both servers and queries). Also you may want to consider upgrading server. But before it would be good idea to find actual bottlenecks.
    Supportex.Net server management, full range of services. EU-based outsourced company. Since 1998.
    Outstanding quality for high performance projects; clustering and high-availability solutions, DDoS protection.
    Cisco/Juniper network management & deployment assistance. Network design and monitoring.

  7. #7
    Join Date
    Jan 2006
    Location
    Toronto, Canada
    Posts
    317
    Quote Originally Posted by barbus View Post
    I would start with MySQL optimizing (both servers and queries). Also you may want to consider upgrading server. But before it would be good idea to find actual bottlenecks.
    right now, the cpu is
    Intel(R) Xeon(R) CPU E31230 @ 3.20GHz
    with 12 G Ram.
    when the load is high, the MEM usage is only 7G, no idea how to maximize MEM usage.

    Here is the mysql config file


    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    user=mysql

    skip-locking
    skip-name-resolve
    skip-host-cach
    skip-bdb
    skip-innodb
    bind-address = xx.xxx.xxx.xx
    # Default to using old password format for compatibility with mysql 3.x
    # clients (those using the mysqlclient10 compatibility package).
    old_passwords=1

    max_connections = 1224
    thread_concurrency = 8
    key_buffer_size=32M
    thread_cache_size = 64
    query_cache_size = 2048M
    read_buffer_size = 2048K
    max_heap_table_size =1024M
    tmp_table_size =1024M
    table_cache =2824


    [mysqld_safe]
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid

  8. #8
    Join Date
    Mar 2005
    Location
    Ten1/0/2
    Posts
    2,529
    try running tuning-primer.sh (google it) and also mysqltuner.pl and post the output of both here - they provide quite a bit of info and recommendations on where to start with tuning the Config.

    Without at least some info on what is going on, then any suggestions on the configs are just guesses.
    CPanel Shared and Reseller Hosting, OpenVZ VPS Hosting. West Coast (LA) Servers and Nodes
    Running Linux since 1.0.8 Kernel!
    Providing Internet Services since 1995 and Hosting Since 2004

  9. #9
    Join Date
    Jan 2006
    Location
    Toronto, Canada
    Posts
    317
    tuning-primer.sh result
    MySQL Version 5.0.95 x86_64

    Uptime = 0 days 5 hrs 59 min 23 sec
    Avg. qps = 2261
    Total Questions = 48762310
    Threads Connected = 57

    Warning: Server has not been running for at least 48hrs.
    It may not be safe to use these recommendations

    To find out more information on how each of these
    runtime variables effects performance visit:
    http://dev.mysql.com/doc/refman/5.0/...variables.html
    Visit http://www.mysql.com/products/enterprise/advisors.html
    for info about MySQL's Enterprise Monitoring and Advisory Service

    SLOW QUERIES
    The slow query log is NOT enabled.
    Current long_query_time = 10 sec.
    You have 152 out of 48762349 that take longer than 10 sec. to complete
    Your long_query_time seems to be fine

    BINARY UPDATE LOG
    The binary update log is NOT enabled.
    You will not be able to do point in time recovery
    See http://dev.mysql.com/doc/refman/5.0/...-recovery.html

    WORKER THREADS
    Current thread_cache_size = 64
    Current threads_cached = 42
    Current threads_per_sec = 0
    Historic threads_per_sec = 13
    Your thread_cache_size is fine

    MAX CONNECTIONS
    Current max_connections = 1224
    Current threads_connected = 76
    Historic max_used_connections = 716
    The number of used connections is 58% of the configured maximum.
    Your max_connections variable seems to be fine.

    No InnoDB Support Enabled!

    MEMORY USAGE
    Max Memory Ever Allocated : 5.27 G
    Configured Max Per-thread Buffers : 5.52 G
    Configured Max Global Buffers : 2.04 G
    Configured Max Memory Limit : 7.56 G
    Physical Memory : 11.72 G
    Max memory limit seem to be within acceptable norms

    KEY BUFFER
    Current MyISAM index space = 244 M
    Current key_buffer_size = 32 M
    Key cache miss rate is 1 : 233
    Key buffer free ratio = 0 %
    You could increase key_buffer_size
    It is safe to raise this up to 1/4 of total system memory;
    assuming this is a dedicated database server.

    QUERY CACHE
    Query cache is enabled
    Current query_cache_size = 2.00 G
    Current query_cache_used = 75 M
    Current query_cache_limit = 1 M
    Current Query cache Memory fill ratio = 3.68 %
    Current query_cache_min_res_unit = 4 K
    Query Cache is 24 % fragmented
    Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
    If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
    Your query_cache_size seems to be too high.
    Perhaps you can use these resources elsewhere
    MySQL won't cache query results that are larger than query_cache_limit in size

    SORT OPERATIONS
    Current sort_buffer_size = 2 M
    Current read_rnd_buffer_size = 256 K
    Sort buffer seems to be fine

    JOINS
    Current join_buffer_size = 132.00 K
    You have had 0 queries where a join could not use an index properly
    Your joins seem to be using indexes properly

    OPEN FILES LIMIT
    Current open_files_limit = 6882 files
    The open_files_limit should typically be set to at least 2x-3x
    that of table_cache if you have heavy MyISAM usage.
    Your open_files_limit value seems to be fine

    TABLE CACHE
    Current table_cache value = 2824 tables
    You have a total of 193 tables
    You have 1817 open tables.
    The table_cache value seems to be fine

    TEMP TABLES
    Current max_heap_table_size = 1.00 G
    Current tmp_table_size = 1.00 G
    Of 18686 temp tables, 32% were created on disk
    Perhaps you should increase your tmp_table_size and/or max_heap_table_size
    to reduce the number of disk-based temporary tables
    Note! BLOB and TEXT columns are not allow in memory tables.
    If you are using these columns raising these values might not impact your
    ratio of on disk temp tables.

    TABLE SCANS
    Current read_buffer_size = 2 M
    Current table scan ratio = 2790 : 1
    read_buffer_size seems to be fine

    TABLE LOCKING
    Current Lock Wait ratio = 1 : 10
    You may benefit from selective use of InnoDB.
    If you have long running SELECT's against MyISAM tables and perform
    frequent updates consider setting 'low_priority_updates=1'
    If you have a high concurrency of inserts on Dynamic row-length tables
    consider setting 'concurrent_insert=2'.
    mysqltuner.pl result

    -------- General Statistics --------------------------------------------------
    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.0.95
    [OK] Operating on 64-bit architecture

    -------- Storage Engine Statistics -------------------------------------------
    [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
    [--] Data in MyISAM tables: 535M (Tables: 176)
    [!!] Total fragmented tables: 1



    -------- Performance Metrics -------------------------------------------------
    [--] Up for: 6h 5m 34s (49M q [2K qps], 1M conn, TX: 23B, RX: 2B)
    [--] Reads / Writes: 97% / 3%
    [--] Total buffers: 3.0G global + 4.6M per thread (1224 max threads)
    [OK] Maximum possible memory usage: 8.6G (73% of installed RAM)
    [OK] Slow queries: 0% (152/49M)
    [OK] Highest usage of available connections: 58% (716/1224)
    [OK] Key buffer size / total MyISAM indexes: 32.0M/244.4M
    [OK] Key buffer hit rate: 99.6% (338M cached / 1M reads)
    [OK] Query cache efficiency: 57.2% (24M cached / 43M selects)
    [OK] Query cache prunes per day: 0
    [OK] Sorts requiring temporary tables: 1% (9K temp sorts / 867K sorts)
    [!!] Temporary tables created on disk: 32% (8K on disk / 27K total)
    [OK] Thread cache hit rate: 84% (292K created / 1M connections)
    [OK] Table cache hit rate: 99% (1K open / 1K opened)
    [OK] Open file limit used: 29% (2K/6K)
    [!!] Table locks acquired immediately: 91%

    -------- Recommendations -----------------------------------------------------
    General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Optimize queries and/or use InnoDB to reduce lock wait

  10. #10
    Join Date
    Apr 2010
    Posts
    386
    Optimize. Move from myisam to innodb, from MySQL to mariadb (with xtradb), use indexes, correctly use it, expand the my.cnf max allowed ram usage.
    Or, just upgrade the server. If you have half a million UV/day, you are sitting on a lot of money. You could even ask rackspace to do the job for you, so that you don't have to setup a crontab that reboots MySQL twice a day (god, I really can't belive you did it). And having someone that knows what he does would increase your uptime, that I'm sure to be rather bad.

    Don't take it bad, I don't want to offend you.

  11. #11
    At a minimum you need to upgrade MySQL to 5.5.x to get the latest InnoDB engine. That alone will give you a nice shot of performance. The latest version of MySQL competes very well against mariadb, some benchmarks indicate it's even faster. And then obviously making sure it's tuned even modestly well.

    From the reports, I'm betting you either have a query design problem, or a database index problem (assuming the problem is in fact related to the database server). It's seemingly not memory related, and the CPU is usually not the culprit unless you're at extreme levels of use.

    You could look at moving to dual socket CPUs if you're seeing any thrashing there. The new e5 lineup is about to arrive, they'll be wildly powerful. You could get two 5645s to acquire a lot more worker cores to handle the connection load.

    I didn't see anything about your drive configuration, but a simple raid 10 would help, either 15k SAS or SSD if you can afford it. Your database should be completely in memory when you move to InnoDB, but the raid 10 performance will help as the engine dumps to disk.

  12. #12
    Join Date
    Apr 2009
    Posts
    1,321
    Quote Originally Posted by heropage View Post
    I have a website has about 500k UV per day.
    I use 3 servers for nginx, and 1 server for mysql.
    the mysql server has high load, sometime up to 100.
    I have to set up a crontab job to restart mysql twice a day. it's still fine now, kind of slow during peak time.

    I am wondering how to use cloud server for mysql.
    Has anybody try http://xeround.com/ ?
    I am curious what the alexa rank for 500000 unique visitors per day is. Are you in top 1000?

  13. #13
    Join Date
    Mar 2005
    Location
    Ten1/0/2
    Posts
    2,529
    Quote Originally Posted by heropage View Post
    tuning-primer.sh result

    mysqltuner.pl result
    For the most part it looks pretty good - minor things only that are unlikly to get you massive gains.

    disk setup on the server?

    If you are not checking and monitoring disk IO - Probably where you need to concentrate on increasing performance.

    Time to hone in and find what the bottleneck is and then address that.
    CPanel Shared and Reseller Hosting, OpenVZ VPS Hosting. West Coast (LA) Servers and Nodes
    Running Linux since 1.0.8 Kernel!
    Providing Internet Services since 1995 and Hosting Since 2004

  14. #14
    Join Date
    Mar 2004
    Posts
    551
    You have very few slow queries, so you don't have queries that are running that badly.

    The query cache has a low hit rate.

    You have a relatively high number of connections (peaked at over 700).

    What type of application is this? The low hit rate indicates it's some type of dynamic site with rapidly-changing data or high cardinality.

    I'd say you need to look at doing some type of application-level caching, like with memcache or even page-level caching (if your application supports it).

    Connection creation is very fast in MySQL (compared to Oracle), but you may want to look in your application to see if you can make this more efficient. For example, make sure that if you have multiple queries on a page, then use the same connection for each.

    If you want to pay for tuning help, I can recommend Percona. They charge by the hour (no retainer commitment) and are experts in this area. I've worked with them on some issues and also attended their training courses.

  15. #15
    Join Date
    Apr 2009
    Location
    inside wht
    Posts
    746
    Why won't you look into using percona or xtradb instead of mysql server. They are good mysql variants .
    24x7 PROACTIVE SERVER MANAGEMENT | OUTSOURCED WEB HOSTING SUPPORT
    Sales : sales @ syslint.com | Call us : (+91)9447607799 | Are you looking for DevOps Admins ?

  16. #16
    I have seen better performance by enabling "dirty reads" when acceptable. This will eliminate your DISTINCT and LIMIT issue. That actually helped me reduce my load on my server.

    http://dev.mysql.com/doc/refman/5.0/...ansaction.html
    - PlanetSudoku.com
    - Millions of Sudoku puzzles including Sudoku 9x9, Multi 2, Multi 4, and Samurai
    - Puzzles from Very Easy to Hard, Logical Solver, Custom Backgrounds, Learning Center, and more...

  17. #17
    Join Date
    Jan 2006
    Location
    Toronto, Canada
    Posts
    317
    i changed to
    key_buffer_size=10400M
    it works better now.

Similar Threads

  1. Replies: 3
    Last Post: 12-30-2010, 07:10 PM
  2. Replies: 0
    Last Post: 03-05-2010, 06:02 PM
  3. Large MySQL Migration
    By EXN-Volkan in forum Hosting Security and Technology
    Replies: 8
    Last Post: 02-07-2008, 03:39 PM
  4. Your thoughts for large mysql website
    By JGRoboMarketing in forum Dedicated Server
    Replies: 6
    Last Post: 11-14-2007, 05:36 PM
  5. Need efficient PHP/mySQL Coding done for large website
    By qubefactor in forum Employment / Job Offers
    Replies: 2
    Last Post: 11-06-2005, 11:39 AM

Posting Permissions

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