menntarra_34

well, i did several tests with speedtest.net, everything was all right. I'm not mixing up kbit kbyte so that is not a problem... Just now i tested dslreports, several servers(washington-comcast), Chicago etc... Average dl speed was 900kb/s, upload was 700kb/s, So while i see a loss between testing in US and Europe it isn't as huge as i'm experiencing with my server. As far as i know my server provider is connected with comcast. Anyway, i confirm this problem to be between my isp and the server provider in US. So thanks for your help

and i guess you're somewhere in US? If yes, i think the reason why it is slow for me, cause I'm in europe. Is it possible that i have so much "loss" between US and Europe. (instead of 1M/s i have 60kb/s...)

By the way, here is my sysctl.conf , if it helps in any ways: # Auto-reboot linux 30 seconds after a kernel panic kernel.panic = 30 kernel.panic_on_oops = 30 # Controls the System Request debugging functionality of the kernel kernel.sysrq = 0 # Controls whether core dumps will append the PID to the core filename. # Useful for debugging multi-threaded applications. kernel.core_uses_pid = 1 #Allow for more PIDs kernel.pid_max = 65536 # The contents of /proc/<pid>/maps and smaps files are only visible to # readers that are allowed to ptrace() the process kernel.maps_protect = 1 #Enable ExecShield protection kernel.exec-shield = 1 kernel.randomize_va_space = 1 # Controls the maximum size of a message, in bytes kernel.msgmnb = 65536 # Controls the default maxmimum size of a mesage queue kernel.msgmax = 65536 ### ### IMPROVE SYSTEM MEMORY MANAGEMENT ### ### # Increase size of file handles and inode cache fs.file-max = 209708 # Do less swapping vm.swappiness = 10 vm.dirty_ratio = 60 vm.dirty_background_ratio = 2 # specifies the minimum virtual address that a process is allowed to mmap vm.mmap_min_addr = 4096 # No overcommitment of available memory vm.overcommit_ratio = 0 vm.overcommit_memory = 0 # Set maximum amount of memory allocated to shm to 256MB kernel.shmmax = 268435456 kernel.shmall = 268435456 # Keep at least 64MB of free RAM space available vm.min_free_kbytes = 65536 ### ### GENERAL NETWORK SECURITY OPTIONS ### ### #Prevent SYN attack, enable SYNcookies (they will kick-in when the max_syn_backlog reached) net.ipv4.tcp_syncookies = 1 net.ipv4.tcp_syn_retries = 5 net.ipv4.tcp_synack_retries = 2 net.ipv4.tcp_max_syn_backlog = 4096 # Disables packet forwarding net.ipv4.ip_forward = 0 net.ipv4.conf.all.forwarding = 0 net.ipv4.conf.default.forwarding = 0 net.ipv6.conf.all.forwarding = 0 net.ipv6.conf.default.forwarding = 0 # Disables IP source routing net.ipv4.conf.all.send_redirects = 0 net.ipv4.conf.default.send_redirects = 0 net.ipv4.conf.all.accept_source_route = 0 net.ipv4.conf.default.accept_source_route = 0 net.ipv6.conf.all.accept_source_route = 0 net.ipv6.conf.default.accept_source_route = 0 # Enable IP spoofing protection, turn on source route verification net.ipv4.conf.all.rp_filter = 1 net.ipv4.conf.default.rp_filter = 1 # Disable ICMP Redirect Acceptance net.ipv4.conf.all.accept_redirects = 0 net.ipv4.conf.default.accept_redirects = 0 net.ipv6.conf.all.accept_redirects = 0 net.ipv6.conf.default.accept_redirects = 0 # Disable Log Spoofed Packets, Source Routed Packets, Redirect Packets net.ipv4.conf.all.log_martians = 0 net.ipv4.conf.default.log_martians = 0 # Decrease the time default value for tcp_fin_timeout connection net.ipv4.tcp_fin_timeout = 15 # Decrease the time default value for connections to keep alive net.ipv4.tcp_keepalive_time = 300 net.ipv4.tcp_keepalive_probes = 5 net.ipv4.tcp_keepalive_intvl = 15 # Don't relay bootp net.ipv4.conf.all.bootp_relay = 0 # Don't proxy arp for anyone net.ipv4.conf.all.proxy_arp = 0 # Turn on SACK net.ipv4.tcp_dsack = 1 net.ipv4.tcp_sack = 1 net.ipv4.tcp_fack = 1 # Turn on the tcp_timestamps net.ipv4.tcp_timestamps = 1 # Don't ignore directed pings net.ipv4.icmp_echo_ignore_all = 0 # Enable ignoring broadcasts request net.ipv4.icmp_echo_ignore_broadcasts = 1 # Enable bad error message Protection net.ipv4.icmp_ignore_bogus_error_responses = 1 # Allowed local port range net.ipv4.ip_local_port_range = 16384 65536 # Enable a fix for RFC1337 - time-wait assassination hazards in TCP net.ipv4.tcp_rfc1337 = 1 ### ### TUNING NETWORK PERFORMANCE ### ### # Do a 'modprobe tcp_cubic' first net.ipv4.tcp_congestion_control = cubic # Turn on the tcp_window_scaling net.ipv4.tcp_window_scaling = 1 # Increase the maximum total buffer-space allocatable # This is measured in units of pages (4096 bytes) net.ipv4.tcp_mem = 65536 131072 262144 net.ipv4.udp_mem = 65536 131072 262144 # Increase the read-buffer space allocatable net.ipv4.tcp_rmem = 8192 87380 16777216 net.ipv4.udp_rmem_min = 16384 net.core.rmem_default = 131072 net.core.rmem_max = 16777216 # Increase the write-buffer-space allocatable net.ipv4.tcp_wmem = 8192 65536 16777216 net.ipv4.udp_wmem_min = 16384 net.core.wmem_default = 131072 net.core.wmem_max = 16777216 # Increase number of incoming connections net.core.somaxconn = 32768 # Increase number of incoming connections backlog net.core.netdev_max_backlog = 4096 net.core.dev_weight = 64 # Increase the maximum amount of option memory buffers net.core.optmem_max = 65536 # Increase the maximum number of skb-heads to be cached #net.core.hot_list_length = 1024 # Increase the tcp-time-wait buckets pool size to prevent simple DOS attacks net.ipv4.tcp_max_tw_buckets = 1440000 net.ipv4.tcp_tw_recycle = 1 net.ipv4.tcp_tw_reuse = 1 # Limit number of orphans, each orphan can eat up to 16M (max wmem) of unswappable memory net.ipv4.tcp_max_orphans = 16384 net.ipv4.tcp_orphan_retries = 0 # Increase the maximum memory used to reassemble IP fragments net.ipv4.ipfrag_high_thresh = 512000 net.ipv4.ipfrag_low_thresh = 446464 # don't cache ssthresh from previous connection net.ipv4.tcp_no_metrics_save = 1 net.ipv4.tcp_moderate_rcvbuf = 1 # Increase RPC slots sunrpc.tcp_slot_table_entries = 32 sunrpc.udp_slot_table_entries = 32 # Increase size of RPC datagram queue length net.unix.max_dgram_qlen = 50 # Don't allow the arp table to become bigger than this net.ipv4.neigh.default.gc_thresh3 = 2048 # Tell the gc when to become aggressive with arp table cleaning. # Adjust this based on size of the LAN. 1024 is suitable for most /24 networks net.ipv4.neigh.default.gc_thresh2 = 1024 # Adjust where the gc will leave arp table alone - set to 32. net.ipv4.neigh.default.gc_thresh1 = 32 # Adjust to arp table gc to clean-up more often net.ipv4.neigh.default.gc_interval = 30 # Increase TCP queue length net.ipv4.neigh.default.proxy_qlen = 96 net.ipv4.neigh.default.unres_qlen = 6 # Enable Explicit Congestion Notification (RFC 3168), disable it if it doesn't work for you net.ipv4.tcp_ecn = 1 net.ipv4.tcp_ecn = 2 net.ipv4.tcp_reordering = 3 # How many times to retry killing an alive TCP connection net.ipv4.tcp_retries2 = 15 net.ipv4.tcp_retries1 = 3 # This will enusre that immediatly subsequent connections use the new values net.ipv4.route.flush = 1 net.ipv6.route.flush = 1 and please test this file, and tell me what is the HIGHEST speed you get when you download it(harmless 6 MB file), and also tell me your location around the world: http://74.63.79.124/games/files/Combat3.zip

