MySQL Java - Loss of connection is large InnoDB table select...

Author

Loss of connection is large InnoDB table select...

A.J. Blanchard \

2005-08-29, 3:23 am

=20
Greetings to all,

Could someone explain the error below. The DB had become so fouled that
we had to drop and recreate the entire DB. It was receiving burst of
inserts from client machines in the order of 100,000+ in a matter of
seconds and the delete rate was much slower on the server machine. It
had to process the "events" to see if they made any sense.

mysql> select * from EVENT_QUEUE where id=3D'1';=20
Empty set (44.83 sec)

=3D=3D=3D> this is good since we had long past and remove ID=3D1.

mysql> select * from EVENT_QUEUE;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Now I have learned that its *my bad* to use an InnoDB table for high
volume inserts and deletes. However, I also have requirements to survive
non-loss of data with a power outage. So MEMORY tables and easily
corrupted MyISAM tables were out (or so I thought).

ID is an auto-increment primary key. I also am using stock settings
since I only just found that great "DB Design and Tuning" book from
MySQL.=20

Any and all suggestions welcome.=20

A. J.
--

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=3...sie.nctu.edu.tw

tom-mathews@supanet.com

2005-08-29, 7:23 am

AJ
Looks like your indexing is f@*cked. Either that, or you need to start doing some more intelligent queue handling to increase the delete speed (try more than one daemon, avoiding conflicts of course). If it takes 40 secs to search on a single PK, then eit
her the index needs to be rebuilt or else you really do have too much data in that table for an OLTP app.
If you really need that much data to be currently active, then I would reccommend moving to memory cluster, and backup to hard files real-time.
Tom
--
Message sent with Supanet E-mail

-----Original Message-----
From: "A.J. Blanchard \(ajblanch\)" <ajblanch@cisco.com>
To: <java@lists.mysql.com>
Subject: Loss of connection is large InnoDB table select...

>
> Greetings to all,
>
> Could someone explain the error below. The DB had become so fouled that
> we had to drop and recreate the entire DB. It was receiving burst of
> inserts from client machines in the order of 100,000+ in a matter of
> seconds and the delete rate was much slower on the server machine. It
> had to process the "events" to see if they made any sense.
>
> mysql> select * from EVENT_QUEUE where id='1';=20
> Empty set (44.83 sec)
>
> ===> this is good since we had long past and remove ID=1.
>
> mysql> select * from EVENT_QUEUE;
> ERROR 2013 (HY000): Lost connection to MySQL server during query
>
> Now I have learned that its *my bad* to use an InnoDB table for high
> volume inserts and deletes. However, I also have requirements to survive
> non-loss of data with a power outage. So MEMORY tables and easily
> corrupted MyISAM tables were out (or so I thought).
>
> ID is an auto-increment primary key. I also am using stock settings
> since I only just found that great "DB Design and Tuning" book from
> MySQL.
>
> Any and all suggestions welcome.
>
> A. J.
> --
>
> --
> MySQL Java Mailing List
> For list archives: http://lists.mysql.com/java
> To unsubscribe: http://lists.mysql.com/java?unsub=t...ews@supanet.com
>

Signup to supanet at https://signup.supanet.com/cgi-bin/...igin=sigwebmail

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=m...sie.nctu.edu.tw

Sponsored Links