best use of index and missing values
Hi, I have one table : CREATE TABLE `ressources_summary` ( `res_type` tinyint(3) unsigned NOT NULL default '0', `res_id` int(10) unsigned NOT NULL default '0', `comment_count` smallint(5) unsigned NOT NULL default '0', `comment_last_timestamp` timestamp NOT NULL default '-00-00 00:00:00', `comment_last_user_id` int(11) NOT NULL default '0', PRIMARY KEY (`res_type`,`res_id`) ) ENGINE=MyISAM it gather summary on comments for any ressources (poll, articles, forums, admin). A ressource is uniquely identify by (res_type, res_id), the others columns are self-explanatory (I hope). I'm wondering about the efficiency of storing a comment_count of 0 (and user_id=0, timestamp=0). There is comments on 20% of the ressources. What is the cost of looking for a row that does not exists in a table ? Would it be more efficient to only store summary for comment_count greater than 0 and knowing missing rows have a count of zero, or to store everything ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bind-address question
Hi Everyone, I have been searching around for a while, but have had no luck as yet for this question. I am running debian with mysql 4.x on a x86 box. (2.6 series kernel). Anyhow, up until now, I have had a single bind-address option passed, however I need to add another address (so it binds to two, but not all available addresses) - how can I do this in the my.cnf file? Thanks, ANdrew. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble with aborted connections
Hello everybody, I'm using mysql in an clustered environment: Several loadbalanced webservers get the requests from the users' clients and query the needed information from several webservers. Every webserver connects to every database-server. So in this case the webservers are the mysql-clients. Webserver and mysql-server are in the same private network with their own switch between them. Ok, here's my Problem: Im getting a lot (see timestamps) of the following kind massages in my /var/log/mysql/error.log.err: 060414 12:01:45 [Warning] Aborted connection 2149 to db: 'board_5' user: ' boardu_5' host: `ws4' (Got timeout reading communication packets) 060414 12:02:44 [Warning] Aborted connection 3020 to db: 'board_5' user: ' boardu_5' host: `ws5' (Got timeout reading communication packets) 060414 12:03:18 [Warning] Aborted connection 3508 to db: 'board_5' user: ' boardu_5' host: `ws5' (Got timeout reading communication packets) 060414 12:03:19 [Warning] Aborted connection 3538 to db: 'board_5' user: ' boardu_5' host: `ws3' (Got timeout reading communication packets) 060414 12:04:01 [Warning] Aborted connection 4173 to db: 'board_5' user: ' boardu_5' host: `ws5' (Got timeout reading communication packets) 060414 12:04:33 [Warning] Aborted connection 4719 to db: 'board_5' user: ' boardu_5' host: `ws3' (Got timeout reading communication packets) As you can see the error occours from different clients. But the same problem exists on the other mysql-servers as well. The db-server has a load between 0.7 and 1.7 Versions: Linux: Linux db5 2.6.14.2 #4 SMP Thu Nov 17 09:54:44 CET 2005 x86_64 GNU/Linux Mysql: *4.1.11-Debian_4-log *Apache: Apache/2.0.55 (Unix) PHP/4.4.2 Mysql-System: Intel(R) Pentium(R) D CPU 3.00GHz with 4 Gig Memory On the client-side (webserver) I'm getting the follwing error-message: Lost connection to MySQL server during query mysql error number: 2013 for each of the above entries. Actually I've no idea where I could go on searching for the bottleneck or any existing problem. Why are are the connections timing out ? What parameters are relevant ? I'd be thankful for every idea and suggestion. Greetings Marco
Out of control connections
Hello all, I've got a problem where a php web application is intermitantly making bursts of 500+ connections to a mysql database which then locks out all other connections. These connection don't apparently do anything query wise they just use up connections. The problem is when it happens I can't get a processlist to see what user is causing it due to too many connection and therefore track down the offending web app. I can do a netstat but that only confirms which web server the out of controll connections are coming from. The connections then just die off on their own in about a minute. Does anyone know of any way I can log these connections or some other way of catching it in progress from myslq so I can figure out which website is causing it and then go correct the code? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Out of control connections
Hi Jeff. If you are the admin, then maybe you and only one or two others should have the 'Super' privilege. From the 5.0.18 manual: 13.5.4.16. SHOW PROCESSLIST Syntax SHOW [FULL] PROCESSLIST SHOW PROCESSLIST shows you which threads are running. You can also get this information using the mysqladmin processlist statement. If you have the SUPER privilege, you can see all threads. Otherwise, you can see only your own threads (that is, threads associated with the MySQL account that you are using). See Section 13.5.5.3, KILL Syntax. If you do not use the FULL keyword, only the first 100 characters of each statement are shown in the Info field. This statement is very useful if you get the too many connections error message and want to find out what is going on. MySQL reserves one extra connection to be used by accounts that have the SUPER privilege, to ensure that administrators should always be able to connect and check the system (assuming that you are not giving this privilege to all your users). The output of SHOW PROCESSLIST may look like this: /snip Also, what about checking your logs for the rogue connections. Would there be any clues there? What about this also from the 5.0.18 manual: 5.9.4. Limiting Account Resources One means of limiting use of MySQL server resources is to set the max_user_connections system variable to a non-zero value. However, this method is strictly global, and does not allow for management of individual accounts. In addition, it limits only the number of simultaneous connections made using a single account, and not what a client can do once connected. Both types of control are interest to many MySQL administrators, particularly those working for Internet Service Providers. In MySQL 5.0, you can limit the following server resources for individual accounts: The number of queries that an account can issue per hour The number of updates that an account can issue per hour The number of times an account can connect to the server per hour Regards Keith In theory, theory and practice are the same; in practice they are not. To unsubscribe from this list, please see detailed instructions already posted at: http://marc.theaimsgroup.com/?l=php-installm=114138567814319w=2 On Fri, 14 Apr 2006, Jeff wrote: To: mysql@lists.mysql.com From: Jeff [EMAIL PROTECTED] Subject: Out of control connections Hello all, I've got a problem where a php web application is intermitantly making bursts of 500+ connections to a mysql database which then locks out all other connections. These connection don't apparently do anything query wise they just use up connections. The problem is when it happens I can't get a processlist to see what user is causing it due to too many connection and therefore track down the offending web app. I can do a netstat but that only confirms which web server the out of controll connections are coming from. The connections then just die off on their own in about a minute. Does anyone know of any way I can log these connections or some other way of catching it in progress from myslq so I can figure out which website is causing it and then go correct the code? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 3 to 5 upgrade
At 8:32 -0700 13/4/06, paul rivers wrote: Going from 3 to 5 can break a number of important things. For example, join syntax semantics and precedence rules have changed since 3, and it is certainly possible this could break your code in important and dramatic ways. You should plan on spending time checking out all the SQL. Just as important, your MySQL host should really provide a 5.x playground for you to check your app out in for at least several weeks prior to the upgrade. Just waking up one morning with the database upgraded is almost surely going to be a mess. Yeah - I think the playground is unlikely to happen. They certainly haven't said anything about providing such a facility. What I'm banking on is that my own usage of MySQL so far (I'm learning, I'm learning!) is sufficiently primitive that it won't be upset too much by the change! -- Cheers... Chris Highway 57 Web Development -- http://highway57.co.uk/ I think I think; therefore I think I am. -- Ambrose Bierce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3 to 5 upgrade
Hi, Yeah - I think the playground is unlikely to happen. They certainly haven't said anything about providing such a facility. If they don't give a test machine, or don't provide a smooth migration (like installing a new server, and keeping the old one running 3.23), you should consider complaining (loudly !)... My provider upgraded from 3.23 to 4.1, and there was still few glitches (missing grants for temporary tables, lock,and still no InnoDB). What I'm banking on is that my own usage of MySQL so far (I'm learning, I'm learning!) is sufficiently primitive that it won't be upset too much by the change! Have a serious look at : http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-3-23.html http://dev.mysql.com/doc/refman/4.1/en/upgrading-from-4-0.html http://dev.mysql.com/doc/refman/5.0/en/upgrading-from-4-1.html There is many things likely to break, CONCAT, display of Timestamp, default values for timestamp, precedence for left join, and so on... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication, sort of (5.0.18)
2006/4/13, Duzenbury, Rich [EMAIL PROTECTED]: Hi all, I have two servers that have production data on them, and then one server where I would like to keep an active copy of the data. Currently, there is a job that runs in the middle of the night that basically does a mysqldump --host=production_server --all-databases | mysql --host=backup_server It takes a long time to copy over all the data, when in reality, it's not changing that much. The great thing about this approach is that yesterdays tables are very nearby and easily accessible in the case where someone fat-fingers some data. The trick to this is that I want the backup server to be one day behind the production server, not real time mirrored, so maybe replication isn't the right thing, I am not sure. I am using the innodb storage engine for most things, though there are a few myIsam tables. Setup the replication, then do a cron job with a start/stop slave ? http://dev.mysql.com/doc/refman/4.1/en/stop-slave.html The things is that if you modify data on the slave, they won't go back to the original state... the backup assure you a clean copy. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Optimizing SQL statement
2006/4/13, Puiu Hrenciuc [EMAIL PROTECTED]: Hi, I have a table ( MyISAM, MySQL 5.0.20, FreeBSD ) containing network traffic data : record_time datetime - time when recording was added ip char(15) - ip that generated the traffic type tinyint(3) - traffic type ( 1 - local, 2 - internet ) inbound int(10) - in bytes outbound int(10) - out bytes Records are inserted each 5 minutes through a cron script. Currently there are 3,330,367 rows. Primary index is defined on ( ip, type, record_time ), columns in that order. Also there is an index defined only on record_time You don't need a char(15) to store the IP. an unsigned integer should be enough. there is INET_ATON and INET_NTOA to do the convertion. http://dev.mysql.com/doc/refman/4.1/en/miscellaneous-functions.html You'll save 11 bytes ! index will be smaller, and it'll give you a better response time I think. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump ignores auto_increment
Figured out what was causing it The /etc/my.cnf had a line in it, in the [mysqldump] section... compatible=mysql40 Comment that line out and it works - we need that when sending data to customers who are still running mysql40. Now I know whats causing it, I can work around it. Many thanks, Ian Collins. On 4/14/06, Imran Chaudhry [EMAIL PROTECTED] wrote: The box that fails is running Redhat 7.3 (for customer backward compatibilty). I dont have any other 7.3 boxes to try - everything else is newer. Was there a MySQL installation that came with 7.3 by default? If this wasn't properly taken out of service, then you may be using an old mysqldump with a newer server (or vice versa) and this may cause the issue with autoincrement that you're describing. On the RH7.3 box can you issue these commands and tell me the results: mysqld -V mysqldump -V echo $PATH If these options are not taken, then can you confirm the mysqlserver version by starting mysql client and typing status; I'm just reaching here, by I suspect that even though you have upgraded to 4.1 over a default mysql install, your PATH still points to the old mysql progs. -- http://www.ImranChaudhry.info MySQL Database Management Design Services
Re: How could I know which transaction or thread hold the lock
Sheeri, Gu Lei, SHOW PROCESSLIST only knows about MySQL table locks. To list row locks, you need to use innodb_lock_monitor: http://dev.mysql.com/doc/refman/5.0/en/innodb-monitor.html Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php - Original Message - From: sheeri kritzer [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, April 07, 2006 10:23 PM Subject: Re: How could I know which transaction or thread hold the lock While the query is still running, type mysql show processlist or mysql show full processlist find the query(ies) with the State column having a value of Locked -Sheeri On 4/7/06, 古雷 [EMAIL PROTECTED] wrote: Hello: How could I know which transaction or thread hold the lock? For example: show innodb status\G ---TRANSACTION 0 78076313, ACTIVE 3906 sec, process no 12729, OS thread id 2952076208 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 1024 MySQL thread id 268, query id 40997 localhost root Updating UPDATE CORPSMSINFO SET PERMISSIONS='1000' WHERE CUSTOMERID='100010A' --- TRX HAS BEEN WAITING 13 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 20032 n bits 192 index `PRIMARY` of table `ea191/CORPSMSINFO` trx id 0 78076313 lock_mode X locks rec but not gap waiting Record lock, heap no 122 PHYSICAL RECORD: n_fields 19; compact format; info bits 0 0: len 11; hex 3130303030303030313041; asc 100010A;; 1: len 6; hex 04a755e1; asc U ;; 2: len 7; hex 00562927be; ascV)' ;; 3: len 3; hex 415350; asc ASP;; 4: len 4; hex 4435c9e1; asc D5 ;; 5: len 5; hex 61646d696e; asc admin;; 6: len 4; hex 4434cb9b; asc D4 ;; 7: len 4; hex 4434cb9b; asc D4 ;; 8: len 1; hex 30; asc 0;; 9: SQL NULL; 10: SQL NULL; 11: SQL NULL; 12: len 4; hex 4434cb9b; asc D4 ;; 13: len 4; hex 4434cb9b; asc D4 ;; 14: len 1; hex 30; asc 0;; 15: len 2; hex 3220; asc 2 ;; 16: SQL NULL; 17: len 30; hex 313030303030303030303030303030303030303030303030303030303030; asc 10;...(truncated); 18: len 4; hex 8000; asc ;; -- How could I find which one hold that lock? Regards, gu lei -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