I've contacted my server provider and they told me they never have limitations such as this. funny thing is that i have another server rented, near my current location, and i tried to download file with "wget" command, and i got 1MB/sec speed...

One more thing: i also have lighttpd installed on a separate port, and i tried that as well and it has the same limitation as httpd.

I don't know why this is happening, but, it seems apache is limiting the per visitor speed, which is bad, cause my site provides stuff for download. So my limit is about 70-100kb/sec , however with shell, i can download at 1M/sec rate , i also tried to check to download several files, and i could download all of them at the same time with the speed of 70-100kb/sec, which clearly shows me that there should be a limiting factor... DO note : i have mod_bandwidth installed, but i'm NOT using it. i tried to put Sendbuffersize = 10000000 , but it didn't change a thing. SYSTEM: CPANEL 64 bit, centos 5 apache 2.2.19

well i already showed mysqltuner results, and if i run this command: select TABLE_NAME,Data_free from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free > 0; /I attached picture of output of the command/ It shows the same number, about 160-168 tables fragmented... Whatsmore, i checked indexes on these tables and the cardinality is constantly changing on all indexes on these fragmented tables. I also checked a table, and tried to find fragmented part of it, by trying to delete first part of the table(fragmentation dissapeard), and later i imported the original back again and deleted the other part, and strangely fragmentation dissapeared again. So the only thing what was important is to delete a spesific amount of data of the table. maybe it has something to do with indexes? [attachment deleted by admin]

