PHp Mysql OPtimization使用慢速日志查询

Question

hi ther i'm still working on mysql performance currently i start to check the -log-slow-query command on mysql server.

it returns me the next result:

/usr/sbin/mysqld, Version: 5.0.81-community-log (MySQL Community Edition (GPL)). started with:

Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument
110211  0:28:16       1 Connect     promls_sysdba@localhost on 
              1 Query       SET NAMES utf8
              1 Init DB     promls_box
              1 Query       select id , fullname from users where company_id = 7
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       select id , name from groups where company_id = 7 and context = "agency"
              1 Query       SELECT FOUND_ROWS() AS total_results
110211  0:28:26       1 Query       SET NAMES utf8
              1 Init DB     promls_box
              1 Query       SELECT  SQL_CALC_FOUND_ROWS property_id, property_type,property_name, bathrooms, bedrooms, for_sale, for_rent, for_vacational, units, 
                            city,estate,country, rent_price, sale_price, min_price,mid_price, hig_price, max_sale,min_sale, min_rent, max_rent, min_vrental,max_vrental,
                            negotiation,property_status,
                            category_es category, image, dev_logo, inventory,company_id,broker_id FROM property_listing  where property_status in('active','active-rented','active-sold')   group by parent   order by min_price asc, mid_price asc, hig_price asc, rent_price asc, sale_price asc  limit 10, 10
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       SET NAMES utf8
              1 Init DB     promls_box
              1 Query       select st.id estate_id,st.description estate from active_locations group by estate_id
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       select city_id , city  from active_locations where estate_id =272 group by city_id
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       SET NAMES utf8
              1 Init DB     promls_box
              1 Query       select f.id, f.name_es as name from families f inner join category_types ct on ct.category = f.id where type='category' group by f.id
              1 Query       SELECT FOUND_ROWS() AS total_results
              1 Query       select t.id, t.name_es as name from property_types t inner join category_types ct on ct.property_type = t.id where  ct.category =12
              1 Query       SELECT FOUND_ROWS() AS total_results
110211  0:28:56       2 Connect     root@localhost on 
              2 Query       select id_area , description from areas where parent_area = 2
              2 Quit       
110211  0:28:58       1 Quit       
110211  0:29:01       3 Connect     promls_sysdba@localhost on 
              3 Query       SET NAMES utf8
              3 Init DB     promls_box
              3 Query       select id , fullname from users where company_id = 7
              3 Query       SELECT FOUND_ROWS() AS total_results
              3 Query       select id , name from groups where company_id = 7 and context = "agency"
              3 Query       SELECT FOUND_ROWS() AS total_results
110211  0:29:43       4 Connect     viva_webjun@localhost on 
              4 Init DB     viva_blogen
              4 Query       SET NAMES utf8
              4 Query       select name, val from txp_prefs where prefs_id=1 AND user_name=''
              4 Query       select name, data from txp_lang where lang='en-gb' AND ( event='public' OR event='common')
              4 Query       select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order
              4 Query       select page, css from txp_section where name = 'default' limit 1
              4 Query       select unix_timestamp(val) from txp_prefs where `name` = 'lastmod' limit 1
              4 Query       select name from txp_section where in_rss != '1'
              4 Query       select *, unix_timestamp(Posted) as uPosted, unix_timestamp(LastMod) as uLastMod, unix_timestamp(Expires) as uExpires, ID as thisid from textpattern where Status = 4 and Section != 'about'  and Posted < now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') order by Posted desc limit 5
              4 Query       select RealName from txp_users where `name` = 'webjunkie01' limit 1
              4 Query       select unix_timestamp(Posted) from textpattern where Posted <= now() and Status >= 4 order by Posted desc limit 1
              4 Quit       
