5 ms·

A few questions Ankur: 1. How many rows were actually present in the tables (in all)? 2. What did the VSZ and RSS for mysqld and memsqld(?) look like at peak

A few questions Ankur: 1. How many rows were actually present in the tables (in all)? 2. What did the VSZ and RSS for mysqld and memsqld(?) look like at peak and after benchmarking was completed? 3. What did the CPU usage of each mysqld and memsqld(?) look like at peak and what was the total amount of processor time used by each database? 4. As a percentage, how many statements were inserts and how many were queries - am guessing I might be able to get an idea my looking at the code, but it's easier to ask - esp. since you'll know more about the code. 5. AUTOCOMMIT ON or OFF? (dunno what the defaults are).

ankrgyl 14y ago
Hey Dhruv, In the case of the 24 core machine... The database is initialised with 560000 players. For every action an event is written to the database, so at the end of the 10 minute run there are ~aps * 600 = 1.2 million rows for MySQL and 40 million rows for MemSQL. Then there's the active games: MySQL ends with a million active games, and MemSQL ends with the steady of 5.6 million games. The total number of rows at the end is around 2.8 million for MySQL and around 48 million for MemSQL. MySQL VSZ ends and peaks at 6 gB, RSS ends and peaks at 2GB. For MemSQL, the RSS and VSZ end and peak at 18 gB and 19 gB, respectively. MySQL keeps each of the 24 cores at 25% utilization. MemSQL runs with all cores at 100%. On the 8 core machine, MySQL keeps each core 80% utilized. The number of inserts and queries can be easier explained when looking at https://github.com/memsql/bench/blob/master/sql_database.py https://github.com/memsql/bench/blob/master/sql_database.py, starting at get_games and ending at log. Every action is composed of one call to get_games, one call to either start_game, make_move or win_game and one call to log. On average, each action has two selects and between two and three writes (with one update). Because client code is shared between MemSQL and MySQL, we could not use multistatement transactions, and every statement was executed in its own transaction. All of the code for the benchmark can be found at github.com/memsql/bench.
- continuations 14y ago
  > MySQL VSZ ends and peaks at 6 gB, RSS ends and peaks at 2GB. For MemSQL, the RSS and VSZ end and peak at 18 gB and 19 gB, respectively. So the RSS of MemSQL is almost 10x larger than that of MySQL's. Is that going to be generally the case - that MemSQL will consume about 10X more memory than MySQL for the same dataset? What causes this difference? Is it because skiplist requires more memory than B-tree?
  - ankrgyl 14y ago
    It has more to do with how much data is stored. MemSQL stores more than 30 times as many rows as MySQL by the end of the experiment.
