Re: mysql + xmpp server +test loading - did I reach any physical limit?

[gigi kent]

Hi,

wow! lots of stuff out of order, thank you!

Until I grasp and start changing things as you have suggested, here are some points / clarification requests:



- this server I am testing against is not a production server
- table tig_pairs, nid, uid, pkey and pval:
* uid is the same as uid from tig_users
* nid is the same as nid from tig_nodes. How this work:
* pkey: some tigase (XMPP server) plugin (let's name it MyPlugin) wants to write some data into the DB, It will write under such "path" as /[uid]/PublicData/MyPlugin/SomeKey. Here, PublicData and MyPlugin are tig_nodes wtih corresponding nid field each, whereas SomeKey corresponds to pkey field.
* pVal: the value to be inserted

Basically, tig_nodes together with tig_pairs tables will emulate random 'paths' for storing key/value pairs. So.. Is this enough to suggest a primary key ?

- Up for: 58m 20s >>Might be better to wait a few hours.
Why would I ? Again, a test session only takes about 1.5 hours or so, and the Exceptions start popping up after about 30 minutes.

- EAV ?... well, it looks like one, but without the metadata tables http://en.wikipedia.org/wiki/Entity-attribute-value_model#Structure_of_an_EAV_table describes. This is not going to change anyway as it would mean to rewrite the whole server.

- innodb_flush_log_at_trx_commit | 1 - switching to 2
At the expense of security ? Like, what will I be missing on ?

- [!!] Total fragmented tables: 5
Tried defragmenting them anyway but msyql spitted a note about how they are not optimizable.

- Don't worry. 155 opens over 14 hours
It was not 14 hours of active mysql usage, but only 1.5 hours. And I still don't get it why is it that I only get some <40% cache hit when the opened tables = 155 and the able_open_cache = 200.

- http://mysql.rjweb.org/doc.php/memory
Nice plain english explanations you have there!

- The original question was about INSERT performance?
Uhm, no, not mine :)

- Yes, slow logs are set to count everything. Here is that percona tool output. Too tired to go through it myself - http://pastebin.com/Dm5V75EE

Thanks,
kellogs