Results 1 to 17 of 17
Thread: mysql for a large website
-
03-06-2012, 11:56 AM #1Web Hosting Guru
- 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/ ?
-
03-06-2012, 12:34 PM #2Web Hosting Evangelist
- 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.
-
03-06-2012, 01:44 PM #3WebHostingTalk Lover
- 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 ...
-
03-06-2012, 02:07 PM #4Web Hosting Guru
- Join Date
- Jan 2006
- Location
- Toronto, Canada
- Posts
- 317
-
03-06-2012, 02:08 PM #5Web Hosting Guru
- Join Date
- Jan 2006
- Location
- Toronto, Canada
- Posts
- 317
-
03-06-2012, 02:39 PM #6Aspiring Evangelist
- 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.
-
03-06-2012, 02:45 PM #7Web Hosting Guru
- Join Date
- Jan 2006
- Location
- Toronto, Canada
- Posts
- 317
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
-
03-06-2012, 05:01 PM #8Web Hosting Master
- 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
-
03-06-2012, 05:29 PM #9Web Hosting Guru
- 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'.
-------- 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
-
03-06-2012, 06:15 PM #10Aspiring Evangelist
- 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.
-
03-06-2012, 11:00 PM #11Newbie
- Join Date
- Dec 2011
- Posts
- 16
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.
-
03-06-2012, 11:46 PM #12Web Hosting Master
- Join Date
- Apr 2009
- Posts
- 1,321
-
03-07-2012, 08:23 AM #13Web Hosting Master
- Join Date
- Mar 2005
- Location
- Ten1/0/2
- Posts
- 2,529
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
-
03-07-2012, 10:39 AM #14Web Hosting Master
- 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.
-
03-07-2012, 10:59 AM #15Web Hosting Master
- 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 ?
-
03-07-2012, 11:10 AM #16Junior Guru Wannabe
- Join Date
- Feb 2012
- Posts
- 36
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...
-
03-21-2012, 02:11 PM #17Web Hosting Guru
- Join Date
- Jan 2006
- Location
- Toronto, Canada
- Posts
- 317
i changed to
key_buffer_size=10400M
Similar Threads
-
Wanted: graphic designer(s) to do a few dozen website headers (large - extra large)
By admin031 in forum Design RequestsReplies: 3Last Post: 12-30-2010, 07:10 PM -
Professional Website plans / large MySql DB size websites / SSD technology
By Beast5 in forum Shared Hosting OffersReplies: 0Last Post: 03-05-2010, 06:02 PM -
Large MySQL Migration
By EXN-Volkan in forum Hosting Security and TechnologyReplies: 8Last Post: 02-07-2008, 03:39 PM -
Your thoughts for large mysql website
By JGRoboMarketing in forum Dedicated ServerReplies: 6Last Post: 11-14-2007, 05:36 PM -
Need efficient PHP/mySQL Coding done for large website
By qubefactor in forum Employment / Job OffersReplies: 2Last Post: 11-06-2005, 11:39 AM