Re: does anyone else have problems sending mails to this list ?
Am 22.03.2016 um 02:05 schrieb Jan Steinman: please refrain from answering when you have no clue how DNS blacklists are working and what about others are talking Well, excuse me! Someone put on their bossy pants this morning! A simple “That’s not what’s going on here” would have been much nicer deserved by what when one is answering to a over days ongoing thread without read what's topic? signature.asc Description: OpenPGP digital signature
Re: does anyone else have problems sending mails to this list ?
> please refrain from answering when you have no clue how DNS blacklists are > working and what about others are talking Well, excuse me! Someone put on their bossy pants this morning! A simple “That’s not what’s going on here” would have been much nicer. J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does anyone else have problems sending mails to this list ?
Am 20.03.2016 um 20:22 schrieb Reindl Harald: Am 20.03.2016 um 20:16 schrieb Jan Steinman: From: Reindl HaraldDate: 17 March 2016 at 06:27:22 PDT Am 17.03.2016 um 14:06 schrieb Lentes, Bernd: i still can't send my question to the ML. Our outgoing Mailer has a new IP which is not listed (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#), our domain is not listed (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#), my E-Mail include neither any link nor an attachment, it's formatted as plain-text but i still get it back: at least not terrible good senderscore.com LISTED 127.0.4.63 Uhm… the 127 Class A network is the “super local network,” used for processes on the same physical machine. You need to look at other Received: headers than the ones that start with 127 please refrain from answering when you have no clue how DNS blacklists are working and what about others are talking - thank you! here is your homework for today: https://en.wikipedia.org/wiki/DNSBL BTW: one of my jobs is to build up spamfilters for hundrets of users including maintain own local DNSBL/DNSWL services for scoring if the wiki page is to long for you: https://www.spamhaus.org/zen/ Return CodesData Source Contains 127.0.0.2 Direct UBE sources, spam operations & spam services 127.0.0.3 Direct snowshoe spam sources detected via automation 127.0.0.4-7 CBL (3rd party exploits such as proxies, trojans, etc.) 127.0.0.10-11 End-user Non-MTA IP addresses set by ISP outbound mail policy signature.asc Description: OpenPGP digital signature
Re: does anyone else have problems sending mails to this list ?
Am 20.03.2016 um 20:16 schrieb Jan Steinman: From: Reindl HaraldDate: 17 March 2016 at 06:27:22 PDT Am 17.03.2016 um 14:06 schrieb Lentes, Bernd: i still can't send my question to the ML. Our outgoing Mailer has a new IP which is not listed (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#), our domain is not listed (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#), my E-Mail include neither any link nor an attachment, it's formatted as plain-text but i still get it back: at least not terrible good senderscore.com LISTED 127.0.4.63 Uhm… the 127 Class A network is the “super local network,” used for processes on the same physical machine. You need to look at other Received: headers than the ones that start with 127 please refrain from answering when you have no clue how DNS blacklists are working and what about others are talking - thank you! here is your homework for today: https://en.wikipedia.org/wiki/DNSBL BTW: one of my jobs is to build up spamfilters for hundrets of users including maintain own local DNSBL/DNSWL services for scoring signature.asc Description: OpenPGP digital signature
Re: does anyone else have problems sending mails to this list ?
> From: Reindl Harald> Date: 17 March 2016 at 06:27:22 PDT > > Am 17.03.2016 um 14:06 schrieb Lentes, Bernd: >> i still can't send my question to the ML. Our outgoing Mailer has a new IP >> which is not listed >> (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#), >> our domain is not listed >> (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#), >> my E-Mail include neither any link nor an attachment, it's formatted as >> plain-text but i still get it back: > > at least not terrible good > senderscore.com LISTED127.0.4.63 Uhm… the 127 Class A network is the “super local network,” used for processes on the same physical machine. You need to look at other Received: headers than the ones that start with 127. Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does anyone else have problems sending mails to this list ?
Hi, i still can't send my question to the ML. Our outgoing Mailer has a new IP which is not listed (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#), our domain is not listed (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#), my E-Mail include neither any link nor an attachment, it's formatted as plain-text but i still get it back: "This is the mail system at host mtaextp1.scidom.de. I'm sorry to have to inform you that your message could not be delivered to one or more recipients. It's attached below. For further assistance, please send mail to postmaster. If you do so, please include this problem report. You can delete your own text from the attached returned message. The mail system: host lists-mx.mysql.com[137.254.60.71] said: 552 spam score exceeded threshold (#5.6.1) (in reply to end of DATA command)" Can anyone points out the error ? Thanks. Bernd -- Bernd Lentes Systemadministration institute of developmental genetics Gebäude 35.34 - Raum 208 HelmholtzZentrum München bernd.len...@helmholtz-muenchen.de phone: +49 (0)89 3187 1241 fax: +49 (0)89 3187 2294 Wer Visionen hat soll zum Hausarzt gehen Helmut Schmidt - Am 9. Mrz 2016 um 12:56 schrieb Reindl Harald h.rei...@thelounge.net: > Am 09.03.2016 um 11:44 schrieb Lentes, Bernd: >> i tried yesterday several times to send an e-Mail to this list. E-Mail is >> formatted as plain-text, has no links included and no attachment but is >> declined as spam. This is what is bounced: >> >> : host lists-mx.mysql.com[137.254.60.71] said: 552 >> spam >> score exceeded threshold (#5.6.1) (in reply to end of DATA command) >> >> Any ideas? > > surely: > > spam.dnsbl.sorbs.net LISTED 127.0.0.6 > bl.spamcannibal.org LISTED 127.0.0.2 > > Received-SPF: none (helmholtz-muenchen.de: No applicable sender policy > available) receiver=amysql-list-wsv01.oracle.com; identity=mailfrom; > envelope-from="bernd.len...@helmholtz-muenchen.de"; > helo=mtaextp1.scidom.de; client-ip=146.107.3.105 > > PTR: mtaextp1.scidom.de > Country: DE (Germany) > ISP: Helmholtz Zentrum Muenchen Helmholtz Zentrum Muenchen Deutsches Forschungszentrum fuer Gesundheit und Umwelt (GmbH) Ingolstaedter Landstr. 1 85764 Neuherberg www.helmholtz-muenchen.de Aufsichtsratsvorsitzende: MinDir'in Baerbel Brumme-Bothe Geschaeftsfuehrer: Prof. Dr. Guenther Wess, Dr. Alfons Enhsen, Renate Schlusen (komm.) Registergericht: Amtsgericht Muenchen HRB 6466 USt-IdNr: DE 129521671 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: does anyone else have problems sending mails to this list ?
Am 17.03.2016 um 14:06 schrieb Lentes, Bernd: i still can't send my question to the ML. Our outgoing Mailer has a new IP which is not listed (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3a146.107.103.20=toolpage#), our domain is not listed (http://mxtoolbox.com/SuperTool.aspx?action=blacklist%3ahelmholtz-muenchen.de=toolpage#), my E-Mail include neither any link nor an attachment, it's formatted as plain-text but i still get it back: at least not terrible good senderscore.com LISTED 127.0.4.63 would be 1.0 SpamAssasin points on our setup, not much but not a good sign, hence we use 50 DNSBL/DNSWL with different scores and it's worth to catch most spam while prevent false positives "This is the mail system at host mtaextp1.scidom.de. I'm sorry to have to inform you that your message could not be delivered to one or more recipients. It's attached below. For further assistance, please send mail to postmaster. If you do so, please include this problem report. You can delete your own text from the attached returned message. The mail system: host lists-mx.mysql.com[137.254.60.71] said: 552 spam score exceeded threshold (#5.6.1) (in reply to end of DATA command)" Can anyone points out the error ? not without the full message try to send me the same mail (subject, content, attachments whatever) and i can bounce you back a SpamAssassin report signature.asc Description: OpenPGP digital signature
Re: does anyone else have problems sending mails to this list ?
Am 09.03.2016 um 11:44 schrieb Lentes, Bernd: i tried yesterday several times to send an e-Mail to this list. E-Mail is formatted as plain-text, has no links included and no attachment but is declined as spam. This is what is bounced:: host lists-mx.mysql.com[137.254.60.71] said: 552 spam score exceeded threshold (#5.6.1) (in reply to end of DATA command) Any ideas? surely: spam.dnsbl.sorbs.netLISTED 127.0.0.6 bl.spamcannibal.org LISTED 127.0.0.2 Received-SPF: none (helmholtz-muenchen.de: No applicable sender policy available) receiver=amysql-list-wsv01.oracle.com; identity=mailfrom; envelope-from="bernd.len...@helmholtz-muenchen.de"; helo=mtaextp1.scidom.de; client-ip=146.107.3.105 PTR: mtaextp1.scidom.de Country: DE (Germany) ISP: Helmholtz Zentrum Muenchen signature.asc Description: OpenPGP digital signature
Re: does anyone else have problems sending mails to this list ?
Bernd, >: host lists-mx.mysql.com[137.254.60.71] said: 552 spam > score exceeded threshold (#5.6.1) (in reply to end of DATA command) > > Any ideas ? Whatever provider you are using to send mail has been blacklisted? It happens with some ISP who are not diligent enough to remove the spammers from their networks, legitimate customers get penalized. Best regards, Olivier -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
does anyone else have problems sending mails to this list ?
Hi, i tried yesterday several times to send an e-Mail to this list. E-Mail is formatted as plain-text, has no links included and no attachment but is declined as spam. This is what is bounced:: host lists-mx.mysql.com[137.254.60.71] said: 552 spam score exceeded threshold (#5.6.1) (in reply to end of DATA command) Any ideas ? Thanks. Bernd -- Bernd Lentes Systemadministration institute of developmental genetics Gebäude 35.34 - Raum 208 HelmholtzZentrum München bernd.len...@helmholtz-muenchen.de phone: +49 (0)89 3187 1241 fax: +49 (0)89 3187 2294 Wer Visionen hat soll zum Hausarzt gehen Helmut Schmidt Helmholtz Zentrum München -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
FW: MySQL Problems
Hi, We are running on opensuse that defaults to a mariadb instead of mysql install, so we were running icinga 1.9.2 / idoutils 1.9.2 on a opensuse ppc system, with mariadb 5.5.33. This generated a lot of errors in de database (with restarts): InnoDB: Assertion failure in thread 70366933971360 in file buf0buf.ic line 1277 Some googling showed this could be a problem in Percona XtraDB, which is as INNODB replacement in MariaDB. Next step: install MySQL Server instead of MariaDB. We were able to install MySQL-5.5.6 in PPC, and the number of MySQL crashes went down to 1 in 2 days now (instead of 8 crashes a night). For now this is acceptable, and we started with icinga-web on a separate system. We now have the following setup: LPAR 1 (OpenSuSE 13.1) - Icinga /idoutils (1.9.2) LPAR 2 (OpenSuSE 13.1) - MySQL 5.5.6 X86 (OpenSuSE 12.2) - Icinga-Web 1.9.2 When we log in in icinga-web we get a screen showing we don't have enough permissions, and no servers show up. After debugging, googling a.s.o. I found that if we install the databases on mariaDB 5.5.33 (local on X86), we get all the hosts services. Installing icinga-web db local, and icinga db remote (LPAR2), we can see the hosts, but not the services. I tried the following statement in the different DB's (X86 DB is a restore of the LPAR2 dump): LPAR2: MySQL [nagios] select count(*) from icinga_hosts join icinga_objects on icinga_hosts.host_object_id=icinga_objects.object_id join icinga_hoststatus on icinga_hoststatus.host_object_id=icinga_objects.object_id; +--+ | count(*) | +--+ |0 | +--+ 1 row in set (0.00 sec) X86: mysql select count(*) from icinga_hosts join icinga_objects on icinga_hosts.host_object_id=icinga_objects.object_id join icinga_hoststatus on icinga_hoststatus.host_object_id=icinga_objects.object_id; +--+ | count(*) | +--+ | 153 | +--+ 1 row in set (0.00 sec) Both statements are executed as root user in mysql (so all grants should be OK) and are on the same database. The only difference I see is the mysql / mariadb version, it looks like the syntax in the newer mysql has been changed. Anyone can shed a light on this ;) Hans Scheffers AIX / Linux Systeembeheer
RE: Concurrent read performance problems
Please provide SHOW CREATE TABLE and SHOW TABLE STATUS for each table. It smells like there is an inconsistency in the datatype of facts.accounts.id and what it is JOINing to. Also provide the full SELECT. How much RAM do you have? -Original Message- From: Johan De Meersman [mailto:vegiv...@tuxera.be] Sent: Sunday, August 11, 2013 2:16 PM To: Brad Heller Cc: Johnny Withers; MySQL General List Subject: Re: Concurrent read performance problems Good to hear. A word of warning, though: make sure you don't have more connections allocating those buffers than your machine can handle memory- wise, or you'll start swapping and performance will REALLY go down the drain. A query/index based solution would still be preferred. Could you for instance materialize that subselect and periodically refresh it? Other tricks may also be available. Brad Heller b...@cloudability.com wrote: Johan, your suggestion to tweak max_heap_table_size and tmp_table_size fixed the issue. Bumping them both to 512MB got our performance back on-par. I came up with a way to avoid the contention using a complex set of temp tables, but performance was abysmal. By reverting to the more straight-forward query with the subselect as well as tweaking the max_healp_table_size and tmp_table_size I saw no resource contention causing slowdowns, as well as a 12x performance boost. Thanks for your help! *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 1:32 PM, Johan De Meersman vegiv...@tuxera.bewrote: True, which is why I said I suspected file-based sort :-) At one million rows, that seems to be an accurate guess, too. Still on the phone, though, and in bed. I'll read the thread better tomorrow, but you might get some benefit from cutting out the subselect if that's possible. If you have plenty of memory, have a look at the max_temp_table_size and max_heap_table variables, too; those decide when the sort goes to disk. Johnny Withers joh...@pixelated.net wrote: Just because it says filrsort doesn't mean it'll create a file on disk. Table schema and full query would be helpful here too http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort -mean-in-mysql/ On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com wrote: Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like there is a lot of filesort goin' on here. Also note that I'm only using the first two fields of the covering index (intentionally). +--+-++---+ -- ++-+ -- +-+ -- + | id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | +--+-++---+ -- ++-+ -- +-+ -- + |1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL| NULL | 1004685 | Using temporary; Using filesort | |2 | DERIVED | accounts | range | PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 | NULL | 3 | Using where; Using index; Using temporary; Using filesort | |2 | DERIVED | facts | ref | covering | covering | 4 | facts.accounts.id| 334895 | Using where | +--+-++---+ -- ++-+ -- +-+ -- + *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.be wrote: On my phone now, but it smells of file-based sorting, making disk access the bottleneck. Can you provide the explain? Brad Heller b...@cloudability.com wrote: Hey list, first time posting here so apologies if this is the wrong forum for this but I'm really out of options on how to solve this problem! *Short version
Re: Concurrent read performance problems
On my phone now, but it smells of file-based sorting, making disk access the bottleneck. Can you provide the explain? Brad Heller b...@cloudability.com wrote: Hey list, first time posting here so apologies if this is the wrong forum for this but I'm really out of options on how to solve this problem! *Short version:* 1. High concurrent reads, performing the same well-indexed query type to the same two tables. 2. No additional traffic at all--just reads from these two tables. No writes anywhere. 3. Very fast (sub-second) when server is only servicing one request at a time. 4. Very slow (30s+) when server is servicing 2-3 requests at a time. Questions: 1. What resource is under contention for servicing read-only queries if you have a large buffer pool and a plenty-big thread cache? 2. What parameters can I tune to increase concurrent reads to these two tables? *Long version:* I've got a MySQL server that has only about 50 connections open to it at any given time. It basically only has one OLAP query type being ran against it that amounts to something like this: SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate) FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...; These queries are well indexed and run very well individually--sub-second, usually even faster. When I run a few of these queries simultaneously (2-3 on my laptop, 6-7 on our production boxes) performance grinds to a halt: Consistently about 30 seconds to service a query. Ideally, I'd like to be able to run 50+ of these queries concurrently. I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on both machines. I've tried tweaking the following my.cnf parameters to be higher: thread_concurrency = 20 thread_cache_size = 340 innodb_buffer_pool_size=2G A few other my.cnf parameters that I have set: innodb_file_format=Barracuda innodb_file_format_max=Barracuda innodb_file_per_table=1 skip-external-locking innodb_log_files_in_group=2 innodb_log_file_size=2000M max_allowed_packet=64M Thanks in advance, Brad Heller -- Sent from Kaiten Mail. Please excuse my brevity.
Re: Concurrent read performance problems
Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like there is a lot of filesort goin' on here. Also note that I'm only using the first two fields of the covering index (intentionally). +--+-++---+++-+---+-+---+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | +--+-++---+++-+---+-+---+ |1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL| NULL | 1004685 | Using temporary; Using filesort | |2 | DERIVED | accounts | range | PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 | NULL | 3 | Using where; Using index; Using temporary; Using filesort | |2 | DERIVED | facts | ref | covering | covering | 4 | facts.accounts.id| 334895 | Using where | +--+-++---+++-+---+-+---+ *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.bewrote: On my phone now, but it smells of file-based sorting, making disk access the bottleneck. Can you provide the explain? Brad Heller b...@cloudability.com wrote: Hey list, first time posting here so apologies if this is the wrong forum for this but I'm really out of options on how to solve this problem! *Short version:* 1. High concurrent reads, performing the same well-indexed query type to the same two tables. 2. No additional traffic at all--just reads from these two tables. No writes anywhere. 3. Very fast (sub-second) when server is only servicing one request at a time. 4. Very slow (30s+) when server is servicing 2-3 requests at a time. Questions: 1. What resource is under contention for servicing read-only queries if you have a large buffer pool and a plenty-big thread cache? 2. What parameters can I tune to increase concurrent reads to these two tables? *Long version:* I've got a MySQL server that has only about 50 connections open to it at any given time. It basically only has one OLAP q uery type being ran against it that amounts to something like this: SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate) FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...; These queries are well indexed and run very well individually--sub-second, usually even faster. When I run a few of these queries simultaneously (2-3 on my laptop, 6-7 on our production boxes) performance grinds to a halt: Consistently about 30 seconds to service a query. Ideally, I'd like to be able to run 50+ of these queries concurrently. I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on both machines. I've tried tweaking the following my.cnf parameters to be higher: thread_concurrency = 20 thread_cache_size = 340 innodb_buffer_pool_size=2G A few other my.cnf parameters that I have set: innodb_file_format=Barracuda innodb_file_format_max=Barracuda innodb_file_per_table=1 skip-external-locking innodb_log_files_in_group=2 innodb_log_file_size=2000M max_allowed_packet=64M Thanks in advance, Brad Heller -- Sent from Kaiten Mail. Please excuse my brevity.
Re: Concurrent read performance problems
Just because it says filrsort doesn't mean it'll create a file on disk. Table schema and full query would be helpful here too http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/ On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com wrote: Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like there is a lot of filesort goin' on here. Also note that I'm only using the first two fields of the covering index (intentionally). +--+-++---+++-+---+-+---+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | +--+-++---+++-+---+-+---+ |1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL| NULL | 1004685 | Using temporary; Using filesort | |2 | DERIVED | accounts | range | PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 | NULL | 3 | Using where; Using index; Using temporary; Using filesort | |2 | DERIVED | facts | ref | covering | covering | 4 | facts.accounts.id| 334895 | Using where | +--+-++---+++-+---+-+---+ *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.be wrote: On my phone now, but it smells of file-based sorting, making disk access the bottleneck. Can you provide the explain? Brad Heller b...@cloudability.com wrote: Hey list, first time posting here so apologies if this is the wrong forum for this but I'm really out of options on how to solve this problem! *Short version:* 1. High concurrent reads, performing the same well-indexed query type to the same two tables. 2. No additional traffic at all--just reads from these two tables. No writes anywhere. 3. Very fast (sub-second) when server is only servicing one request at a time. 4. Very slow (30s+) when server is servicing 2-3 requests at a time. Questions: 1. What resource is under contention for servicing read-only queries if you have a large buffer pool and a plenty-big thread cache? 2. What parameters can I tune to increase concurrent reads to these two tables? *Long version:* I've got a MySQL server that has only about 50 connections open to it at any given time. It basically only has one OLAP q uery type being ran against it that amounts to something like this: SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate) FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...; These queries are well indexed and run very well individually--sub-second, usually even faster. When I run a few of these queries simultaneously (2-3 on my laptop, 6-7 on our production boxes) performance grinds to a halt: Consistently about 30 seconds to service a query. Ideally, I'd like to be able to run 50+ of these queries concurrently. I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on both machines. I've tried tweaking the following my.cnf parameters to be higher: thread_concurrency = 20 thread_cache_size = 340 innodb_buffer_pool_size=2G A few other my.cnf parameters that I have set: innodb_file_format=Barracuda innodb_file_format_max=Barracuda innodb_file_per_table=1 skip-external-locking innodb_log_files_in_group=2 innodb_log_file_size=2000M max_allowed_packet=64M Thanks in advance, Brad Heller -- Sent from Kaiten Mail. Please excuse my brevity.
Re: Concurrent read performance problems
True, which is why I said I suspected file-based sort :-) At one million rows, that seems to be an accurate guess, too. Still on the phone, though, and in bed. I'll read the thread better tomorrow, but you might get some benefit from cutting out the subselect if that's possible. If you have plenty of memory, have a look at the max_temp_table_size and max_heap_table variables, too; those decide when the sort goes to disk. Johnny Withers joh...@pixelated.net wrote: Just because it says filrsort doesn't mean it'll create a file on disk. Table schema and full query would be helpful here too http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/ On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com wrote: Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like there is a lot of filesort goin' on here. Also note that I'm only using the first two fields of the covering index (intentionally). +--+-++---+++-+---+-+---+ | id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | +--+-++---+++-+---+-+---+ |1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL| NULL | 1004685 | Using temporary; Using filesort | |2 | DERIVED | accounts | range | PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 | NULL | 3 | Using where; Using index; Using temporary; Using filesort | |2 | DERIVED | facts | ref | covering | covering | 4 | facts.accounts.id| 334895 | Using where | +--+-++---+++-+---+-+---+ *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.be wrote: On my phone now, but it smells of file-based sorting, making disk access the bottleneck. Can you provide the explain? Brad Heller b...@cloudability.com wrote: Hey list, first time posting here so apologies if this is the wrong forum for this but I'm really out of options on how to solve this problem! *Short version:* 1. High concurrent reads, performing the same well-indexed query type to the same two tables. 2. No additional traffic at all--just reads from these two tables. No writes anywhere. 3. Very fast (sub-second) when server is only servicing one request at a time. 4. Very slow (30s+) when server is servicing 2-3 requests at a time. Questions: 1. What resource is under contention for servicing read-only queries if you have a large buffer pool and a plenty-big thread cache? 2. What parameters can I tune to increase concurrent reads to these two tables? *Long version:* I've got a MySQL server that has only about 50 connections open to it at any given time. It basically only has one OLAP q uery type being ran against it that amounts to something like this: SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate) FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...; These queries are well indexed and run very well individually--sub-second, usually even faster. When I run a few of these queries simultaneously (2-3 on my laptop, 6-7 on our production boxes) performance grinds to a halt: Consistently about 30 seconds to service a query. Ideally, I'd like to be able to run 50+ of these queries concurrently. I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on both machines. I've tried tweaking the following my.cnf parameters to be higher: thread_concurrency = 20 thread_cache_size = 340 innodb_buffer_pool_size=2G A few other my.cnf parameters that I have set: innodb_file_format=Barracuda innodb_file_format_max=Barracuda
Re: Concurrent read performance problems
Johan, your suggestion to tweak max_heap_table_size and tmp_table_size fixed the issue. Bumping them both to 512MB got our performance back on-par. I came up with a way to avoid the contention using a complex set of temp tables, but performance was abysmal. By reverting to the more straight-forward query with the subselect as well as tweaking the max_healp_table_size and tmp_table_size I saw no resource contention causing slowdowns, as well as a 12x performance boost. Thanks for your help! *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 1:32 PM, Johan De Meersman vegiv...@tuxera.bewrote: True, which is why I said I suspected file-based sort :-) At one million rows, that seems to be an accurate guess, too. Still on the phone, though, and in bed. I'll read the thread better tomorrow, but you might get some benefit from cutting out the subselect if that's possible. If you have plenty of memory, have a look at the max_temp_table_size and max_heap_table variables, too; those decide when the sort goes to disk. Johnny Withers joh...@pixelated.net wrote: Just because it says filrsort doesn't mean it'll create a file on disk. Table schema and full query would be helpful here too http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/ On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com wrote: Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like there is a lot of filesort goin' on here. Also note that I'm only using the first two fields of the covering index (intentionally). +--+-++---+ -- ++-+ -- +-+ -- + | id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | +--+-++---+ -- ++-+ -- +-+ -- + |1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL| NULL | 1004685 | Using temporary; Using filesort | |2 | DERIVED | accounts | range | PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 | NULL | 3 | Using where; Using index; Using temporary; Using filesort | |2 | DERIVED | facts | ref | covering | covering | 4 | facts.accounts.id| 334895 | Using where | +--+-++---+ -- ++-+ -- +-+ -- + *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.be wrote: On my phone now, but it smells of file-based sorting, making disk access the bottleneck. Can you provide the explain? Brad Heller b...@cloudability.com wrote: Hey list, first time posting here so apologies if this is the wrong forum for this but I'm really out of options on how to solve this problem! *Short version:* 1. High concurrent reads, performing the same well-indexed query type to the same two tables. 2. No additional traffic at all--just reads from these two tables. No writes anywhere. 3. Very fast (sub-second) when server is only servicing one request at a time. 4. Very slow (30s+) when server is servicing 2-3 requests at a time. Questions: 1. What resource is under contention for servicing read-only queries if you have a large buffer pool and a plenty-big thread cache? 2. What parameters can I tune to increase concurrent reads to these two tables? *Long version:* I've got a MySQL server that has only about 50 connections open to it at any given time. It basically only has one OLAP q uery type being ran against it that amounts to something like this: S ELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate) FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...; These queries are well indexed and run very well individually--sub-second, usually
Re: Concurrent read performance problems
Good to hear. A word of warning, though: make sure you don't have more connections allocating those buffers than your machine can handle memory-wise, or you'll start swapping and performance will REALLY go down the drain. A query/index based solution would still be preferred. Could you for instance materialize that subselect and periodically refresh it? Other tricks may also be available. Brad Heller b...@cloudability.com wrote: Johan, your suggestion to tweak max_heap_table_size and tmp_table_size fixed the issue. Bumping them both to 512MB got our performance back on-par. I came up with a way to avoid the contention using a complex set of temp tables, but performance was abysmal. By reverting to the more straight-forward query with the subselect as well as tweaking the max_healp_table_size and tmp_table_size I saw no resource contention causing slowdowns, as well as a 12x performance boost. Thanks for your help! *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 1:32 PM, Johan De Meersman vegiv...@tuxera.bewrote: True, which is why I said I suspected file-based sort :-) At one million rows, that seems to be an accurate guess, too. Still on the phone, though, and in bed. I'll read the thread better tomorrow, but you might get some benefit from cutting out the subselect if that's possible. If you have plenty of memory, have a look at the max_temp_table_size and max_heap_table variables, too; those decide when the sort goes to disk. Johnny Withers joh...@pixelated.net wrote: Just because it says filrsort doesn't mean it'll create a file on disk. Table schema and full query would be helpful here too http://www.mysqlperformanceblog.com/2009/03/05/what-does-using-filesort-mean-in-mysql/ On Aug 11, 2013 1:28 PM, Brad Heller b...@cloudability.com wrote: Yes sorry, here's the explain. It was taken from MariaDB 5.5.32. Looks like there is a lot of filesort goin' on here. Also note that I'm only using the first two fields of the covering index (intentionally). +--+-++---+ -- ++-+ -- +-+ -- + | id | select_type | table | type | possible_keys | key| key_len | ref | rows| Extra | +--+-++---+ -- ++-+ -- +-+ -- + |1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL| NULL | 1004685 | Using temporary; Using filesort | |2 | DERIVED | accounts | range | PRIMARY,unique_account_identifiers | unique_account_identifiers | 257 | NULL | 3 | Using where; Using index; Using temporary; Using filesort | |2 | DERIVED | facts | ref | covering | covering | 4 | facts.accounts.id| 334895 | Using where | +--+-++---+ -- ++-+ -- +-+ -- + *Brad Heller *| Engineering Lead | Cloudability.com | 541-231-1514 | Skype: brad.heller | @bradhe http://www.twitter.com/bradhe | @cloudabilityhttp://www.twitter.com/cloudability On Sun, Aug 11, 2013 at 8:45 AM, Johan De Meersman vegiv...@tuxera.be wrote: On my phone now, but it smells of file-based sorting, making disk access the bottleneck. Can you provide the explain? Brad Heller b...@cloudability.com wrote: Hey list, first time posting here so apologies if this is the wrong forum for this but I'm really out of options on how to solve this problem! *Short version:* 1. High concurrent reads, performing the same well-indexed query type to the same two tables. 2. No additional traffic at all--just reads from these two tables. No writes anywhere. 3. Very fast (sub-second) when server is only servicing one request at a time. 4. Very slow (30s+) when server is servicing 2-3 requests at a time. Questions: 1. What resource is under contention for servicing read-only queries if you have a large buffer pool and a plenty-big thread cache? 2. What parameters can I tune to increase concurrent reads to these two tables? *Long version:*
Concurrent read performance problems
Hey list, first time posting here so apologies if this is the wrong forum for this but I'm really out of options on how to solve this problem! *Short version:* 1. High concurrent reads, performing the same well-indexed query type to the same two tables. 2. No additional traffic at all--just reads from these two tables. No writes anywhere. 3. Very fast (sub-second) when server is only servicing one request at a time. 4. Very slow (30s+) when server is servicing 2-3 requests at a time. Questions: 1. What resource is under contention for servicing read-only queries if you have a large buffer pool and a plenty-big thread cache? 2. What parameters can I tune to increase concurrent reads to these two tables? *Long version:* I've got a MySQL server that has only about 50 connections open to it at any given time. It basically only has one OLAP query type being ran against it that amounts to something like this: SELECT (3 fields with 1 count) FROM (SELECT (3 fields with 1 aggregate) FROM table INNER JOIN ... WHERE ... GROUP BY ...) dt GROUP BY ...; These queries are well indexed and run very well individually--sub-second, usually even faster. When I run a few of these queries simultaneously (2-3 on my laptop, 6-7 on our production boxes) performance grinds to a halt: Consistently about 30 seconds to service a query. Ideally, I'd like to be able to run 50+ of these queries concurrently. I've tried MySQL 5.5.27 and MySQL 5.6.13 and get the same results on both machines. I've tried tweaking the following my.cnf parameters to be higher: thread_concurrency = 20 thread_cache_size = 340 innodb_buffer_pool_size=2G A few other my.cnf parameters that I have set: innodb_file_format=Barracuda innodb_file_format_max=Barracuda innodb_file_per_table=1 skip-external-locking innodb_log_files_in_group=2 innodb_log_file_size=2000M max_allowed_packet=64M Thanks in advance, Brad Heller
Re: problems with relationships created by mysql
oops..sorryproblem seems to be with base...so i meant to post it in base mailing list On Mon, May 13, 2013 at 9:37 AM, rounak jain rounak.m...@gmail.com wrote: I have connected MySql to Base. I created some relationships in MySql. They are properly visible in the Relationships chart in Base. See image. http://imgur.com/qtM8fVN,jwvJpen Other than that, they are non-existent. For example, while I try to create a sub-form, I see that the option to create subform via existing relationship is disabled. See image. http://imgur.com/qtM8fVN,jwvJpen#1 Kindly guide me.
Problems compiling 5.6.10 under CygWIN for Windows
I'm using CygWIN to compile MySQL 5.6.10 for Windows. Using the source code (Generic Linux (Architecture Independent), Compressed TAR Archive) Version 5.6.10. I have no problems compiling (Generic Linux (Architecture Independent), Compressed TAR Archive) Version 5.5.29 source code. This is the output. [ 27%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/fts/fts0que.cc.o /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/fts/fts0que.cc: In function `dberr_t fts_query_phrase_search(fts_query_t*, const fts_string_t*)': /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/fts/fts0que.cc:2359: warning: 'token' might be used uninitialized in this function [ 27%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/fts/fts0sql.cc.o [ 27%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/fts/fts0tlex.cc.o [ 27%] Building CXX object storage/innobase/CMakeFiles/innobase.dir/handler/ha_innodb.cc.o In file included from /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.h:647, from /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:95: /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic: In function `int fts_write_object_id(ib_id_t, char*)': /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38: error: expected `)' before PRIx64 /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38: warning: conversion lacks type at end of format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38: warning: too many arguments for format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38: warning: conversion lacks type at end of format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:38: warning: too many arguments for format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic: In function `ulint fts_read_object_id(ib_id_t*, const char*)': /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51: error: expected `)' before PRIx64 /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51: warning: conversion lacks type at end of format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51: warning: too many arguments for format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51: warning: conversion lacks type at end of format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/include/fts0priv.ic:51: warning: too many arguments for format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc: In function `int innobase_close_connection(handlerton*, THD*)': /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:3733: error: expected `)' before PRIu64 /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:3735: warning: spurious trailing `%' in format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:3735: warning: too many arguments for format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc: In function `dberr_t calc_row_difference(upd_t*, uchar*, uchar*, TABLE*, uchar*, ulint, row_prebuilt_t*, THD*)': /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6855: error: expected `)' before PRIu64 /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6857: warning: spurious trailing `%' in format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6857: warning: too many arguments for format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6867: error: expected `)' before PRIu64 /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6873: warning: spurious trailing `%' in format /cygdrive/e/cygwin/mysql-5.6.10/storage/innobase/handler/ha_innodb.cc:6873: warning: too many arguments for format storage/innobase/CMakeFiles/innobase.dir/build.make:1023: recipe for target `storage/innobase/CMakeFiles/innobase.dir/handler/ha_innodb.cc.o' failed make[2]: *** [storage/innobase/CMakeFiles/innobase.dir/handler/ha_innodb.cc.o] Error 1 CMakeFiles/Makefile2:755: recipe for target `storage/innobase/CMakeFiles/innobase.dir/all' failed make[1]: *** [storage/innobase/CMakeFiles/innobase.dir/all] Error 2 Makefile:146: recipe for target `all' failed make: *** [all] Error 2 Best regards, Michael
problems with mysql and php after system software upgrade
During a recent system software upgrade (CentOS) on the front-end of our computing cluster, the mysql area was upgraded without our realizing that it was going to be. Now, php and mysql do not communicate well with one another. The version of mysql on the system after the upgrade is 5.1.61. The mysql originally used was 5.0.45. PhP was not upgraded and sits at version 5.3.3. Execution of a simple php script on our web server ?php DEFINE('DB_USER','root'); DEFINE('DB_PASSWORD', 'XXX'); DEFINE('DB_HOST', 'localhost'); DEFINE('DB_NAME', 'mysql'); $dbc = mysql_connect(DB_HOST,DB_USER,DB_PASSWORD); if (!$dbc) { die('Connect Error (' . mysql_errno() . ') ' . mysql_error()); $db_selected = mysql_select_db(DB_NAME); if (!$db_selected) { die ('Can\'t use' . DB_NAME . ': ' . mysql_error()); } echo 'Success... Your connection to the Database is working'; } ? gives the result - Connect Error (1045) Access denied for user 'root'@'localhost' (using password: YES) While I can access mysql in a simple fashion [mysql --user=root --pass= gives me a mysql prompt], a straightforward mysqladmin command (mysqladmin version) gives a similar error to the Connect Error above: mysqladmin version mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Do I have a msyql/php version mismatch? I have tried a number of potential remedies I found on line but have been unsuccessful to this point. Guidance from experts would be much appreciated. Wayne Leslie
RE: problems with INNODB tables
Some mixture. Perhaps 35% of RAM for buffer_pool and 10% for key_buffer. It depends on which needs more caching. Note: The key_buffer does not need to be bigger than the total of all MyISAM indexes (Index_length in SHOW TABLE STATUS, or size of .MYI files). The buffer_pool does not need to be bigger than the total of data+index for InnoDB files. -Original Message- From: Malka Cymbalista [mailto:malki.cymbali...@weizmann.ac.il] Sent: Wednesday, April 25, 2012 3:15 AM To: Rick James; Andrés Tello Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: RE: problems with INNODB tables Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memory where it tells you to do one thing if using MYIASM tables and another if using INNODB tables. We are using both. Any suggestions? Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.il 08-9343036 -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Monday, April 23, 2012 9:42 PM To: Andrés Tello; Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: RE: problems with INNODB tables Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036 -- MySQL General
RE: problems with INNODB tables
Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memory where it tells you to do one thing if using MYIASM tables and another if using INNODB tables. We are using both. Any suggestions? Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.il 08-9343036 -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Monday, April 23, 2012 9:42 PM To: Andrés Tello; Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: RE: problems with INNODB tables Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: problems with INNODB tables
switch to innodb... and use one_file_per_table I use both, but I try to use myisam for cataloges. Innodb and myisam are truly different engines, they do things completely different, for example, with myisam you have parameters to configure the size of the memory for the indexes, and several others, meanwhile most of innodb performase is bound to innodb buffer pools, and with the newerst mysql version, yo u can have several innodb buffer pools lowering your mutex wait a lot... Also you can switch from myisam to innodb quickly, for that type of tasks, I do a mysqldump with tab formatted texts because it gives 2 files per table, 1 file with the sql query to create de database and other, tab delimited file with all the data of that table, to be used with mysqlimport For a properly recommendation, we would need to know much more about the system using the database, some statistics... What is the database used for? On Wed, Apr 25, 2012 at 5:14 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: Thanks for your answer. I read http://mysql.rjweb.org/doc.php/memorywhere it tells you to do one thing if using MYIASM tables and another if using INNODB tables. We are using both. Any suggestions? Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.il 08-9343036 -Original Message- From: Rick James [mailto:rja...@yahoo-inc.com] Sent: Monday, April 23, 2012 9:42 PM To: Andrés Tello; Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: RE: problems with INNODB tables Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has
Re: problems with INNODB tables
Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036
RE: problems with INNODB tables
Check your memory usage according to http://mysql.rjweb.org/doc.php/memory -Original Message- From: Andrés Tello [mailto:mr.crip...@gmail.com] Sent: Monday, April 23, 2012 9:00 AM To: Malka Cymbalista Cc: mysql@lists.mysql.com; Shlomit Afgin; Ronen Hayun Subject: Re: problems with INNODB tables Weird, I use a lot Innodb, and no issue, I even kill bravely the mysql process with pkill -9 -f mysql Y suppose the way drupal is being programed. PHP open and closes database connections each time a webpage with db access is issued. When a php exceution ends and the apache webserver have fullfilled the http request, again, php memory is freed and connections closed... UNLESS:.. you are using a mem cached db connection, wich I doubt it since drupal doens't requiere one, or using persistent connections, again, I doubt it, because persistante database connections aren't recommended to innodb tables... Mysql server by default can handles 100 conections, if you get to thata limit you need to fine tune the number of connections allowed. show full processlist can give you a better idea of what is going on, connections with the sleep status, are open connections with no currently no transacctions... I never use script based stop, I always use mysqladmin -u root -p -h localhost shutdown which properly tells mysql to flush tables and terminate. I can almost bet that you are using Ubuntu... ubuntu had given me sometimes very hard times because of the edgy code they use to use, ext4 last version, and so on... what can you tell us about that? How much amount of memory you have? How much concurrent apache/php users you have? Can you provide more cuantitive data please? Hardware, php version, distro, kernel... Cheers... To start, 100 process is quite a lot, something isn't fine. Each time On Mon, Apr 23, 2012 at 9:10 AM, Malka Cymbalista malki.cymbali...@weizmann.ac.il wrote: We are running MySQL version 5.0.45 on a Linux machine. Over the past few months we have been having several problems: 1. Our mysql processes have increased the memory used from about .3% per process to 8% per process 2. We sometimes can have over 100 processes running which brings the machine to its knees and we have to stop and start MySQL in order to kill all the processes. We think that maybe the processes are not finishing normally and are just hanging around. 3. The machine is a web server and in the last few months we are moving over to drupal 7 to build our sites and Drupal 7 requires INNODB tables. Sometimes, when we restart MySQL using the commands /etc/init.d/mysql stop and /etc/init.d/mysql start our sites that were built in drupal 7 do not come up. In order for the INNODB tables to work, we have to stop mysql, rename the ibdata1 file, copy it back to ibdata1 and then restart mysql. Otherwise the INNODB tables are not accessable. In the past all our tables were MYIASM. Our problems started as we started using more and more INNODB tables. Is there anything special that has to be done to configure MySQL when using INNODB tables? We clearly have a problem but we have no idea where to start looking. Our error logs don't show anything. If anyone has any suggestions, we will be happy to hear them. We are considering hiring a consultant who is an expert in MySQL. We are in Israel and we are open to suggestions. Thanks for any help. Malki Cymbalista Webmaster, Weizmann Institute of Science malki.cymbali...@weizmann.ac.ilmailto:malki.cymbali...@weizmann.ac.il 08-9343036 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Problems Connecting with SSL
Hi Tim, all! Sorry, but I have to start with a negative remark: Why, oh why, don't you mention the version number you are using? I hope it is some roughly current 5.1, but I'd rather like you to tell it. Tim Gustafson wrote: I have three mySQL server, all of which are SSL-enable and all of which are generally accessible from remote clients over SSL. However, one of my FreeBSD mySQL clients is having problems connecting to any of the servers. Running mySQL under truss reports the following: Looking at this output tells something went terribly wrong: file descriptor 2 is mishandled. -- clock_gettime(13,{1298593067.0 }) = 0 (0x0) open(/dev/urandom,O_RDONLY,00) = 4 (0x4) read(4,\a\^V\M-)\^C\M-L{`\M^O\M^@\M-v...,32) = 32 (0x20) sendto(2,\^V\^C\^A\0Y\^A\0\0U\^C\^A\M-m...,94,0x0,NULL,0x0) = 94 (0x5e) recvfrom(2,\^V,1,0x2,NULL,0x0) = 1 (0x1) ioctl(2,FIONREAD,0xd784) = 0 (0x0) recvfrom(2,\^V\^C\^A\0J\^B\0\0F\^C\^AMf\M-u...,1819,0x0,NULL,0x0) = 1819 (0x71b) In the above lines, socket calls 'sendto()' and 'recvfrom()' are applied to FD 2. Whatever the transmitted data may be, this should not be done: FD 2 is standard error, so it is a file and no socket, and receiving from it doesn't make any sense at all. As the problem happens only for one of your combinations, I suspect that for some unknown reason FD 2 was closed in that run, and then this number got returned when a socket was to be created. Again, this should not happen: The only reason to close FD 2 should be to re-open standard error to some other destination, which is unlikely for the client to do. [[...]] shutdown(2,SHUT_RDWR) = 0 (0x0) close(2) = 0 (0x0) So FD 2 is closed now. fstat(1,{ mode=crw--w ,inode=113,size=0,blksize=4096 }) = 0 (0x0) ioctl(1,TIOCGETA,0xe390) = 0 (0x0) write(2,ERROR 2026 (HY000): ,20) ERR#9 'Bad file descriptor' write(2,SSL connection error,20) ERR#9 'Bad file descriptor' write(2,\n,1)ERR#9 'Bad file descriptor' ... but with a closed FD 2 there is no chance to output an error message. (The operations on FD 1 also look suspicious to me.) write(1,\a,1)= 1 (0x1) close(4) = 0 (0x0) process exit, rval = 1 -- Can anyone tell me, based on the truss output above, what might be going wrong? I'm using the exact same command line options and ssl-ca file that I am using on other clients, which can connect successfully. I can't really tell what happened. It might be that file descriptor 2 (and probably also 1) were closed without good reason, and then creating a socket (or opening some file) returned these descriptors. You might check the preceding truss output for a close(2), IMO that would likely be an error - but I have no idea why it happened. (As an aside: feature request: better SSL connection debugging, please!) I assume your SSL problems might just be a consequence of inappropriate actions on FDs 1 and 2. Did you do any redirection, are you running into disk full, or is there any similar rsk on your stdout and stderr? HTH, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems Connecting with SSL
Sorry, but I have to start with a negative remark: Why, oh why, don't you mention the version number you are using? I hope it is some roughly current 5.1, but I'd rather like you to tell it. Doh! Right you are! Sorry, that was totally my bad. mysql select version(); ++ | version() | ++ | 5.1.51-log | ++ The server and the client are both running FreeBSD as follows: FreeBSD 8.1-RELEASE-p2 Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems Connecting with SSL
Interesting that you can request advice from finland vs calling from redwood shores CA for technical advice first take a look at: http://dev.mysql.com/doc/refman/5.0/en/secure-basics.html once you've determined the algorithm you will want to implement take a look at SSL 1,2,3 and TLS 1.0,1.1,1.2 and determine which protocol works best for you http://en.wikipedia.org/wiki/Transport_Layer_Security feel free to ping me offline for implementation details In my original post, I stated that I had SSL connections up and running on all but this one client. I'm intimately familiar with the inner workings of SSL and TLS, and I'm positive that the servers are configured correctly, and that in general my clients are all able to connect using SSL. It's just this one client that is failing, and absent any useful debugging information, I'm having a hard time understanding why. As to why I didn't call Redwood Shores: I didn't realize that Oracle was in the business of giving out free tech support. I just e-mailed the mySQL general support list, which is usually what I do when I need help from an Open Source software group. :) Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems Connecting with SSL
perhaps a misconfiguration on the failing client .. can you go to successful client, enable DEBUG level debugging and log the connection and then go to the failing client..enable DEBUG level debugging and log the connection to the failing client this would determine deltas between the failing and successful connections and you compare each log side-by-side to ascertain the point of failure. joerg ..any thoughts on this matter?? Well that's exactly my question: how does one enable debugging of this kind of connection on the mySQL client? I get no more information when I use --verbose --verbose --verbose than when I don't. Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems Connecting with SSL
Hi! Martin Gainty wrote: [[...]] joerg ..any thoughts on this matter?? Sorry - none. I never dealt with the inner workings of the SSL library. Just seeing file descriptor 2 (standard error) used for socket operations makes me assume *very* strongly that something went wrong well before that part: For some (until now: unknown) reason standard error must have been closed, so that FD 2 became available for the socket creation, and in a MYSQL client application I don't see any reason to ever close standard error. I propose Tim should check the full truss output for some preceding close(2). Hopefully, the surroundings of that call will give a hint what went wrong. (I hope it was caused by some write error, disk full, permission problem, ... which Tim can fix. I of course agree such an error should not make the program do a close(2), but it might happen nonetheless.) That's all I can say here. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@oracle.com ORACLE Deutschland B.V. Co. KG, Komturstrasse 18a, D-12099 Berlin Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven Amtsgericht Muenchen: HRA 95603 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problems Connecting with SSL
I have three mySQL server, all of which are SSL-enable and all of which are generally accessible from remote clients over SSL. However, one of my FreeBSD mySQL clients is having problems connecting to any of the servers. Running mySQL under truss reports the following: -- clock_gettime(13,{1298593067.0 })= 0 (0x0) open(/dev/urandom,O_RDONLY,00) = 4 (0x4) read(4,\a\^V\M-)\^C\M-L{`\M^O\M^@\M-v...,32) = 32 (0x20) sendto(2,\^V\^C\^A\0Y\^A\0\0U\^C\^A\M-m...,94,0x0,NULL,0x0) = 94 (0x5e) recvfrom(2,\^V,1,0x2,NULL,0x0) = 1 (0x1) ioctl(2,FIONREAD,0xd784) = 0 (0x0) recvfrom(2,\^V\^C\^A\0J\^B\0\0F\^C\^AMf\M-u...,1819,0x0,NULL,0x0) = 1819 (0x71b) clock_gettime(13,{1298593067.0 })= 0 (0x0) clock_gettime(13,{1298593067.0 })= 0 (0x0) gettimeofday({1298593067.844592 },0x0) = 0 (0x0) sendto(2,\^V\^C\^A\0F\^P\0\0B\0@\M-B\M^Y...,134,0x0,NULL,0x0) = 134 (0x86) recvfrom(2,\^U,1,0x2,NULL,0x0) = 1 (0x1) ioctl(2,FIONREAD,0xd784) = 0 (0x0) recvfrom(2,\^U\^C\^A\0\^B\^B\n\^V\0\0\^B...,33,0x0,NULL,0x0) = 33 (0x21) close(3) = 0 (0x0) shutdown(2,SHUT_RDWR)= 0 (0x0) close(2) = 0 (0x0) fstat(1,{ mode=crw--w ,inode=113,size=0,blksize=4096 }) = 0 (0x0) ioctl(1,TIOCGETA,0xe390) = 0 (0x0) write(2,ERROR 2026 (HY000): ,20) ERR#9 'Bad file descriptor' write(2,SSL connection error,20) ERR#9 'Bad file descriptor' write(2,\n,1) ERR#9 'Bad file descriptor' write(1,\a,1) = 1 (0x1) close(4) = 0 (0x0) process exit, rval = 1 -- Can anyone tell me, based on the truss output above, what might be going wrong? I'm using the exact same command line options and ssl-ca file that I am using on other clients, which can connect successfully. (As an aside: feature request: better SSL connection debugging, please!) Tim Gustafson Baskin School of Engineering UC Santa Cruz t...@soe.ucsc.edu 831-459-5354 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problems storing a blob using php.
Debian squeeze, mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i486) using readline 6.1, PHP 5.3.3-2 with Suhosin-Patch (cli) (built: Oct 21 2010 18:58:27). I'm not sure if this group or the php one is more appropriate but thought I would try here first as it is perhaps a mysql setting I am missing. I have been unable to get prepared statements to store a blob. using this test code:- function saveImage($event) { $stmt = getDB()-prepare(INSERT INTO images (image) VALUES (?)); $null = NULL; $stmt-bind_param(b, $null); $fp = fopen(common-images/takeaway.png, r); while (!feof($fp)) { $stmt-send_long_data(0, fread($fp, 8192)); /* I also tried get_file_contents */ } fclose($fp); $stmt-execute(); logtext($stmt-error); } I get the following error in my home brew log file: Incorrect arguments to mysqld_stmt_execute I have checked that fp is valid. My table is defined as follows:- , | CREATE TABLE `images` ( | `id` int(10) unsigned NOT NULL AUTO_INCREMENT, | `fkey` int(10) unsigned DEFAULT NULL, | `image` blob, | `name` varchar(30) NOT NULL, | `type` varchar(30) NOT NULL, | PRIMARY KEY (`id`), | KEY `deleteimages` (`fkey`), | CONSTRAINT `deleteimages` FOREIGN KEY (`fkey`) REFERENCES `events` (`id`) ON DELETE CASCADE | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 ` I'm sure it must be something glaringly obvious but being a bit of a noob to mysql I'm not sure what to do to narrow it down. I can set the blob field directly. Its the send_long_data that seems to cause the arg error at the execute phase. Any debugging hits/configure hints would be much appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
Hi, We're chaning it to INT(9). Apparently someone remembered to change the type of data in this field from an alphanumeric value to an INT(9). I'm going to change this asap. Thanks BR AJ On Mon, Sep 6, 2010 at 5:17 AM, mos mo...@fastmail.fm wrote: At 04:44 AM 9/3/2010, Alexandre Vieira wrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ Alexandre, Do you have UserId declared as CHAR? It looks numeric to me. If it is stored as an integer then don't use the ' ' in the select statement otherwise it needs to convert it. If UserId values are integers and you have the column defined as CHAR, then declare the column UserId as integer or BigInt and the searches should be faster than searching on CHAR. Mike On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers joh...@pixelated.net wrote: What about an explain of this query: SELECT * FROM clientinfo WHERE userid='182106617'; -JW On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira nul...@gmail.com wrote: John, Johnny, Thanks for the prompt answer. mysql SHOW CREATE TABLE clientinfo; ++--+ | Table | Create Table | ++--+ | clientinfo | CREATE TABLE `clientinfo` ( `userid` varchar(21) NOT NULL default '', `units` float default NULL, `date_last_query` datetime default NULL, `last_acc` int(10) unsigned default NULL, `date_last_units` datetime default NULL, `notification` int(10) unsigned NOT NULL default '0', `package` char(1) default NULL, `user_type` varchar(5) default NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | ++--+ 1 row in set (0.00 sec) mysql SHOW INDEX FROM clientinfo; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | clientinfo | 0 | PRIMARY |1 | userid | A | 460056 | NULL | NULL | | BTREE | | +++--+--+-+---+-+--++--++-+ 1 row in set (0.00 sec) SELECT * FROM clientinfo WHERE userid='182106617'; UPDATE clientinfo SET units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0 WHERE userid='152633876'; INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(), notification=0, package='D', user_type='PRE'; DELETE FROM clientinfo WHERE units='155618918'; There are no other type of queries. We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM. We also run some
Re: Performance problems on MySQL
Hi List, In a 20m interval in our max load I have: OS WAIT ARRAY INFO: reservation count 637, signal count 625 Mutex spin waits 0, rounds 19457, OS waits 428 RW-shared spins 238, OS waits 119; RW-excl spins 13, OS waits 8 (The values are the difference between the start and end of this 20m interval) The machine has 2 CPU's and usually has 40-50% of idle CPU. Our workload consists on lots of parallel simple queries (SELECTs and UPDATEs with a simple condition on the PK) on a 500k record/40MB table with an INDEX on the PK. | innodb_sync_spin_loops | 20 | | innodb_thread_concurrency | 16 | | innodb_thread_sleep_delay | 1 | I've been sampling my innodb status and there are always 16 queries inside InnoDB and some 20-30 in queue. Therefore lowering thread_sleep_delay won't help. Since I have 47 spin rounds per OS Wait, would innodb gain something with rising sync_spin_loops a little bit? Also, should I be capping thread_concurrency with a 2 CPU machine? Unfortunately this machine only has 2 RAID1 disks. I can't spread the disk load (datafile/logfiles) between disks. extended device statistics r/sw/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device 0.0 83.70.0 379.3 0.0 1.00.1 11.5 1 94 d2 (/var) Usually the iostat busy indicator is near 100%. Any hints on something I could tune to have less OS Waits and help with the Disk I/O? = 100906 18:33:40 INNODB MONITOR OUTPUT = Per second averages calculated from the last 47 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 266140, signal count 259267 Mutex spin waits 0, rounds 7407879, OS waits 179189 RW-shared spins 93878, OS waits 46196; RW-excl spins 9473, OS waits 7311 --- LOG --- Log sequence number 62 2833945222 Log flushed up to 62 2833944847 Last checkpoint at 62 2828803314 1 pending log writes, 0 pending chkp writes 18419416 log i/o's done, 37.64 log i/o's/second -- ROW OPERATIONS -- 16 queries inside InnoDB, 27 queries in queue 1 read views open inside InnoDB Main thread id 11, state: sleeping Number of rows inserted 603196, updated 9006533, deleted 111028, read 30145300 0.17 inserts/s, 18.49 updates/s, 0.00 deletes/s, 41.47 reads/s If nothing else can be done I'll advise the client to acquire new HW for this BD. By the way, upgrading from 5.0.45-log to 5.1.50 would make a huge difference in terms of performance? BR AJ On Mon, Sep 6, 2010 at 10:46 AM, Alexandre Vieira nul...@gmail.com wrote: Hi, We're chaning it to INT(9). Apparently someone remembered to change the type of data in this field from an alphanumeric value to an INT(9). I'm going to change this asap. Thanks BR AJ On Mon, Sep 6, 2010 at 5:17 AM, mos mo...@fastmail.fm wrote: At 04:44 AM 9/3/2010, Alexandre Vieira wrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ Alexandre, Do you have UserId declared as CHAR? It looks numeric to me. If it is stored as an integer then don't use the ' ' in the select statement otherwise it needs to convert it. If UserId values are integers and you have the column defined as CHAR, then declare the column UserId as integer or BigInt and the searches should be faster than searching on CHAR. Mike On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers joh...@pixelated.net wrote: What about an explain of this query: SELECT * FROM clientinfo WHERE userid='182106617'; -JW On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira nul...@gmail.com wrote: John, Johnny, Thanks for the prompt answer. mysql SHOW CREATE TABLE clientinfo; ++--+ | Table | Create Table |
Re: Performance problems on MySQL
On 9/3/2010 3:15 PM, Johnny Withers wrote: It seems that when your index is PRIMARY on InnoDB tables, it's magic and is part of the data thereby it is not included in the index_length field. I have never noticed this. I don't think adding a new index will make a difference. You could try moving your log files to a different disk array than where your data is. If you have binary and query logging enabled, it's probably a good idea. Johnny is correct. The PRIMARY KEY to an InnoDB table is indeed part of the data: http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.html That explains why there is no length to this index. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
At 04:44 AM 9/3/2010, Alexandre Vieira wrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ Alexandre, Do you have UserId declared as CHAR? It looks numeric to me. If it is stored as an integer then don't use the ' ' in the select statement otherwise it needs to convert it. If UserId values are integers and you have the column defined as CHAR, then declare the column UserId as integer or BigInt and the searches should be faster than searching on CHAR. Mike On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers joh...@pixelated.net wrote: What about an explain of this query: SELECT * FROM clientinfo WHERE userid='182106617'; -JW On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira nul...@gmail.com wrote: John, Johnny, Thanks for the prompt answer. mysql SHOW CREATE TABLE clientinfo; ++--+ | Table | Create Table | ++--+ | clientinfo | CREATE TABLE `clientinfo` ( `userid` varchar(21) NOT NULL default '', `units` float default NULL, `date_last_query` datetime default NULL, `last_acc` int(10) unsigned default NULL, `date_last_units` datetime default NULL, `notification` int(10) unsigned NOT NULL default '0', `package` char(1) default NULL, `user_type` varchar(5) default NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | ++--+ 1 row in set (0.00 sec) mysql SHOW INDEX FROM clientinfo; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | clientinfo | 0 | PRIMARY |1 | userid | A | 460056 | NULL | NULL | | BTREE | | +++--+--+-+---+-+--++--++-+ 1 row in set (0.00 sec) SELECT * FROM clientinfo WHERE userid='182106617'; UPDATE clientinfo SET units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0 WHERE userid='152633876'; INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(), notification=0, package='D', user_type='PRE'; DELETE FROM clientinfo WHERE units='155618918'; There are no other type of queries. We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM. We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre
Re: Performance problems on MySQL
Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers joh...@pixelated.net wrote: What about an explain of this query: SELECT * FROM clientinfo WHERE userid='182106617'; -JW On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira nul...@gmail.com wrote: John, Johnny, Thanks for the prompt answer. mysql SHOW CREATE TABLE clientinfo; ++--+ | Table | Create Table | ++--+ | clientinfo | CREATE TABLE `clientinfo` ( `userid` varchar(21) NOT NULL default '', `units` float default NULL, `date_last_query` datetime default NULL, `last_acc` int(10) unsigned default NULL, `date_last_units` datetime default NULL, `notification` int(10) unsigned NOT NULL default '0', `package` char(1) default NULL, `user_type` varchar(5) default NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | ++--+ 1 row in set (0.00 sec) mysql SHOW INDEX FROM clientinfo; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | clientinfo | 0 | PRIMARY |1 | userid | A | 460056 | NULL | NULL | | BTREE | | +++--+--+-+---+-+--++--++-+ 1 row in set (0.00 sec) SELECT * FROM clientinfo WHERE userid='182106617'; UPDATE clientinfo SET units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0 WHERE userid='152633876'; INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(), notification=0, package='D', user_type='PRE'; DELETE FROM clientinfo WHERE units='155618918'; There are no other type of queries. We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM. We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com
Re: Performance problems on MySQL
On 02/09/2010 6:05 p, Alexandre Vieira wrote: Hi Jangita, I'm 15779 innodb_buffer_pool_pages_free from a total of 22400. That's 246MB of 350MB free. | Innodb_buffer_pool_pages_data | 6020 | | Innodb_buffer_pool_pages_dirty| 1837 | | Innodb_buffer_pool_pages_flushed | 673837 | | Innodb_buffer_pool_pages_free | 15779 | | Innodb_buffer_pool_pages_latched | 0 | | Innodb_buffer_pool_pages_misc | 601| | Innodb_buffer_pool_pages_total| 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 48471963 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 21700478 | Why would I need to increase? Thanks BR AJ I'm guessing (just a guess) that you have alot free buffer_pool_size because mysql doesn't use it because it cannot fit information into it at one go so doesn't use it at all? Try and up it; if it doesn't work you could always set it back. -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
Ok, so I'm stumped? What kind of hardware is behind this thing? -JW On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.com wrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ
Re: Performance problems on MySQL
Hi, I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it makes a little difference but not enough for the application to run in real time processing. It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM. MySQL is eating 179MB of RAM and 5,4% of CPU. PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 6229 mysql 455M 179M sleep 580 0:03.11 5,4% mysqld/68 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to grow and we have ~50% of free CPU time. Currently I can't use the replication server since the application running on top if this BD can only talk to 1 data source. At the moment it's also not possible to change the application in order to make it use the DB more wisely. Basically we have a table with lots of selects, lots of update, lots of inserts and deletes. Data manipulation is random, doesn't follow any specific pattern. All working concurrently. A big bottleneck is: 8 queries inside InnoDB, 28 queries in queue 1 read views open inside InnoDB Increasing innodb_thread_concurrency might help without causing any problems to the overall performance. Makes total sense if you read: http://peter-zaitsev.livejournal.com/9138.html Thanks in advance. BR AJ On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.net wrote: Ok, so I'm stumped? What kind of hardware is behind this thing? -JW On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.com wrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ -- Alexandre Vieira - nul...@gmail.com
Re: Performance problems on MySQL
What does SHOW TABLE STATUS LIKE 'table_name' Say about this table? -JW On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.com wrote: Hi, I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it makes a little difference but not enough for the application to run in real time processing. It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM. MySQL is eating 179MB of RAM and 5,4% of CPU. PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 6229 mysql 455M 179M sleep 580 0:03.11 5,4% mysqld/68 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to grow and we have ~50% of free CPU time. Currently I can't use the replication server since the application running on top if this BD can only talk to 1 data source. At the moment it's also not possible to change the application in order to make it use the DB more wisely. Basically we have a table with lots of selects, lots of update, lots of inserts and deletes. Data manipulation is random, doesn't follow any specific pattern. All working concurrently. A big bottleneck is: 8 queries inside InnoDB, 28 queries in queue 1 read views open inside InnoDB Increasing innodb_thread_concurrency might help without causing any problems to the overall performance. Makes total sense if you read: http://peter-zaitsev.livejournal.com/9138.html Thanks in advance. BR AJ On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote: Ok, so I'm stumped? What kind of hardware is behind this thing? -JW On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Performance problems on MySQL
Hi, mysql SHOW TABLE STATUS LIKE 'clientinfo'; +++-++++-+-+--+---++-+-++---+--+++ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment| +++-++++-+-+--+---++-+-++---+--+++ | clientinfo | InnoDB | 10 | Compact| 504762 | 82 | 41500672 | 0 |0 | 0 | NULL | 2010-09-01 03:21:36 | NULL| NULL | latin1_swedish_ci | NULL || InnoDB free: 276480 kB | +++-++++-+-+--+---++-+-++---+--+++ 1 row in set (0.02 sec) BR AJ On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers joh...@pixelated.net wrote: What does SHOW TABLE STATUS LIKE 'table_name' Say about this table? -JW On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.com wrote: Hi, I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it makes a little difference but not enough for the application to run in real time processing. It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM. MySQL is eating 179MB of RAM and 5,4% of CPU. PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 6229 mysql 455M 179M sleep 580 0:03.11 5,4% mysqld/68 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to grow and we have ~50% of free CPU time. Currently I can't use the replication server since the application running on top if this BD can only talk to 1 data source. At the moment it's also not possible to change the application in order to make it use the DB more wisely. Basically we have a table with lots of selects, lots of update, lots of inserts and deletes. Data manipulation is random, doesn't follow any specific pattern. All working concurrently. A big bottleneck is: 8 queries inside InnoDB, 28 queries in queue 1 read views open inside InnoDB Increasing innodb_thread_concurrency might help without causing any problems to the overall performance. Makes total sense if you read: http://peter-zaitsev.livejournal.com/9138.html Thanks in advance. BR AJ On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote: Ok, so I'm stumped? What kind of hardware is behind this thing? -JW On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com
Re: Performance problems on MySQL
Very confusing... Why is index_length zero ? On top of that, there's only 500K rows in the table with a data size of 41MB. Maybe InnoDB is flushing to disk too often? What's the output of iostat -dxk 60 ? (run for a minute+ to get 2 output girds) -- *Johnny Withers* jwith...@ecashsoftware.com 601.919.2275 x112 [image: eCash Software Systems] On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira nul...@gmail.com wrote: Hi, mysql SHOW TABLE STATUS LIKE 'clientinfo'; +++-++++-+-+--+---++-+-++---+--+++ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment| +++-++++-+-+--+---++-+-++---+--+++ | clientinfo | InnoDB | 10 | Compact| 504762 | 82 | 41500672 | 0 |0 | 0 | NULL | 2010-09-01 03:21:36 | NULL| NULL | latin1_swedish_ci | NULL || InnoDB free: 276480 kB | +++-++++-+-+--+---++-+-++---+--+++ 1 row in set (0.02 sec) BR AJ On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers joh...@pixelated.netwrote: What does SHOW TABLE STATUS LIKE 'table_name' Say about this table? -JW On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.comwrote: Hi, I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it makes a little difference but not enough for the application to run in real time processing. It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM. MySQL is eating 179MB of RAM and 5,4% of CPU. PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 6229 mysql 455M 179M sleep 580 0:03.11 5,4% mysqld/68 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to grow and we have ~50% of free CPU time. Currently I can't use the replication server since the application running on top if this BD can only talk to 1 data source. At the moment it's also not possible to change the application in order to make it use the DB more wisely. Basically we have a table with lots of selects, lots of update, lots of inserts and deletes. Data manipulation is random, doesn't follow any specific pattern. All working concurrently. A big bottleneck is: 8 queries inside InnoDB, 28 queries in queue 1 read views open inside InnoDB Increasing innodb_thread_concurrency might help without causing any problems to the overall performance. Makes total sense if you read: http://peter-zaitsev.livejournal.com/9138.html Thanks in advance. BR AJ On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote: Ok, so I'm stumped? What kind of hardware is behind this thing? -JW On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Performance problems on MySQL
| Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment| +++-++++-+-+--+---++-+-++---+--+++ | clientinfo | InnoDB | 10 | Compact| 504762 | 82 |41500672 | 0 |0 | 0 | NULL | 2010-09-01 03:21:36 | NULL| NULL | latin1_swedish_ci | NULL || InnoDB free: 276480 kB | +++-++++-+-+--+---++-+-++---+--+++ 1 row in set (0.02 sec) BR AJ On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers joh...@pixelated.netwrote: What does SHOW TABLE STATUS LIKE 'table_name' Say about this table? -JW On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.comwrote: Hi, I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it makes a little difference but not enough for the application to run in real time processing. It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM. MySQL is eating 179MB of RAM and 5,4% of CPU. PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 6229 mysql 455M 179M sleep 580 0:03.11 5,4% mysqld/68 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to grow and we have ~50% of free CPU time. Currently I can't use the replication server since the application running on top if this BD can only talk to 1 data source. At the moment it's also not possible to change the application in order to make it use the DB more wisely. Basically we have a table with lots of selects, lots of update, lots of inserts and deletes. Data manipulation is random, doesn't follow any specific pattern. All working concurrently. A big bottleneck is: 8 queries inside InnoDB, 28 queries in queue 1 read views open inside InnoDB Increasing innodb_thread_concurrency might help without causing any problems to the overall performance. Makes total sense if you read: http://peter-zaitsev.livejournal.com/9138.html Thanks in advance. BR AJ On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote: Ok, so I'm stumped? What kind of hardware is behind this thing? -JW On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com
Re: Performance problems on MySQL
of iostat -dxk 60 ? (run for a minute+ to get 2 output girds) -- *Johnny Withers* jwith...@ecashsoftware.com 601.919.2275 x112 [image: eCash Software Systems] On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira nul...@gmail.comwrote: Hi, mysql SHOW TABLE STATUS LIKE 'clientinfo'; +++-++++-+-+--+---++-+-++---+--+++ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment| +++-++++-+-+--+---++-+-++---+--+++ | clientinfo | InnoDB | 10 | Compact| 504762 | 82 |41500672 | 0 |0 | 0 | NULL | 2010-09-01 03:21:36 | NULL| NULL | latin1_swedish_ci | NULL || InnoDB free: 276480 kB | +++-++++-+-+--+---++-+-++---+--+++ 1 row in set (0.02 sec) BR AJ On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers joh...@pixelated.netwrote: What does SHOW TABLE STATUS LIKE 'table_name' Say about this table? -JW On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira nul...@gmail.comwrote: Hi, I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it makes a little difference but not enough for the application to run in real time processing. It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM. MySQL is eating 179MB of RAM and 5,4% of CPU. PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 6229 mysql 455M 179M sleep 580 0:03.11 5,4% mysqld/68 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to grow and we have ~50% of free CPU time. Currently I can't use the replication server since the application running on top if this BD can only talk to 1 data source. At the moment it's also not possible to change the application in order to make it use the DB more wisely. Basically we have a table with lots of selects, lots of update, lots of inserts and deletes. Data manipulation is random, doesn't follow any specific pattern. All working concurrently. A big bottleneck is: 8 queries inside InnoDB, 28 queries in queue 1 read views open inside InnoDB Increasing innodb_thread_concurrency might help without causing any problems to the overall performance. Makes total sense if you read: http://peter-zaitsev.livejournal.com/9138.html Thanks in advance. BR AJ On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote: Ok, so I'm stumped? What kind of hardware is behind this thing? -JW On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Performance problems on MySQL
the VARCHAR(23) on the userid PK and it makes a little difference but not enough for the application to run in real time processing. It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM. MySQL is eating 179MB of RAM and 5,4% of CPU. PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP 6229 mysql 455M 179M sleep 580 0:03.11 5,4% mysqld/68 The machine has ~1GB of free memory. MySQL and InnoDB has free pages to grow and we have ~50% of free CPU time. Currently I can't use the replication server since the application running on top if this BD can only talk to 1 data source. At the moment it's also not possible to change the application in order to make it use the DB more wisely. Basically we have a table with lots of selects, lots of update, lots of inserts and deletes. Data manipulation is random, doesn't follow any specific pattern. All working concurrently. A big bottleneck is: 8 queries inside InnoDB, 28 queries in queue 1 read views open inside InnoDB Increasing innodb_thread_concurrency might help without causing any problems to the overall performance. Makes total sense if you read: http://peter-zaitsev.livejournal.com/9138.html Thanks in advance. BR AJ On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers joh...@pixelated.netwrote: Ok, so I'm stumped? What kind of hardware is behind this thing? -JW On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira nul...@gmail.comwrote: Hi Johnny, mysql EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694'; ++-++---+---+-+-+---+--+---+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-++---+---+-+-+---+--+---+ | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23 | const |1 | | ++-++---+---+-+-+---+--+---+ 1 row in set (0.53 sec) Thanks BR AJ -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- Alexandre Vieira - nul...@gmail.com
Re: Performance problems on MySQL
It seems that when your index is PRIMARY on InnoDB tables, it's magic and is part of the data thereby it is not included in the index_length field. I have never noticed this. I don't think adding a new index will make a difference. You could try moving your log files to a different disk array than where your data is. If you have binary and query logging enabled, it's probably a good idea. -- *Johnny Withers* jwith...@ecashsoftware.com 601.919.2275 x112 [image: eCash Software Systems] On Fri, Sep 3, 2010 at 12:45 PM, Alexandre Vieira nul...@gmail.com wrote: Hi, When creating a table in MySQL with a PK it automatically creates an INDEX, correct? The Index_Length: 0 is rather strange..I've created a new INDEX on top of my PK column on my test system and Index_Length shows a big value different from 0. Do you think this might have any impact? mysql show index from gwtraffic.clientinfo; ++++--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | ++++--+-+---+-+--++--++-+ | clientinfo | 0 | PRIMARY|1 | userid | A | 548216 | NULL | NULL | | BTREE | | | clientinfo | 1 | userid_idx |1 | userid | A | 548216 | NULL | NULL | | BTREE | | ++++--+-+---+-+--++--++-+ 2 rows in set (0.01 sec) mysql show table status LIKE 'clientinfo'; +++-++++-+-+--+---++-+-++---+--++-+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +++-++++-+-+--+---++-+-++---+--++-+ | clientinfo | InnoDB | 10 | Compact| 548216 | 62 | 34144256 | 0 | 5783552 | 0 | NULL | 2010-09-03 17:38:16 | NULL| NULL | latin1_swedish_ci | NULL || InnoDB free: 1214464 kB | +++-++++-+-+--+---++-+-++---+--++-+ 1 row in set (0.00 sec) I'm trying to stress my test DB but can't measure any different results with or without the second INDEX. Regarding the disks.. the DB is updated 20+ times every second. Writing the log, checkpoint to disk, etc.. can cause that much load? BR AJ
Performance problems on MySQL
Hi list, I'm having some performance problems on my 5.0.45-log DB running on Solaris 8 (V240). We only have one table and two apps selecting, updating, inserting and deleting massively and randomly from this table. The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only one condition on an unique varchar indexed column. The table has 500k records and has been OPTIMIZED 32h ago. I've ran some sampling and: A SELECT costs between 400ms and 600ms. An UPDATE costs between 800ms and 1300ms. A DELETE costs between 900ms and 1300ms An INSERT costs always 900ms 2000ms. At any given time the DB is handling 60-80 operations every second. It does not scale any more than this because all the application connections to the DB are being used and waiting for the DB to move. Our application queues requests and it lags our clients. The perl mysqltuner only whines about Query cache disabled but since I get an ~20 updates every second I can't get any query cache hits, so I disabled it. If it makes any difference, we're replicating everything to another server that don't serve any queries. The DB has a 32 hour uptime. Any help is most welcome. You can find my.cnf, show status and show innodb status below. Kind regards Alex ### my.cnf: sql-mode=STRICT_ALL_TABLES old_passwords =1 skip-bdb max_connections =100 max_allowed_packet =1M table_cache =512 sort_buffer_size=2M read_buffer_size=4M read_rnd_buffer_size=8M thread_cache_size =16 query_cache_limit =32M thread_concurrency =8 max_heap_table_size =28M tmp_table_size =12M innodb_buffer_pool_size =350M innodb_additional_mem_pool_size =15M innodb_log_buffer_size =6M innodb_flush_log_at_trx_commit =1 innodb_lock_wait_timeout=50 ### mysql show status where Value NOT LIKE 0; +---++ | Variable_name | Value | +---++ | Aborted_clients | 88 | | Aborted_connects | 37590 | | Binlog_cache_use | 2148392| | Bytes_received| 1117 | | Bytes_sent| 8772 | | Com_change_db | 1 | | Com_delete| 4 | | Com_insert| 3 | | Com_select| 2 | | Com_show_databases| 1 | | Com_show_fields | 3 | | Com_show_status | 2 | | Com_show_tables | 1 | | Compression | OFF| | Connections | 276096 | | Created_tmp_files | 5 | | Created_tmp_tables| 4 | | Flush_commands| 1 | | Handler_commit| 14 | | Handler_prepare | 14 | | Handler_read_key | 8 | | Handler_read_rnd_next | 263| | Handler_write | 395| | Innodb_buffer_pool_pages_data | 6019 | | Innodb_buffer_pool_pages_dirty| 1858 | | Innodb_buffer_pool_pages_flushed | 593993 | | Innodb_buffer_pool_pages_free | 15784 | | Innodb_buffer_pool_pages_misc | 597| | Innodb_buffer_pool_pages_total| 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_requests | 42797013 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_write_requests | 19096507 | | Innodb_data_fsyncs| 4319683| | Innodb_data_pending_fsyncs| 1 | | Innodb_data_read | 60231680 | | Innodb_data_reads | 3514 | | Innodb_data_writes| 4496721| | Innodb_data_written | 1259458560 | | Innodb_dblwr_pages_written| 593993 | | Innodb_dblwr_writes | 12967 | | Innodb_log_write_requests | 2111208| | Innodb_log_writes | 4285654| | Innodb_os_log_fsyncs | 4303114| | Innodb_os_log_pending_fsyncs | 1 | | Innodb_os_log_written | 3264897024 | | Innodb_page_size | 16384 | | Innodb_pages_created | 2476 | | Innodb_pages_read | 3543 | | Innodb_pages_written | 593993 | | Innodb_row_lock_time | 1339668| | Innodb_row_lock_time_avg | 379| | Innodb_row_lock_time_max | 10631 | | Innodb_row_lock_waits | 3531 | | Innodb_rows_deleted
Re: Performance problems on MySQL
Can you show us the table structure and sample queries? On Thursday, September 2, 2010, Alexandre Vieira nul...@gmail.com wrote: Hi list, I'm having some performance problems on my 5.0.45-log DB running on Solaris 8 (V240). We only have one table and two apps selecting, updating, inserting and deleting massively and randomly from this table. The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only one condition on an unique varchar indexed column. The table has 500k records and has been OPTIMIZED 32h ago. I've ran some sampling and: A SELECT costs between 400ms and 600ms. An UPDATE costs between 800ms and 1300ms. A DELETE costs between 900ms and 1300ms An INSERT costs always 900ms 2000ms. At any given time the DB is handling 60-80 operations every second. It does not scale any more than this because all the application connections to the DB are being used and waiting for the DB to move. Our application queues requests and it lags our clients. The perl mysqltuner only whines about Query cache disabled but since I get an ~20 updates every second I can't get any query cache hits, so I disabled it. If it makes any difference, we're replicating everything to another server that don't serve any queries. The DB has a 32 hour uptime. Any help is most welcome. You can find my.cnf, show status and show innodb status below. Kind regards Alex ### my.cnf: sql-mode =STRICT_ALL_TABLES old_passwords =1 skip-bdb max_connections =100 max_allowed_packet =1M table_cache =512 sort_buffer_size =2M read_buffer_size =4M read_rnd_buffer_size =8M thread_cache_size =16 query_cache_limit =32M thread_concurrency =8 max_heap_table_size =28M tmp_table_size =12M innodb_buffer_pool_size =350M innodb_additional_mem_pool_size =15M innodb_log_buffer_size =6M innodb_flush_log_at_trx_commit =1 innodb_lock_wait_timeout =50 ### mysql show status where Value NOT LIKE 0; +---++ | Variable_name | Value | +---++ | Aborted_clients | 88 | | Aborted_connects | 37590 | | Binlog_cache_use | 2148392 | | Bytes_received | 1117 | | Bytes_sent | 8772 | | Com_change_db | 1 | | Com_delete | 4 | | Com_insert | 3 | | Com_select | 2 | | Com_show_databases | 1 | | Com_show_fields | 3 | | Com_show_status | 2 | | Com_show_tables | 1 | | Compression | OFF | | Connections | 276096 | | Created_tmp_files | 5 | | Created_tmp_tables | 4 | | Flush_commands | 1 | | Handler_commit | 14 | | Handler_prepare | 14 | | Handler_read_key | 8 | | Handler_read_rnd_next | 263 | | Handler_write | 395 | | Innodb_buffer_pool_pages_data | 6019 | | Innodb_buffer_pool_pages_dirty | 1858 | | Innodb_buffer_pool_pages_flushed | 593993 | | Innodb_buffer_pool_pages_free | 15784 | | Innodb_buffer_pool_pages_misc | 597 | | Innodb_buffer_pool_pages_total | 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_requests | 42797013 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_write_requests | 19096507 | | Innodb_data_fsyncs | 4319683 | | Innodb_data_pending_fsyncs | 1 | | Innodb_data_read | 60231680 | | Innodb_data_reads | 3514 | | Innodb_data_writes | 4496721 | | Innodb_data_written | 1259458560 | | Innodb_dblwr_pages_written | 593993 | | Innodb_dblwr_writes | 12967 | | Innodb_log_write_requests | 2111208 | | Innodb_log_writes | 4285654 | | Innodb_os_log_fsyncs | 4303114 | | Innodb_os_log_pending_fsyncs | 1 | | Innodb_os_log_written | 3264897024 | | Innodb_page_size | 16384 | | Innodb_pages_created | 2476 | | Innodb_pages_read | 3543 | | Innodb_pages_written | 593993
Re: Performance problems on MySQL
What is the hardware spec? Anything else running on the box? Why are you replicating but not making use of the slave? Can you post the output of SHOW CREATE TABLE? Regards John On 2 September 2010 12:50, Alexandre Vieira nul...@gmail.com wrote: Hi list, I'm having some performance problems on my 5.0.45-log DB running on Solaris 8 (V240). We only have one table and two apps selecting, updating, inserting and deleting massively and randomly from this table. The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only one condition on an unique varchar indexed column. The table has 500k records and has been OPTIMIZED 32h ago. I've ran some sampling and: A SELECT costs between 400ms and 600ms. An UPDATE costs between 800ms and 1300ms. A DELETE costs between 900ms and 1300ms An INSERT costs always 900ms 2000ms. At any given time the DB is handling 60-80 operations every second. It does not scale any more than this because all the application connections to the DB are being used and waiting for the DB to move. Our application queues requests and it lags our clients. The perl mysqltuner only whines about Query cache disabled but since I get an ~20 updates every second I can't get any query cache hits, so I disabled it. If it makes any difference, we're replicating everything to another server that don't serve any queries. The DB has a 32 hour uptime. Any help is most welcome. You can find my.cnf, show status and show innodb status below. Kind regards Alex ### my.cnf: sql-mode=STRICT_ALL_TABLES old_passwords =1 skip-bdb max_connections =100 max_allowed_packet =1M table_cache =512 sort_buffer_size=2M read_buffer_size=4M read_rnd_buffer_size=8M thread_cache_size =16 query_cache_limit =32M thread_concurrency =8 max_heap_table_size =28M tmp_table_size =12M innodb_buffer_pool_size =350M innodb_additional_mem_pool_size =15M innodb_log_buffer_size =6M innodb_flush_log_at_trx_commit =1 innodb_lock_wait_timeout=50 ### mysql show status where Value NOT LIKE 0; +---++ | Variable_name | Value | +---++ | Aborted_clients | 88 | | Aborted_connects | 37590 | | Binlog_cache_use | 2148392| | Bytes_received| 1117 | | Bytes_sent| 8772 | | Com_change_db | 1 | | Com_delete| 4 | | Com_insert| 3 | | Com_select| 2 | | Com_show_databases| 1 | | Com_show_fields | 3 | | Com_show_status | 2 | | Com_show_tables | 1 | | Compression | OFF| | Connections | 276096 | | Created_tmp_files | 5 | | Created_tmp_tables| 4 | | Flush_commands| 1 | | Handler_commit| 14 | | Handler_prepare | 14 | | Handler_read_key | 8 | | Handler_read_rnd_next | 263| | Handler_write | 395| | Innodb_buffer_pool_pages_data | 6019 | | Innodb_buffer_pool_pages_dirty| 1858 | | Innodb_buffer_pool_pages_flushed | 593993 | | Innodb_buffer_pool_pages_free | 15784 | | Innodb_buffer_pool_pages_misc | 597| | Innodb_buffer_pool_pages_total| 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_requests | 42797013 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_write_requests | 19096507 | | Innodb_data_fsyncs| 4319683| | Innodb_data_pending_fsyncs| 1 | | Innodb_data_read | 60231680 | | Innodb_data_reads | 3514 | | Innodb_data_writes| 4496721| | Innodb_data_written | 1259458560 | | Innodb_dblwr_pages_written| 593993 | | Innodb_dblwr_writes | 12967 | | Innodb_log_write_requests | 2111208| | Innodb_log_writes | 4285654| | Innodb_os_log_fsyncs | 4303114| | Innodb_os_log_pending_fsyncs | 1 | | Innodb_os_log_written | 3264897024 | | Innodb_page_size | 16384 | | Innodb_pages_created | 2476
Performance problems on MySQL
John, Johnny, Thanks for the prompt answer. mysql SHOW CREATE TABLE clientinfo; ++--+ | Table | Create Table | ++--+ | clientinfo | CREATE TABLE `clientinfo` ( `userid` varchar(21) NOT NULL default '', `units` float default NULL, `date_last_query` datetime default NULL, `last_acc` int(10) unsigned default NULL, `date_last_units` datetime default NULL, `notification` int(10) unsigned NOT NULL default '0', `package` char(1) default NULL, `user_type` varchar(5) default NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | ++--+ 1 row in set (0.00 sec) mysql SHOW INDEX FROM clientinfo; +++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+---+-+--++--++-+ | clientinfo | 0 | PRIMARY |1 | userid | A | 460056 | NULL | NULL | | BTREE | | +++--+--+-+---+-+--++--++-+ 1 row in set (0.00 sec) SELECT * FROM clientinfo WHERE userid='182106617'; UPDATE clientinfo SET units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units=now(),notification=0 WHERE userid='152633876'; INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(), notification=0, package='D', user_type='PRE'; DELETE FROM clientinfo WHERE units='155618918'; There are no other type of queries. We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM. We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex On Thu, Sep 2, 2010 at 1:52 PM, John Daisley daisleyj...@googlemail.comwrote: What is the hardware spec? Anything else running on the box? Why are you replicating but not making use of the slave? Can you post the output of SHOW CREATE TABLE? Regards John
Re: Performance problems on MySQL
On 02/09/2010 4:46 p, Alexandre Vieira wrote: John, Johnny, Thanks for the prompt answer. ... We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex Increase innodb_buffer_pool_size say to 1GB? -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
Hi Jangita, I'm 15779 innodb_buffer_pool_pages_free from a total of 22400. That's 246MB of 350MB free. | Innodb_buffer_pool_pages_data | 6020 | | Innodb_buffer_pool_pages_dirty| 1837 | | Innodb_buffer_pool_pages_flushed | 673837 | | Innodb_buffer_pool_pages_free | 15779 | | Innodb_buffer_pool_pages_latched | 0 | | Innodb_buffer_pool_pages_misc | 601| | Innodb_buffer_pool_pages_total| 22400 | | Innodb_buffer_pool_read_ahead_rnd | 1 | | Innodb_buffer_pool_read_ahead_seq | 0 | | Innodb_buffer_pool_read_requests | 48471963 | | Innodb_buffer_pool_reads | 3497 | | Innodb_buffer_pool_wait_free | 0 | | Innodb_buffer_pool_write_requests | 21700478 | Why would I need to increase? Thanks BR AJ On Thu, Sep 2, 2010 at 4:47 PM, Jangita jang...@jangita.com wrote: On 02/09/2010 4:46 p, Alexandre Vieira wrote: John, Johnny, Thanks for the prompt answer. ... We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex Increase innodb_buffer_pool_size say to 1GB? -- Jangita | +256 76 91 8383 | Y! MSN: jang...@yahoo.com Skype: jangita | GTalk: jangita.nyag...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=nul...@gmail.com -- Alexandre Vieira - nul...@gmail.com
RE: Performance problems on MySQL
Have you considered adding a secondary index on the units column for your delete queries? DELETE FROM clientinfo WHERE units='155618918'; -Original Message- From: Alexandre Vieira [mailto:nul...@gmail.com] Sent: Thursday, September 02, 2010 8:46 AM To: John Daisley; joh...@pixelated.net Cc: mysql@lists.mysql.com Subject: Performance problems on MySQL John, Johnny, Thanks for the prompt answer. mysql SHOW CREATE TABLE clientinfo; ++-- + | Table | Create Table | ++-- + | clientinfo | CREATE TABLE `clientinfo` ( `userid` varchar(21) NOT NULL default '', `units` float default NULL, `date_last_query` datetime default NULL, `last_acc` int(10) unsigned default NULL, `date_last_units` datetime default NULL, `notification` int(10) unsigned NOT NULL default '0', `package` char(1) default NULL, `user_type` varchar(5) default NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | ++-- + 1 row in set (0.00 sec) mysql SHOW INDEX FROM clientinfo; +++--+--+-+- --+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+- --+-+--++--++-+ | clientinfo | 0 | PRIMARY |1 | userid | A | 460056 | NULL | NULL | | BTREE | | +++--+--+-+- --+-+--++--++-+ 1 row in set (0.00 sec) SELECT * FROM clientinfo WHERE userid='182106617'; UPDATE clientinfo SET units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units =now(),notification=0 WHERE userid='152633876'; INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(), notification=0, package='D', user_type='PRE'; DELETE FROM clientinfo WHERE units='155618918'; There are no other type of queries. We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM. We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex On Thu, Sep 2, 2010 at 1:52 PM, John Daisley daisleyj...@googlemail.comwrote: What is the hardware spec? Anything else running on the box? Why are you replicating but not making use of the slave? Can you post the output of SHOW CREATE TABLE? Regards John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Performance problems on MySQL
Hi Travis, Sorry, bad copy/paste. That DELETE statement is wrong. The application executes: DELETE FROM clientinfo WHERE userid='x'; BR AJ On Thu, Sep 2, 2010 at 5:23 PM, Travis Ard travis_...@hotmail.com wrote: Have you considered adding a secondary index on the units column for your delete queries? DELETE FROM clientinfo WHERE units='155618918'; -Original Message- From: Alexandre Vieira [mailto:nul...@gmail.com] Sent: Thursday, September 02, 2010 8:46 AM To: John Daisley; joh...@pixelated.net Cc: mysql@lists.mysql.com Subject: Performance problems on MySQL John, Johnny, Thanks for the prompt answer. mysql SHOW CREATE TABLE clientinfo; ++-- + | Table | Create Table | ++-- + | clientinfo | CREATE TABLE `clientinfo` ( `userid` varchar(21) NOT NULL default '', `units` float default NULL, `date_last_query` datetime default NULL, `last_acc` int(10) unsigned default NULL, `date_last_units` datetime default NULL, `notification` int(10) unsigned NOT NULL default '0', `package` char(1) default NULL, `user_type` varchar(5) default NULL, PRIMARY KEY (`userid`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | ++-- + 1 row in set (0.00 sec) mysql SHOW INDEX FROM clientinfo; +++--+--+-+- --+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +++--+--+-+- --+-+--++--++-+ | clientinfo | 0 | PRIMARY |1 | userid | A | 460056 | NULL | NULL | | BTREE | | +++--+--+-+- --+-+--++--++-+ 1 row in set (0.00 sec) SELECT * FROM clientinfo WHERE userid='182106617'; UPDATE clientinfo SET units=0.0,date_last_query=now(),user_type='POS',last_acc=167,date_last_units =now(),notification=0 WHERE userid='152633876'; INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(), notification=0, package='D', user_type='PRE'; DELETE FROM clientinfo WHERE units='155618918'; There are no other type of queries. We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM. We also run some other applications in the server, but nothing that consumes all the CPU/Memory. The machine has almost 1GB of free memory and 50% of idle CPU time at any time. TIA BR Alex On Thu, Sep 2, 2010 at 1:52 PM, John Daisley daisleyj...@googlemail.comwrote: What is the hardware spec? Anything else running on the box? Why are you replicating but not making use of the slave? Can you post the output of SHOW CREATE TABLE? Regards John -- Alexandre Vieira - nul...@gmail.com
collation problems
Hi listers mysql server here is mysql-server-5.1.48-2.fc13.x86_64 this morning i created a message with a literal string in chinese in it. the messages in the application i used are stored in a mysql database, when you submit them, like in a sent folder. With this chinese literal in it, i, however, got ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' when i sent the message. without the chinese literal, the message was stored in the mysql db correctly. i, then, changed the connection names to utf8 and collation to utf8_bin and then the session parameters looked like: mysql set names utf8; Query OK, 0 rows affected (0.00 sec) mysql set collation_connection = utf8_bin; Query OK, 0 rows affected (0.00 sec) mysql show session variables like character_set%; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) mysql show session variables like collation%; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | utf8_bin | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql i thought, if you set the collation_connection to utf8_bin, you may send fairly everything to mysql. but still, when i tried to concat the new string (including the chinese characers) i got: mysql update suomi_contacts2 set history = concat(now(), ' ', '' ,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will ever happen to you.\r\n 葛斯克 愛德華 / 台北市八德路四段\r\n\r\n\r\n \r\nsuomi\r\n', ' ', '--- ', history) where counter = 1127; ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' Question: What did i miss? what do i have to change? Thanks in advance suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: collation problems
can u please list out the table structure...as collation can also be set at column level regards anandkl On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com wrote: Hi listers mysql server here is mysql-server-5.1.48-2.fc13.x86_64 this morning i created a message with a literal string in chinese in it. the messages in the application i used are stored in a mysql database, when you submit them, like in a sent folder. With this chinese literal in it, i, however, got ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' when i sent the message. without the chinese literal, the message was stored in the mysql db correctly. i, then, changed the connection names to utf8 and collation to utf8_bin and then the session parameters looked like: mysql set names utf8; Query OK, 0 rows affected (0.00 sec) mysql set collation_connection = utf8_bin; Query OK, 0 rows affected (0.00 sec) mysql show session variables like character_set%; +--++ | Variable_name| Value | +--++ | character_set_client | utf8 | | character_set_connection | utf8 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results| utf8 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) mysql show session variables like collation%; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | utf8_bin | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql i thought, if you set the collation_connection to utf8_bin, you may send fairly everything to mysql. but still, when i tried to concat the new string (including the chinese characers) i got: mysql update suomi_contacts2 set history = concat(now(), ' ', '' ,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will ever happen to you.\r\n 葛斯克 愛德華 / 台北市八德路四段\r\n\r\n\r\n \r\nsuomi\r\n', ' ', '--- ', history) where counter = 1127; ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' Question: What did i miss? what do i have to change? Thanks in advance suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: collation problems
Hi Ananda table structure is: mysql show full columns from suomi_contacts2; +--+--+---+--+-+---+-+-+-+ | Field| Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--+--+---+--+-+---+-+-+-+ | name | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | firm | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | title| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone_std| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | fax | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | mail | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | standard_mail| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | comment | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | status | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | url | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | businesscategory | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | address | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | addon| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | givenname| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | history | longtext | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | favorit | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | last_update | timestamp| NULL | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references | | | task_link| int(11) | NULL | YES | | NULL | | select,insert,update,references | | | counter | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | +--+--+---+--+-+---+-+-+-+ 20 rows in set (0.00 sec) mysql suomi On 2010-08-31 14:52, Ananda Kumar wrote: can u please list out the table structure...as collation can also be set at column level regards anandkl On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com mailto:my...@ayni.com wrote: Hi listers mysql server here is mysql-server-5.1.48-2.fc13.x86_64 this morning i created a message with a literal string in chinese in it. the messages in the application i used are stored in a mysql database, when you submit them, like in a sent folder. With this chinese literal in it, i, however, got ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' when i sent the message. without the chinese literal, the message was stored in the mysql db correctly. i, then, changed the connection names to utf8 and collation to utf8_bin and then the
Re: collation problems
can u please give the output of desc suomi_contacts2; 2010/8/31 mysql my...@ayni.com Hi Ananda table structure is: mysql show full columns from suomi_contacts2; +--+--+---+--+-+---+-+-+-+ | Field| Type | Collation | Null | Key | Default | Extra | Privileges | Comment | +--+--+---+--+-+---+-+-+-+ | name | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | firm | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | title| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | phone_std| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | fax | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | mail | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | standard_mail| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | comment | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | status | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | url | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | businesscategory | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | address | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | addon| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | givenname| text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | history | longtext | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | favorit | text | latin1_swedish_ci | YES | | NULL | | select,insert,update,references | | | last_update | timestamp| NULL | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | select,insert,update,references | | | task_link| int(11) | NULL | YES | | NULL | | select,insert,update,references | | | counter | int(10) unsigned | NULL | NO | PRI | NULL | auto_increment | select,insert,update,references | | +--+--+---+--+-+---+-+-+-+ 20 rows in set (0.00 sec) mysql suomi On 2010-08-31 14:52, Ananda Kumar wrote: can u please list out the table structure...as collation can also be set at column level regards anandkl On Tue, Aug 31, 2010 at 6:00 PM, mysql my...@ayni.com mailto:my...@ayni.com wrote: Hi listers mysql server here is mysql-server-5.1.48-2.fc13.x86_64 this morning i created a message with a literal string in chinese in it. the messages in the application i used are stored in a mysql database, when you submit them, like in a sent folder. With this chinese literal in it, i, however, got ERROR 1271 (HY000): Illegal mix of collations for operation 'concat' when i sent the message. without
Re: collation problems
On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: collation problems
did u try changing the collation for history column to UTF8 and try the update. 2010/8/31 mysql my...@ayni.com On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: collation problems
Hi Ananda not sofar. But if you recommend it, i will give it a try. thanks so much. suomi On 2010-08-31 15:41, Ananda Kumar wrote: did u try changing the collation for history column to UTF8 and try the update. 2010/8/31 mysql my...@ayni.com mailto:my...@ayni.com On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[SOLVED] Re: collation problems
Hi Ananda that worked fine: mysql alter table suomi_contacts2 modify history longtext character set utf8 collate utf8_bin; Query OK, 6327 rows affected (0.34 sec) Records: 6327 Duplicates: 0 Warnings: 0 mysql mysql update suomi_contacts2 set history = concat(now(), ' ', '' ,'concerne: utf-8-bin collation for chinese charset',' ', 'Hoi Suomixer,\r\n\r\nIf you\'re careful enough, nothing bad or good will ever happen to you.\r\n 葛斯克 愛德華 / 台北市八德路四段\r\n\r\n\r\n\r \nsuomi\r\n044 280 22 44\r\n079 239 29 01\r\n', ' ', '--- ', history) where counter = 1127; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql Thank you very much suomi On 2010-08-31 15:41, Ananda Kumar wrote: did u try changing the collation for history column to UTF8 and try the update. 2010/8/31 mysql my...@ayni.com mailto:my...@ayni.com On 2010-08-31 15:17, Ananda Kumar wrote: desc suomi_contacts2; mysql desc suomi_contacts2; +--+--+--+-+---+-+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---+-+ | name | text | YES | | NULL | | | firm | text | YES | | NULL | | | title| text | YES | | NULL | | | phone| text | YES | | NULL | | | phone_std| text | YES | | NULL | | | fax | text | YES | | NULL | | | mail | text | YES | | NULL | | | standard_mail| text | YES | | NULL | | | comment | text | YES | | NULL | | | status | text | YES | | NULL | | | url | text | YES | | NULL | | | businesscategory | text | YES | | NULL | | | address | text | YES | | NULL | | | addon| text | YES | | NULL | | | givenname| text | YES | | NULL | | | history | longtext | YES | | NULL | | | favorit | text | YES | | NULL | | | last_update | timestamp| NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP | | task_link| int(11) | YES | | NULL | | | counter | int(10) unsigned | NO | PRI | NULL | auto_increment | +--+--+--+-+---+-+ 20 rows in set (0.00 sec) mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=anan...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
On Thu, Aug 26, 2010 at 6:04 AM, Norman Khine nor...@khine.net wrote: hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: As one other stated, it should not lose position when you stop and start the master or slave in a controlled manner. And also stated was that the way you were doing it was causing a loss of data, and you'll need to use maatkit to fix it, or if you can afford some downtime, stop mysql on both and copy the whole database(s) from master to slave (and reconfigure replication of course). On the slave, I'd be curious what the result was when you did only a STOP SLAVE; and then START SLAVE; because in my estimation, chances are it would have picked right up where it was supposed to. Your issue is likely that your slave has a large slave_net_timeout value (default is 3600 seconds IIRC). That means your slave will take 1 hour to finally give up on the replication connection (that the master closed on it), close the connection, and then reopen it. Lower that setting to something smaller, I've gone as low as 30 seconds, but I would only do that on a reliable network. Try lowering it to 300 (5 minutes) on your slave. When you restart the master, the slave should timeout after only 5 minutes and then resume replication. -- Regards... Todd I seek the truth...it is only persistence in self-delusion and ignorance that does harm. -- Marcus Aurealius -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
master-slave replication sync problems.
hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql show master status; +--+--+--+--+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+--+ 1 row in set (0.00 sec) [SLAVE] mysql stop slave; mysql change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql start slave; is this correct or is there a better way to do this? thanks -- ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ ǝq s,ʇǝן ʇǝʎ % .join( [ {'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
since u r starting slave by postion beyond master, and if some of the changes are already present on slave, it would error out with duplicate. You need show slave status\G; slave stop; show slave status\G;..wait for few min..than again show slave status\G;just to make sure...slave is in complete sync with master... Here u will see the master log file and position. U need to use this, next time u start ur slave to sync with master. regards anandkl On Thu, Aug 26, 2010 at 6:34 PM, Norman Khine nor...@khine.net wrote: hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql show master status; +--+--+--+--+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+--+ 1 row in set (0.00 sec) [SLAVE] mysql stop slave; mysql change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql start slave; is this correct or is there a better way to do this? thanks -- ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ ǝq s,ʇǝן ʇǝʎ % .join( [ {'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: master-slave replication sync problems.
That is really bad, you will loose changes. You shouldnt have to do anything when rebooting either the master or slave. If the master is down, then the slave recieves no updates. If the slave is down, when it comes back up it checks the master log pos and plays thro any changes that are necessary to bring it up to date... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
True, But some times, this does not work and u need to know the master bin-log file and position to start, so that there is no loss of data. regards anandkl On Thu, Aug 26, 2010 at 6:39 PM, a.sm...@ukgrid.net wrote: That is really bad, you will loose changes. You shouldnt have to do anything when rebooting either the master or slave. If the master is down, then the slave recieves no updates. If the slave is down, when it comes back up it checks the master log pos and plays thro any changes that are necessary to bring it up to date... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: master-slave replication sync problems.
i see, so the best is to just stop slave and then check the master status, and when the master status syncs then i start the slave? On Thu, Aug 26, 2010 at 3:09 PM, a.sm...@ukgrid.net wrote: That is really bad, you will loose changes. You shouldnt have to do anything when rebooting either the master or slave. If the master is down, then the slave recieves no updates. If the slave is down, when it comes back up it checks the master log pos and plays thro any changes that are necessary to bring it up to date... -- ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ ǝq s,ʇǝן ʇǝʎ % .join( [ {'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Yes, You need to note down the master bin-log file name and position on the slave, this is a must. regards anandkl On Thu, Aug 26, 2010 at 7:34 PM, Norman Khine nor...@khine.net wrote: i see, so the best is to just stop slave and then check the master status, and when the master status syncs then i start the slave? On Thu, Aug 26, 2010 at 3:09 PM, a.sm...@ukgrid.net wrote: That is really bad, you will loose changes. You shouldnt have to do anything when rebooting either the master or slave. If the master is down, then the slave recieves no updates. If the slave is down, when it comes back up it checks the master log pos and plays thro any changes that are necessary to bring it up to date... -- ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ ǝq s,ʇǝן ʇǝʎ % .join( [ {'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: master-slave replication sync problems.
Quoting Norman Khine nor...@khine.net: i see, so the best is to just stop slave and then check the master status, and when the master status syncs then i start the slave? Well Im willing to hear from others experiences, but if you really shouldnt have to do anything. If you want you can issue a stop slave before the reboot and a start slave after, but it shouldnt make any difference. And Im not sure what you refer to when you talk about the master status syncing. The master will continue to increment the binlog when changes are made regardless of whether the slave is up. If the slave has been rebooted and is behind the master binlog pos it should automatically sync those changes when started... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Smith, I never said, this wont work.Some times, there are chances of lossing data. regards anandkl On Thu, Aug 26, 2010 at 8:48 PM, a.sm...@ukgrid.net wrote: Quoting Norman Khine nor...@khine.net: i see, so the best is to just stop slave and then check the master status, and when the master status syncs then i start the slave? Well Im willing to hear from others experiences, but if you really shouldnt have to do anything. If you want you can issue a stop slave before the reboot and a start slave after, but it shouldnt make any difference. And Im not sure what you refer to when you talk about the master status syncing. The master will continue to increment the binlog when changes are made regardless of whether the slave is up. If the slave has been rebooted and is behind the master binlog pos it should automatically sync those changes when started... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
Re: master-slave replication sync problems.
Hi, The best way to use sync the master and slave is using mk-checksum tools. just google it and use the the tools for online sync of master and slave. Jeetendra Ranjan MySQL DBA --- On Thu, 26/8/10, Norman Khine nor...@khine.net wrote: From: Norman Khine nor...@khine.net Subject: master-slave replication sync problems. To: mysql@lists.mysql.com Date: Thursday, 26 August, 2010, 6:34 PM hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql show master status; +--+--+--+--+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+--+ 1 row in set (0.00 sec) [SLAVE] mysql stop slave; mysql change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql start slave; is this correct or is there a better way to do this? thanks -- ˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ ǝq s,ʇǝן ʇǝʎ % .join( [ {'*':'@','^':'.'}.get(c,None) or chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jitendra_ran...@yahoo.com
Re: master-slave replication sync problems.
Quoting Ananda Kumar anan...@gmail.com: Smith, I never said, this wont work.Some times, there are chances of lossing data. regards anandkl If you have experience of this fair enough. Theres no reason it should make any difference, as everything is based upon the binlog file and position... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Quoting jitendra ranjan jitendra_ran...@yahoo.com: Hi, The best way to use sync the master and slave is using mk-checksum tools. just google it and use the the tools for online sync of master and slave. Jeetendra Ranjan MySQL DBA Yes, data integrity isnt guaranteed with MySQL replication. So if you want to be sure good idea to use the maartkit tools... Tho this isnt necessarily answering the original question, which is to do with the master and slave not showing the same binlog file and log position. If replication is working at all, these will be in sync (assuming the changes on the master arent happening faster than replication can keep up). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: master-slave replication sync problems.
ssh to the slave mysql -uroot -pPASSWORD -P3306 -hlocalhost show slave status\G If the Slave IO is NOT Running, but SQL is, then simply try to restart the slave... *** 1. row *** Slave_IO_State: Master_Host: 10.10.10.45 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: No Slave_SQL_Running: Yes start slave; show slave status\G Otherwise if it's a replication issue such as this, then you must skip over the bad SQL queries one at a time till the slave syncs. *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.41 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: agis_core_2008 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1061 Last_Error: Error 'Duplicate key name 'id_operator'' on query. The SQL statement will give you an idea of where the master and slave went askew. If these are recent commands you did you can guess as to how much to increment the SKIP_COUNTER below, otherwise, you just have to do it one at a time until they sync again. mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Repeat the above statements over and over until you see two YES rows. *** 1. row *** Slave_IO_Running: Yes Slave_SQL_Running: Yes -Original Message- From: Norman Khine [mailto:nor...@khine.net] Sent: Thursday, August 26, 2010 6:05 AM To: mysql@lists.mysql.com Subject: master-slave replication sync problems. hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql show master status; +--+--+--+ --+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+ --+ 1 row in set (0.00 sec) [SLAVE] mysql stop slave; mysql change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql start slave; is this correct or is there a better way to do this? thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: master-slave replication sync problems.
Hello List, On 8/26/2010 3:00 PM, Daevid Vincent wrote: ssh to the slave mysql -uroot -pPASSWORD -P3306 -hlocalhost show slave status\G If the Slave IO is NOT Running, but SQL is, then simply try to restart the slave... *** 1. row *** Slave_IO_State: Master_Host: 10.10.10.45 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: No Slave_SQL_Running: Yes start slave; show slave status\G Otherwise if it's a replication issue such as this, then you must skip over the bad SQL queries one at a time till the slave syncs. *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 10.10.10.41 Master_User: slave Master_Port: 3306 ... Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: agis_core_2008 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1061 Last_Error: Error 'Duplicate key name 'id_operator'' on query. The SQL statement will give you an idea of where the master and slave went askew. If these are recent commands you did you can guess as to how much to increment the SKIP_COUNTER below, otherwise, you just have to do it one at a time until they sync again. mysql stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave status\G Repeat the above statements over and over until you see two YES rows. *** 1. row *** Slave_IO_Running: Yes Slave_SQL_Running: Yes -Original Message- From: Norman Khine [mailto:nor...@khine.net] Sent: Thursday, August 26, 2010 6:05 AM To: mysql@lists.mysql.com Subject: master-slave replication sync problems. hello, i have a working master-slave replication, the problem i find is that if i restart the MASTER there is a difference in the MASTER_LOG_FILE and MASTER_LOG_POS on the SLAVE. what is the correct way to keep the two slaves in sync even after i restart the server. the way i do it now is to: [MASTER] mysql show master status; +--+--+--+ --+ | mysql-bin.10 | 13405429 | upgrade,tracker,bugs | mysql,information_schema | +--+--+--+ --+ 1 row in set (0.00 sec) [SLAVE] mysql stop slave; mysql change master to MASTER_HOST='master.domain.com', MASTER_USER='replicator', MASTER_PASSWORD='xxx', MASTER_LOG_FILE='mysql-bin.10', MASTER_LOG_POS=13301215; mysql start slave; is this correct or is there a better way to do this? thanks To me, it appears that many of you are not fully versed in the theory of operations for how MySQL replication actually functions. Granted, there are two formats for replication (ROW and STATEMENT) but the general process remains the same. Here is a nutshell summary of the process. ** on the master ** m1) The MySQL master is instructed to change some data. m2) The data is changed and the results are committed to disk. If you rollback the changes before you commit them, then there is nothing to replicate. Only the InnoDB engine supports this type of rollback. m3) The change committed in step 2 is written to the binary log (repeat from step m1 until the Master is shutdown) ** on the slave - the IO thread ** (assuming that the slave is already configured with compatible data, a starting position, and the proper credentials to act as a slave) o1) The SLAVE IO thread requests information from the master's binary logs. This information is identified by a file name and a byte offset from the start of that file o2) The SLAVE IO thread copies all available information from the master's binary logs into a local copy of those logs known as the relay logs. (repeat from o1 until the SLAVE IO thread is stopped(by error or by command) or the slave is shutdown) ** on the slave - the SQL thread ** s1) Once an unapplied change has been completely buffered into the relay logs, the SLAVE SQL thread attempts to apply the change to the slave's data. s2) If LOG SLAVE UPDATES is enabled, copy the applied change (using the correct format) into the slave's binary log. (repeat from s1 until the SLAVE SQL thread is stopped (by error or by command) or the slave is shutdown) ** As you can tell by this very simplified process description, there is no attempt to rectify one dataset to the other. Replication operates under the principle that if you perform identical changes to identical sets of data, you will end up with identical end results. Various replication filters can omit certain changes
Problems posting to the list lists.mysql.com
Hello, I tried to response to a posting on the list named in the subject. But it is rejected with the following message: SMTP error from remote mail server after end of data: host lists.mysql.com [213.136.52.31]: 552 spam score exceeded threshold (#5.6.1) Neither this emailaddress is used for spam purposes nor I offered products. Please, how can I use the list so that I can response to posts? I read the list via gmane.org Best regards Frank Becker -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems posting to the list lists.mysql.com
At 10:51 AM 2/15/2010, Frank Becker wrote: Hello, I tried to response to a posting on the list named in the subject. But it is rejected with the following message: SMTP error from remote mail server after end of data: host lists.mysql.com [213.136.52.31]: 552 spam score exceeded threshold (#5.6.1) Neither this emailaddress is used for spam purposes nor I offered products. Please, how can I use the list so that I can response to posts? I read the list via gmane.org Best regards Frank Becker Frank, This has happened before. Please contact webmas...@mysql.com and they may be able to solve the problem. http://www.google.ca/#q=552+spam+score+exceeded+threshold+mysqlhl=ensa=2fp=8e6c6930b7d53e73 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems posting to the list lists.mysql.com
Is the MySQL mailing list admin subscribed to the list? I have also sent a message or more telling about this issue, with no response. -- Octavian mos mo...@fastmail.fm wrote in message news:6.0.0.22.2.20100215120015.02cd4...@mail.messagingengine.com... At 10:51 AM 2/15/2010, Frank Becker wrote: Hello, I tried to response to a posting on the list named in the subject. But it is rejected with the following message: SMTP error from remote mail server after end of data: host lists.mysql.com [213.136.52.31]: 552 spam score exceeded threshold (#5.6.1) Neither this emailaddress is used for spam purposes nor I offered products. Please, how can I use the list so that I can response to posts? I read the list via gmane.org Best regards Frank Becker Frank, This has happened before. Please contact webmas...@mysql.com and they may be able to solve the problem. http://www.google.ca/#q=552+spam+score+exceeded+threshold+mysqlhl=ensa=2fp=8e6c6930b7d53e73 Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problems with slave_skip_errors on replication
Hi friends, Last weekend I made an environment that use a MySQL Server version 4.1 that was defined to be the MASTER and other one version 5.1 defined as SLAVE. Because the application that was concept working over exception, often the SLAVE server got new error and replication stops. Well, I configured the my.cnf file of the SLAVE to slave_skip_errors as you may see specified after this massage, but, the replication continue stops, even after this configurations. mysql show variables like 'slave_skip_errors'; +---+---+ | Variable_name | Value | +---+---+ | slave_skip_errors | 1 | +---+---+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Queueing master event to the relay log Master_Host: 172.28.8.70 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bmg58-bin.000265 Read_Master_Log_Pos: 251871 Relay_Log_File: pid-file-relay-bin.07 Relay_Log_Pos: 961348 Relay_Master_Log_File: bmg58-bin.03 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x.', 'x.sp', null)' Skip_Counter: 0 Exec_Master_Log_Pos: 952913 Relay_Log_Space: 264590369 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x', 'x.sp', null)' 1 row in set (0.00 sec) Have other thing to do, or this problem is made by the mix of versions? Best regards! -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
Re: Problems with slave_skip_errors on replication
Hi Wagner, You have to start the server with the option as below for skipping the error. --slave-skip-errorr= 1062 or all 1062 - will skip the your error as the error number is 1062 and all will skip all the errors. You have to mention specific error numbers to skip the same. -- Thanks Suresh Kuna MySQL DBA On Mon, Jan 25, 2010 at 6:06 PM, Wagner Bianchi wagnerbianch...@gmail.comwrote: Hi friends, Last weekend I made an environment that use a MySQL Server version 4.1 that was defined to be the MASTER and other one version 5.1 defined as SLAVE. Because the application that was concept working over exception, often the SLAVE server got new error and replication stops. Well, I configured the my.cnf file of the SLAVE to slave_skip_errors as you may see specified after this massage, but, the replication continue stops, even after this configurations. mysql show variables like 'slave_skip_errors'; +---+---+ | Variable_name | Value | +---+---+ | slave_skip_errors | 1 | +---+---+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Queueing master event to the relay log Master_Host: 172.28.8.70 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: bmg58-bin.000265 Read_Master_Log_Pos: 251871 Relay_Log_File: pid-file-relay-bin.07 Relay_Log_Pos: 961348 Relay_Master_Log_File: bmg58-bin.03 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x.', 'x.sp', null)' Skip_Counter: 0 Exec_Master_Log_Pos: 952913 Relay_Log_Space: 264590369 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x', 'x.sp', null)' 1 row in set (0.00 sec) Have other thing to do, or this problem is made by the mix of versions? Best regards! -- Wagner Bianchi - Web System Developer and Database Administrator Phone: (31) 8654-9510 / 3272-0226 E-mail: wagnerbianch...@gmail.com Lattes: http://lattes.cnpq.br/2041067758113940 Twitter: http://twitter.com/wagnerbianchi Skype: infodbacet
Re: Problems with slave_skip_errors on replication
Ok, Suresh. . .I started MySQL with slave_skip_errors = all and It solve the problem! Thanks. WB 2010/1/25 Suresh Kuna sureshkumar...@gmail.com Hi Wagner, You have to start the server with the option as below for skipping the error. --slave-skip-errorr= 1062 or all 1062 - will skip the your error as the error number is 1062 and all will skip all the errors. You have to mention specific error numbers to skip the same. -- Thanks Suresh Kuna MySQL DBA On Mon, Jan 25, 2010 at 6:06 PM, Wagner Bianchi wagnerbianch...@gmail.com wrote: Hi friends, Last weekend I made an environment that use a MySQL Server version 4.1 that was defined to be the MASTER and other one version 5.1 defined as SLAVE. Because the application that was concept working over exception, often the SLAVE server got new error and replication stops. Well, I configured the my.cnf file of the SLAVE to slave_skip_errors as you may see specified after this massage, but, the replication continue stops, even after this configurations. mysql show variables like 'slave_skip_errors'; +---+---+ | Variable_name | Value | +---+---+ | slave_skip_errors | 1 | +---+---+ 1 row in set (0.00 sec) mysql show slave status\G *** 1. row *** Slave_IO_State: Queueing master event to the relay log Master_Host: 172.28.8.70 Master_User: slave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: X58-bin.000265 Read_Master_Log_Pos: 251871 Relay_Log_File: pid-file-relay-bin.07 Relay_Log_Pos: 961348 Relay_Master_Log_File: X58-bin.03 Slave_IO_Running: Yes Slave_SQL_Running: No Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 1062 Last_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x.', 'x.sp', null)' Skip_Counter: 0 Exec_Master_Log_Pos: 952913 Relay_Log_Space: 264590369 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: NULL Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 1062 Last_SQL_Error: Error 'Duplicate entry '731493' for key 'PRIMARY'' on query. Default database: 'database'. Query: 'INSERT INTO tb_usuario (ocu_codigo, ocu_tipo, usu_codigo, ocu_data, ocu_obs, login_responsavel, ocu_ip) VALUES( null, 67, 'C986CC89AC1C071835E341D18011D25Z', now(), 'x', 'x.sp', null)' 1 row in set (0.00 sec) Have other thing to do, or this problem is made by the mix of versions? Best regards!
Binary log problems
I tried to look at the binary logs by using phpMyAdmin, but got an error: SQL error: SHOW BINLOG EVENTS LIMIT 0, 30; MySQL said: #1220 - Error when executing command SHOW BINLOG EVENTS: Wrong offset or I/O error. From the error-file: 100111 16:15:40 [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 100, event_type: 2 100111 16:16:51 [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 100, event_type: 2 100111 16:17:08 [ERROR] Error in Log_event::read_log_event(): 'Found invalid event in binary log', data_len: 100, event_type: 2 What is causing this? How can I fix this? I'm using version mySQL 5.1.42. -- Jørn Dahl-Stamnes homepage: http://www.dahl-stamnes.net/dahls/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problems with auto_increment updating when (i think) it shouldn't
Hey folks. I'm getting some weird behaviour out of Auto_increment. If I enter a attempt to INSERT a row into a table with a UNIQUE index, where the insert would violate uniqueness of existing data, I'm seeing the auto_increment increase even though the insert fails. The server in question is 5.1.34 running as master. Slave is also 5.1.34. First noticed through a script operating over ODBC, but replicated by hand through the query browser. I couldn't see anything in the ref manual stating this as standard behaviour -- but I easily could have missed something there. Can someone point me in the right direction? Thank you! Martin Using Mysql 5.1.34 TEST CASE: CREATE TABLE `test`.`test_table` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_2` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; insert some values 1, 'test' 2, 'test2' 3, 'test3' SHOW TABLE STATUS Nametest_table Engine InnoDB Version 10 Row_format Compact Rows3 Avg_row_length 5461 Data_length 16384 Max_data_length 0 Index_length16384 Data_free 0 Auto_increment 4 Create_time 2009-08-07 09:33:04 Update_time Check_time Collation latin1_swedish_ci Checksum Create_options Comment --- INSERT INTO test.test_table (name) VALUES ('test') SHOW TABLE STATUS Nametest_table ... Auto_increment 5 --- INSERT IGNORE test.test_table (name) VALUES ('test') SHOW TABLE STATUS Nametest_table ... Auto_increment 6 -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems with auto_increment updating when (i think) it shouldn't
It will also update the auto_increment column when you ROLLBACK a failed insert: mysql USE test; Database changed mysql SELECT * FROM t1\G Empty set (0.00 sec) mysql DROP TABLE t1; Query OK, 0 rows affected (0.06 sec) mysql mysql CREATE TABLE t1( - id INT UNSIGNED NOT NULL AUTO_INCREMENT, - c1 VARCHAR(255), - PRIMARY KEY(id) - ) ENGINE=InnoDB; Query OK, 0 rows affected (0.13 sec) mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1(c1) VALUES('TEST1'); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO t1(c1) VALUES('TEST2'); Query OK, 1 row affected (0.00 sec) mysql ROLLBACK; Query OK, 0 rows affected (0.02 sec) mysql SHOW CREATE TABLE t1\G *** 1. row *** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c1` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB *AUTO_INCREMENT=3* DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql SELECT * FROM t1\G Empty set (0.00 sec) mysql INSERT INTO t1(c1) VALUES('TEST3'); Query OK, 1 row affected (0.03 sec) mysql SELECT * FROM t1\G *** 1. row *** id: 3 c1: TEST3 1 row in set (0.00 sec) mysql I believe this is how it has to work. In the event that I start a transaction, then another transaction starts, mine fails, the other completes and commit's, it has to get ID #3 and not ID #1. At the time the transaction was taking place, ID #1 and #2 were in use. Essentially, your SQL statement is a single transaction with AUTO_COMMIT set to '1'. On Fri, Aug 7, 2009 at 8:55 AM, Proemial proem...@gmail.com wrote: Hey folks. I'm getting some weird behaviour out of Auto_increment. If I enter a attempt to INSERT a row into a table with a UNIQUE index, where the insert would violate uniqueness of existing data, I'm seeing the auto_increment increase even though the insert fails. The server in question is 5.1.34 running as master. Slave is also 5.1.34. First noticed through a script operating over ODBC, but replicated by hand through the query browser. I couldn't see anything in the ref manual stating this as standard behaviour -- but I easily could have missed something there. Can someone point me in the right direction? Thank you! Martin Using Mysql 5.1.34 TEST CASE: CREATE TABLE `test`.`test_table` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_2` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; insert some values 1, 'test' 2, 'test2' 3, 'test3' SHOW TABLE STATUS Nametest_table Engine InnoDB Version 10 Row_format Compact Rows3 Avg_row_length 5461 Data_length 16384 Max_data_length 0 Index_length16384 Data_free 0 Auto_increment 4 Create_time 2009-08-07 09:33:04 Update_time Check_time Collation latin1_swedish_ci Checksum Create_options Comment --- INSERT INTO test.test_table (name) VALUES ('test') SHOW TABLE STATUS Nametest_table ... Auto_increment 5 --- INSERT IGNORE test.test_table (name) VALUES ('test') SHOW TABLE STATUS Nametest_table ... Auto_increment 6 -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
Re: Problems with auto_increment updating when (i think) it shouldn't
Hmm, that makes sense. I should have thought of that. Thanks! On Fri, Aug 7, 2009 at 12:32 PM, Johnny Withersjoh...@pixelated.net wrote: It will also update the auto_increment column when you ROLLBACK a failed insert: mysql USE test; Database changed mysql SELECT * FROM t1\G Empty set (0.00 sec) mysql DROP TABLE t1; Query OK, 0 rows affected (0.06 sec) mysql mysql CREATE TABLE t1( - id INT UNSIGNED NOT NULL AUTO_INCREMENT, - c1 VARCHAR(255), - PRIMARY KEY(id) - ) ENGINE=InnoDB; Query OK, 0 rows affected (0.13 sec) mysql START TRANSACTION; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1(c1) VALUES('TEST1'); Query OK, 1 row affected (0.00 sec) mysql INSERT INTO t1(c1) VALUES('TEST2'); Query OK, 1 row affected (0.00 sec) mysql ROLLBACK; Query OK, 0 rows affected (0.02 sec) mysql SHOW CREATE TABLE t1\G *** 1. row *** Table: t1 Create Table: CREATE TABLE `t1` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `c1` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql SELECT * FROM t1\G Empty set (0.00 sec) mysql INSERT INTO t1(c1) VALUES('TEST3'); Query OK, 1 row affected (0.03 sec) mysql SELECT * FROM t1\G *** 1. row *** id: 3 c1: TEST3 1 row in set (0.00 sec) mysql I believe this is how it has to work. In the event that I start a transaction, then another transaction starts, mine fails, the other completes and commit's, it has to get ID #3 and not ID #1. At the time the transaction was taking place, ID #1 and #2 were in use. Essentially, your SQL statement is a single transaction with AUTO_COMMIT set to '1'. On Fri, Aug 7, 2009 at 8:55 AM, Proemial proem...@gmail.com wrote: Hey folks. I'm getting some weird behaviour out of Auto_increment. If I enter a attempt to INSERT a row into a table with a UNIQUE index, where the insert would violate uniqueness of existing data, I'm seeing the auto_increment increase even though the insert fails. The server in question is 5.1.34 running as master. Slave is also 5.1.34. First noticed through a script operating over ODBC, but replicated by hand through the query browser. I couldn't see anything in the ref manual stating this as standard behaviour -- but I easily could have missed something there. Can someone point me in the right direction? Thank you! Martin Using Mysql 5.1.34 TEST CASE: CREATE TABLE `test`.`test_table` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `index_2` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1; insert some values 1, 'test' 2, 'test2' 3, 'test3' SHOW TABLE STATUS Name test_table Engine InnoDB Version 10 Row_format Compact Rows 3 Avg_row_length 5461 Data_length 16384 Max_data_length 0 Index_length 16384 Data_free 0 Auto_increment 4 Create_time 2009-08-07 09:33:04 Update_time Check_time Collation latin1_swedish_ci Checksum Create_options Comment --- INSERT INTO test.test_table (name) VALUES ('test') SHOW TABLE STATUS Name test_table ... Auto_increment 5 --- INSERT IGNORE test.test_table (name) VALUES ('test') SHOW TABLE STATUS Name test_table ... Auto_increment 6 -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Replication problems: slave fails to update
I'm trying to resolve a frustrating replication problem with my databases. The master contains a number of schema, only using Innodb tables. Updates happen regularly, usually using bulk inserts of the form INSERT ... ON DUPLICATE UPDATE. Data is mostly numbers. The missing queries contain no non-deterministic functions, no BLOB/TEXT fields, no triggers or procedures, and no variables. The queries are generated by various C programs, connecting through Connector/ODBC. typical table (explain results): 'nodeID', 'int(10) unsigned', 'NO', 'PRI', '', '' 'calculationID', 'int(10) unsigned', 'NO', 'PRI', '', '' 'columnID', 'tinyint(3) unsigned', 'NO', 'PRI', '1', '' 'value', 'double', 'NO', '', '', '' 'lastUpdate', 'timestamp', 'NO', '', 'CURRENT_TIMESTAMP', 'on update CURRENT_TIMESTAMP typical bulk insert: INSERT INTO risk.risk_node_tree (nodeID, calculationID, columnID, value, lastUpdate) VALUES (1,2,1,1000,null),... ON DUPLICATE KEY UPDATE value=VALUES(value) Replication is active, and I am able to test it by doing single point modifications through the query browser. However, the bulk inserts seem to vanish: The Master updates, but the slave does not. There are no errors in the log file. SHOW SLAVE STATUS states no problems. I have attempted changing binlog_format, and have received the same results on all three settings. The Master has no settings to ignore any particular schema. Using the query browser, and running the same exact query with the same user, results in the query properly replicating. Both instances are 5.1.34. This is causing me to have to resynchronize the databases every night, which is getting to be something of a chore. Does anyone have any idea what might be happening, or could suggest an avenue of investigation? Any help would be greatly appreciated. Martin -- --- This is a signature. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems After MySql 5.1.34 - THANKS
Thanks to everyone that offered all the valuable suggestions. SImply replaced the shared-community with shared-compat and all seesm fine. Will live with, or deal with, the missmatch in php libraries later, but for now everything seems to work. Thanks again to everyone that offered such detailed and helpful suggestions :) Gary Smith wrote: You are right. I misspoke regarding mysql - php - apache hell. It happens anytime an interface changes. From: Mark [ad...@asarian-host.net] Sent: Thursday, May 07, 2009 5:57 AM To: mysql@lists.mysql.com Subject: RE: Problems After MySql 5.1.34 Gary wrote: Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. Well, you don't actually have to recompile PHP entirely, of course: just its mysql.so extension. @TS: Other than that, you basically need to recompile *everything* (or its mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw; so you'd need to build DBD:mysql as well (same for Python, etc). Walter wrote: Any (major)upgrade of mysql client requires the dependent subsystem to upgrade also. Anything else would be careless since you do not know if the interface has changed. Actually, you *do* know: that's what the changelog is for. :) When C header changes are made, an upgrade is in order. If not, when upgrading between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you won't need to recompile all system-wide MySQL client extensions. I've done this many times, without issue: you just need to be absolutely sure no header changes were made (when in doubt, recompile). I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's working just fine, but I spent several hours recompiling MySL client stuff; without doing so, your apps will likely behave erratically, or just segfault altogether. This isn't a MySL hell exclusively, btw. You'll get the same issue upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the beast. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=li...@openenterprise.ca -- _ Johnny Stork Open Enterprise Solutions Empowering Business With Open Solutions http://www.openenterprise.ca Mountain Hosting Secure Hosting Solutions for Business http://www.mountainhosting.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems After MySql 5.1.34
there is nothing you can make. Any (major)upgrade of mysql client requires the dependent subsystem to upgrade also. Anything else would be careless since you do not know if the interface has changed. basicly you can install both version of libraries and hope for the best. i would do this only as last rescue if an update is not possible. it is in general not clever. re, wh Gary Smith schrieb: Johnny, Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. This is why you are receiving the error through PHP. Of course, I could be wrong, in which case I know people will probably jump me for it. If this is the case, please do as I would like to be wrong here as it would make my compiling life easier every time I update MySql on all of my boxes. Gary From: Johnny Stork [li...@openenterprise.ca] Sent: Wednesday, May 06, 2009 1:03 PM Cc: mysql@lists.mysql.com Subject: Re: Problems After MySql 5.1.34 Typo, moved from 5.0.67 to 5.1.34 Johnny Stork wrote: I recently upgraded an asterisk/trixbox server to mysql 5.1.34 from 5.64. Access to the db seesm fine from the shell, phpmyadmin or even the Trixbox/FreePBX tool, but trying to apply Trixbox changes, or running a pear update produces the errors below. I created a couple of sl but this did not seem to fix the problem. Below is the error and contents of /usr/lib r...@asterisk:~# pear update PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/mysql.so' - /usr/lib/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib/php/modules/mysql.so) in Unknown on line 0 PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/mysqli.so' - /usr/lib/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib/php/modules/mysqli.so) in Unknown on line 0 Segmentation fault r...@asterisk:~# ls -la /usr/lib/libmy* lrwxrwxrwx 1 root root 26 May 6 09:52 /usr/lib/libmysqlclient_r.so - libmysqlclient_r.so.16.0.0 lrwxrwxrwx 1 root root 28 May 6 11:26 /usr/lib/libmysqlclient_r.so.15 - /usr/lib/libmysqlclient_r.so lrwxrwxrwx 1 root root 28 May 6 11:18 /usr/lib/libmysqlclient_r.so.15.0.0 - /usr/lib/libmysqlclient_r.so lrwxrwxrwx 1 root root 26 May 6 09:52 /usr/lib/libmysqlclient_r.so.16 - libmysqlclient_r.so.16.0.0 -rwxr-xr-x 1 root root 2052884 Mar 31 22:48 /usr/lib/libmysqlclient_r.so.16.0.0 lrwxrwxrwx 1 root root 24 May 6 09:52 /usr/lib/libmysqlclient.so - libmysqlclient.so.16.0.0 lrwxrwxrwx 1 root root 26 May 6 11:14 /usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so lrwxrwxrwx 1 root root 24 May 6 09:52 /usr/lib/libmysqlclient.so.16 - libmysqlclient.so.16.0.0 -rwxr-xr-x 1 root root 2044464 Mar 31 22:48 /usr/lib/libmysqlclient.so.16.0.0 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Problems After MySql 5.1.34
Hi Johnny, all! Johnny Stork wrote: Typo, moved from 5.0.67 to 5.1.34 Ok, that helps - I checked the contents. Johnny Stork wrote: I recently upgraded an asterisk/trixbox server to mysql 5.1.34 from 5.64. Access to the db seesm fine from the shell, phpmyadmin or even the Trixbox/FreePBX tool, but trying to apply Trixbox changes, or running a pear update produces the errors below. I created a couple of sl but this did not seem to fix the problem. Below is the error and contents of /usr/lib Somewhere during this upgrade you seem to have lost the shared libraries of the MySQL client side which you had with version 5.0.67: /usr/lib/libmysqlclient_r.so.15.0.0 /usr/lib/libmysqlclient.so.15.0.0 MySQL 5.0 comes with version 15 of these libraries, MySQL 5.1 comes with version 16. How these are handled depends on how you install MySQL (assuming Linux): a) If you use tar.gz packages, then MySQL 5.0 has brought version 15 and created symlinks from the unversioned name to the versioned: /usr/lib/libmysqlclient.so - /usr/lib/libmysqlclient.so.15 /usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so.15.0.0 /usr/lib/libmysqlclient.so.15.0.0 would be a real file. (Similar for libmysqlclient_r.so) Then, upgrading to 5.1 would bring version 16: /usr/lib/libmysqlclient.so - /usr/lib/libmysqlclient.so.16 /usr/lib/libmysqlclient.so.16 - /usr/lib/libmysqlclient.so.16.0.0 Again, /usr/lib/libmysqlclient.so.16.0.0 would be a real file. Note that this installation changes the symlimk for the unversioned name, but would not touch version 15, there still is /usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so.15.0.0 /usr/lib/libmysqlclient.so.15.0.0 would still exist as a real file. b) If you use RPMs, then installing MySQL 5.0 has set up version 15 of the client libraries in the same way. However, the upgrade to MySQL 5.1 would have removed the files brought by 5.0 and just installed the new version 16 (specifically, the 5.1 shared RPM does this). If you use applications that were built against older versions of the MySQL client libraries (like Trixbox), you must not lose these older files. For this purpose, we provide shared-compat RPMs, they contain the current and all previous versions of the client libraries. See this contents listing: prompt rpm -qlvp MySQL-shared-compat-5.1.34-0.glibc23.i386.rpm | cut -c1-11,37- lrwxrwxrwx24 Apr 5 23:56 /usr/lib/libmysqlclient.so - libmysqlclient.so.16.0.0 lrwxrwxrwx24 Apr 5 23:56 /usr/lib/libmysqlclient.so.10 - libmysqlclient.so.10.0.0 -rwxr-xr-x224606 Apr 5 23:56 /usr/lib/libmysqlclient.so.10.0.0 lrwxrwxrwx24 Apr 5 23:56 /usr/lib/libmysqlclient.so.12 - libmysqlclient.so.12.0.0 -rwxr-xr-x221492 Apr 5 23:56 /usr/lib/libmysqlclient.so.12.0.0 lrwxrwxrwx24 Apr 5 23:56 /usr/lib/libmysqlclient.so.14 - libmysqlclient.so.14.0.0 -rwxr-xr-x 1267848 Apr 5 23:56 /usr/lib/libmysqlclient.so.14.0.0 lrwxrwxrwx24 Apr 5 23:56 /usr/lib/libmysqlclient.so.15 - libmysqlclient.so.15.0.0 -rwxr-xr-x 1942232 Apr 5 23:56 /usr/lib/libmysqlclient.so.15.0.0 lrwxrwxrwx24 Apr 5 23:56 /usr/lib/libmysqlclient.so.16 - libmysqlclient.so.16.0.0 -rwxr-xr-x 1989112 Apr 5 23:56 /usr/lib/libmysqlclient.so.16.0.0 lrwxrwxrwx26 Apr 5 23:56 /usr/lib/libmysqlclient_r.so - libmysqlclient_r.so.16.0.0 lrwxrwxrwx26 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.10 - libmysqlclient_r.so.10.0.0 -rwxr-xr-x230560 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.10.0.0 lrwxrwxrwx26 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.12 - libmysqlclient_r.so.12.0.0 -rwxr-xr-x225752 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.12.0.0 lrwxrwxrwx26 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.14 - libmysqlclient_r.so.14.0.0 -rwxr-xr-x 1274796 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.14.0.0 lrwxrwxrwx26 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.15 - libmysqlclient_r.so.15.0.0 -rwxr-xr-x 1950652 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.15.0.0 lrwxrwxrwx26 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.16 - libmysqlclient_r.so.16.0.0 -rwxr-xr-x 1999100 Apr 5 23:56 /usr/lib/libmysqlclient_r.so.16.0.0 lrwxrwxrwx21 Apr 5 23:56 /usr/lib/libndbclient.so - libndbclient.so.2.0.0 lrwxrwxrwx21 Apr 5 23:56 /usr/lib/libndbclient.so.2 - libndbclient.so.2.0.0 -rwxr-xr-x759988 Apr 5 23:56 /usr/lib/libndbclient.so.2.0.0 For comparison, this is the 5.1.34 shared RPM: rpm -qlvp MySQL-shared-5.1.34-0.glibc23.i386.rpm | cut -c1-11,37- lrwxrwxrwx24 Mar 31 22:47 /usr/lib/libmysqlclient.so - libmysqlclient.so.16.0.0 lrwxrwxrwx24 Mar 31 22:47 /usr/lib/libmysqlclient.so.16 - libmysqlclient.so.16.0.0 -rwxr-xr-x 1998728 Mar 31 22:48 /usr/lib/libmysqlclient.so.16.0.0 lrwxrwxrwx26 Mar 31 22:47 /usr/lib/libmysqlclient_r.so -
Re: Problems After MySql 5.1.34
Hi Gary, all, Gary Smith wrote: Johnny, Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. Which other approach(es) did you try? This is why you are receiving the error through PHP. Of course, I could be wrong, in which case I know people will probably jump me for it. If this is the case, please do as I would like to be wrong here as it would make my compiling life easier every time I update MySql on all of my boxes. Please see my other mail: - Using tar.gz, the old version of the client libs should not be touched when you upgrade MySQL. - Using RPMs, you need to install shared-compat (and not shared) if you are using application binaries built against older version(s). If you found any problems with this, please tell us! We are considering to change the contents of shared-compat RPMs, so that they would not replace shared any more but just complement it (bring just the old libs, not the current one). The advantage would be that you then can install (or uninstall) them without affecting your clients built against the current version, especially those coming from MySQL directly. However, this would mean a change in usage pattern for existing users, and we are not yet sufficiently sure that our users would welcome it. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Problems After MySql 5.1.34
Gary wrote: Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. Well, you don't actually have to recompile PHP entirely, of course: just its mysql.so extension. @TS: Other than that, you basically need to recompile *everything* (or its mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw; so you'd need to build DBD:mysql as well (same for Python, etc). Walter wrote: Any (major)upgrade of mysql client requires the dependent subsystem to upgrade also. Anything else would be careless since you do not know if the interface has changed. Actually, you *do* know: that's what the changelog is for. :) When C header changes are made, an upgrade is in order. If not, when upgrading between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you won't need to recompile all system-wide MySQL client extensions. I've done this many times, without issue: you just need to be absolutely sure no header changes were made (when in doubt, recompile). I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's working just fine, but I spent several hours recompiling MySL client stuff; without doing so, your apps will likely behave erratically, or just segfault altogether. This isn't a MySL hell exclusively, btw. You'll get the same issue upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the beast. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Problems After MySql 5.1.34
Depends on your OS, I guess. On FreeBSD you can just go to the 'php5-extensions' port, run a 'make config', and deselect everything but the MySQL extension. Then it will only build mysql.so for you. Very easy. :) - Mark From: Martin Gainty [mailto:mgai...@hotmail.com] Sent: donderdag 7 mei 2009 15:15 To: ad...@asarian-host.net; mysql@lists.mysql.com Subject: RE: Problems After MySql 5.1.34 Gary this isnt mysql's fault that php has such abysmal build tools..try requesting a web developer to simply download php and run the proprietary binary tools after a week of sleepless nights and endless searching for the right dll/so/lib and endless searching for the right tools i guarantee they will call no joy and implement the webapp in Java! Martin __ Disclaimer and Confidentiality/Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité This message is confidential. If you should not be the intended receiver, then we ask politely to report. Each unauthorized forwarding or manufacturing of a copy is inadmissible. This message serves only for the exchange of information and has no legal binding effect. Due to the easy manipulation of emails we cannot take responsibility over the the contents. Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. From: ad...@asarian-host.net Subject: RE: Problems After MySql 5.1.34 Date: Thu, 7 May 2009 12:57:42 + To: mysql@lists.mysql.com Gary wrote: Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. Well, you don't actually have to recompile PHP entirely, of course: just its mysql..so extension. @TS: Other than that, you basically need to recompile *everything* (or its mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw; so you'd need to build DBD:mysql as well (same for Python, etc). Walter wrote: Any (major)upgrade of mysql client requires the dependent subsystem to upgrade also. Anything else would be careless since you do not know if the interface has changed. Actually, you *do* know: that's what the changelog is for. :) When C header changes are made, an upgrade is in order. If not, when upgrading between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you won't need to recompile all system-wide MySQL client extensions. I've done this many times, without issue: you just need to be absolutely sure no header changes were made (when in doubt, recompile).. I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's working just fine, but I spent several hours recompiling MySL client stuff; without doing so, your apps will likely behave erratically, or just segfault altogether. This isn't a MySL hell exclusively, btw. You'll get the same issue upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the beast. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com _ Windows Live: Keep your life in sync. Check it out. http://windowslive.com/explore?ocid=TXT_TAGLM_BR_life_in_synch_052009
RE: Problems After MySql 5.1.34
Typically we see the problems with RH/Cent when you upgrade through those channels and then do an install of the 5.1.x series on top of that. I tried it about 9 months ago, so the details are fuzzy. I do know that in attempts to recover we extracted the RPM contents and tried to copy them manually to satisfy the dependency but in the end that didn't work for us for some reason. We ended up just creating a new RPM for 5.1.x and then recompiling the dependent apps against that and storing all of them in our own repo for yum update. From: joerg.bru...@sun.com [joerg.bru...@sun.com] Sent: Thursday, May 07, 2009 2:40 AM To: Gary Smith Cc: mysql@lists.mysql.com Subject: Re: Problems After MySql 5.1.34 Hi Gary, all, Gary Smith wrote: Johnny, Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. Which other approach(es) did you try? This is why you are receiving the error through PHP. Of course, I could be wrong, in which case I know people will probably jump me for it. If this is the case, please do as I would like to be wrong here as it would make my compiling life easier every time I update MySql on all of my boxes. Please see my other mail: - Using tar.gz, the old version of the client libs should not be touched when you upgrade MySQL. - Using RPMs, you need to install shared-compat (and not shared) if you are using application binaries built against older version(s). If you found any problems with this, please tell us! We are considering to change the contents of shared-compat RPMs, so that they would not replace shared any more but just complement it (bring just the old libs, not the current one). The advantage would be that you then can install (or uninstall) them without affecting your clients built against the current version, especially those coming from MySQL directly. However, this would mean a change in usage pattern for existing users, and we are not yet sufficiently sure that our users would welcome it. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Problems After MySql 5.1.34
You are right. I misspoke regarding mysql - php - apache hell. It happens anytime an interface changes. From: Mark [ad...@asarian-host.net] Sent: Thursday, May 07, 2009 5:57 AM To: mysql@lists.mysql.com Subject: RE: Problems After MySql 5.1.34 Gary wrote: Welcome to the hell that is php + apache + mysql. If you upgrade your MySql (especially major versions 5.0 = 5.1) you will also need to recompile php against the new MySql client libs. We've had very limited success trying to get it to work otherwise. Well, you don't actually have to recompile PHP entirely, of course: just its mysql.so extension. @TS: Other than that, you basically need to recompile *everything* (or its mysql dynamic libraries) when you upgrade MySQL. This includes Perl, btw; so you'd need to build DBD:mysql as well (same for Python, etc). Walter wrote: Any (major)upgrade of mysql client requires the dependent subsystem to upgrade also. Anything else would be careless since you do not know if the interface has changed. Actually, you *do* know: that's what the changelog is for. :) When C header changes are made, an upgrade is in order. If not, when upgrading between minor versions, say, from 5.0.51 - 5.0.67 (just an example), you won't need to recompile all system-wide MySQL client extensions. I've done this many times, without issue: you just need to be absolutely sure no header changes were made (when in doubt, recompile). I recently upgraded to 5.1.34 as well; and it was indeed a hell. :) It's working just fine, but I spent several hours recompiling MySL client stuff; without doing so, your apps will likely behave erratically, or just segfault altogether. This isn't a MySL hell exclusively, btw. You'll get the same issue upgrading PostgreSQL, or BerkeleyDB, etc. It's just the nature of the beast. - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=g...@primeexalia.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Problems After MySql 5.1.34
I recently upgraded an asterisk/trixbox server to mysql 5.1.34 from 5.64. Access to the db seesm fine from the shell, phpmyadmin or even the Trixbox/FreePBX tool, but trying to apply Trixbox changes, or running a pear update produces the errors below. I created a couple of sl but this did not seem to fix the problem. Below is the error and contents of /usr/lib r...@asterisk:~# pear update PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/mysql.so' - /usr/lib/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib/php/modules/mysql.so) in Unknown on line 0 PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/mysqli.so' - /usr/lib/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib/php/modules/mysqli.so) in Unknown on line 0 Segmentation fault r...@asterisk:~# ls -la /usr/lib/libmy* lrwxrwxrwx 1 root root 26 May 6 09:52 /usr/lib/libmysqlclient_r.so - libmysqlclient_r.so.16.0.0 lrwxrwxrwx 1 root root 28 May 6 11:26 /usr/lib/libmysqlclient_r.so.15 - /usr/lib/libmysqlclient_r.so lrwxrwxrwx 1 root root 28 May 6 11:18 /usr/lib/libmysqlclient_r.so.15.0.0 - /usr/lib/libmysqlclient_r.so lrwxrwxrwx 1 root root 26 May 6 09:52 /usr/lib/libmysqlclient_r.so.16 - libmysqlclient_r.so.16.0.0 -rwxr-xr-x 1 root root 2052884 Mar 31 22:48 /usr/lib/libmysqlclient_r.so.16.0.0 lrwxrwxrwx 1 root root 24 May 6 09:52 /usr/lib/libmysqlclient.so - libmysqlclient.so.16.0.0 lrwxrwxrwx 1 root root 26 May 6 11:14 /usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so lrwxrwxrwx 1 root root 24 May 6 09:52 /usr/lib/libmysqlclient.so.16 - libmysqlclient.so.16.0.0 -rwxr-xr-x 1 root root 2044464 Mar 31 22:48 /usr/lib/libmysqlclient.so.16.0.0 -- _ Johnny Stork Open Enterprise Solutions Empowering Business With Open Solutions http://www.openenterprise.ca Mountain Hosting Secure Hosting Solutions for Business http://www.mountainhosting.ca
Re: Problems After MySql 5.1.34
Typo, moved from 5.0.67 to 5.1.34 Johnny Stork wrote: I recently upgraded an asterisk/trixbox server to mysql 5.1.34 from 5.64. Access to the db seesm fine from the shell, phpmyadmin or even the Trixbox/FreePBX tool, but trying to apply Trixbox changes, or running a pear update produces the errors below. I created a couple of sl but this did not seem to fix the problem. Below is the error and contents of /usr/lib r...@asterisk:~# pear update PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/mysql.so' - /usr/lib/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib/php/modules/mysql.so) in Unknown on line 0 PHP Warning: PHP Startup: Unable to load dynamic library '/usr/lib/php/modules/mysqli.so' - /usr/lib/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib/php/modules/mysqli.so) in Unknown on line 0 Segmentation fault r...@asterisk:~# ls -la /usr/lib/libmy* lrwxrwxrwx 1 root root 26 May 6 09:52 /usr/lib/libmysqlclient_r.so - libmysqlclient_r.so.16.0.0 lrwxrwxrwx 1 root root 28 May 6 11:26 /usr/lib/libmysqlclient_r.so.15 - /usr/lib/libmysqlclient_r.so lrwxrwxrwx 1 root root 28 May 6 11:18 /usr/lib/libmysqlclient_r.so.15.0.0 - /usr/lib/libmysqlclient_r.so lrwxrwxrwx 1 root root 26 May 6 09:52 /usr/lib/libmysqlclient_r.so.16 - libmysqlclient_r.so.16.0.0 -rwxr-xr-x 1 root root 2052884 Mar 31 22:48 /usr/lib/libmysqlclient_r.so.16.0.0 lrwxrwxrwx 1 root root 24 May 6 09:52 /usr/lib/libmysqlclient.so - libmysqlclient.so.16.0.0 lrwxrwxrwx 1 root root 26 May 6 11:14 /usr/lib/libmysqlclient.so.15 - /usr/lib/libmysqlclient.so lrwxrwxrwx 1 root root 24 May 6 09:52 /usr/lib/libmysqlclient.so.16 - libmysqlclient.so.16.0.0 -rwxr-xr-x 1 root root 2044464 Mar 31 22:48 /usr/lib/libmysqlclient.so.16.0.0 -- _ Johnny Stork Open Enterprise Solutions Empowering Business With Open Solutions http://www.openenterprise.ca Mountain Hosting Secure Hosting Solutions for Business http://www.mountainhosting.ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org