mke

Hejka, na wstepie powiem ze jestem swiezy wiec prosze o lopatologiczny dobor slow Mam vps, testuje jedna strone oparta na Joomli i komponencie ogloszeniowym. Wszystko dzialalo w miare ok dopoki nie wrzucilem do bazy wiekszej ilosci ogloszen. Uzycie CPU przez mysqld skacze momentami znacznie(patrz rezultat "top") kiedy proboje np wyswietlic prosta strone z kilkunastoma rezulatatami. Byc moze jest to wina zle napisanego komponentu, byc moze cos jest nie tak z baza (probowalem juz optymalizacji i naprawy wszystkich tabel w phpmyadmin), byc moze servera lub konfiguracji. rezulatat polecenia "top" (przy niemal zerowym ruchu, tylko ja) postanowilem zapisywac w logach wolne zapytania (powyzej 1 sek) oto wynik mysqldumpslow ktorego do konca nie rozumiem, tzn nie wiem jakie wnioski mam z tego wyciagnac, zastanawiam sie czy np Rows_sent: 49 Rows_examined: 161357 to cos normalnego w sensie liczbowym. [root@vps ~]# mysqldumpslow /var/log/wolne_zapytania.log Reading mysql slow query log from /var/log/wolne_zapytania.log Count: 34 Time=2.18s (74s) Lock=0.00s (0s) Rows=49.0 (1666), uk146[uk146]@localhost SELECT cat.title, cat.id, (SELECT count(*) FROM jos_gbl_ads as a WHERE a.category_id = cat.id AND a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND (a.country_id=N)) AS adCount FROM jos_gbl_categories as cat WHERE published=N AND cat.access<=N Count: 3 Time=2.00s (6s) Lock=0.00s (0s) Rows=3291.0 (9873), uk146[uk146]@localhost SELECT a.*,concat(a.id,'S',a.alias) as id,c.title as country,cat.title as category,r.title as region,u.email as uemail,u.username,u.id as uid,(select image from jos_gbl_ads_images where published='S' and ad_id=a.id order by ordering asc limit N) as image,(select extension from jos_gbl_ads_images where published='S' and ad_id=a.id order by ordering asc limit N) as extension from jos_gbl_ads as a LEFT JOIN jos_gbl_countries as c on c.id=a.country_id LEFT JOIN jos_gbl_categories as cat on cat.id=a.category_id LEFT JOIN jos_gbl_regions as r on r.id=a.region_id LEFT JOIN jos_users as u on u.id=a.user_id WHERE a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND a.country_id in (N) AND a.title RLIKE 'S' AND cat.access<=N AND cat.published=N GROUP BY a.id ORDER BY a.created_date desc ,a.ordering a moj my.cnf [mysqld] #moje zmiany long_query_time=1 log_slow_queries=/var/log/wolne_zapytania.log set-variable=local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment # these lines and adjust the connectstring as needed. #ndbcluster #ndb-connectstring="nodeid=4;host=localhost:1186" skip-bdb set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = innodb_thread_concurrency=2 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-bdb set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K "/etc/my.cnf" 48L, 1340C 6,0-1 Top