Of course! But with innodb instead of optimize, you should defragment them by ALTER TABLE tablename ENGINE=InnoDB; Anyway i tried bith optimize, and alter table, none of them works...

i still haven't found any answers, fragmented table number is the same...

(NOTE: i recently converted my old myisam database tables to innodb, and this is the result) i found a strange problem: nearly all new innodb tables are reported to be fragmented, i tried to 1.) optimize them 2.) repair them 3.) ALTER TABLE table_name Engine= INNODB; The third should have solved it ( i read that in innodb the alter table method works...) But still none of them got repaired... I tried to find some kind of order of the fragmented tables, but found none: i mean there are tables fragmented with only id field in them. There are tables with id and varchar fields etc... I also tried with one of them to convert back to myisam and repair, than convert back to innodb without success... I also saw in mysql doc, that dumping table, droping it and importing back does the same so, i gave that a try with one table but didn't help. (http://dev.mysql.com/doc/refman/5.0/en/innodb-file-defragmenting.html) mysqltuner: -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.56-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 8G (Tables: 373) [--] Data in InnoDB tables: 9G (Tables: 186) [!!] Total fragmented tables: 161 -------- Performance Metrics ------------------------------------------------- [--] Up for: 3m 26s (30K q [149.500 qps], 1K conn, TX: 389M, RX: 24M) [--] Reads / Writes: 89% / 11% [--] Total buffers: 19.4G global + 12.2M per thread (512 max threads) [OK] Maximum possible memory usage: 25.5G (81% of installed RAM) [OK] Slow queries: 0% (42/30K) [OK] Highest usage of available connections: 34% (176/512) [OK] Key buffer size / total MyISAM indexes: 3.0G/2.9G [!!] Key buffer hit rate: 91.0% (202K cached / 18K reads) [OK] Query cache efficiency: 57.4% (14K cached / 24K selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 917 sorts) [OK] Temporary tables created on disk: 10% (73 on disk / 688 total) [OK] Thread cache hit rate: 88% (176 created / 1K connections) [OK] Table cache hit rate: 98% (586 open / 593 opened) [OK] Open file limit used: 3% (304/7K) [OK] Table locks acquired immediately: 99% (22K immediate / 22K locks) [OK] InnoDB data size / buffer pool: 9.4G/14.7G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate

Thank you! I did a lot of changes, and load gone down , but still a little high, and as i looked at the slow query log, i think that there are a lot of inserts and updates of my tables, would that be a good idea to make another separate db just for UPDATES/INSERTS/REPLACES , and use it for that, and the other db, just for READS, and i would sync them once or twice a day, maybe that is a good idea? If yes, do you know any good sync methods of 2 db-s?

Thanks for your answer, i made som modification, but because it is a search engine, if there are criterias, then i NEED THE ORDER BY mysql function a lot of times, and i need to order that 250.000 ids, cause i want to get ids from all of it. I mean if i just include for example 30 ids and order them, that is not okay, cause then the data won't be real becase the order by will only order that 30 result set... What do you mean why use left join, what do you advise instead, that would mean performance increase?

I wrote a search script but i think it needs to be changed cause it is not very good, and my server have VERY high load, i explain it, ask if you don't understand : I post the problematic part, any suggestion is welcome, on how to change the structure of mysql tables, or php code... ###Tables are like this: ###tables with their coloumns: ###big_table(id,value1,value2,value3 etc...) ###search_words(id,words) ###connection(search_words_id,big_table_id) ###Our goal is to get results from big_table based on words from search_words table! $keywords are like this: "plane car football" $keywords = explode(" ", $keyword); foreach ($keywords as $value) { SELECT big_table_id FROM search_words AS t LEFT JOIN connection AS tv ON tv.search_words_id = t.id WHERE words = '". $value ."' AND big_table_id IS NOT NULL $result = mysql_query($query); while ($row = mysql_fetch_array($result, MYSQL_NUM)) { $big_id_results[$value][] = $row[0]; } } if (count($big_id_results) == 1) { $all_ids = array_shift($big_id_results); } else { $all_ids = call_user_func_array('array_intersect', $big_id_results); } ###And lastly we get results from big table, with the the ids: $query = "SELECT * FROM big_table WHERE active=1 AND id IN (".implode(", ",$all_ids)." So basicly this is my search algorithm, if you can think of anything better , don't hesitate to tell me... My long queries are because of this last query: $query = "SELECT * FROM big_table WHERE active=1 AND id IN (".implode(", ",$all_ids).") LIMIT 0 , 30"; Because sometimes "$all_ids" have like 250.000 ids separated by comma...

