php - Seemingly Random Hangups/Lags Loading Website -


we run os commerce powered web page on our own dedicated server. in april of last year, forced upgrade server. @ around point, started notice there seemingly random website outages our page not viewed computer , solution @ time hard restart on our server. happened month. problem has been solved, faced different challenge. our website slowing down seemingly @ random. page loads , other times takes 2 minutes load. there not appear pattern can figure out. website www.westerndepot.com. have tried looking our website health yslow , similar, queries have run show our website rates "a". appears waiting times fluctuating rather actual loading times. recent firebug diagnostic showed 23.9 second wait time 24.17 second load time.

the server has 64gb of ram , current max ram used shows @ 10gb. using sme server 9.0. server composed of dual wd caviar black 1tb drives mirrored using software raid under sme 9 833 gb free space remaining. products_to_categories table has 18375 entries 13923 products 2427 products inactive. there 385 total categories.

our mysql configuration file follows:

character-set-server=utf8 collation-server=utf8_general_ci key_buffer_size=2048m preload_buffer_size=512m query_cache_limit=64m query_cache_size=512m query_cache_type=1 query_prealloc_size=512m read_buffer_size=2m read_rd_buffer_size=4m sort_buffer_size=2m thread_cache_size=300 join_buffer_size=256k table_open_cache=512k tmp_table_size=256m max_heap_table_size=256m slow_query_log=1 max_connections=500 concurrent_insert=2 log_output=file

the following example of slow query log.

    # query_time: 12.277704  lock_time: 0.000193 rows_sent: 1  rows_examined: 39652497     set timestamp=1427835801;     select count(distinct p.products_id) total  products p left join manufacturers  m using(manufacturers_id) left join (select * (select products_id, specials_new_products_price , expires_date, status specials status = 1 order products_id, specials_new_products_price,  expires_date) t group products_id) s on p.products_id = s.products_id left join  products_to_notes ptn on ptn.products_id = p.products_id join products_description  pd on p.products_id = pd.products_id join (select products_id  products_to_categories (categories_id not in (728)) group products_id)   p2c on p.products_id = p2c.products_id p.products_status = '1';     # query_time: 11.654512  lock_time: 0.000367 rows_sent: 240  rows_examined: 36425129     set timestamp=1427835812;     select distinct m.manufacturers_name, p.products_model, p.products_image,  p.image_folder, p.image_display, p.products_quantity, p.hide_qty_over,  p.products_bundle, if(s.status, s.expires_date, null) expires_date,   m.manufacturers_id, p.products_id, pd.products_name, p.products_msrp,  p.products_price, p.products_tax_class_id, if(s.status,  s.specials_new_products_price, null) specials_new_products_price, if(s.status,  s.specials_new_products_price, p.products_price) final_price,  p.sold_in_bundle_only, p.products_type , pd.extra_value_id6 , pd.extra_value_id10  products p left join manufacturers m using(manufacturers_id) left join  (select * (select products_id, specials_new_products_price, expires_date,  status specials status = 1 order products_id,  specials_new_products_price, expires_date) t group products_id) s on  p.products_id = s.products_id left join products_to_notes ptn on ptn.products_id =  p.products_id join products_description pd on p.products_id = pd.products_id join  (select products_id products_to_categories (categories_id not in (728))  group products_id)  p2c on p.products_id = p2c.products_id  p.products_status = '1' order products_name  limit 2160, 240;

all slow queries come "advanced search".
site uses multiple sales per product contribution.

any thoughts? did miss simple? help, directions, thoughts, or contacts appreciated.

have seen results of mysql explain command? suppose there's lack of indexes in db tables.

see docs mode details.


Comments

Post a Comment

Popular posts from this blog

cakephp - simple blog with croogo -

i not problem, made simple blog in croogo, problem is not adding or modifying or delete, because arabic language ?? function add in controller `public function admin_add() { $this->set('title_for_layout', __('add part')); if (!empty($this->request->data)) { $this->part->create(); if ($this->part->save($this->request->data)) { $this->session->setflash(__('the part has been saved'), 'default', array('class' => 'success')); $this->redirect(array('action' => 'index'));//, $this->part->id)); } else { $this->session->setflash(__('the part not saved. please, try again.'), 'default', array('class' => 'error')); } } }` => model `class part extends appmodel { public $name = 'part'; var $belongst...
Read more

How to group boxplot outliers in gnuplot -

i have large set of data points. try plot them boxplot, of outliers exact same value , represented on line beside each other. found how set horizontal distance between outliers in gnuplot boxplot , doesn't much, apparently not possible. is possible group outliers together, print 1 point , print number in brackets beside indicate how many points there are? think make more readable in graph. for information, have 3 boxplots 1 x value , times 6 in 1 graph. using gnuplot 5 , played around pointsize, doesn't reduce distance anymore. hope can help! edit: set terminal pdf set output 'dat.pdf' file0 = 'dat1.dat' file1 = 'dat2.dat' file2 = 'dat3.dat' set pointsize 0.2 set notitle set xlabel 'x' set ylabel 'y' header = system('head -1 '.file0); n = words(header) set xtics ('' 1) set [i=1:n] xtics add (word(header, i) i) set style data boxplot plot file0 using (1-0.25):1:(0.2) boxplot lw 2 lc rgb '#8b0000...
Read more

bash - Performing variable substitution in a string -

i have string contains use of variable, , i'd substitute variable's value string. right best have is #!/bin/bash foo='$name; echo bar' name="the name" expanded="$(eval echo "$foo")" echo "$expanded" which has obvious defects: prints the name bar while i'd print the name; echo bar instead of eval can bash's regex matching bash's string replacement: foo='$name; echo bar' name="the name" [[ "$foo" =~ \$([[:alnum:]]+) ]] && s="${!bash_rematch[1]}" && expanded="${foo/\$${bash_rematch[1]}/$s}" echo "$expanded" name; echo bar
Read more