Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
Mr-Y Posted September 24, 2009 Posted September 24, 2009 Does anyone know or can explain what the below is ? Its from the mysql process list mysql cpu = 25.3 mem = 13.4 If i kill the process it comes back straight away and builds the load up It doesn't finish either, my IPB v3.03 is dreadfully slow, at least 20secs load time on any page in the board However the test board (same version) is not slow hope somebody can help :)Process 14303 attached - interrupt to quit select(12, [11], NULL, NULL, NULL) = 1 (in [11]) fcntl64(11, F_SETFL, O_RDWR|O_NONBLOCK) = 0 accept(11, {sa_family=AF_FILE, path=@}, [2]) = 25 fcntl64(11, F_SETFL, O_RDWR) = 0 getsockname(25, {sa_family=AF_FILE, path="/tmp/mysql.soc"}, [18]) = 0 fcntl64(25, F_SETFL, O_RDONLY) = 0 fcntl64(25, F_GETFL) = 0x2 (flags O_RDWR) fcntl64(25, F_SETFL, O_RDWR|O_NONBLOCK) = 0 setsockopt(25, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported) futex(0x86a2024, FUTEX_WAKE, 1) = 1 futex(0x86a2020, FUTEX_WAKE, 1) = 1 futex(0x869ffe0, FUTEX_WAKE, 1) = 1 select(12, [11], NULL, NULL, NULL) = 1 (in [11]) fcntl64(11, F_SETFL, O_RDWR|O_NONBLOCK) = 0 accept(11, {sa_family=AF_FILE, path=@}, [2]) = 25 fcntl64(11, F_SETFL, O_RDWR) = 0 My server info also Server Information Processor Information Processor #1 Vendor: GenuineIntel Processor #1 Name: Intel® Pentium® 4 CPU 3.06GHz Processor #1 speed: 3082.796 MHz Processor #1 cache size: 512 KB Processor #2 Vendor: GenuineIntel Processor #2 Name: Intel® Pentium® 4 CPU 3.06GHz Processor #2 speed: 3082.796 MHz Processor #2 cache size: 512 KB Memory Information Memory: 1032692k/1047488k available (1884k kernel code, 14108k reserved, 759k data, 188k init, 129984k highmem) System Information Linux server.wyredbytes.com 2.6.9-55.0.12.ELsmp #1 SMP Fri Nov 2 11:19:08 EDT 2007 i686 i686 i386 GNU/Linux Physical Disks hda: ST3120022A, ATA DISK drive hda: max request size: 1024KiB hda: 234441648 sectors (120034 MB) w/2048KiB Cache, CHS=16383/255/63, UDMA(100) hda: cache flushes supported Current Memory Usage total used free shared buffers cached Mem: 1033572 891356 142216 0 20132 510700 -/+ buffers/cache: 360524 673048 Swap: 2096472 6072 2090400 Total: 3130044 897428 2232616 Current Disk Usage Filesystem Size Used Avail Use% Mounted on /dev/hda3 108G 11G 93G 11% / /dev/hda1 122M 17M 100M 14% /boot none 505M 0 505M 0% /dev/shm /usr/tmpDSK 485M 11M 449M 3% /tmp /tmp 485M 11M 449M 3% /var/tmp my.cnf if it helps # The MySQL server [mysqld] #port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 256M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M join_buffer_size = 256 low_priority_updates=1 concurrent_insert=2 thread_cache = 8 query_cache_size= 5M tmp_table_size = 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 2 myisam_data_pointer_size = 7 log-slow-queries = mysqllog long_query_time = 5 log-queries-not-using-indexes log-slow-admin-statements # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # skip-networking # Replication Master Server (default) # binary logging is required for replication # log-bin=mysql-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname [mysqldump] quick max_allowed_packet = 256M [mysql] no-auto-rehash max_allowed_packet = 256M # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 128M sort_buffer_size = 128M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout also some of the last entry's from my slow log SELECT id, member_id, member_name, seo_name, login_type, running_time, member_group, uagent_type FROM ibf_sessions WHERE running_time > 1253823900; # Time: 090924 22:25:12 # User@Host: komp_komp[komp_komp] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 56 Rows_examined: 56 SELECT id, member_id, member_name, seo_name, login_type, running_time, member_group, uagent_type FROM ibf_sessions WHERE running_time > 1253823912; # Time: 090924 22:25:13 # User@Host: komp_komp[komp_komp] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 56 Rows_examined: 56 SELECT id, member_id, member_name, seo_name, login_type, running_time, member_group, uagent_type FROM ibf_sessions WHERE running_time > 1253823913; # Time: 090924 22:25:14 # User@Host: komp_komp[komp_komp] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 20 Rows_examined: 40 SELECT s.*,m.member_id, m.member_group_id, m.members_display_name, m.members_seo_name, mgroup_others FROM ibf_shoutbox_shouts s LEFT JOIN ibf_members m ON ( m.member_id=s.s_mid ) ORDER BY s.s_date DESC LIMIT 0,20; # Time: 090924 22:25:22 # User@Host: komp_komp[komp_komp] @ localhost [] # Query_time: 0 Lock_time: 0 Rows_sent: 56 Rows_examined: 56 SELECT id, member_id, member_name, seo_name, login_type, running_time, member_group, uagent_type FROM ibf_sessions WHERE running_time > 1253823922; also from /tuning-primer.sh Avg. qps = 11 Total Questions = 967 Threads Connected = 1 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/en/server-system-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 enabled. Current long_query_time = 5 sec. You have 13 out of 996 that take longer than 5 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/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 8 Current threads_cached = 3 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 100 Current threads_connected = 1 Historic max_used_connections = 4 The number of used connections is 4% of the configured maximum. You are using less than 10% of your configured max_connections. Lowering max_connections could help to avoid an over-allocation of memory See "MEMORY USAGE" section to make sure you are not over-allocating MEMORY USAGE Max Memory Ever Allocated : 295 M Configured Max Per-thread Buffers : 619 M Configured Max Global Buffers : 271 M Configured Max Memory Limit : 890 M Physical Memory : 1009 M Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 177 M Current key_buffer_size = 256 M Key cache miss rate is 1 : 19 Key buffer free ratio = 88 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is enabled Current query_cache_size = 5 M Current query_cache_used = 2 M Current query_cache_limit = 1 M Current Query cache Memory fill ratio = 58.96 % Current query_cache_min_res_unit = 4 K MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 1 M Current read_rnd_buffer_size = 4 M Sort buffer seems to be fine JOINS Current join_buffer_size = 12.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 = 1024 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 = 350 tables You have a total of 292 tables You have 293 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 16 M Current tmp_table_size = 16 M Of 228 temp tables, 6% were created on disk Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 1 M Current table scan ratio = 329 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 0 : 1200 Your table locking seems to be fine
Mr-Y Posted September 26, 2009 Author Posted September 26, 2009 did i miss something ? no-one have anything to say on the above ?
Bono Posted September 26, 2009 Posted September 26, 2009 First you need to wait 48h for tuning-primer to examine your settings. Second try to optimize your database, and then check again if there will be slow queries. Third do hdparm -t /dev/hda maybe disk switched to pio mode or something so everything is slowed down, with 11qps you shouldn't have any slow queries. Fourth do also iostat to check if your hdd is too slow, you are really using old hdd.
Mr-Y Posted September 26, 2009 Author Posted September 26, 2009 Hi and thanks for replying, i will get on that straight away, i will report back cause it may help others also :)
Mr-Y Posted September 26, 2009 Author Posted September 26, 2009 Gonna try and see if the recommendations below will make any difference unless anyone has any other ideas ;) My IPB is 5.5 years old and has gone from v1.3 to 3.03 in that time if it makes any difference. My database = 133 table(s) 1,300,948 Queries MyISAM Size = 1.0 GiB hdparm -t /dev/hda /dev/hda: Timing buffered disk reads: 164 MB in 3.02 seconds = 54.24 MB/sec iostat Linux 2.6.9-55.0.12.ELsmp (server.wyredbytes.com) 09/26/2009 avg-cpu: %user %nice %sys %iowait %idle 15.60 0.01 1.63 0.34 82.42 Device: tps Blk_read/s Blk_wrtn/s Blk_read Blk_wrtn hda 6.25 49.18 186.69 21593748 81972220 hda1 0.00 0.00 0.00 702 4 hda2 0.02 0.05 0.09 21740 40344 hda3 25.39 48.36 186.60 21233642 81931464 /tuning-primer.sh -- MYSQL PERFORMANCE TUNING PRIMER -- - By: Matthew Montgomery - MySQL Version 5.0.81-community-log i686 Uptime = 2 days 0 hrs 1 min 31 sec Avg. qps = 4 Total Questions = 739702 Threads Connected = 1 Server has been running for over 48hrs. It should be safe to follow 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/en/server-system-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 enabled. Current long_query_time = 5 sec. You have 8868 out of 739723 that take longer than 5 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/en/point-in-time-recovery.html WORKER THREADS Current thread_cache_size = 8 Current threads_cached = 7 Current threads_per_sec = 0 Historic threads_per_sec = 0 Your thread_cache_size is fine MAX CONNECTIONS Current max_connections = 100 Current threads_connected = 1 Historic max_used_connections = 20 The number of used connections is 20% of the configured maximum. Your max_connections variable seems to be fine. MEMORY USAGE Max Memory Ever Allocated : 394 M Configured Max Per-thread Buffers : 619 M Configured Max Global Buffers : 271 M Configured Max Memory Limit : 890 M Physical Memory : 1009 M Max memory limit seem to be within acceptable norms KEY BUFFER Current MyISAM index space = 178 M Current key_buffer_size = 256 M Key cache miss rate is 1 : 83 Key buffer free ratio = 87 % Your key_buffer_size seems to be fine QUERY CACHE Query cache is enabled Current query_cache_size = 5 M Current query_cache_used = 2 M Current query_cache_limit = 1 M Current Query cache Memory fill ratio = 59.37 % Current query_cache_min_res_unit = 4 K MySQL won't cache query results that are larger than query_cache_limit in size SORT OPERATIONS Current sort_buffer_size = 1 M Current read_rnd_buffer_size = 4 M Sort buffer seems to be fine JOINS Current join_buffer_size = 12.00 K You have had 14 queries where a join could not use an index properly You should enable "log-queries-not-using-indexes" Then look for non indexed joins in the slow query log. If you are unable to optimize your queries you may want to increase your join_buffer_size to accommodate larger joins in one pass. Note! This script will still suggest raising the join_buffer_size when ANY joins not using indexes are found. OPEN FILES LIMIT Current open_files_limit = 1024 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 = 350 tables You have a total of 292 tables You have 304 open tables. The table_cache value seems to be fine TEMP TABLES Current max_heap_table_size = 16 M Current tmp_table_size = 16 M Of 3253 temp tables, 21% were created on disk Created disk tmp tables ratio seems fine TABLE SCANS Current read_buffer_size = 1 M Current table scan ratio = 2691 : 1 read_buffer_size seems to be fine TABLE LOCKING Current Lock Wait ratio = 1 : 75 You may benefit from selective use of MyISAM.
Bono Posted September 26, 2009 Posted September 26, 2009 Hmm that looks OK, I would recommend to report this issue with IPS. After upgrade have you rebuild all the stuff IPS recommends and after that have you optimized your database?
Mr-Y Posted September 26, 2009 Author Posted September 26, 2009 HI, going to check to make sure i did everything for the upgrade then i think i will report the issue thanks again :)
Bono Posted September 27, 2009 Posted September 27, 2009 Keep us informed, when IPS detect your problem maybe it would help someone in the future.
Mr-Y Posted September 29, 2009 Author Posted September 29, 2009 ok i put a ticket in, but not 2 seconds after i did i found the problem for me this addon (SOS30) Number of Users per Skin v1.0.0 was a major factor in slowing the loadtimes of my board, dont wanna ruffle feathers, just thought best to post my probs and answers. thanks all for reading.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.