110211  0:29:44       5 Connect     viva_webjun@localhost on 
              5 Init DB     viva_blogen
              5 Query       SET NAMES utf8
              5 Query       select name, val from txp_prefs where prefs_id=1 AND user_name=''
              5 Query       select name, data from txp_lang where lang='en-gb' AND ( event='public' OR event='common')
              5 Query       select name, code, version from txp_plugin where status = 1 AND type IN (0,1) order by load_order
              5 Query       select page, css from txp_section where name = 'default' limit 1
              5 Query       select unix_timestamp(val) from txp_prefs where `name` = 'lastmod' limit 1
              5 Query       select name from txp_section where in_rss != '1'
              5 Query       select *, unix_timestamp(Posted) as uPosted, unix_timestamp(LastMod) as uLastMod, unix_timestamp(Expires) as uExpires, ID as thisid from textpattern where Status = 4 and Section != 'about'  and Posted < now() and (now() <= Expires or Expires = '0000-00-00 00:00:00') order by Posted desc limit 5
              5 Query       select RealName from txp_users where `name` = 'webjunkie01' limit 1
              5 Query       select unix_timestamp(Posted) from textpattern where Posted <= now() and Status >= 4 order by Posted desc limit 1
              5 Quit       
110211  0:30:02       6 Connect     root@localhost on 
              6 Query       show processlist
              6 Quit       
110211  0:32:28       3 Quit       
              7 Connect     promls_sysdba@localhost on 
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       select id , fullname from users where company_id = 7
              7 Query       SELECT FOUND_ROWS() AS total_results
              7 Query       select id , name from groups where company_id = 7 and context = "agency"
              7 Query       SELECT FOUND_ROWS() AS total_results
110211  0:33:02       7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       select * from message_listing where (company_id = 7 or agency_id =7 ) and ( broker_id =2 ) and readed !=1 limit 0,5
              7 Query       SELECT FOUND_ROWS() AS total_results
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       select * from message_listing  where message_type = 'networking' and (category_message ='all' or (category_message='city' and area_id = 588  ) or (category_message='estate' and area_id = 272 ) or (category_message='agency' and company_id = 7 ))  limit 0,5
              7 Query       SELECT FOUND_ROWS() AS total_results
              7 Query       SET NAMES utf8
              7 Init DB     promls_box
              7 Query       select SQL_CALC_FOUND_ROWS id , name, contact, email_contact, phone_contact, address, phone,
            fax, email, website, creation_date, last_modification, area_id,  area, status , logo, type, owner_id, users,
              creator, creator_id
    from companies_listing where  status = 'active' and type in( 'sponsor' )
              7 Query       SELECT FOUND_ROWS() AS total_results
110211  0:34:20       7 Quit       
110211  0:34:44       8 Connect     promls_sysdba@localhost on 
              8 Query       SET NAMES utf8
              8 Init DB     promls_box
              8 Query       select id , fullname from users where company_id = 7
              8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       select id , name from groups where company_id = 7 and context = "agency"
              8 Query       SELECT FOUND_ROWS() AS total_results
110211  0:35:01       9 Connect     root@localhost on 
              9 Query       show processlist
              9 Quit       
110211  0:35:16       8 Query       SET NAMES utf8
              8 Init DB     promls_box
              8 Query       SELECT  SQL_CALC_FOUND_ROWS property_id, property_type,property_name, bathrooms, bedrooms, for_sale, for_rent, for_vacational, units, 
                            city,estate,country, rent_price, sale_price, min_price,mid_price, hig_price, max_sale,min_sale, min_rent, max_rent, min_vrental,max_vrental,
                            negotiation,property_status,
                            category_es category, image, dev_logo, inventory,company_id,broker_id FROM property_listing  where property_status in('active','active-rented','active-sold')   group by parent   order by min_price asc, mid_price asc, hig_price asc, rent_price asc, sale_price asc  limit 0, 10
110211  0:35:17       8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       SET NAMES utf8
              8 Init DB     promls_box
              8 Query       select st.id estate_id,st.description estate from active_locations group by estate_id
              8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       select city_id , city  from active_locations where estate_id =272 group by city_id
              8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       SET NAMES utf8
              8 Init DB     promls_box
              8 Query       select f.id, f.name_es as name from families f inner join category_types ct on ct.category = f.id where type='category' group by f.id
              8 Query       SELECT FOUND_ROWS() AS total_results
              8 Query       select t.id, t.name_es as name from property_types t inner join category_types ct on ct.property_type = t.id where  ct.category =12
              8 Query       SELECT FOUND_ROWS() AS total_results
