84 reputation
27
bio website
location
age
visits member for 2 years
seen Apr 12 at 21:22

Jun
13
awarded  Notable Question
Sep
8
awarded  Notable Question
Apr
13
awarded  Popular Question
Jan
21
awarded  Popular Question
Jul
24
comment How to use insert delay with the InnoDB engine and use less connection for insert statements?
I agree to this approach- I'm batching ~1500 inserts every 5 seconds on one app and it is sub second. mysql seems to have some mechanism implemented internally that makes batch inserts happen really really quickly.
Jul
21
accepted InnoDB : Deadlock from one reader and one writer
Jul
20
comment InnoDB : Deadlock from one reader and one writer
Ok. I assume I need to set tx_isolation for the session doing the read (ie 'select .. into'). This is in a stored procedure. so in this stored procedure, I could issue the stmt 'Set Session TRANSACTION ISOLATION LEVEL READ COMMITTED;'. Or, do I have to issue that statement from the client before invoking the stored procedure? environment is C program using library -lmysqlclient. perhaps when I establish the connection to the database, this can be specified as a session option (will look into this).
Jul
20
comment InnoDB : Deadlock from one reader and one writer
ok I think I get what you are saying now. I could try this globally : [mysqld] binlog_format=row tx_isolation=READ-COMMITTED
Jul
20
comment InnoDB : Deadlock from one reader and one writer
One more sort of question. What impact would it have if I got rid of INSERT INTO and just ran a straight select on nasd_real and did the summation that is trying to be done (and one reason for INSERT INTO) in the client? perhaps INSERT INTO should simply be avoided in cases when the table being selected from is under a heavy UPDATE load.
Jul
20
comment InnoDB : Deadlock from one reader and one writer
How does "replication" factor into this? I am not doing any replication right now. Are you saying : replicate the table nasd_real, and do a select on that? I am simply trying here to derive an approx portfolio value during the trading day. therefore, I just need really a snapshot of what the values are in nasd_real. I thought Repeatable-Read this is exactly what it was for. I will do more DD, but it seems to be READ-COMMITTED is more strict. but perhaps what you are saying is, the locking will be such that Deadlock wont result. Excuse the long note! any insight appreciated.
Jul
20
asked InnoDB : Deadlock from one reader and one writer
Jul
20
accepted whether a big (but not used) ibdata1 slow anything down
Jul
20
comment whether a big (but not used) ibdata1 slow anything down
Thanks for all the information much appreciated!
Jul
19
asked whether a big (but not used) ibdata1 slow anything down
Jul
5
accepted Capabilities of InnoDB INSERT Performance
Jul
5
comment Capabilities of InnoDB INSERT Performance
So you would set the pool_instances to each NUMA node, in this case, not each 'physical CPU'. ok. also read the Jeremy Cole blog piece referenced - which was fascinating but didn't see direct tie in to innodb_buffer_pool_instances, rather it seems piece addressed swappiness issue (brilliantly solved). Was it something someone said in the threads? Thx for all the help! --Don
Jul
5
awarded  Commentator
Jul
5
comment Capabilities of InnoDB INSERT Performance
NUMA output and physical CPU modules/dies have been detached from each other for a couple of generations of processor. His interpretation would be right in the first gen of multicore or anything with a hyperthreaded bus, but not so much with this stuff. Yes, it has 128GB.
Jul
5
comment Capabilities of InnoDB INSERT Performance
It's actually 2 CPU modules, with eight total cores each. The scheduler treats them as 16 individual CPUs. In this generation of AMD processor, each module has two dies with four cores on each die. They go to some trouble to make each core as close to a full-fledged CPU with its own bus and cache, there is essentially no difference between 4x4, 2x8, or 16x1, except for cooling. On this sort of system, I have always tuned to the number of cores, not modules or dies.
Jul
5
comment Capabilities of InnoDB INSERT Performance
Here is the response that I got back from my admin :