Re: Req. suitable .cnf file for Server used by 2000 users daily
Hi, I run the mysqltuner at my server as below and i got error like below. [r...@127 /]# ./mysqltuner.pl MySQLTuner 1.0.1 - Major Hayden ma...@mhtx.net Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [!!] Attempted to use login credentials, but they were invalid. On some other server this script is running absolutely fine without any change in mysqltuner.pl. Please guide me how can i run this script Thanks Regards Jeetendra Ranjan - Original Message - From: Bruce Ferrell bferr...@baywinds.org To: jeetendra.ran...@sampatti.com Cc: mysql@lists.mysql.com Sent: Thursday, October 15, 2009 12:20 PM Subject: Re: Req. suitable .cnf file for Server used by 2000 users daily Have a look at mysqltuner. It reads the stats from a running mysql instances and makes suggestions for what can be changed http://blog.mysqltuner.com/ Gavin Towey wrote: Hi, This script might help with some tuning suggestions, run it after you have some production traffic running against your database. https://launchpad.net/mysql-tuning-primer Also you should enable the slow query log, so you can capture queries to be optimized: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Regards, Gavin Towey -Original Message- From: jeetendra.ran...@sampatti.com [mailto:jeetendra.ran...@sampatti.com] Sent: Wednesday, October 14, 2009 3:21 AM To: mysql@lists.mysql.com Subject: Req. suitable .cnf file for Server used by 2000 users daily Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * Operating System : Red Hat Fedora Core 8 Processor : Intel Core 2 Quad - 2.83 GHz, RAM : 4 GB Total Disk Space : 600 GB (300 GB usable) RAID : RAID1 Disk Drive(s) : 300 GB (Drive #1), 300 GB (Drive #2) Bandwidth Quota : 500 GB Firewall : PIX 501 Version : 5.0.81-community-log Version_comment : MySQL Community Edition (GPL) Version Compile Machine : i686 Version Compile OS : pc-linux-gnu my.cnf details *** [mysqld] 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 key_buffer = 16M key_buffer_size=4M sort_buffer_size=2M query_cache_size=64M log-bin log_queries_not_using_indexes=1 long_query_time=1 log_slow_queries=slowQry.log join_buffer_size=4M max_connections=150 max_allowed_packet = 32M table_cache = 256 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M thread_stack=5M thread_cache_size=128M connect_timeout=30 query_cache_limit=32M log-error # Comment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 4M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks in advance Regards Jeetendra Ranjan The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message.
Prepared statements and printing Queries
I know this is more a PHP question but no one on the list at news.php.net is responding. So I was hoping someone here might know. If I am using the mysqli extension and prepared statements, after I execute bind_param, is there a away to print the actual query that gets sent to the server? I have looked through the PHP docs and can't seem to find it. Of course I could be blind. Chris W -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Req. suitable .cnf file for Server used by 2000 users daily
this error indicates either root doesn't have sufficient privilege or the password entered was bad. try running it this way: ./mysqltuner.pl --user root --pass password Jeetendra Ranjan wrote: Hi, I run the mysqltuner at my server as below and i got error like below. [r...@127 /]# ./mysqltuner.pl MySQLTuner 1.0.1 - Major Hayden ma...@mhtx.net Bug reports, feature requests, and downloads at http://mysqltuner.com/ Run with '--help' for additional options and output filtering Please enter your MySQL administrative login: root Please enter your MySQL administrative password: [!!] Attempted to use login credentials, but they were invalid. On some other server this script is running absolutely fine without any change in mysqltuner.pl. Please guide me how can i run this script Thanks Regards Jeetendra Ranjan - Original Message - From: Bruce Ferrell bferr...@baywinds.org To: jeetendra.ran...@sampatti.com Cc: mysql@lists.mysql.com Sent: Thursday, October 15, 2009 12:20 PM Subject: Re: Req. suitable .cnf file for Server used by 2000 users daily Have a look at mysqltuner. It reads the stats from a running mysql instances and makes suggestions for what can be changed http://blog.mysqltuner.com/ Gavin Towey wrote: Hi, This script might help with some tuning suggestions, run it after you have some production traffic running against your database. https://launchpad.net/mysql-tuning-primer Also you should enable the slow query log, so you can capture queries to be optimized: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Regards, Gavin Towey -Original Message- From: jeetendra.ran...@sampatti.com [mailto:jeetendra.ran...@sampatti.com] Sent: Wednesday, October 14, 2009 3:21 AM To: mysql@lists.mysql.com Subject: Req. suitable .cnf file for Server used by 2000 users daily Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * Operating System : Red Hat Fedora Core 8 Processor : Intel Core 2 Quad - 2.83 GHz, RAM : 4 GB Total Disk Space : 600 GB (300 GB usable) RAID : RAID1 Disk Drive(s) : 300 GB (Drive #1), 300 GB (Drive #2) Bandwidth Quota : 500 GB Firewall : PIX 501 Version : 5.0.81-community-log Version_comment : MySQL Community Edition (GPL) Version Compile Machine : i686 Version Compile OS : pc-linux-gnu my.cnf details *** [mysqld] 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 key_buffer = 16M key_buffer_size=4M sort_buffer_size=2M query_cache_size=64M log-bin log_queries_not_using_indexes=1 long_query_time=1 log_slow_queries=slowQry.log join_buffer_size=4M max_connections=150 max_allowed_packet = 32M table_cache = 256 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M thread_stack=5M thread_cache_size=128M connect_timeout=30 query_cache_limit=32M log-error # Comment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 4M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks in advance Regards Jeetendra Ranjan The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
VFP to MySQL Query Optimization
I feel like there should be a better way to do this... So I'm hoping someone will be able to advise. We have contacts that belong to organizations. We also have a document tracker. Holding over from VFP you can have up to six organization ids on the document and up to six contact ids. Right now the select to see if a contact has documents looks like this... SELECT * FROM Contacts WHERE (id IN (SELECT contid1 FROM documents) OR id IN (SELECT contid2 FROM documents) OR id IN (SELECT contid3 FROM documents) OR id IN (SELECT contid4 FROM documents) OR id IN (SELECT contid5 FROM documents) OR id IN (SELECT contid6 FROM documents) OR orgid IN (SELECT orgid1 FROM documents) OR orgid IN (SELECT orgid2 FROM documents) OR orgid IN (SELECT orgid3 FROM documents) OR orgid IN (SELECT orgid4 FROM documents) OR orgid IN (SELECT orgid5 FROM documents) OR orgid IN (SELECT orgid6 FROM documents)) Which is UGLY... and I feel like there should be a better way (I know I could break that out into a many-many relationship via a third linking table but I'm not 'able' to do that now.) The only change I can think of is to union the two halves of the select but I'm not sure if that would be better... (IE id in (select contid1 from documents union select contid2 from documents) etc) Any advice or is this the best I'm going to get until I can reorganize the underlying structure? Thanks! Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Distinct max() and separate unique value
I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1481 | 127 | | 300 | 127 | |1503 | 122 | |1494 | 122 | |1470 | 122 | |1468 | 122 | |1205 | 122 | |1062 | 122 | | 316 | 122 | | 306 | 122 | | 228 | 122 | | 222 | 122 | | 216 | 122 | | 211 | 122 | | 184 | 122 | | 155 | 122 | | 149 | 122 | | 134 | 122 | | 128 | 122 | | 124 | 122 | | 119 | 122 | | 113 | 122 | | 109 | 122 | | 105 | 122 | | 93 | 122 | | 91 | 122 | | 87 | 122 | +-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct max() and separate unique value
I would try: select max(object_id), term_taxonomy_id group by term_taxonomy_id order by term_taxonomy_id; max(column) returns a single value so distinct is not needed. The group by and order by should only have columns thaqt are displayed and that are not aggregate columns. - Original Message - From: Eric Anderson e...@macandbumble.com To: mysql@lists.mysql.com Sent: Tuesday, October 20, 2009 3:42 PM Subject: Distinct max() and separate unique value I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1481 | 127 | | 300 | 127 | |1503 | 122 | |1494 | 122 | |1470 | 122 | |1468 | 122 | |1205 | 122 | |1062 | 122 | | 316 | 122 | | 306 | 122 | | 228 | 122 | | 222 | 122 | | 216 | 122 | | 211 | 122 | | 184 | 122 | | 155 | 122 | | 149 | 122 | | 134 | 122 | | 128 | 122 | | 124 | 122 | | 119 | 122 | | 113 | 122 | | 109 | 122 | | 105 | 122 | | 93 | 122 | | 91 | 122 | | 87 | 122 | +-+--+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=my...@dawiz.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Distinct max() and separate unique value
On Tue, 20 Oct 2009, DaWiz wrote: I would try: select max(object_id), term_taxonomy_id group by term_taxonomy_id order by term_taxonomy_id; max(column) returns a single value so distinct is not needed. The group by and order by should only have columns thaqt are displayed and that are not aggregate columns. You end up with the same object_id. select max(object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id order by term_taxonomy_id; ++--+ | max(object_id) | term_taxonomy_id | ++--+ | 1503 | 122 | | 1503 | 127 | ++--+ I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? select max(distinct object_id), term_taxonomy_id from wp_term_relationships where term_taxonomy_id IN (122,127) group by term_taxonomy_id, object_id order by term_taxonomy_id desc, object_id desc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Low performance due high network latency - batching ?
Hola List /. We are facing a preformance issue with a desktop application which connects remotly to a Mysql / DB ( ping times 300-800ms). We do not have time to invest in modifications to create a 2-tier ... Web application The initial plan was to implement a Client cache or a local DB (mysql slave or XML files). After reviwing the code and the sql logs I figured out there are a lot of insert/updates and replaces within loops. Sometimes there are more than 200 inserts statments coming for a loop. What I did was to create a long string with all statments separated with semicolons (batching) and then just send them all in just one statment. This reduced the major application use case time in 300% I am planning to write some code that can keep all modification statments in memory until a select is needed so then send first the commands in memory and then the select Any of you have some experience using this approach /? Any drawback you can see /? Thanks for your comments. -r.
Re: Distinct max() and separate unique value
- Original Message - From: Eric Anderson ke...@on-e.com To: mysql@lists.mysql.com Sent: Tuesday, October 20, 2009 4:05 PM Subject: Re: Distinct max() and separate unique value I'm trying to formulate a query on a Wordpress database that will give me the highest 'object_id' with the highest 'term_taxonomy_id', something like: +-+--+ | max(distinct object_id) | term_taxonomy_id | +-+--+ |1503 | 127 | |1494 | 122 | +-+--+ But I just can't seem to get there? I confess I did not understand what you are trying to do. If what you actually want is the highest 'term_taxonomy_id' for each distinct objhect_id then the query would be: select object_id, max(term_taxonomy_id) where term_taxonomy_id IN (122,127)group by object_id order by object_id desc; This query will not take into consideration term_taxonomy_Id values other than 122 and 127, it also will not return object_id's without a term_taxonomy_Id value of 122 or 127. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