110211  0:35:43      10 Connect     promls_sysdba@localhost on 
             10 Query       SET NAMES utf8
             10 Init DB     promls_box
             10 Query       select id , fullname from users where company_id = 7
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       select id , name from groups where company_id = 7 and context = "agency"
             10 Query       SELECT FOUND_ROWS() AS total_results
              8 Quit       
110211  0:36:08      10 Query       SET NAMES utf8
             10 Init DB     promls_box
             10 Query       SELECT  SQL_CALC_FOUND_ROWS property_id, property_type,property_name, bathrooms, bedrooms, for_sale, for_rent, for_vacational, units, 
                            city,estate,country, rent_price, sale_price, min_price,mid_price, hig_price, max_sale,min_sale, min_rent, max_rent, min_vrental,max_vrental,
                            negotiation,property_status,
                            category_es category, image, dev_logo, inventory,company_id,broker_id FROM property_listing  where property_status in('active','active-rented','active-sold')   group by parent   order by min_price asc, mid_price asc, hig_price asc, rent_price asc, sale_price asc  limit 0, 10
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       SET NAMES utf8
             10 Init DB     promls_box
             10 Query       select st.id estate_id,st.description estate from active_locations group by estate_id
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       select city_id , city  from active_locations where estate_id =272 group by city_id
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       SET NAMES utf8
             10 Init DB     promls_box
             10 Query       select f.id, f.name_es as name from families f inner join category_types ct on ct.category = f.id where type='category' group by f.id
             10 Query       SELECT FOUND_ROWS() AS total_results
             10 Query       select t.id, t.name_es as name from property_types t inner join category_types ct on ct.property_type = t.id where  ct.category =12
             10 Query       SELECT FOUND_ROWS() AS total_results
110211  0:36:21      10 Quit       
110211  0:36:22      11 Connect     promls_sysdba@localhost on 
             11 Query       SET NAMES utf8
             11 Init DB     promls_box
             11 Query       select id , fullname from users where company_id = 7
             11 Query       SELECT FOUND_ROWS() AS total_results
             11 Query       select id , name from groups where company_id = 7 and context = "agency"
             11 Query       SELECT FOUND_ROWS() AS total_results

Looks like takes much time on connections from php to mysql, how could i fix that?

Answer 1

I'm finding your log to be more confusing than helpful; none of those queries look particularly complex, but then again, I don't know what your table structure looks like, how much server load you have, or how many records you have in your tables. If the queries themselves are actually running really slowly, you might need to look into whether or not your tables are properly indexed. If you google about mysql indexing you can find a ton of information, and if you really want to get into it, check out this ebook: http://sql-performance-explained.com/

I also noticed there's a few queries using ORDER BY and LIMIT statements together. In that case, you should look at the relationship those two command have together, and how you can optimize those types of queries. Check this site out for more information:

http://www.mysqlperformanceblog.com/2006/09/01/order-by-limit-performance-optimization/

But if it's not the queries, perhaps, it's as you suggest that MySQL is having a hard time creating a connection to the database. If you have a lot of users, it could be that you have too few available connections, and you need to increase the number of connections MySQL allows concurrently. Look at here for how to do that:

http://rackerhacker.com/2007/01/24/increase-mysql-connection-limit/

On the other you, might want to consider using persistent connection in your PHP application, if it's taking a long time for it to connect to the database.

http://php.net/manual/en/features.persistent-connections.php

These can reduce the amount of time PHP spends connecting to the database. Alternatively, if you are already using persistent connection, then it could be that there is an error in your PHP code that is creating a connection, starting a transaction, but then holding on to it, and hogging the connection, leaving no available connection for other users. Make sure you're using proper error handling when your PHP app is interfacing with the database.

Again, these are just some of my best guesses based on what you've shown. I hope it gets you going in the right direction.