- dhruvbird 14y ago
  Thanks Ankur for the details! I saw you mention in another comment that memsql stores almost 30x the (rows) data as mysql by the end of the experiment. I would be curious to know why this is so, and what that data is. Also, from what I understand after reading the paper, the lock-free skip-list node is pretty heavy (more than the 3~4 pointers that a naive implementation would use). What is the # of bytes it occupies in memory. Assume that the data field is just a pointer (8 bytes). Curious: Do you always store the data as a pointer or does it get stored inline for smaller types?
  - ankrgyl 14y ago
    @Dhruv - The significant increase in number of rows is because of throughput. The experiment was run for the same length for all three databases, but MemSQL sustained a higher throughput, resulting in more data inserted. Refer to the simulation code (https://github.com/memsql/bench/blob/master/benchmark.py https://github.com/memsql/bench/blob/master/benchmark.py) to see exactly what is going on. - We actually print information about row overhead to stderr/memsql_tracelog. The best way to find out is to run this experiment yourself :-) - Data gets inlined for smaller types.
    - dhruvbird 14y ago
      @Ankur - Thanks again for the clarification - that's helpful information.
- timaelliott 14y ago
  @ankrgyl > every statement was executed in its own transaction. I do not see any begin(), rollback() or commit()'s in your code. According to the mysql-python docs, http://mysql-python.sourceforge.net/FAQ.html#my-data-disappeared-or-won-t-go-away http://mysql-python.sourceforge.net/FAQ.html#my-data-disappe..., autocommit is disabled by default. Therefore each of your workers appears to be operating inside one very large transaction and never actually committing anything to the database? Can you clarify?
  - deleted 14y ago
    [deleted]
  - deleted 14y ago
    [deleted]
  - ankrgyl 14y ago
    Referring to https://github.com/memsql/bench/blob/master/sql_database.py https://github.com/memsql/bench/blob/master/sql_database.py , we use _mysql, not mysqldb. Here's some code that demonstrates: In [1]: import _mysql In [2]: c = _mysql.connect(host='127.0.0.1', user='root') In [3]: c.query("SELECT @@session.autocommit") In [4]: c.store_result().fetch_row(1) Out[4]: (('1',),)

ankrgyl 14y ago

Hey Dhruv, In the case of the 24 core machine... The database is initialised with 560000 players. For every action an event is written to the database, so at the end of the 10 minute run there are ~aps * 600 = 1.2 million rows for MySQL and 40 million rows for MemSQL. Then there's the active games: MySQL ends with a million active games, and MemSQL ends with the steady of 5.6 million games. The total number of rows at the end is around 2.8 million for MySQL and around 48 million for MemSQL. MySQL VSZ ends and peaks at 6 gB, RSS ends and peaks at 2GB. For MemSQL, the RSS and VSZ end and peak at 18 gB and 19 gB, respectively. MySQL keeps each of the 24 cores at 25% utilization. MemSQL runs with all cores at 100%. On the 8 core machine, MySQL keeps each core 80% utilized. The number of inserts and queries can be easier explained when looking at https://github.com/memsql/bench/blob/master/sql_database.py https://github.com/memsql/bench/blob/master/sql_database.py, starting at get_games and ending at log. Every action is composed of one call to get_games, one call to either start_game, make_move or win_game and one call to log. On average, each action has two selects and between two and three writes (with one update). Because client code is shared between MemSQL and MySQL, we could not use multistatement transactions, and every statement was executed in its own transaction. All of the code for the benchmark can be found at github.com/memsql/bench.

continuations 14y ago
> MySQL VSZ ends and peaks at 6 gB, RSS ends and peaks at 2GB. For MemSQL, the RSS and VSZ end and peak at 18 gB and 19 gB, respectively. So the RSS of MemSQL is almost 10x larger than that of MySQL's. Is that going to be generally the case - that MemSQL will consume about 10X more memory than MySQL for the same dataset? What causes this difference? Is it because skiplist requires more memory than B-tree?
- ankrgyl 14y ago
  It has more to do with how much data is stored. MemSQL stores more than 30 times as many rows as MySQL by the end of the experiment.
dhruvbird 14y ago
Thanks Ankur for the details! I saw you mention in another comment that memsql stores almost 30x the (rows) data as mysql by the end of the experiment. I would be curious to know why this is so, and what that data is. Also, from what I understand after reading the paper, the lock-free skip-list node is pretty heavy (more than the 3~4 pointers that a naive implementation would use). What is the # of bytes it occupies in memory. Assume that the data field is just a pointer (8 bytes). Curious: Do you always store the data as a pointer or does it get stored inline for smaller types?
- ankrgyl 14y ago
  @Dhruv - The significant increase in number of rows is because of throughput. The experiment was run for the same length for all three databases, but MemSQL sustained a higher throughput, resulting in more data inserted. Refer to the simulation code (https://github.com/memsql/bench/blob/master/benchmark.py https://github.com/memsql/bench/blob/master/benchmark.py) to see exactly what is going on. - We actually print information about row overhead to stderr/memsql_tracelog. The best way to find out is to run this experiment yourself :-) - Data gets inlined for smaller types.
  - dhruvbird 14y ago
    @Ankur - Thanks again for the clarification - that's helpful information.
timaelliott 14y ago
@ankrgyl > every statement was executed in its own transaction. I do not see any begin(), rollback() or commit()'s in your code. According to the mysql-python docs, http://mysql-python.sourceforge.net/FAQ.html#my-data-disappeared-or-won-t-go-away http://mysql-python.sourceforge.net/FAQ.html#my-data-disappe..., autocommit is disabled by default. Therefore each of your workers appears to be operating inside one very large transaction and never actually committing anything to the database? Can you clarify?
- deleted 14y ago
  [deleted]
- deleted 14y ago
  [deleted]
- ankrgyl 14y ago
  Referring to https://github.com/memsql/bench/blob/master/sql_database.py https://github.com/memsql/bench/blob/master/sql_database.py , we use _mysql, not mysqldb. Here's some code that demonstrates: In [1]: import _mysql In [2]: c = _mysql.connect(host='127.0.0.1', user='root') In [3]: c.query("SELECT @@session.autocommit") In [4]: c.store_result().fetch_row(1) Out[4]: (('1',),)