Well i have high mysql usage, but it does not makes this high load, just here is the example i recorded today: See: mysql uses about 338% CPU, but that is OKAY! Load is 5.72: top - 16:11:49 up 5 days, 4:41, 1 user, load average: 5.72, 6.25, 7.04 Tasks: 448 total, 1 running, 447 sleeping, 0 stopped, 0 zombie Cpu(s): 48.4%us, 15.2%sy, 0.0%ni, 20.8%id, 10.8%wa, 0.3%hi, 4.6%si, 0.0%st Mem: 32950216k total, 32795368k used, 154848k free, 144844k buffers Swap: 34996216k total, 192k used, 34996024k free, 24051068k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND 5382 mysql 15 0 5683m 2.8g 4404 S 338.0 8.8 681:55.53 mysqld 14034 nobody 15 0 254m 31m 11m S 20.5 0.1 1:41.59 httpd 14160 nobody 16 0 253m 32m 13m D 14.9 0.1 1:37.69 httpd 14075 nobody 15 0 252m 31m 12m S 12.2 0.1 1:38.79 httpd 4798 nobody 16 0 658m 343m 288m D 10.9 1.1 1224:49 lighttpd 14142 nobody 15 0 260m 38m 11m S 10.3 0.1 1:41.42 httpd 14145 nobody 15 0 251m 31m 12m S 6.3 0.1 1:38.43 httpd Here mysql uses: 283% , but load is 13.80 ( it just dropped from 17...) top - 16:56:54 up 5 days, 5:26, 1 user, load average: 13.80, 14.37, 11.52 Tasks: 448 total, 10 running, 438 sleeping, 0 stopped, 0 zombie Cpu(s): 61.8%us, 13.5%sy, 0.0%ni, 14.2%id, 4.3%wa, 0.3%hi, 5.9%si, 0.0%st Mem: 32950216k total, 32778408k used, 171808k free, 142180k buffers Swap: 34996216k total, 192k used, 34996024k free, 23590200k cached PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ RUSER COMMAND 5382 mysql 15 0 5922m 3.0g 4408 S 283.9 9.6 795:20.24 mysql mysqld 14126 nobody 17 0 254m 33m 13m R 51.0 0.1 2:05.92 nobody httpd 14042 nobody 17 0 252m 32m 13m R 41.0 0.1 2:16.45 nobody httpd 14185 nobody 15 0 255m 34m 12m S 21.2 0.1 2:17.98 nobody httpd 13989 nobody 15 0 259m 38m 16m S 20.8 0.1 2:20.51 nobody httpd 14138 nobody 15 0 255m 36m 13m S 18.2 0.1 2:23.18 nobody httpd 14060 nobody 15 0 251m 31m 12m S 14.2 0.1 1:53.44 nobody httpd 14107 nobody 15 0 253m 32m 13m S 11.2 0.1 2:06.62 nobody httpd 4798 nobody 17 0 914m 457m 402m R 9.9 1.4 1237:19 nobody lighttpd So all in all my mysql uses 200-400 % cpu, but that is not a problem, have never been a problem... now these high loads are occuring and i don't know the cause... Some additional info: About apache: i have keepalive on, with keepalive timeout = 1 And here is my prefork info: StartServers 256 MinSpareServers 256 MaxSpareServers 256 ServerLimit 256 MaxClients 256 MaxRequestsPerChild 0

Hello, my server has high load problems... I have 2 XEON E5506 cpus which means 2x4 cores = 8 , so when my load is more than 8, i should be worried... And now it is constantly at about 16-24 !!! I have 32gb memory. My my.cnf is tuned pretty much at the level where it perfect (all mysql performance scripts report that it is all OK) What is strange is that my websites are working fine. My memory swap is zero, also my cpu %wa is pretty low! So i have no clue what could cause problems, please advise... Also here is output from top: top - 11:55:48 up 5 days, 25 min, 1 user, load average: 16.63, 16.98, 17.10 Tasks: 450 total, 13 running, 436 sleeping, 0 stopped, 1 zombie Cpu(s): 74.8%us, 18.3%sy, 0.0%ni, 1.5%id, 0.7%wa, 0.2%hi, 4.5%si, 0.0%st Mem: 32950216k total, 32827592k used, 122624k free, 143960k buffers Swap: 34996216k total, 296k used, 34995920k free, 22197412k cached as you can see cpu usage is HIGH, what my question is: 18.3% sy is high too, isn't it? If yes, how can i found what is causing this high system usage? Thanks in advance