EXPLAIN SELECT cat.title, cat.id, ( SELECT count( * ) FROM jos_gbl_ads AS a WHERE a.category_id = cat.id AND a.status =1 AND ( a.expiry_date > NOW( ) || a.expiry_date = 'S' ) AND ( a.country_id =77 ) ) AS adCount FROM jos_gbl_categories AS cat WHERE published =1 AND cat.access <=0 po dodaniu powyzszych indeksow mam id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY cat ALL NULL NULL NULL NULL 49 Using where 2 DEPENDENT SUBQUERY a ref idx_cat_id_country_id,idx_country_id idx_cat_id_country_id 10 uk146.cat.id,const 127 Using where zauwazanla zmiana poprawy dzialania, zuzycie CPU przez mysql juz nie skacze powyzej 100% byc moze jest to czesciowo zwiazane z wlaczeniem cachu moj obecny my.cnf w oparciu o rady z mysqltuner [mysqld] long_query_time=1 log_slow_queries=/var/log/wolne_zapytania.log set-variable=local-infile=0 datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 # To allow mysqld to connect to a MySQL Cluster management daemon, uncomment # these lines and adjust the connectstring as needed. #ndbcluster #ndb-connectstring="nodeid=4;host=localhost:1186" skip-bdb #skip-innodb query_cache_size=8M query_cache_limit=4M thread_cache_size=4 join_buffer_size=1M key_buffer_size=24M table_cache=64 set-variable = innodb_buffer_pool_size=2M set-variable = innodb_additional_mem_pool_size=500K set-variable = innodb_log_buffer_size=500K set-variable = innodb_thread_concurrency=2 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid efekt? Prawdopodobnie dzieki dodaniu indexow w moim pliku do ktergo zapisywane sa wolne zapytania nie widze zadnych wolnych zapytan powyzej 1 sekundy Mysle ze jeszcze mozna zmienic troche, zoptymalizowac zapytania, ale glowny cel jest juz blisko. Teraz moja kolej skontaktowac sie z Tworcami tego komponentu i zasugerowac co maja poprawic. Dla potomnosci, moze komus sie przyda, powiem ze rozpatrywalismy komponent o nazwie Listbingo. Aha niezaleznie jak dalej potoczy sie ten temat dziekuje wszystkim za pomoc.

EXPLAIN SELECT cat.title, cat.id, ( SELECT count( * ) FROM jos_gbl_ads AS a WHERE a.category_id = cat.id AND a.status =1 AND ( a.expiry_date > NOW( ) || a.expiry_date = 'S' ) AND ( a.country_id =77 ) ) AS adCount FROM jos_gbl_categories AS cat WHERE published =1 AND cat.access <=0 id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY cat ALL NULL NULL NULL NULL 49 Using where 2 DEPENDENT SUBQUERY a ALL NULL NULL NULL NULL 3292 Using where

