> The table is currently 1.5GB and can't read from it in my code or a query in MySQL Workbench.
Is that problem resolved? (It is strange; there is no good reason for it that I can think of.)
> innodb_buffer_pool_size = 1000M, I could not acces the database from root.
With 4GB of RAM, that should not have been "too big". Did any other settings get increased. What value did you end up with? (Bigger is better -- until it is so big that it, plus everything else, won't fit in RAM.)
OK, max_connections=601 was probably chewing up too much space.
Those three things (RAM, buffer_pool_size, max_connections) are a juggling act.
> Imagine all 20000 devices
> alive for only 20 seconds
Do they need a full 20 seconds? How often do they connect? If only once an hour, that would be an average of much less than 500.
Where I am headed... Any of these would help:
* add more RAM
* spread out the connections
* decrease the connection time
> wait_timeout = 300
> long_query_time = 10
> slow_query_log_file = slow_query.log
Those look ok. Was there an error message in mysqld.err? Maybe this is somehow relevant:
http://bugs.mysql.com/bug.php?id=69703