and then I loaded 300 projects / 6,815 repos into facade and tried building cache 🤣

[sgoggins]

128 gig of ram, solid state drives …. got all the repos and analysis_data .. its been 21 hours loading project_weekly_cache .. no cpu usage, so I am guessing I have the database eating disk …

I’ve already made a set of modification and database config notes on my fork at sgoggins/facade:augur branch … I’m thinking I an rewrite the query that loads cache to go after one repository or project group at a time .. since this is a nominal, 4 hour thing for me (a very experienced database guy / formerly well compensated Oracle DBA) I thought I would circle back and see if you would approve a pull request that modularized some of the functions in facade-worker.py into another python file. Or how you would recommend doing this.

The refactoring would change how cache is built and have options for execution. I think:

1. Cache would not be rebuilt at the project level when "recache" is tagged automatically
2. Cache at the project level would be rebuilt one project at a time
3. I will explore a process of accumulating project level details from repository level cache, which may require some changes to the repository cache.
4. Cache would be rebuilt at the repo level one repo at at time.
5. i would take a parameter that enabled wholesale cache building at the project and repo level as is the case today for smaller scale implementations
6. I will explore the potential to keep cache without destroying it on each recache

What do you think @brianwarner ?

[sgoggins]

I do think I can put the mysql temp directory on another device and squeeze more out of the database, btw ... but I think I have reached a scale where db optimizations alone are not going to be sufficient. As an old DBA, I recognize that when I can't blame the network, I can definitely blame the app 🤣

[brianwarner]

I continue to be astounded by how you're using this at scale, @sgoggins. Very, very impressive (and way more than I've ever tried!)

I would agree, it's probably the database that's the issue. If I had to point my finger to one suspect, it's the way that Facade neurotically stuffs data into the database at every opportunity. I had originally done it this way so that if it failed mid-run, not much would be lost.

That said, the sheer volume of database transactions creates a maaaaaaasive amount of overhead in big databases. I think there's a fairly simple fix that will be low hanging fruit, whereby the database transactions for a single repo are accumulated into a temporary in-memory database and then only pushed into the analysis_data table at the very end. In the case where facade-worker.py fails, you'll still lose the stuff that was in-memory, but the performance gains should make up for it.

The reason I say this is the lowest hanging fruit is that by reducing database traffic, it may become feasible to use the native Python MySQL connector (PyMySQL). In a drag race it's considerably slower than the mysqldb, but using pymysql gives us the ability to use PyPy. Everything in Facade works with PyPy already, and it'll even choose pymysql when run under PyPy.

[sgoggins]

@brianwarner : did a lot of optimization fo the MariaDB parameters (which are the same as MySQL, but you know that story I am sure.) I have them in a file in my fork right now, but perhaps they belong instead in a read me.

Speeds up sorting when building facade cache

tmp_table_size = 16106127360 max_heap_table_size = 16106127360 query_cache_limit = 4M query_cache_size = 512M join_buffer_size = 8M

Timeouts deal with long running connections. Mostly needed when first scanning a large number of repos

thread_pool_idle_timeout = 40000 connect_timeout = 280000 deadlock_timeout_short = 100000 deadlock_timeout_long = 50000000 delayed_insert_timeout = 120000 innodb_lock_wait_timeout = 200000 lock_wait_timeout = 150000 interactive_timeout = 86500 net_read_timeout = 30000 net_write_timeout = 50000
slave_net_timeout = 40000 wait_timeout = 1500000

Yes, this array of timeout parameters is a bit "shotgun"

128 Gigs of RAM on server.

innodb_buffer_pool_size = 107374182400

Helps with sorting

key_buffer = 256M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 16

Connections are not a facade issue

max_connections = 250

A little caching helps with some of the queries

table_cache = 16K

Nice to know

log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 20 expire_logs_days = 10 max_binlog_size = 100M