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?
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.
I have inherited a horrible PHP backend that has bugs that make it query the DB hundreds of times (up to 600 for the sites index page). The client won't pay to do the neccesary backend rewrite, but I wanted to speed things up.
The queries are all small, on one smallish database, but the backend connects and selects a DB for every single query.
I wrote the following quick code to minimize this part of the traffic:
function DBConnectOnce($dbhost, $dbname, $dbuser, $dbpasswd) {
$GLOBALS['dbrecord_Debug'] = ($dbDebug == 'true'); // @TODO better way to handle this within the class?
if(isset($GLOBALS['DBRECORDCONFIGSTATUS'])&&$GLOBALS['DBRECORDCONFIGSTATUS']==$dbuser.$dbname.$dbhost){//check to see if the connectionon the proper type has been made.
$dblink=unserialize($GLOBALS['DBRECORDCONNECTION']);
} else {
$GLOBALS['DBRECORDCONFIGSTATUS']=$dbuser.$dbname.$dbhost;
$GLOBALS['DBRECORDCONNECTION']=serialize($dblink = mysql_connect($dbhost, $dbuser, $dbpasswd));
if($dblink === false) {
trigger_error("DBConnectOnce: Unable to connect to database server - ".mysql_error(), E_USER_ERROR);
}
if(!mysql_select_db($dbname)) {
trigger_error("DBConnectOnce: Unable to select database - $dbname at $dbhost", E_USER_ERROR);
}
}
return $dblink;
}
It uses globals to check what type of DB connection you've already made, and use it if it matches the type of DB connection you need to make.
![Interactive visualization of a graph in python [closed]](https://www.devze.com/res/2023/04-10/09/92d32fe8c0d22fb96bd6f6e8b7d1f457.gif)
加载中,请稍侯......
精彩评论