|
Donation/Premium |
|
 |
|
|
|
|
|
|
|
Survey |
|
|
|
|
|
|
|
|
|
|
|
| View previous topic :: View next topic |
| Author |
Message |
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
 Posted: Wed Feb 27, 2008 1:25 am Post subject: Exec Summary 26 Feb 2008 |
|
|
Index:
First thread on our performance issues currently at twelve pages:  /t215785-Optimizing_Performance.html
Initial summary: /t216007-Executive_summary_21_Feb_2008.html
Secondary: /t216034-Exec_Sum_22_Feb_2008.html
Tertiary: /t216241-Executive_Summary_24_Feb_2008.html
query_cache_size discussion: /t215943-MySQL_query_cache_size_improvements.html
Possible hardware solutions: /t216413-Thinking_aloud.html
Greetings human. This is an attempt to rollup the above links into this post.
www.castlecops.com has been having performance issues going on for a couple weeks now. For the bulk of that time we were under the impression some daemon or code was not optimized. Next (now) we suspect its hardware. Replacement card has come in, we now need to schedule maintenance. Ultimately, we rely heavily on MySQL and PHP and in our infrastructure, they are very demanding and require dedicated high performing servers. You can read more about that in the last link above. I suspect that moving forward our performance will not only rely on continued optimizations, but on high performance systems.
- How to increase performance via changes to daemons such as Apache, PHP, and MySQL: http://goddess-gate.com/dc2/index.php/post/255
- Tips on accelerators: http://www.gena01.com/forum/gena01_blog/performance_tips_apc_vs_eaccelerator-t201.0.html
- Yahoo Yslow: http://developer.yahoo.com/yslow/
Some MySQL "tuners":
- http://mysqltuner.com/
- http://jeremy.zawodny.com/mysql/mytop/
- http://hackmysql.com/mysqlreport
- http://www.day32.com/MySQL/tuning-primer.sh
A good MySQL blog: http://www.mysqlperformanceblog.com
http://www.mysqlperformanceblog.com/files/presentations/UC2007-MySQL-Performance-Cookbook.pdf
http://www.mysqlperformanceblog.com/files/presentations/OSDBCON2006-MySQL-Performance-Optimization.pdf
Scaling MySQL on FreeBSD: http://people.freebsd.org/~kris/scaling/mysql.html
Duplex mismatch: http://www.joelonsoftware.com/items/2008/01/22.html
Web page optimization: http://www.websiteoptimization.com/services/analyze/
What gave me the impression that our 3ware 9000 series controller card was bad were the results of iostat -x. %b was rather high, not good. We're going with Areca now.
MySQL was migrated to a temporary system which ran very nicely. Today additional load was placed on that system (external to CC) which has been causing our performance issues to crop up again. So MySQL is a resource hog.
Our current my.cnf:
| Code: | [mysqld]
#___KEY BUFFER___#
key_buffer = 1G
key_cache_division_limit = 100
#___VARIOUS BUFFERS___#
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 5M
read_rnd_buffer_size = 2M
#___THREAD AND TABLE CACHES___#
table_open_cache = 1024
thread_cache_size = 200
tmp_table_size = 25M
max_heap_table_size = 25M
table_definition_cache = 1024
#___QUERY CACHE___#
query_cache_type = 1
query_alloc_block_size = 16K
query_prealloc_size = 8K
query_cache_limit = 2M
query_cache_size = 128M
query_cache_min_res_unit = 1K
#___TIMEOUTS___#
wait_timeout = 100
interactive_timeout = 100
#___LOGS___#
log = 0
log_warnings = 0
general_log = 0
log_output = FILE
slow_query_log = 0
log_slow_queries = 0
log_queries_not_using_indexes = 0
#___FORCE MYSQL TO USE INDEX___#
max_seeks_for_key = 1000
#___MISC___#
socket = /tmp/mysql.sock
server-id = 1
max_connections = 200
concurrent_insert = 2
low_priority_updates = 1
thread_concurrency = 2
max_allowed_packet = 16M
myisam-recover = BACKUP,FORCE
skip-locking
skip-innodb
|
After a couple days, I changed the query cache size in runtime to 250M.
mytop reports: Queries: 918.9M qps: 2262 for an uptime of 4 days and 22 hours.
When the system has additional load, the queries that get bogged down are simple in memory ones. No filesorts, temps, etc. MySQL wants to be alone. And in our environment, PHP runs pretty hard, so the two of them competing for resources on the main server was not a good thing.
Optimizations continue, but I believe we need to go down a path of acquiring high performance servers. One for mysqld and one for apache/php.
At the moment I have the usual forum views updates rerouting to another location. Please disregard the stale view counts.
Comments, questions, suggestions are very much welcome.
Thank you,
Paul
|
|
| Back to top |
|
 |