wynik mysqltuner >> MySQLTuner 1.0.1 - Major Hayden <major@mhtx.net> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.0.79-log [OK] Operating on 32-bit architecture with less than 2GB RAM -------- Storage Engine Statistics ------------------------------------------- [--] Status: -Archive -BDB -Federated +InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 100M (Tables: 1232) [--] Data in InnoDB tables: 2M (Tables: 153) [--] Data in MEMORY tables: 0B (Tables: 1) [!!] Total fragmented tables: 97 -------- Performance Metrics ------------------------------------------------- [--] Up for: 20h 17m 55s (1M q [24.856 qps], 17K conn, TX: 961M, RX: 302M) [--] Reads / Writes: 97% / 3% [--] Total buffers: 27.0M global + 2.7M per thread (100 max threads) [OK] Maximum possible memory usage: 295.7M (57% of installed RAM) [OK] Slow queries: 0% (55/1M) [OK] Highest usage of available connections: 8% (8/100) [OK] Key buffer size / total MyISAM indexes: 8.0M/28.8M [OK] Key buffer hit rate: 99.7% (41M cached / 133K reads) [!!] Query cache is disabled [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 492K sorts) [!!] Joins performed without indexes: 5185 [OK] Temporary tables created on disk: 20% (4K on disk / 24K total) [!!] Thread cache is disabled [!!] Table cache hit rate: 0% (64 open / 21K opened) [OK] Open file limit used: 11% (121/1K) [OK] Table locks acquired immediately: 99% (1M immediate / 1M locks) [!!] InnoDB data size / buffer pool: 2.5M/2.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate Adjust your join queries to always utilize indexes Set thread_cache_size to 4 as a starting value Increase table_cache gradually to avoid file descriptor limits Variables to adjust: query_cache_size (>= 8M) join_buffer_size (> 128.0K, or always use indexes with joins) thread_cache_size (start at 4) table_cache (> 64) innodb_buffer_pool_size (>= 2M) a teraz wynik mysqlreport Use of uninitialized value in multiplication (*) at ./mysqlreport line 829. Use of uninitialized value in formline at ./mysqlreport line 1227. MySQL 5.0.79-log uptime 0 20:43:30 Sat Aug 14 19:07:10 2010 __ Key _________________________________________________________________ Buffer used 2.45M of 8.00M %Used: 30.61 Current 2.87M %Usage: 35.94 Write hit 90.89% Read hit 99.67% __ Questions ___________________________________________________________ Total 1.85M 24.8/s DMS 1.76M 23.6/s %Total: 95.15 Com_ 81.14k 1.1/s 4.38 COM_QUIT 17.88k 0.2/s 0.97 -Unknown 9.21k 0.1/s 0.50 Slow 1 s 56 0.0/s 0.00 %DMS: 0.00 Log: ON DMS 1.76M 23.6/s 95.15 SELECT 1.71M 22.9/s 92.46 97.18 UPDATE 20.93k 0.3/s 1.13 1.19 DELETE 12.87k 0.2/s 0.70 0.73 REPLACE 10.11k 0.1/s 0.55 0.57 INSERT 5.84k 0.1/s 0.32 0.33 Com_ 81.14k 1.1/s 4.38 set_option 43.16k 0.6/s 2.33 change_db 23.97k 0.3/s 1.29 admin_comma 9.59k 0.1/s 0.52 __ SELECT and Sort _____________________________________________________ Scan 316.73k 4.2/s %SELECT: 18.51 Range 170.16k 2.3/s 9.94 Full join 5.33k 0.1/s 0.31 Range check 0 0/s 0.00 Full rng join 0 0/s 0.00 Sort scan 451.61k 6.1/s Sort range 43.64k 0.6/s Sort mrg pass 0 0/s __ Table Locks _________________________________________________________ Waited 79 0.0/s %Total: 0.00 Immediate 1.77M 23.7/s __ Tables ______________________________________________________________ Open 64 of 64 %Cache: 100.00 Opened 26.31k 0.4/s __ Connections _________________________________________________________ Max used 8 of 100 %Max: 8.00 Total 17.88k 0.2/s __ Created Temp ________________________________________________________ Disk table 5.00k 0.1/s Table 19.98k 0.3/s Size: 32.0M File 5 0.0/s __ Threads _____________________________________________________________ Running 1 of 1 Cached 0 of 0 %Hit: 0.01 Created 17.88k 0.2/s Slow 0 0/s __ Aborted _____________________________________________________________ Clients 5 0.0/s Connects 88 0.0/s __ Bytes _______________________________________________________________ Sent 981.93M 13.2k/s Received 309.51M 4.1k/s __ InnoDB Buffer Pool __________________________________________________ Usage 2.00M of 2.00M %Used: 100.00 Read hit 97.70% Pages Free 0 %Total: 0.00 Data 127 99.22 %Drty: 0.00 Misc 1 0.78 Latched 0.00 Reads 70.51k 0.9/s From file 1.62k 0.0/s 2.30 Ahead Rnd 0 0/s Ahead Sql 0 0/s Writes 5.65k 0.1/s Flushes 159 0.0/s Wait Free 0 0/s __ InnoDB Lock _________________________________________________________ Waits 0 0/s Current 0 Time acquiring Total 0 ms Average 0 ms Max 0 ms __ InnoDB Data, Pages, Rows ____________________________________________ Data Reads 1.63k 0.0/s Writes 1.23k 0.0/s fsync 1.13k 0.0/s Pending Reads 0 Writes 0 fsync 0 Pages Created 18 0.0/s Read 1.62k 0.0/s Written 159 0.0/s Rows Deleted 28 0.0/s Inserted 45 0.0/s Read 19.05k 0.3/s Updated 1.01k 0.0/s Jak dobrze rozumiem wg tego przewodnika http://hackmysql.com/mysqlreportguide 1) wyglada ze poziom "key buffer" jest ok 2) w sekcji "Questions" brakuje mi "QC Hits", rezulat niewlaczonego cache 3) widac ze moj sever jest "SELECT heavy" 4) wysoki poziom "scan" czyli czesto przeszukiwana jest cala tabela, "full join" tez tak sobie, nie wiem co oznacza duza wartosc "range" 5) brakuje "Query Cache Report" 6) Locks Report - ok 7) tables report - zwiekszyc table_cache system ? 8) Connections Report - wyglada ok 9) Created Temp Report - wyglada ok 10) InnoDB Buffer Pool Report - buffer pool space - uzycie 100% niedobrze Buffer Pool Read hit - 97,7%, za malo? 11) InnoDB Lock Report - ok 12) inoDB Data, Pages, Rows Report - ok? Mozna probowac optymalizowac config, jakie ustawienia i wartosci radzicie? Problem z tymi wynikami polega na tym ze dane pochodza z zapytan z innych stron na tym samym serwerze ktore chodza wydaje mi sie w miare przyzwoicie. Zeby ugryzc ta testowa o ktora mi chodzi trzeba jednak pewnie przeanalizowac EXPLAIN dla wybranych zapytan.

