ASTRAPI Posted November 18, 2010 Posted November 18, 2010 Hello On my server the performance of read and open topics and everything on the forum is super high and less than half second even on pick times. My problem is sql when i have only 10 users per minute the database shows load of cpu: 114% and ram 5%. At pick time it goes to about cpu 170% and ram if i am not wrong is the same about 5%. The problem is at pick time when i try to edit pin unpin move a topic as it takes more than 20 seconds and sometimes i get 500 intrnal server error. (researching about that error i set all folders on correct permissions and i use 775 permissions on the places that need it...) I think something is always hit the sql and don't let it to calm down :( From cpanel and statistics i was trace the process and got this:Process 27265 attached - interrupt to quit select(4, [3], NULL, NULL, NULL) = 1 (in [3]) fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0 accept(3, {sa_family=AF_FILE, path=@""}, [8516025424675864578]) = 444 fcntl(3, F_SETFL, O_RDWR) = 0 getsockname(444, {sa_family=AF_FILE, path="/var/lib/mysql\1"...}, [8516025424675864604]) = 0 fcntl(444, F_SETFL, O_RDONLY) = 0 fcntl(444, F_GETFL) = 0x2 (flags O_RDWR) fcntl(444, F_SETFL, O_RDWR|O_NONBLOCK) = 0 setsockopt(444, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported) futex(0xda3544, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xda3540, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1 futex(0xda2960, FUTEX_WAKE_PRIVATE, 1) = 1 select(4, [3], NULL, NULL, NULL) = 1 (in [3]) fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0 accept(3, {sa_family=AF_FILE, path=@""}, [8516025424675864578]) = 70 fcntl(3, F_SETFL, O_RDWR) = 0 getsockname(70, {sa_family=AF_FILE, path="/var/lib/mysql\1"...}, [8516025424675864604]) = 0 fcntl(70, F_SETFL, O_RDONLY) = 0 fcntl(70, F_GETFL) = 0x2 (flags O_RDWR) fcntl(70, F_SETFL, O_RDWR|O_NONBLOCK) = 0 setsockopt(70, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported) futex(0xda3544, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xda3540, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1 futex(0xda2960, FUTEX_WAKE_PRIVATE, 1) = 1 select(4, [3], NULL, NULL, NULL) = 1 (in [3]) fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0 accept(3, {sa_family=AF_FILE, path=@""}, [8516025424675864578]) = 444 fcntl(3, F_SETFL, O_RDWR) = 0 getsockname(444, {sa_family=AF_FILE, path="/var/lib/mysql\1"...}, [8516025424675864604]) = 0 fcntl(444, F_SETFL, O_RDONLY) = 0 fcntl(444, F_GETFL) = 0x2 (flags O_RDWR) fcntl(444, F_SETFL, O_RDWR|O_NONBLOCK) = 0 setsockopt(444, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported) futex(0xda3544, FUTEX_WAKE_OP_PRIVATE, 1, 1, 0xda3540, {FUTEX_OP_SET, 0, FUTEX_OP_CMP_GT, 1}) = 1 futex(0xda2960, FUTEX_WAKE_PRIVATE, 1) = 0 select(4, [3], NULL, NULL, NULL) = 1 (in [3]) fcntl(3, F_SETFL, O_RDWR|O_NONBLOCK) = 0 accept(3, {sa_family=AF_FILE, path=@""}, [8516025424675864578]) = 444 fcntl(3, F_SETFL, O_RDWR) = 0 getsockname(444, {sa_family=AF_FILE, path="/var/lib/mysql\1"...}, [8516025424675864604]) = 0 fcntl(444, F_SETFL, O_RDONLY) = 0 fcntl(444, F_GETFL) = 0x2 (flags O_RDWR) fcntl(444, F_SETFL, O_RDWR|O_NONBLOCK) = 0 My server: 2x Intel Xeon Quad 5405 16GB ram 4x SAS disks raid 10 If any expert have an idea please let me know :) Thank you
Gary. Posted November 20, 2010 Posted November 20, 2010 I seen and done your server not long back and I can honestly say its pushing to the very max, Its one hell of a BIG / Active community with over 200 members per min. Now you have to re-think that you are running an OLD board " 2. Version " with some heavy modifications what require alot of resources. Now I would honestly think about upgrading. Re-post what your mysql tuner says with your current my.cnf file so we can see what you changed and such to cause issues.vi /etc/my.cnfperl mysqltuner.pl
ASTRAPI Posted November 21, 2010 Author Posted November 21, 2010 Its one hell of a BIG / Active community with over 200 members per min. The problem starts at 300 users per minute as the 300 is my maximum pick time.But it is not in any way a huge forum with 300 users per minute ! :) Now I would honestly think about upgrading. IPB 3? Almost double resources? mysqltuner: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.51 [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 1G (Tables: 150) [--] Data in MEMORY tables: 15M (Tables: 1) [!!] Total fragmented tables: 17 -------- Performance Metrics ------------------------------------------------- [--] Up for: 22d 8h 47m 17s (189M q [98.267 qps], 11M conn, TX: 9101B, RX: 98B) [--] Reads / Writes: 69% / 31% [--] Total buffers: 1.4G global + 288.5M per thread (300 max threads) [!!] Maximum possible memory usage: 85.9G (547% of installed RAM) [OK] Slow queries: 0% (100K/189M) [OK] Highest usage of available connections: 23% (69/300) [OK] Key buffer size / total MyISAM indexes: 1.0G/501.7M [OK] Key buffer hit rate: 100.0% (90B cached / 772K reads) [OK] Query cache efficiency: 42.4% (56M cached / 132M selects) [!!] Query cache prunes per day: 97140 [OK] Sorts requiring temporary tables: 0% (52 temp sorts / 1M sorts) [!!] Joins performed without indexes: 45426 [OK] Temporary tables created on disk: 2% (5K on disk / 248K total) [OK] Thread cache hit rate: 99% (69 created / 11M connections) [!!] Table cache hit rate: 0% (585 open / 93K opened) [OK] Open file limit used: 17% (702/4K) [OK] Table locks acquired immediately: 98% (134M immediate / 136M locks) -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Increase table_cache gradually to avoid file descriptor limits Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** query_cache_size (> 128M) join_buffer_size (> 256.0M, or always use indexes with joins) table_cache (> 1536) my.cnf: [mysqld] skip-innodb skip-locking skip-networking thread_concurrency=16 safe-show-database tmp_table_size=256M max_heap_table_size=128M query_cache_limit=3M query_cache_size=128M query_cache_type=1 max_user_connections=300 max_connections=300 interactive_timeout=30 wait_timeout=50 connect_timeout=30 low_priority_updates=1 thread_cache_size=512 key_buffer=2048M join_buffer_size=256M max_allowed_packet=64M table_cache=1024 record_buffer=1M sort_buffer_size=16M read_buffer_size=16M max_connect_errors=10 myisam_sort_buffer_size=512M concurrent_insert=2 [isamchk] key_buffer=128M sort_buffer128M read_buffer=32M write_buffer=32M [myisamchk] key_buffer=128M sort_buffer=128M read_buffer=32M write_buffer=32M [mysql] no-auto-rehash Also i notice from top command that i have 3,5GB free of ram and the mysqld is using about 220%cpu and 5mb ram about. On cpanel stats the mysql cpu usage is about 115% on the same time.... My server is: # 2x Intel Xeon Quad 5405 # 16GB RAM # 4x SAS disks raid 10 Thank you :)
Gary. Posted November 21, 2010 Posted November 21, 2010 I'm going from what your tuner says on this, And I will make some other addjustment what will be marked in red and the ones I added will be marked in blue, The ones I remove will be marked in Black, The main one is join_buffer_size, Thats extremely high.[mysqld] log-slow-queries=/var/lib/mysql/slow.log skip-innodb skip-locking skip-networking - Already as default so no need this. thread_concurrency=16 This does not exist in linux. safe-show-database This is already default in 5.1 mysql. tmp_table_size=256M This is quite possible that its to low max_heap_table_size=128M query_cache_limit=3M query_cache_size=128M query_cache_type=1 max_user_connections=300 Same value as max_default so no need for this. max_connections=300 interactive_timeout=30 wait_timeout=50 connect_timeout=30 low_priority_updates=1 This is already default in mysql 5.1 so again, No need. thread_cache_size=512 key_buffer=2048M, Again this value is to high and will consume many memory. join_buffer_size=256M This is extremely high, Probably why your mysql usuage is high. max_allowed_packet=64M table_cache=1024This is to low, You have 16GB memory, around 4086 is reasonable for that. record_buffer=1M sort_buffer_size=16M read_buffer_size=16M max_connect_errors=10 myisam_sort_buffer_size=512M concurrent_insert=2 [isamchk] key_buffer=128M sort_buffer128M read_buffer=32M write_buffer=32M [myisamchk] key_buffer=128M sort_buffer=128M read_buffer=32M write_buffer=32M [mysql] no-auto-rehash so you want to use this one below[mysqld] log-slow-queries=/var/lib/mysql/slow.log skip-innodb tmp_table_size=512M max_heap_table_size=128M query_cache_limit=3M query_cache_size=128M query_cache_type=1 max_connections=300 interactive_timeout=30 wait_timeout=50 connect_timeout=30 thread_cache_size=512 key_buffer=512M join_buffer_size=6M max_allowed_packet=64M table_cache=4086 record_buffer=1M sort_buffer_size=16M read_buffer_size=16M max_connect_errors=10 myisam_sort_buffer_size=512M concurrent_insert=2 [isamchk] key_buffer=128M sort_buffer128M read_buffer=32M write_buffer=32M [myisamchk] key_buffer=128M sort_buffer=128M read_buffer=32M write_buffer=32M [mysql] no-auto-rehash Save and restart mysql server:/etc/init.d/mysql restart Also IPB is not double the resource. Your community would function on 128MB and run very well, If you set that on your's now you will get memory errors. I have a few big communities on me and with our setup they consume next to nothing. We have over 70IPB boards on us and we run 4GB DDR3. Yet out memory usuage is 20% ish, And thats with shoutcast servers being on the same machine and that has over 100 active streams. I like IPB compaired to what it was. Now it can only consume to what you give it and how things are installed, Only badly installed scripts will consume all resources.
ASTRAPI Posted November 21, 2010 Author Posted November 21, 2010 Thank you very much for your help i will try them and let you know :)
ASTRAPI Posted November 22, 2010 Author Posted November 22, 2010 What is the difference between max_user_connections=300 and max_connections=300 ? What is a good value for both? If i increase them to 500 it will use mmore memory or cpu or both?
Gary. Posted November 22, 2010 Posted November 22, 2010 max_connections should always be higher than max_user_connections. The value all depends on the server itself, But 300 is way more than enough, I would only use 500 if your planning on hosting eBay or such. The connections goes of the per based client applications used and called for mysql, Now IPB would go no where near 10 let alone 500. But also increasing would also allocate more memory to be used, You will probably get a warning saying memory usuage is dangerously high within mysql tuner. Our values are: max_user_connections= 50 max_connections=200 Our mysql usuage is around 0.06% with 5.7% memory and currently serving 70 + IPB boards, Plus other applications such as VB, wordpress, Jooluma, Possible more if we looked deeply into it. Screen shot below is taken at peak time: Current apache connections: 2506
Recommended Posts
Archived
This topic is now archived and is closed to further replies.