Re: mysql_pconnect() made things worse
Brian Dunning wrote: I have many sites accessing one MySQL server, all using the same IP, username, password via a PHP $connection = mysql_connect($ip,$user,$pass);. The server is extremely busy, hammered 24x7, but almost all traffic is coming from one very short script. About once a day we're getting No database selected with that one table busy and the only way out is mysqladmin flush-hosts. In that one busy script, I replaced mysql_connect() with mysql_pconnect() according to some advice I received, and the server immediately went back to No database selected and all hung up. Everything I read seemed to indicate that what I did should have made things better, not worse. Can someone suggest what my next steps should be? Check your max_connections setting which shows how many connections your server will allow at any time: SHOW VARIABLES LIKE '%connections%'; And checking status: SHOW STATUS LIKE '%connections%'; Will tell you how many connections you've used simultaneously. Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: unable to create function in MySQL 5.0
Ted Yu wrote: I got an error that I don't understand: Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 3080 to server version: 5.0.18-log mysql create function do_system returns integer soname 'raptor_udf.so'// ERROR 1146 (42S02): Table 'mysql.proc' doesn't exist It's a known MySQL bug - http://bugs.mysql.com/bug.php?id=24750 Use the schema mentioned there to recreate the mysql proc table. Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 5.0.27
Hi, What impact does the MySQL 5.0.27 ABI changes have on various components available from the MySQL Website: * OBDC 3.51 * php pdo-mysql * php ext/mysql Does the ABI breakage which this release fixes cause issues when connecting from 5.0.26 clients or only for clients which use functionality which was broken by the MySQL ABI? Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.1
Sid Lane wrote: any update on the 5.1 general release date? is it still on target for Q4 - Q1? any narrower window? Last I heard, Q2 next year. Regards --jm On 8/30/06, Colin Charles [EMAIL PROTECTED] wrote: Logan, David (SST - Adelaide) wrote: Hi! Does anybody have any idea when 5.1 may come to General Release? I am particularly interested in MySQL Cluster as I have several databases (around 50) totalling 26Gb and would like to consider moving to this version because of the Cluster Disk Data Storage and the fact the current hardware doesn't have the RAM requirements to hold these databases. I expect late (Q4/2006) to early (Q1/2007) as a good time for release. I plan to store the data on a SAN and was wondering if anybody had any experience with this as yet? This is why you're after 5.1, so you get disk as opposed to memory only based storage then -- Colin Charles, Community Engineer MySQL AB, Melbourne, Australia, www.mysql.com Office: [EMAIL PROTECTED] / +46 18 174 400 ext. 4528 Mobile: +614 12 593 292 / Skype: colincharles MySQL Forge: http://forge.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which to download
Ratheesh K J wrote: Hello all, Just wanted to know what is the difference between Red Hat Enterprise Linux 3 RPM (x86) downloads and Linux x86 generic RPM (dynamically linked) downloads Which one should I download for a RHEL 3 system? Hi Ratheesh, Use the RHEL3 one. Regards --jm Thanks, Ratheesh Bhat K J -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to speed up query of indexed column with 5M rows?
Dan Buettner wrote: bowen - Right now, it appears your performance hinges on I/O to the disk drive. The reason you are seeing fast performance when querying against the primary key(SELECT COUNT(*)) is it is only reading from the index, which is probably all in memory. When you do a SELECT * even when against an indexed field, it has to access the table data, not just the index, so it hits the disk drive. MyISAM tables always have that row count available hence the reason why SELECT COUNT(*) FROM table is always fast. Regards --jm There are a couple of things you can do to improve speed, probably a fair amount in this case: 1 - increase amount of RAM in machine. This will allow more data to be cached in memory, for faster access. When MySQL first starts up, performance may be slow, as it reads from disk to fill the caches, but then will be fast going forward. 2 - install some sort of striped disk storage system to allow faster access to data on disk. Not as fast as RAM but still a big boost, and performance will be more consistent. I'd recommend doing both if possible. Dan On 10/9/06, bowen [EMAIL PROTECTED] wrote: How to speed up query of indexed column with 5M rows? I have a table with more than 5M rows. (400M .MYD 430M .MYI). It took 27 seconds to do a common select...where... in the index column. I can not bear the long run. Vmstat show that system was bounded by IO busy.(Always more than 13000 bi/s, blocks input / second .) I increase some options to speed up query. I try SET GLOBAL key_buffer_size=256*1024*1024, the query still need 14 seconds. try read_buffer_size = 2M again, down to 9s. It is a great improvement, but the result still can not be acceptable, no matter to multiple query. Is there any method to speed up the query. I found the select by primary key of the 5M Row is very quickly(0.05s). I found if it is IO busy, process will hang much time for loading. If index is cached in memory, process will be much more faster. Bottleneck is IO performance. How to improve mysql io performance? Mysql 4.1.21 + linux 2.6.17 + 512M memory + IDE Disk udma5 --- CREATE TABLE /*!32312 IF NOT EXISTS*/ `flow` ( `id` bigint(20) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `owner` int(10) NOT NULL default '0', `uuid` varchar(36) NOT NULL default '', `length` int(11) default '0', `time` int(11) default '0', PRIMARY KEY (`id`), UNIQUE KEY `id` (`id`), UNIQUE KEY `flow_uuidindex` (`uuid`), KEY `flow_nameindex` (`name`), KEY `flow_fk_owner` (`owner`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; mysql explain select * from flow where owner=11251; ++-+---+--+---+--+-+--+-+-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ++-+---+--+---+--+-+--+-+-+ | 1 | SIMPLE | flow | ALL | NULL | NULL | NULL | NULL | 5122593 | Using where | ++-+---+--+---+--+-+--+-+-+ 1 row in set (0.00 sec) mysql explain select * from flow where owner=11251; Empty set (18.82 sec) If there is no method to improve, can you advise me a redesign of table? I think there is a way to do if I only use unique key in where. Thanks Owner index was 253285 unique owner, probably means user. On average, one user have 20 flows. mysql select count(*) from owner; +--+ | count(*) | +--+ | 253285 | +--+ 1 row in set (0.00 sec) Sorry for misspell of gid. It is uuid of the flow. I think I should redesign the table. But how to solve the huge one-multiple relationship. The flow table is the only largest table in the database, the others are relative small just like the owner table size. I have a design． Create an additinal field in owner table to store the pk(or uuid) of flows owned by this user. Than use a store procedure to update this field called by changing to the flow table. But I think it is a bad design, right ? And also mysql 4.1.x do not support store procedure. I do not want to migrate the whole database, and I am sure there are some incompatible. Thanks... -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can I set many IP address with bind-address ? If not, how can do same thing ?
KLEIN Stéphane wrote: Hi, Can I set many IP address with bind-address ? If not, how can do same thing ? In documentation, I read this : ''' -bind-address=IP The IP address to bind to. ''' They don't say if I can use comma to separate two or many IP. Thanks for your help, Stephane Hi Stephane, You can only specify one IP address to bind to. Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedure issues
Hi, I have a bit of a weird question. A couple of rows in a table I'm importing with data has WW_D_D_D style row content which is different from the usual WW_DD format data which many millions of rows contain. During the process of normalising the data is there any way to distinguish during a stored procedure which format is being used prior to deciding how to store the extra D_D_D differently to the DD one? DROP FUNCTION IF EXISTS addpackage; DELIMITER $$ CREATE FUNCTION `addpackage` (application VARCHAR(10)) RETURNS int(11) DETERMINISTIC BEGIN DECLARE number_id INT; DECLARE number_check VARCHAR(2); DECLARE duplicate_key CONDITION FOR SQLSTATE '23000'; DECLARE CONTINUE HANDLER FOR duplicate_key SET @duplicate = 1; SET @duplicate = 0; SELECT SUBSTRING(application from 8 for 2) INTO number_check; IF number_check = '' THEN RETURN '-1'; END IF; INSERT INTO packages (package_id, client_id, package_name) VALUES (SUBSTRING(application from 8 for 2), 0, ''); SELECT package_id INTO number_id FROM packages WHERE package_id=SUBSTRING(application from 8 for 2); RETURN number_id; END $$ DELIMITER ; Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.1
Logan, David (SST - Adelaide) wrote: Hi Folks, Does anybody have any idea when 5.1 may come to General Release? I am particularly interested in MySQL Cluster as I have several databases (around 50) totalling 26Gb and would like to consider moving to this version because of the Cluster Disk Data Storage and the fact the current hardware doesn't have the RAM requirements to hold these databases. Robin from MySQL has said Q1 2007. Kai and Max have both mentioned Q4 2006. I plan to store the data on a SAN and was wondering if anybody had any experience with this as yet? I have current thoughts along the lines of 2 x Pentium 4 1Ghz, 1Gb RAM as management nodes 4 x Pentium 4 1Ghz, 1Gb RAM as SQL nodes 2 x Pentium 4 1Ghz (x 8 cpus), 8Gb RAM as data nodes You will have two copies of the data on those two data nodes. Kai demonstrated MySQL Cluster on 5.1.11 running four data nodes on his laptop, which basically allows one to have two copies of data mirrored over two nodes (see quickly hacked picture). At least with having more data nodes one had two copies of data you reduce the single point of failure. Having more data nodes allows you to split up data across nodes, etc. Regards --jm The databases are currently running, replicating and serving around 800 queries per second utilising a 100M network. Thanks --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stability of MySQL 5.1.11
Hi, I'm wondering what the status of MySQL 5.1.11 is regarding the roadmap for 5.1 changing from beta to generally available? Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/ http://www.powertrip.co.za/blog/ #include std/disclaimer.h -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Runing MySQL on boot
Andrew Kuebler wrote: I'm running FreeBSD and MySQL version 4.1. How do I get FreeBSD to load MySQL on boot? I don't see a script file that came with the installation. Thank you. Andrew Hi Andrew, You need edit your /etc/rc.conf file and add the following line: mysql_enable=YES Which basically tells the script that MySQL should be started when the server boots up. Regards --jm -- Jacques Marneweck http://www.powertrip.co.za/blog/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Should I buy commercial license?
Kenji HIROHAMA wrote: (I've posted to forums, but also send to this ML.) Hi, I took a look at mysql.com, but I still have a question about commercial license. If we build and host a web-system with MySQL, which provide services to customers not for free. Customers pay for our services. In this case, we don't distribute our softwre/system directly to our customers. I heard from someone of MySQL AB, probaly CEO, that people should buy commercial license if MySQL Server is used for their business. (in other words, making profit with MySQL Server.) Should we buy commercial license in my case? Hi Hirohama, Due to the fact that MySQL is licensed under a dual licensing system, you do not need to purchase a license of MySQL for using the database software for commercial use. You have a choice in the case that you don't like the terms of the GPL license[1], you can choose to license the software under a commercial license[2] from MySQL AB. For example you write a web interface to access data stored on a MySQL database, you do not need a license for MySQL, in terms of the GPL and you can freely utilise any version of MySQL which has been licensed under the GPL even if they change the license for future versions of MySQL you can continue to use the last version which was released under the GPL. Regards --jm [1] http://www.mysql.com/company/legal/licensing/opensource-license.html [2] http://www.mysql.com/company/legal/licensing/commercial-license.html Thanks, hirohama -- Jacques Marneweck http://www.powertrip.co.za/blog/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Server - automatic shutdown
Hi Sheni, I would start by running MySQL 3.23.58 which is the latest released version of the MySQL 3.23 branch. Also running MySQL on FreeBSD with linuxthreads enabled. If you cvsup your ports collection doing something like: cd /usr/ports/databases/mysql323-server make WITH_LINUXTHREADS=yes BUILD_OPTIMIZED=yes If you use portupgrade remember to use portupgrade -v mysql-server -M WITH_LINUXTHREADS=yes BUILD_OPTIMIZED=yes I tend to find running MySQL with linuxthreads and built with some optimisations improves MySQL's speed on Regards --jm -Original Message- From: Sheni R. Meledath [mailto:[EMAIL PROTECTED] Sent: 26 May 2004 08:29 AM To: MySQL Masters Subject: MySQL Server - automatic shutdown Hello: We are using MySQL database on a FreeBSD server. The problem we are facing is sometimes the MySQL server is automatically shutting down. There is no error logged on the server. The entry in the log file mysite.com.err shows: 040525 21:52:37 /usr/local/mysql-3.23.49/libexec/mysqld: Normal shutdown This happened more than once in the last 2 months. Can anybody help me to fix this problem on the server. Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Counting values from two different tables:
Hi all, I'm basically trying to get a count of banner ad impresssions and banner ad click-thrus from phpAdsNew into a simple report on another site to figure out how to accurately get the stats to pass onto php and smarty to output onto a webpage for a user to see their stats on an application I'm working on: mysql SELECT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(DISTINCT phpads_adviews.t_stamp) AS adviews, COUNT(phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid=phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +--+--+- +--+ | md5_url | md5_status | adviews | adclicks | +--+--+- +--+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 615 | 616 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1147 | 1165 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 691 | 694 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 | 607 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1192 | 1212 | +--+--+- +--+ 5 rows in set (0.56 sec) mysql SELECT DISTINCT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(DISTINCT phpads_adviews.t_stamp) AS adviews, COUNT(phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid=phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +--+--+- +--+ | md5_url | md5_status | adviews | adclicks | +--+--+- +--+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 615 | 616 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1147 | 1165 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 691 | 694 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 | 607 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1192 | 1212 | +--+--+- +--+ 5 rows in set (0.53 sec) mysql SELECT DISTINCT MD5(phpads_banners.url) AS md5_url, MD5(phpads_banners.status) AS md5_status, COUNT(phpads_adviews.t_stamp) AS adviews, COUNT(phpads_adclicks.t_stamp) AS adclicks FROM phpads_banners LEFT JOIN phpads_clients ON phpads_banners.clientid=phpads_clients.clientid LEFT JOIN phpads_adviews ON phpads_adviews.bannerid=phpads_banners.bannerid LEFT JOIN phpads_adclicks ON phpads_adclicks.bannerid=phpads_banners.bannerid WHERE phpads_banners.clientid=phpads_clients.clientid AND phpads_clients.parent='1' AND (phpads_adviews.bannerid=phpads_banners.bannerid OR phpads_adclicks.bannerid =phpads_banners.bannerid) GROUP BY (phpads_banners.bannerid) ORDER BY phpads_banners.imageurl ASC LIMIT 0, 5; +--+--+- +--+ | md5_url | md5_status | adviews | adclicks | +--+--+- +--+ | 6cf98f14f2313c246d3b558ba43ba252 | 8d36cad0e1fc395e7f931054ba4526a1 | 616 |0 | | f94fba911bf6f75b654c48652b267a25 | 24e870981b14670bc5088553d2c2e923 | 1165 | 1165 | | a6d2869ac7ec767967adc794d1a65b6a | 6492c8df730871ea95c4467e474971c1 | 694 | 694 | | 8842f977ef2e9fa7cec00e1922a25f4f | 9d880b3da1e3403116afc2bafe828478 | 607 |0 | | da790b58ee2c0c04f3a4197017e7c246 | 3b9a5b211a931e991afdcd5aedebc8f6 | 1212 |0 | +--+--+- +--+ 5 rows in set (0.17 sec) This