Wlasnie przeczytalem ten rozdzial i mysle ze rozumiem dlaczego powinienem go miec, tylko jak zrobic te indexy? Bardzo dziwne bo np. polecenie SELECT cat.title, cat.id, (SELECT count(*) FROM jos_gbl_ads as a WHERE a.category_id = cat.id AND a.status=N AND (a.expiry_date > NOW() || a.expiry_date='S') AND (a.country_id=N)) AS adCount FROM jos_gbl_categories as cat WHERE published=N AND cat.access<=N korzysta z dwoch tabel jos_gbl_ads as i jos_gbl_categories w ktorych lacznie jest nie wiecej niz 3500 rekordow

Witam szukam osoby ktora skonfigurowalaby mi modul suphp na moim VPS. Po aktualizacji php i myslq nie dziala mi suphp. Mam tam trzy strony wiec trzeba sie upewnic ze kazdy vhost dziala poprawnie i ze nie ma problemu z uprawnieniami. W zasadzie ogloszenie jest kontynuacja tego tematu http://www.webhostingtalk.pl/index.php?sho...75&hl=suphp Oferty i pytania prosze na mke@tlen.pl

Witam, jestem calkowicie nowy w temacie, zlece za pieniadze rozwiazanie mojego problemu, sprawa jest raczej pilna, mam VPS'a (centOS 4.2) za granica i mam problem z uprawnieniami który pojawia sie przy probie postawienia strony opartej na Joomla A lot of people have problems with different PHP softwares like Joomla, different Gallery softwares, especially where need to upload pictures. Why? Because now in a new settings, PHP will work as apache:apache user and any files uploaded via a web interface will get this user rights. Any files uploaded via FTP clients, will get the domain ownership. SO, if you for example want to get all picture files added via WEB INTERFACE, you cannot download or overwrite via FTP because the file rights. Also on install a lot of people need to setup folders to 777 rights to uload in it. Chodzi o mi o to zeby rozwiazac problem z uprawnieniami za pomoca modulu suphp, chcialbym aby modul dzialal dla wszystkich maszyn wirtualnych http://www.suphp.org/Documentation-Module-...llation.en.html Mozliwosci rozwiazania tego zawieraja ponizsze linki http://blog.stuartherbert.com/php/2008/01/...-shared-server/ lub w punkcie 18 http://www.web-hosting-control-panel-addon...cated_Server/6/ sam modul jest juz nawet zainstalowany ale nie moge dojsc jak to skonfigurowac rpm -ql mod_suphp-0.6.2-1.el4.rf /etc/httpd/conf.d/suphp.conf /etc/suphp.conf /usr/lib/httpd/modules/mod_suphp.so /usr/sbin/suphp /usr/share/doc/mod_suphp-0.6.2 /usr/share/doc/mod_suphp-0.6.2/AUTHORS /usr/share/doc/mod_suphp-0.6.2/COPYING /usr/share/doc/mod_suphp-0.6.2/ChangeLog /usr/share/doc/mod_suphp-0.6.2/NEWS /usr/share/doc/mod_suphp-0.6.2/README /usr/share/doc/mod_suphp-0.6.2/doc /usr/share/doc/mod_suphp-0.6.2/doc/CONFIG /usr/share/doc/mod_suphp-0.6.2/doc/INSTALL /usr/share/doc/mod_suphp-0.6.2/doc/LICENSE /usr/share/doc/mod_suphp-0.6.2/doc/README /usr/share/doc/mod_suphp-0.6.2/doc/apache /usr/share/doc/mod_suphp-0.6.2/doc/apache/CONFIG /usr/share/doc/mod_suphp-0.6.2/doc/apache/INSTALL /usr/share/doc/mod_suphp-0.6.2/doc/apache/README /usr/share/doc/mod_suphp-0.6.2/doc/suphp.conf-example

Jaki inne modul lub rozwiazanie mozna zastosowac na ten problem?