jokerfool Posted April 17, 2012 Share Posted April 17, 2012 I am running a dating site with 100k users and the load time of my users is about 5 secs. Specs: 90 GB Storage 1.5 TB Monthly Transfer 2 GB Guaranteed RAM 4 GB Burst RAM Full Specs Hardware RAID 10 CentOS 5 or 6, 32 or 64-bit 4 IP Addresses Incredibly fast disk I/O Intel(R) Xeon(R) CPU L5320 @ 1.86GHz Speed 627.305 MHz Cache 4096 KB 8 cpus on hostmachine This is my config file, can someone have a look at it and see if there are any changes I can make that will speed up the loads times, thank you. [mysqld] log-slow-queries = /var/lib/mysql/vps-slow.log log-queries-not-using-indexes long_query_time = 5 expire_logs_days=10 max_binlog_size=100M safe-show-database local-infile = 0 skip-networking skip-innodb query_cache_limit=8M query_cache_size=256M query_cache_type=1 # Reduced to 180 as memory will not be enough for 500 connections. max_connections=125 max_user_connections=125 # interactive_timeout=100 interactive_timeout=30 # Reduced wait_timeout to prevent idle clients holding connections # wait_timeout=100 wait_timeout=30 connect_timeout=10 thread_stack=128K thread_cache_size=128 myisam-recover=BACKUP #key_buffer_size - 64M for 1GB, 128M for 2GB, 256 for 4GB key_buffer_size=64M #join_buffer_size - 1M for 1GB, 2M for 2GB, 4M for 4GB join_buffer_size=8M max_allowed_packet=32M table_cache=2048 table_definition_cache=2048 #sort_buffer_size - 1M for 1GB, 2M for 2GB, 4M for 4GB sort_buffer_size=4M #read_buffer_size - 1M for 1GB, 2M for 2GB, 4M for 4GB read_buffer_size=4M #read_rnd_buffer_size - 768K for 1GB, 1536K for 2GB, 3072K for 4GB read_rnd_buffer_size=2M max_connect_errors=10 #myisam_sort_buffer_size - 32M for 1GB, 64M for 2GB, 128 for 4GB myisam_sort_buffer_size=64M concurrent_insert=2 low_priority_updates=1 [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] #key_buffer - 64M for 1GB, 128M for 2GB, 256M for 4GB key_buffer=128M #sort_buffer - 64M for 1GB, 128M for 2GB, 256M for 4GB sort_buffer=128M #read_buffer - 16M for 1GB, 32M for 2GB, 64M for 4GB read_buffer=32M #write_buffer - 16M for 1GB, 32M for 2GB, 64M for 4GB write_buffer=32M [myisamchk] #key_buffer - 64M for 1GB, 128M for 2GB, 256M for 4GB key_buffer=128M #sort_buffer - 64M for 1GB, 128M for 2GB, 256M for 4GB sort_buffer=128M #read_buffer - 16M for 1GB, 32M for 2GB, 64M for 4GB read_buffer=32M #write_buffer - 16M for 1GB, 32M for 2GB, 64M for 4GB write_buffer=32M [mysqlhotcopy] interactive-timeout Quote Link to comment Share on other sites More sharing options...
kicken Posted April 17, 2012 Share Posted April 17, 2012 Showing the query in question as well as related tables/indexes would probably be more helpful then the mysql config. Quote Link to comment Share on other sites More sharing options...
jokerfool Posted April 22, 2012 Author Share Posted April 22, 2012 Can you be a bit more specific on what I need to do, I am not good at programming in mysql so anything you ask, please give me a quick description on what you need me to do and I can certainly go from there. Thanks for the reply. Quote Link to comment Share on other sites More sharing options...
fenway Posted April 22, 2012 Share Posted April 22, 2012 Showing the query in question as well as related tables/indexes would probably be more helpful then the mysql config. That couldn't be more specific. Quote Link to comment Share on other sites More sharing options...
awjudd Posted April 22, 2012 Share Posted April 22, 2012 You are logging slow queries to "/var/lib/mysql/vps-slow.log" ... so show us what is in that file. ~awjudd Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.