Server Intermittent Stalls

Classic Servoy
1

Hello Servoy Crew!

We have been slowly growing our customer base and getting more clients on our SaaS server. We have an Amazon EC2 Linux instance and we’re using that with Amazon’s RDS database service and have an instance running there.

Lately, we’ve been seeing a spike in the CPU Utilization on the DB instance and this then slows down all of our clients (both smart and web, but mostly smart)

We’ve done our best to make sure our database is indexed correctly and the db’s maintained and optimized routinely.

When this happens, we do see a lot of these errors:

java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 23 to TIMESTAMP.

We’ve scoured our databases and believe that we’ve rid any datetime field that has the '000-00-00…" as a value.

I have updloaded the most recent current stack / sys info for your entertainment.

Any help would be greatly appreciated.

-Chico

stack_trace_7_20_11.txt (395 KB)

2

you do see spikes on the database server? not the servoy server?

I guess that the moment of that stacktrace, the servoy server doesn’t really do anything, because all i can see is many connections to clients just waiting to do something…
Quickly scanning over the file i can’t even find one client that does a query…

3

I guess the Server Log was cleared before I got the dump.

I will grab a new dump when I see our next warning.

Thanks for your help. It’s appreciated and we’re working hard to get more and more users. :)

4

We got another CPU alert on our DB Server and it thus slowed our App Server.

Grabbed a dump. It’s attached. Does this tell you more?

stack_trace_7_21_11.txt (422 KB)

5

Also, for more reading fun, I have included the server log.

Any ideas? Your help is greatly appreciated.

-Chico

server_log_7_21_11.txt (1.86 MB)

6

i just see for the most part mysql errors
many of those timestamps, i guess you just have to look what that happens, its a pure mysql/data error, servoy can’t do much about that

Also i see some connections errors, do you use a validation query for your server connection? Please use one if you don’t

7

We do use the query validation and then a SELECT 1;

So could you think of another reason for connection errors?

Also, does anyone know how to prevent / fix the timestamp error for MySQL?

8

chico:
Also, does anyone know how to prevent / fix the timestamp error for MySQL?

Just allow null values in those date/timestamp columns. Also those columns tend to have default values set to ‘0000-00-00 00:00:00’, get rid of that too.

Hope this helps.

9

We are allowing null values but for some reason, we still keep getting the errors about the ‘0000-00-00…’ values even though we’ve gone through each table to make sure no timestamp columns have that value.

Of course, we could be missing something as the errors continue.

Is there any way to get WHAT table the errors are occurring in on the server? The error "java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 21 to TIMESTAMP. " is pretty useless.

10

Hi Chico,

And did you also change the default values on the table (not in Servoy but in MySQL)?

11

Actually no. I will do that and see if things get peachy!

12

Actually, yes, they are set to NULL as the default. So never mind that… I still have to figure out the deal here. :P

13

Does anyone know if a date like this in MySQL would cause the error?

‘2010-10-10 00:00:00’

Basically, is the time as a zero value cause the error to fire?

14

Hi Chico,

That is a completely valid timestamp. 00:00:00 is midnight.
It’s the 0000-00-00 that is invalid in the original error.

15

Thanks. So it’s not that. If only the error provided the table / column name! :P

16

We have solved our 0 date issue and that has been helping our server, but today we got more spikes on our MySQL server and customers complaining of slow downs.

I’ve provided a dump for review.

Also, how should this be set if you’re getting about 85-95 clients connecting at a given time? (also, this will steadily grow higher)

Maximum connections active:
Maximum connections idle:
Maximum prepared statements idle:

We are using query validation and Select 1

server_dump_8_2_11.txt (398 KB)

17

looking at the stack dump doesn’t tell much, i even don’t see any query being done at that time

What i do see is that somehow you use quite a lot of memory, its not really that close yet (+/-900MB used and a max of +/-1000MB)
Are there webclients? or are those all smart clients?

max connections i would set on 50, idle around 10, prepared idle can be 100

But it would be good to know what does use so much memory, but for that you need to create stackdumps (thats not that easy, you have to use jvisualvm that comes with the JDK6 installation and so on)

18

A majority of our clients are smart clients.

Our memory settings are:

System Information
Heap memory: allocated=1040512K, used=964087K, max=1040512K
None Heap memory: allocated=89632K, used=82025K, max=184320K

So you’re saying that we have it set too high? Or that too much is being used and you want to know why?

Also, I don’t understand Max db connections. You suggest 50 and we have 100 set. We have 75-85 clients at this time. I take it 1 client != 1 db connection?

Thanks so much.

19

The memory setting is not to high, it is almost to low (because used and max are close…)

The max connections don’t have to be the number of clients, You can do that (and you do need it if you use long transactions)
But for normal use a client holds on to a connection a very short time, so they are shared.

Having many connections and a big idle pool can result in large memory usage, i have seen for example oracle dumps that the driver of oracle did hold on to almost 1GB of memory…

20

thanks jcompagner!

So, right now, we have our db’s set at:

Maximum connections active: 100
Maximum connections idle: 5
Maximum prepared statements idle: 100

We are going to change to:

Maximum connections active: 50
Maximum connections idle: 10
Maximum prepared statements idle: 100

Also, we are going to up our memory to 100000 or do you think we should go higher?

We did optimize our tables last night… it had been about 3 weeks. So far, less warnings today… but it’s still early.

Again, thanks for your time. We greatly appreciate it and in the long run, it brings in more customers for both of us. :)