Bill_Bright
General
Premium Member
Joined: Jan 16, 2004
Posts: 8930
Location: Nebraska, USA
|
|
| Back to top |
|
|
mrrockford
News Admin
AVPE Host
Joined: Apr 24, 2004
Posts: 2994
|
| Posted: Wed Feb 27, 2008 4:20 am Post subject: |
|
|
| Quote: | | Today additional load was placed on that system (external to CC) which has been causing our performance issues to crop up again. |
_________________
"Anyone who considers protocol unimportant has never dealt with a cat."
L. Long
|
|
| Back to top |
|
|
mrrockford
News Admin
AVPE Host
Joined: Apr 24, 2004
Posts: 2994
|
| Posted: Wed Feb 27, 2008 4:21 am Post subject: |
|
|
| Quote: | | Today additional load was placed on that system (external to CC) which has been causing our performance issues to crop up again. |
_________________
"Anyone who considers protocol unimportant has never dealt with a cat."
L. Long
|
|
| Back to top |
|
|
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
| Posted: Wed Feb 27, 2008 4:36 am Post subject: |
|
|
Thanks Mike for pointing out my earlier comment.
Bill, pages did not take regularly 100 seconds plus to load up for pages this past year unless we've been under DDoS trying to mitigate, or the past couple of weeks with daemon/hardware issues.
_________________
Paul Laudanski - http://www.laudanski.com
http://www.linkedin.com/pub/1/49a/17b
|
|
| Back to top |
|
|
moike
PIRT Handler
Premium Member
Joined: May 26, 2006
Posts: 1839
|
| Posted: Wed Feb 27, 2008 1:04 pm Post subject: |
|
|
| Quote: | When the system has additional load, the queries that get bogged down are simple in memory ones. No filesorts, temps, etc. MySQL wants to be alone. And in our environment, PHP runs pretty hard, so the two of them competing for resources on the main server was not a good thing.
Optimizations continue, but I believe we need to go down a path of acquiring high performance servers. One for mysqld and one for apache/php. |
What are the advantages of separate systems over multiple CPUs in a single system?
|
|
| Back to top |
|
|
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
| Posted: Wed Feb 27, 2008 1:33 pm Post subject: |
|
|
When we hosted with applicationx.net, we had mysql on its own box, and the rest on another. Perf was more consistently positive.
In a single case, both php and mysql seek disk time, cpu time, and memory.
Take for instance the IRT backend scripts. When they ran on the main system, perf was degraded due to all the php and mysql processing. Having migrated those scripts elsewhere, the load on the main box diminished.
_________________
Paul Laudanski - http://www.laudanski.com
http://www.linkedin.com/pub/1/49a/17b
|
|
| Back to top |
|
|
moike
PIRT Handler
Premium Member
Joined: May 26, 2006
Posts: 1839
|
| Posted: Wed Feb 27, 2008 2:28 pm Post subject: |
|
|
| Quote: | | In a single case, both php and mysql seek disk time, cpu time, and memory. |
Now that I get into the Freebsd links, I see the problem. FreeBSD does not make effective use of 8 CPUs at all until FreeBSD 7.x, which carries some risk to deploy as soon as it is released.
Also, separate boxes are an easy way to double the disk capacity without needing ZFS, or Networked DAS, or the equivalent. In addition, disk access should be tuned differently for MySQL than Apache/PHP because MySQL has a higher level view of how disk access must be optimized.
So I can see that separate boxes would be a good approach here.
|
|
| Back to top |
|
|
AlphaCentauri
SIRT Handler
Premium Member
Joined: Nov 20, 2003
Posts: 2705
|
| Posted: Wed Feb 27, 2008 3:37 pm Post subject: |
|
|
When I try to follow the link to the "Read here" link at the top of the page, I think I'm getting a not authorized message in German (I think I did manage to register, but I'm still getting it).
|
|
| Back to top |
|
|
Paul
CastleCops Founder
Joined: Feb 22, 2002
Posts: 27351
|
|
| Back to top |
|
|
AlphaCentauri
SIRT Handler
Premium Member
Joined: Nov 20, 2003
Posts: 2705
|
| Posted: Wed Feb 27, 2008 4:31 pm Post subject: |
|
|
Works fine, Danke!
|
|
| Back to top |
|
|
IP: 65.69.*.*
Guest
|
| Posted: Sun Aug 03, 2008 12:43 am Post subject: |
|
|
New job and another baby on the way, Congrats!
Pages are loading in seconds, minutes, and not loading.
Is MySQL resolving host names?
I'm wondering if DNS lookup problems might explain these times.
If so, adding skip-name-resolve to my.cnf may help.
I have found:
http://dev.mysql.com/doc/refman/6.0/en/dns.html
| Quote: |
7.5.10. How MySQL Uses DNS
When a new client connects to mysqld, mysqld spawns a new thread to handle the request. This thread first checks whether the hostname is in the hostname cache. If not, the thread attempts to resolve the hostname:
*If the operating system supports the thread-safe gethostbyaddr_r() and gethostbyname_r() calls, the thread uses them to perform hostname resolution.
*If the operating system does not support the thread-safe calls, the thread locks a mutex and calls gethostbyaddr() and gethostbyname() instead. In this case, no other thread can resolve hostnames that are not in the hostname cache until the first thread unlocks the mutex.
You can disable DNS hostname lookups by starting mysqld with the --skip-name-resolve option. However, in this case, you can use only IP numbers in the MySQL grant tables.
If you have a very slow DNS and many hosts, you can get more performance by either disabling DNS lookups with --skip-name-resolve or by increasing the HOST_CACHE_SIZE define (default value: 128) and recompiling mysqld.
You can disable the hostname cache by starting the server with the --skip-host-cache option. To clear the hostname cache, issue a FLUSH HOSTS statement or execute the mysqladmin flush-hosts command.
To disallow TCP/IP connections entirely, start mysqld with the --skip-networking option.
|
http://dev.mysql.com/doc/refman/6.0/en/freebsd.html
| Quote: |
Unfortunately, certain function calls on FreeBSD are not yet fully thread-safe. Most notably, this includes the gethostbyname() function, which is used by MySQL to convert hostnames into IP addresses. Under certain circumstances, the mysqld process suddenly causes 100% CPU load and is unresponsive. If you encounter this problem, try to start MySQL using the --skip-name-resolve option.
|
More:
http://hackmysql.com/dns
http://jcole.us/blog/archives/2006/04/26/on-ips-hostnames-and-mysql/
http://www.mysqlperformanceblog.com/2008/05/31/dns-achilles-heel-mysql-installation/
http://goddess-gate.com/dc2/index.php/post/255
Good Luck
|
|
| Back to top |
|
|
|
|
|
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You can attach files in this forum
You can download files in this forum
|
Powered by phpBB © 2001 phpBB Group
|
Forum FAQ
Search
Usergroups
Profile
Login to check your private messages
Login
