Too many table-locks
Hi everybody, I've got a little problem with a web and mysql based bulleting-board-application. The board is quite well visited and users are writing aprox. 1 new post per second. In total the db gets aprox. 250 queries/sec. The webserver and mysql-server are running on different hosts, the db server is running on Linux db 2.6.14.2 #4 SMP Thu Nov 17 09:54:44 CET 2005 x86_64 GNU/Linux model name : Intel(R) Pentium(R) D CPU 3.00GHz stepping: 4 cpu MHz : 3000.229 cache size : 1024 KB 4GB RAM. My problem is that I get a lot and very long locks (30 sec and more) on the table which is holding the user's posts. There are aprox. 3.3 Mio records in that table which consumes a size of 1.5 GB. The table-format is myisam. So far switching to innodb mostly soluted my problems with table locks. But because of the table size and the limited Memory I can't switch to inno-db in this case. Are there any other conceptional or technical ideas how to reduce the (long lasting) table locks ? Thanks for any idea in advance ! Best regards, Marco smime.p7s Description: S/MIME Cryptographic Signature
How to switch off auto-checks ?
My mysql-db starts a check tables xy fast for all tables as soon as I re-start the db-server. I didn't find a place where I can control if the db should be checked at start or not. So where can I switch of this checkings at db-server-start ? Thanks in advance Greetings Marco smime.p7s Description: S/MIME Cryptographic Signature
Who's locking ?
Hello list, I've a question about understanding table-locks of myisam-tables: From time to time it happens that my proccesslist gets flooded by Queries, that are waiting for a locked table. Quickly there are 100-500 waiting Queries in the queue - some of them waiting since more than 1000 seconds. My question is: How can I see who (which query) set the lock, which all other queries are waiting for to be released ? I thought there should be at least one update Query in the processlist, which set the lock and which is still working. But there are only locked queries for the locked table - no updates, no deletes. Could somebody try to explain, how I can investigate, who's blocking the line ? smime.p7s Description: S/MIME Cryptographic Signature
Re: Reset (or Defrag) the AUTO_INCREMENT columns
Hi wolverine, of course you could defrag your autoincrement-values, but there's no automation for that - you've do do that via normal insert/update statements. Perhaps you'll need an intermediate table. But: In most cases the autoincrement-value is used as an id (as in your case) - in db-language it is often the (primary) key - which normaly is never ever changed through the live-time of a data-record. If you change your primary key you'll have to change all references to that key in your detail-tables. Greetings, Marco wolverine my schrieb: Hi! I have the following tables and the data, CREATE TABLE category ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY name VARCHAR(50) NOT NULL ); CREATE TABLE user ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL, category TINYINT UNSIGNED REFERENCES category(id) ); SELECT * FROM category; +++ | id | name | +++ | 1 | Classic| | 2 | Gold | | 5 | Platinum | | 6 | Blacklist | +++ SELECT * FROM user; +++--+ | id | name | category | +++--+ | 2 | John | 1| | 3 | Mark | 2| | 5 | Kenneth| 5| | 6 | Sammy | 6| | 8 | Jane | 5| +++--+ Based on the above, the values of both ids are defragmented. The category.id 3 and 4 are deleted and the user.id 1, 4 and 7 are deleted. May I know if there is any way we can reset (or defrag?) the values so that they look like the following? SELECT * FROM category; +++ | id | name | +++ | 1 | Classic| | 2 | Gold | | 3 | Platinum | | 4 | Blacklist | +++ SELECT * FROM user; +++--+ | id | name | category | +++--+ | 1 | John | 1| | 2 | Mark | 2| | 3 | Kenneth| 3| | 4 | Sammy | 4| | 5 | Jane | 3| +++--+ smime.p7s Description: S/MIME Cryptographic Signature
Re: example when indexing hurts simple select?
Hi Gasper, MySql allows to package the index - to get its size smaller and to gain performance. Some information about that can be found here: http://www.mysqlperformanceblog.com/2006/05/13/to-pack-or-not-to-pack-myisam-key-compression/ Gaspar Bakos schrieb: Hi, RE: Have you tried analyze table x; This was quick: mysql analyze table TEST; Table Op Msg_typeMsg_text CAT.TEST analyze status Table is already up to date -- mysql show index from TEST; +---+++--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---+++--+-+---+-+--++--++-+ | TEST | 1 | MMtestfiel |1 | MMtestfiel | A | 838 | NULL | NULL | | BTREE | NULL| | TEST | 1 | MMi_m |1 | MMi_m | A | 25857 | NULL | NULL | YES | BTREE | NULL| +---+++--+-+---+-+--++--++-+ --- I am trying to figure out what the Packed field means. Gaspar smime.p7s Description: S/MIME Cryptographic Signature
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
Re: selecting records newer than say 20 min
select * from table where mytimestamp (unix_timestamp - 20) ?? Gregory Machin schrieb: Hi What, is the easest way to select all the records created in the last 20 min stay based on a column that has a timestamp record. Many Thanks -- Gregory Machin [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reset root password to mysql?
Jerry Swanson schrieb: How to reset mysql password to mysql? mysql -u root ERROR 1045 (0): Access denied for user: '[EMAIL PROTECTED]' (Using password: NO) You could start your mysqld with --*without-grant-tables* option. But keep in mind that this will stop the complete permission system of mysqld and the database will be accessable for everyone. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
